excelexport.java

struts+spring+hibernate自创框架

package com.pegasus.framework.util.export;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Properties;
import java.util.StringTokenizer;

import jxl.Cell;
import jxl.Workbook;
import jxl.biff.EmptyCell;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.pegasus.framework.util.export.constants.ExcelExportConstants;
import com.pegasus.framework.util.export.exception.ExportException;
import com.pegasus.framework.util.export.model.ExcelObject;
import com.pegasus.framework.util.export.model.Position;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */

public class ExcelExport {
	protected final Log log = LogFactory.getLog(this.getClass());
	
	private static final String SHEET_NAME = "sheet";
    private WritableWorkbook workbook;
    private Workbook readOnlyWorkbook;
    private WritableSheet sheet;
    private Properties propFile;

    public static Properties getProp(String fileName) throws ExportException{
        try {
            java.io.InputStream inputstream = ExcelExport.class.getResourceAsStream(fileName);
            if (inputstream == null) {
                throw new ExportException(fileName + " is not exist");
            }
            Properties prop = new Properties();
            prop.load(inputstream);
            return prop;
        } catch (IOException e) {
            throw new ExportException("read " + fileName + " error", e);
        }
    }

    public File initFile(String tempFileString,String propFileName) throws ExportException{
        log.debug("initFile");
        File tempFile = null;
        try {
            tempFile = init(tempFileString);
            this.propFile = getProp(propFileName);
            String sheetName = propFile.getProperty(SHEET_NAME);
            log.debug("sheetName->" + sheetName);
            if(this.workbook.getSheet(sheetName) == null) {
                sheet = this.workbook.getSheet(new Integer(sheetName).intValue());
            } else {
                sheet = this.workbook.getSheet(sheetName);
            }
        } catch(Exception e) {
            log.error("ExcelExport->initFile",e);
            throw new ExportException("ExcelExport->initFile",e);
        }
        log.debug("initFile");
        return tempFile;
    }

   /* public void createExcel(String targetExcelFileName,String propFileName) throws ExportException{
        log.startMethod("createExcel");
        try {
            this.workbook = Workbook.createWorkbook(new File(targetExcelFileName));
            if(propFileName != null && propFileName.length() > 0) {
                this.propFile = ExportConfig.getExportConfigInputStream(propFileName);
            }
        } catch(Exception e) {
            log.exception("ExcelExport->createExcel",e);
            throw new ExportException(e);
        }
        log.finishMethod("createExcel");
    }  */

    public void createSheet(String sheetName , int index)throws ExportException{
        log.debug("createSheet");
        try {
            this.sheet = this.workbook.createSheet(sheetName, index);
        } catch(Exception e) {
            log.error("ExcelExport->createSheet",e);
            throw new ExportException("ExcelExport->createSheet",e);
        }
        log.debug("createSheet");
    }


    private File init(String tempFileString) throws ExportException {
        File tempFile = null;
        try {
            //String tempFileString = ExcelExportConstants.TEMPLATE_FILE_PATH;
            InputStream is = this.getClass().getClassLoader().getResourceAsStream(tempFileString);
            //System.out.println("file: " + this.getClass().getClassLoader().getResource(""));
            this.readOnlyWorkbook = Workbook.getWorkbook(is);
            tempFile = File.createTempFile("comet","comet");
            this.workbook = Workbook.createWorkbook(tempFile, readOnlyWorkbook);
            is.close();
        } catch(Exception e) {
        	e.printStackTrace();
            log.error("ExcelExport->init",e);
            throw new ExportException("ExcelExport->init",e);
        }
        return tempFile;

    }

    private void setValuePosition(WritableCellFormat format,int position) throws ExportException {
        try {
            if (position == ExcelExportConstants.POSITION_LEFT) {
                format.setAlignment(Alignment.LEFT);
            }
            else if (position == ExcelExportConstants.POSITION_CENTER) {
                format.setAlignment(Alignment.CENTRE);
            }
            else if (position == ExcelExportConstants.POSITION_RIGHT) {
                format.setAlignment(Alignment.RIGHT);
            }
            else {
                format.setAlignment(Alignment.LEFT);
            }

        }catch(Exception e) {
            log.error("ExcelExport->setValuePosition",e);
            throw new ExportException("ExcelExport->setValuePosition",e);
        }
    }

    public void setCellValue(String cellName,ExcelObject value,int position) throws ExportException {
        try {
            Position pos = getCellPosition(cellName);
            setCellValue(pos.getX(),pos.getY(),value.getValue(),position,value.getFormat());
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }

    public void setCellValue(String cellName,String value,int position) throws ExportException {
        try {
            Position pos = getCellPosition(cellName);
            setCellValue(pos.getX(),pos.getY(),value,position);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }
    
    private void setCellValue(int x,int y,ExcelObject value,int position) throws ExportException {
        try {
            setValuePosition(value.getFormat(),position);
            Label l = new Label(x-1, y-1, value.getValue(),value.getFormat());
            sheet.addCell(l);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }

    private void setCellValue(int x,int y,String value,int position) throws ExportException {
        try {
            WritableCellFormat format = new WritableCellFormat();
            setValuePosition(format,position);
            Label l = new Label(x-1, y-1, value,format);
            sheet.addCell(l);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }
    
    private void setCellValue(int x,int y,String value,int position,WritableCellFormat wcf) throws ExportException {
        try {
        	WritableCellFormat format = new WritableCellFormat();
            if(wcf != null)
            	format = wcf;
            setValuePosition(format,position);
            Label l = new Label(x-1, y-1, value,format);
            sheet.addCell(l);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }

    public void setCellValue(String cellName,double value,int position) throws ExportException {
        try {
            Position pos = getCellPosition(cellName);
            setCellValue(pos.getX(),pos.getY(),value,position);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }

    private void setCellValue(int x,int y,double value,int position) throws ExportException {
        try {
            WritableCellFormat format = new WritableCellFormat();
            setValuePosition(format,position);
            jxl.write.Number number = new jxl.write.Number(x-1, y-1, value,format);
            sheet.addCell(number);