001/** 002 * 003 * Copyright (c) 2014, the Railo Company Ltd. All rights reserved. 004 * 005 * This library is free software; you can redistribute it and/or 006 * modify it under the terms of the GNU Lesser General Public 007 * License as published by the Free Software Foundation; either 008 * version 2.1 of the License, or (at your option) any later version. 009 * 010 * This library is distributed in the hope that it will be useful, 011 * but WITHOUT ANY WARRANTY; without even the implied warranty of 012 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU 013 * Lesser General Public License for more details. 014 * 015 * You should have received a copy of the GNU Lesser General Public 016 * License along with this library. If not, see <http://www.gnu.org/licenses/>. 017 * 018 **/ 019package lucee.runtime.poi; 020import java.util.Date; 021import java.util.Iterator; 022 023import lucee.commons.io.res.Resource; 024import lucee.commons.lang.StringUtil; 025import lucee.runtime.config.NullSupportHelper; 026import lucee.runtime.exp.CasterException; 027import lucee.runtime.exp.PageException; 028import lucee.runtime.op.Caster; 029import lucee.runtime.op.Decision; 030import lucee.runtime.type.Collection; 031import lucee.runtime.type.KeyImpl; 032import lucee.runtime.type.Struct; 033import lucee.runtime.type.StructImpl; 034import lucee.runtime.type.dt.DateTimeImpl; 035import lucee.runtime.type.it.EntryIterator; 036import lucee.runtime.type.it.KeyIterator; 037import lucee.runtime.type.it.StringIterator; 038import lucee.runtime.type.it.ValueIterator; 039import lucee.runtime.type.util.StructSupport; 040 041import org.apache.poi.POIXMLProperties; 042import org.apache.poi.hpsf.DocumentSummaryInformation; 043import org.apache.poi.hpsf.SummaryInformation; 044import org.apache.poi.hssf.usermodel.HSSFWorkbook; 045import org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart; 046import org.apache.poi.ss.usermodel.Cell; 047import org.apache.poi.ss.usermodel.CellStyle; 048import org.apache.poi.ss.usermodel.CreationHelper; 049import org.apache.poi.ss.usermodel.Drawing; 050import org.apache.poi.ss.usermodel.Row; 051import org.apache.poi.ss.usermodel.Sheet; 052import org.apache.poi.ss.usermodel.Workbook; 053import org.apache.poi.xssf.streaming.SXSSFWorkbook; 054import org.apache.poi.xssf.usermodel.XSSFWorkbook; 055import org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.CTProperties; 056 057public class Excel extends StructSupport implements Cloneable,Struct { 058 059 public static final short FORMAT_UNDEFINED=0; 060 public static final short FORMAT_XSSF=1; 061 public static final short FORMAT_HSSF=2; 062 public static final short FORMAT_SXSSF=4; 063 private Workbook workbook; 064 private Drawing drawing; 065 private short xmlFormat; 066 067 private static final Collection.Key SHEET_NAME=KeyImpl.init("SHEETNAME"); 068 private static final Collection.Key SHEET_NUMBER=KeyImpl.init("SHEETNUMBER"); 069 private static final Collection.Key ROW_COUNT=KeyImpl.init("ROWCOUNT"); 070 private static final Collection.Key SUMMARY_INFO=KeyImpl.init("SUMMARYINFO"); 071 private static final Collection.Key[] keys=new Collection.Key[]{ 072 SHEET_NAME,SHEET_NUMBER,ROW_COUNT,SUMMARY_INFO 073 }; 074 private static final String[] skeys=new String[]{ 075 SHEET_NAME.getString(),SHEET_NUMBER.getString(),ROW_COUNT.getString(),SUMMARY_INFO.getString() 076 }; 077 078 public static final String XSSF_FORMAT = "XSSF"; 079 public static final String HSSF_FORMAT = "HSSF"; 080 public static final String SXSSF_FORMAT = "SXSSF"; 081 082 083 public Excel(String sheetName, short xmlFormat, int rows){ 084 if(FORMAT_XSSF==xmlFormat) workbook=new XSSFWorkbook(); 085 else if(FORMAT_HSSF==xmlFormat) workbook=new HSSFWorkbook(); 086 else if(FORMAT_SXSSF==xmlFormat) workbook=new SXSSFWorkbook(); 087 this.xmlFormat=xmlFormat; 088 Sheet sheet = workbook.createSheet(); 089 drawing = sheet.createDrawingPatriarch(); 090 workbook.setSheetName(0, sheetName); 091 } 092 093 094 private Workbook getWorkbook() { 095 return workbook; 096 } 097 098 099 public void write(Resource res, String password) { 100 // TODO Auto-generated method stub 101 102 } 103 104 105 public void setValue(int rowNumber, int columnNumber, String value) throws CasterException { 106 if(value==null) value=""; 107 Sheet sheet = workbook.getSheet(getExcelSheetName()); 108 109 // get Row 110 Row row = sheet.getRow(rowNumber); 111 if(row==null) row = sheet.createRow(rowNumber); 112 113 // get Cell 114 Cell cell = row.getCell(columnNumber); 115 CellStyle style = null; 116 if(cell != null) { 117 style = cell.getCellStyle(); 118 row.removeCell(cell); 119 } 120 cell = row.createCell(columnNumber); 121 if(style != null) cell.setCellStyle(style); 122 123 CreationHelper createHelper = workbook.getCreationHelper(); 124 boolean isFormula=style != null && style.getDataFormatString().equals("@"); 125 126 127 if(!isFormula && Decision.isNumeric(value)) { 128 cell.setCellType(Cell.CELL_TYPE_NUMERIC); 129 double dbl = Caster.toDoubleValue(value); 130 cell.setCellValue(dbl); 131 _expandColumnWidth(sheet,Caster.toString(dbl),columnNumber); 132 } 133 else if(StringUtil.isEmpty("")) { 134 cell.setCellType(Cell.CELL_TYPE_BLANK); 135 cell.setCellValue(createHelper.createRichTextString("")); 136 } 137 else { 138 cell.setCellType(Cell.CELL_TYPE_STRING); 139 cell.setCellValue(createHelper.createRichTextString(value)); 140 _expandColumnWidth(sheet,value,columnNumber); 141 } 142 143 144 } 145 146 147 private static void _expandColumnWidth(Sheet sheet, String value, int columnNumber) { 148 int colwidth = sheet.getColumnWidth(columnNumber ); 149 int len = (int)((value.length() * 8) / 0.05D); 150 if(colwidth < len) 151 sheet.setColumnWidth(columnNumber, len + 1); 152 } 153 154 155 private String getExcelSheetName() { 156 return workbook.getSheetName(0); 157 } 158 159 private int getSheetIndex() { 160 return workbook.getSheetIndex(getExcelSheetName()); 161 } 162 private int getExcelSheetNumber() { 163 return getSheetIndex()+1; 164 } 165 166 167 168 169 170 171 public Struct getSummaryInfo() { 172 Struct infostruct = new StructImpl(); 173 174 int sheets = workbook.getNumberOfSheets(); 175 infostruct.setEL("SHEETS", new Double(sheets)); 176 if(sheets>0) { 177 StringBuilder sb=new StringBuilder(); 178 for(int i=0; i<sheets; i++){ 179 if(i>0)sb.append(','); 180 sb.append(workbook.getSheetName(i)); 181 } 182 infostruct.setEL("SHEETNAMES", sb.toString()); 183 } 184 185 if(xmlFormat==FORMAT_HSSF) { 186 infostruct.setEL("SPREADSHEETTYPE", "Excel"); 187 188 HSSFWorkbook hssfworkbook = (HSSFWorkbook)workbook; 189 info(infostruct,hssfworkbook.getSummaryInformation()); 190 info(infostruct,hssfworkbook.getDocumentSummaryInformation()); 191 } 192 else if(xmlFormat==FORMAT_XSSF) { 193 infostruct.put("SPREADSHEETTYPE", "Excel (2007)"); 194 195 XSSFWorkbook xssfworkbook = (XSSFWorkbook)workbook; 196 POIXMLProperties props = xssfworkbook.getProperties(); 197 info(infostruct,props.getCoreProperties().getUnderlyingProperties()); 198 info(infostruct,props.getExtendedProperties().getUnderlyingProperties()); 199 } 200 return infostruct; 201 } 202 203 204 205 206 207 208 209 210 211 212 private void info(Struct sct, CTProperties props) { 213 if(props==null) return; 214 set(sct, "COMPANY", props.getCompany()); 215 set(sct, "MANAGER", props.getManager()); 216 } 217 218 private void info(Struct sct, PackagePropertiesPart props) { 219 if(props==null) return; 220 set(sct, "AUTHOR", props.getCreatorProperty().getValue()); 221 set(sct, "CATEGORY", props.getCategoryProperty().getValue()); 222 set(sct, "COMMENTS", props.getDescriptionProperty().getValue()); 223 set(sct, "CREATIONDATE", props.getCreatedProperty().getValue()); 224 set(sct, "KEYWORDS", props.getKeywordsProperty().getValue()); 225 set(sct, "LASTAUTHOR", props.getLastModifiedByProperty().getValue()); 226 set(sct, "LASTEDITED", props.getModifiedProperty().getValue()); 227 set(sct, "SUBJECT", props.getSubjectProperty().getValue()); 228 set(sct, "TITLE", props.getTitleProperty().getValue()); 229 } 230 231 private void info(Struct sct, DocumentSummaryInformation summary) { 232 if(summary==null) return; 233 set(sct,"CATEGORY",summary.getCategory()); 234 set(sct,"COMPANY",summary.getCompany()); 235 set(sct,"MANAGER",summary.getManager()); 236 set(sct,"PRESENTATIONFORMAT",summary.getPresentationFormat()); 237 } 238 239 private void info(Struct sct, SummaryInformation summary) { 240 if(summary==null) return; 241 set(sct,"AUTHOR",summary.getAuthor()); 242 set(sct,"APPLICATIONNAME",summary.getApplicationName()); 243 set(sct,"COMMENTS",summary.getComments()); 244 set(sct,"CREATIONDATE",summary.getCreateDateTime()); 245 set(sct,"KEYWORDS",summary.getKeywords()); 246 set(sct,"LASTAUTHOR",summary.getLastAuthor()); 247 set(sct,"LASTEDITED",summary.getEditTime()); 248 set(sct,"LASTSAVED",summary.getLastSaveDateTime()); 249 set(sct,"REVNUMBER",summary.getRevNumber()); 250 set(sct,"SUBJECT",summary.getSubject()); 251 set(sct,"TITLE",summary.getTitle()); 252 set(sct,"TEMPLATE",summary.getTemplate()); 253 } 254 255 private void set(Struct sct, String name, String value) { 256 sct.setEL(KeyImpl.init(name), StringUtil.toStringEmptyIfNull(value)); 257 } 258 259 private void set(Struct sct, String name, Date value) { 260 Object obj=Caster.toDate(value,false, null,null); 261 if(obj==null)obj=""; 262 sct.setEL(KeyImpl.init(name), obj); 263 } 264 265 private void set(Struct sct, String name, long value) { 266 Object obj=(value!=0)?new DateTimeImpl(value, false):""; 267 sct.setEL(KeyImpl.init(name), obj); 268 } 269 270// Struct methods 271 272 @Override 273 public int size() { 274 return keys.length; 275 } 276 277 278 @Override 279 public Key[] keys() { 280 return keys; 281 } 282 283 284 @Override 285 public Object remove(Key key) throws PageException { 286 return null; 287 } 288 289 290 @Override 291 public Object removeEL(Key key) { 292 return null; 293 } 294 295 296 @Override 297 public void clear() { 298 } 299 300 301 @Override 302 public Object get(Key key) throws PageException { 303 Object value = get(key,NullSupportHelper.NULL()); 304 if(value!=NullSupportHelper.NULL()) return value; 305 throw invalidKey(null,this,key); 306 } 307 308 309 @Override 310 public Object get(Key key, Object defaultValue) { 311 if(key.equals(SHEET_NAME)) return getExcelSheetName(); 312 else if(key.equals(SHEET_NUMBER)) return Caster.toDouble(getExcelSheetNumber()); 313 else if(key.equals(ROW_COUNT)) return Caster.toDouble(0); 314 else if(key.equals(SUMMARY_INFO)) return getSummaryInfo(); 315 return defaultValue; 316 } 317 318 @Override 319 public Object set(Key key, Object value) throws PageException { 320 return value; 321 } 322 323 324 @Override 325 public Object setEL(Key key, Object value) { 326 return value; 327 } 328 329 330 @Override 331 public Collection duplicate(boolean deepCopy) { 332 return this; 333 } 334 335 336 @Override 337 public boolean containsKey(Key key) { 338 return get(key,null)!=null; 339 } 340 341 342 @Override 343 public Iterator<Collection.Key> keyIterator() { 344 return new KeyIterator(keys); 345 } 346 347 @Override 348 public Iterator<String> keysAsStringIterator() { 349 return new StringIterator(keys()); 350 } 351 352 @Override 353 public Iterator<Entry<Key, Object>> entryIterator() { 354 return new EntryIterator(this,keys); 355 } 356 357 @Override 358 public Iterator<Object> valueIterator() { 359 return new ValueIterator(this,keys()); 360 } 361}