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