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}