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    }