Excel.java example

Explorer
railo-master
- railo-java
package railo.runtime.poi;
import java.util.Date;
import java.util.Iterator;

import org.apache.poi.POIXMLProperties;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.internal.PackagePropertiesPart;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.officeDocument.x2006.extendedProperties.CTProperties;

import railo.commons.io.res.Resource;
import railo.commons.lang.StringUtil;
import railo.runtime.config.NullSupportHelper;
import railo.runtime.exp.CasterException;
import railo.runtime.exp.PageException;
import railo.runtime.op.Caster;
import railo.runtime.op.Decision;
import railo.runtime.type.Collection;
import railo.runtime.type.KeyImpl;
import railo.runtime.type.Struct;
import railo.runtime.type.StructImpl;
import railo.runtime.type.dt.DateTimeImpl;
import railo.runtime.type.it.EntryIterator;
import railo.runtime.type.it.KeyIterator;
import railo.runtime.type.it.StringIterator;
import railo.runtime.type.it.ValueIterator;
import railo.runtime.type.util.StructSupport;

public class Excel extends StructSupport implements Cloneable,Struct {

	public static final short FORMAT_UNDEFINED=0;
	public static final short FORMAT_XSSF=1;
	public static final short FORMAT_HSSF=2;
	public static final short FORMAT_SXSSF=4;
	private Workbook workbook;
	private Drawing drawing;
	private short xmlFormat;

	private static final Collection.Key SHEET_NAME=KeyImpl.init("SHEETNAME");
	private static final Collection.Key SHEET_NUMBER=KeyImpl.init("SHEETNUMBER");
	private static final Collection.Key ROW_COUNT=KeyImpl.init("ROWCOUNT");
	private static final Collection.Key SUMMARY_INFO=KeyImpl.init("SUMMARYINFO");
	private static final Collection.Key[] keys=new Collection.Key[]{
		SHEET_NAME,SHEET_NUMBER,ROW_COUNT,SUMMARY_INFO
	};
	private static final String[] skeys=new String[]{
		SHEET_NAME.getString(),SHEET_NUMBER.getString(),ROW_COUNT.getString(),SUMMARY_INFO.getString()
	};

    public static final String XSSF_FORMAT = "XSSF";
    public static final String HSSF_FORMAT = "HSSF";
    public static final String SXSSF_FORMAT = "SXSSF";
	
	
	public Excel(String sheetName, short xmlFormat, int rows){
		if(FORMAT_XSSF==xmlFormat) workbook=new XSSFWorkbook();
		else if(FORMAT_HSSF==xmlFormat) workbook=new HSSFWorkbook();
		else if(FORMAT_SXSSF==xmlFormat) workbook=new SXSSFWorkbook();
		this.xmlFormat=xmlFormat;
        Sheet sheet = workbook.createSheet();
        drawing = sheet.createDrawingPatriarch();
        workbook.setSheetName(0, sheetName);
	}


    private Workbook getWorkbook() {
		return workbook;
	}
    

	public void write(Resource res, String password) {
		// TODO Auto-generated method stub
		
	}

	
	public void setValue(int rowNumber, int columnNumber, String value) throws CasterException {
		if(value==null) value="";
		Sheet sheet = workbook.getSheet(getExcelSheetName());
	    
		// get Row
		Row row = sheet.getRow(rowNumber);
	    if(row==null) row = sheet.createRow(rowNumber);
	     
	    // get Cell
	    Cell cell = row.getCell(columnNumber);
	    CellStyle style = null;
	    if(cell != null) {
	    	style = cell.getCellStyle();
	        row.removeCell(cell);
	    }
	    cell = row.createCell(columnNumber);
	    if(style != null) cell.setCellStyle(style);
	    
        CreationHelper createHelper = workbook.getCreationHelper();
        boolean isFormula=style != null && style.getDataFormatString().equals("@");
        
        
        if(!isFormula && Decision.isNumeric(value)) {
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			double dbl = Caster.toDoubleValue(value);
            cell.setCellValue(dbl);
            _expandColumnWidth(sheet,Caster.toString(dbl),columnNumber);
		}
        else if(StringUtil.isEmpty("")) {
            cell.setCellType(Cell.CELL_TYPE_BLANK);
            cell.setCellValue(createHelper.createRichTextString(""));
        }
        else {
            cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(createHelper.createRichTextString(value));
            _expandColumnWidth(sheet,value,columnNumber);
        } 
	        
	        
	}
	

	private static void _expandColumnWidth(Sheet sheet, String value, int columnNumber) {
		int colwidth = sheet.getColumnWidth(columnNumber );
        int len = (int)((value.length() * 8) / 0.05D);
        if(colwidth < len)
            sheet.setColumnWidth(columnNumber, len + 1);
	}


	private String getExcelSheetName() {
		return workbook.getSheetName(0);
	}
	
	private int getSheetIndex() {
		return workbook.getSheetIndex(getExcelSheetName());
	}
	private int getExcelSheetNumber() {
		return getSheetIndex()+1;
	}
	

	
	
	
	
	public Struct getSummaryInfo() {
        Struct infostruct = new StructImpl();
        
        int sheets = workbook.getNumberOfSheets();
        infostruct.setEL("SHEETS", new Double(sheets));
        if(sheets>0) {
        	StringBuilder sb=new StringBuilder();
            for(int i=0; i<sheets; i++){
            	if(i>0)sb.append(',');
            	sb.append(workbook.getSheetName(i));
            }
            infostruct.setEL("SHEETNAMES", sb.toString());
        }
    	
        if(xmlFormat==FORMAT_HSSF) {
        	infostruct.setEL("SPREADSHEETTYPE", "Excel");
        	
        	HSSFWorkbook hssfworkbook = (HSSFWorkbook)workbook;
            info(infostruct,hssfworkbook.getSummaryInformation());
            info(infostruct,hssfworkbook.getDocumentSummaryInformation());
        } 
        else if(xmlFormat==FORMAT_XSSF)	{
        	infostruct.put("SPREADSHEETTYPE", "Excel (2007)");
            
        	XSSFWorkbook xssfworkbook = (XSSFWorkbook)workbook;
            POIXMLProperties props = xssfworkbook.getProperties();
            info(infostruct,props.getCoreProperties().getUnderlyingProperties());
            info(infostruct,props.getExtendedProperties().getUnderlyingProperties());
        }
        return infostruct;
    }
	



	





	private void info(Struct sct, CTProperties props) {
		if(props==null) return;
		set(sct, "COMPANY", props.getCompany());
		set(sct, "MANAGER", props.getManager());
	}

	private void info(Struct sct, PackagePropertiesPart props) {
		if(props==null) return;
		set(sct, "AUTHOR", props.getCreatorProperty().getValue());
		set(sct, "CATEGORY", props.getCategoryProperty().getValue());
		set(sct, "COMMENTS", props.getDescriptionProperty().getValue());
		set(sct, "CREATIONDATE", props.getCreatedProperty().getValue());
		set(sct, "KEYWORDS", props.getKeywordsProperty().getValue());
		set(sct, "LASTAUTHOR", props.getLastModifiedByProperty().getValue());
		set(sct, "LASTEDITED", props.getModifiedProperty().getValue());
		set(sct, "SUBJECT", props.getSubjectProperty().getValue());
		set(sct, "TITLE", props.getTitleProperty().getValue());
	}

	private void info(Struct sct, DocumentSummaryInformation summary) {
		if(summary==null) return;
		set(sct,"CATEGORY",summary.getCategory());
		set(sct,"COMPANY",summary.getCompany());
		set(sct,"MANAGER",summary.getManager());
		set(sct,"PRESENTATIONFORMAT",summary.getPresentationFormat());
	}

	private void info(Struct sct, SummaryInformation summary) {
		if(summary==null) return;
		set(sct,"AUTHOR",summary.getAuthor());
		set(sct,"APPLICATIONNAME",summary.getApplicationName());
		set(sct,"COMMENTS",summary.getComments());
		set(sct,"CREATIONDATE",summary.getCreateDateTime());
        set(sct,"KEYWORDS",summary.getKeywords());
		set(sct,"LASTAUTHOR",summary.getLastAuthor());
		set(sct,"LASTEDITED",summary.getEditTime());
		set(sct,"LASTSAVED",summary.getLastSaveDateTime());
        set(sct,"REVNUMBER",summary.getRevNumber());
		set(sct,"SUBJECT",summary.getSubject());
		set(sct,"TITLE",summary.getTitle());
		set(sct,"TEMPLATE",summary.getTemplate());
	}

	private void set(Struct sct, String name, String value) {
		sct.setEL(KeyImpl.init(name), StringUtil.toStringEmptyIfNull(value));
	}

	private void set(Struct sct, String name, Date value) {
		Object obj=Caster.toDate(value,false, null,null);
		if(obj==null)obj="";
		sct.setEL(KeyImpl.init(name), obj);
	}

	private void set(Struct sct, String name, long value) {
		Object obj=(value!=0)?new DateTimeImpl(value, false):"";
        sct.setEL(KeyImpl.init(name), obj);
	}
	
// Struct methods

	@Override
	public int size() {
		return keys.length;
	}


	@Override
	public Key[] keys() {
		return keys;
	}


	@Override
	public Object remove(Key key) throws PageException {
		return null;
	}


	@Override
	public Object removeEL(Key key) {
		return null;
	}


	@Override
	public void clear() {
	}


	@Override
	public Object get(Key key) throws PageException {
		Object value = get(key,NullSupportHelper.NULL());
		if(value!=NullSupportHelper.NULL()) return value;
		throw invalidKey(null,this,key);
	}


	@Override
	public Object get(Key key, Object defaultValue) {
		if(key.equals(SHEET_NAME)) return getExcelSheetName();
		else if(key.equals(SHEET_NUMBER)) return Caster.toDouble(getExcelSheetNumber());
		else if(key.equals(ROW_COUNT)) return Caster.toDouble(0);
		else if(key.equals(SUMMARY_INFO)) return getSummaryInfo();
		return defaultValue;
	}

	@Override
	public Object set(Key key, Object value) throws PageException {
		return value;
	}


	@Override
	public Object setEL(Key key, Object value) {
		return value;
	}


	@Override
	public Collection duplicate(boolean deepCopy) {
		return this;
	}


	@Override
	public boolean containsKey(Key key) {
		return get(key,null)!=null;
	}


	@Override
	public Iterator<Collection.Key> keyIterator() {
		return new KeyIterator(keys);
	}
    
    @Override
	public Iterator<String> keysAsStringIterator() {
    	return new StringIterator(keys());
    }
	
	@Override
	public Iterator<Entry<Key, Object>> entryIterator() {
		return new EntryIterator(this,keys);
	}
	
	@Override
	public Iterator<Object> valueIterator() {
		return new ValueIterator(this,keys());
	}
}