excelexport.java

struts+spring+hibernate自创框架

        } 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,WritableCellFormat wcf) throws ExportException {
        try {
            WritableCellFormat format = new WritableCellFormat();
            if(wcf != null)
            	format = wcf;
            setValuePosition(format,position);
            jxl.write.Number number = new jxl.write.Number(x-1, y-1, value,format);
            sheet.addCell(number);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }



    public void setCellValue(String cellName, Date value,String dateFormat,int position) throws ExportException {
        try {
            Position pos = getCellPosition(cellName);
            setCellValue(pos.getX(),pos.getY(),value,dateFormat,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, Date value,String dateFormat,int position) throws ExportException {
        try {
            DateFormat customDateFormat = null;
            if(dateFormat != null && dateFormat.length() > 0)  {
                customDateFormat = new DateFormat(dateFormat);
            } else {
                customDateFormat = new DateFormat(ExcelExportConstants.DEFAULT_DATE_FORMAT);
            }
            WritableCellFormat format = new WritableCellFormat(customDateFormat);
            setValuePosition(format,position);
            DateTime dateCell = new DateTime(x-1, y-1, value, format);
            sheet.addCell(dateCell);
        } catch(Exception e) {
            log.error("ExcelExport->setCellValue",e);
            throw new ExportException("ExcelExport->setCellValue",e);
        }
        log.debug("setCellValue");
    }

    public int insertRowValue(String cellName,ArrayList data) throws ExportException {
        Position pos = null;
        try {
            pos = getCellPosition(cellName);
            insertRowValue(cellName,pos.getY(),data);
        } catch(Exception e) {
            log.error("ExcelExport->insertRowValue",e);
            throw new ExportException("ExcelExport->insertRowValue",e);
        }
        log.debug("insertRowValue");
        return pos.getY() +1;
    }
    
    public int insertRowExcelObject(String cellName,ArrayList<ExcelObject> data) throws ExportException {
        Position pos = null;
        try {
            pos = getCellPosition(cellName);
            insertRowExcelObject(cellName,pos.getY(),data);
        } catch(Exception e) {
            log.error("ExcelExport->insertRowValue",e);
            throw new ExportException("ExcelExport->insertRowValue",e);
        }
        log.debug("insertRowValue");
        return pos.getY() +1;
    }
    
    public int insertRowExcelObject(String cellName,int y,ArrayList<ExcelObject> data) throws ExportException {
        Position pos = null;
        try {
            this.sheet.insertRow(y);
            pos = getCellPosition(cellName);
            int max = pos.getX() + data.size();
            int index=0;
            for(int i = pos.getX();i < max;i++) {
                setCellValue(i+1,y,data.get(index),ExcelExportConstants.POSITION_LEFT);
                index++;
            }
        } catch(Exception e) {
            log.error("ExcelExport->insertRowValue",e);
            throw new ExportException("ExcelExport->insertRowValue",e);
        }
        log.debug("insertRowValue");
        return y +1;
    }


    public int insertRowValue(String cellName,int y,ArrayList data) throws ExportException {
        Position pos = null;
        try {
            this.sheet.insertRow(y);
            pos = getCellPosition(cellName);
            int max = pos.getX() + data.size();
            int index=0;
            for(int i = pos.getX();i < max;i++) {
                setCellValue(i+1,y,(String)data.get(index),ExcelExportConstants.POSITION_LEFT);
                index++;
            }
        } catch(Exception e) {
            log.error("ExcelExport->insertRowValue",e);
            throw new ExportException("ExcelExport->insertRowValue",e);
        }
        log.debug("insertRowValue");
        return y +1;
    }





    public void commit() throws ExportException {
        try {
            workbook.write();
            workbook.close();
            readOnlyWorkbook.close();
        }
        catch (Exception ex) {
            log.error("ExcelExport->commit",ex);
            throw new ExportException("ExcelExport->commit",ex);
        }
        log.debug("commit");
    }

    public void rollback() throws ExportException {
        try {
            workbook.close();
            readOnlyWorkbook.close();
        }
        catch (Exception ex) {
            log.error("ExcelExport->rollback",ex);
            throw new ExportException("ExcelExport->rollback",ex);
        }
        log.debug("rollback");
    }

    private Position getCellPosition(String cellName){
        log.debug("getCellPosition");
        Position position = new Position();
        String positionString = this.propFile.getProperty(cellName);
        log.debug("cellName : "+cellName);
        log.debug("positionString : "+positionString);
        StringTokenizer st = new StringTokenizer(positionString,",");
        position.setX(new Integer(st.nextToken()).intValue());
        position.setY(new Integer(st.nextToken()).intValue());
        log.debug("getCellPosition");
        return position;
    }

    private WritableCellFormat getCellFormat(Cell cell) {
		CellFormat oldFormat = cell.getCellFormat();
		WritableCellFormat format = null;

		if (cell instanceof EmptyCell) {
			format = new WritableCellFormat();
		} else {
			format = new WritableCellFormat(oldFormat);
		}
		return format;
	}
    
    public static WritableFont createDefaultWcf() {
    	WritableCellFormat format = new WritableCellFormat();
    	WritableFont wfont =  new WritableFont(format.getFont());
    	return wfont;
    }

	private void copyCellFormat(WritableCell newCell, Cell oldCell) {
		newCell.setCellFormat(getCellFormat(oldCell));
	}
    	  
    public static void main(String args[]) {
        ExcelExport export = new ExcelExport();
        try {
            Date now = Calendar.getInstance().getTime();
            
           //export.initFile("d://Format3.properties");
           // export.setCellValue("containerNo","comet1111111111111",0);
          //  export.setCellValue("owner",now,"",0);
           // export.setCellValue(11,7,1111.3324,0);
           // export.addCellValue(1,12,1111.3324,0);
          // ArrayList list = new ArrayList();
          // for (int i = 0; i < 12; i++) {
          //     list.add("comet---" + i);
          // }
           //export.insertRowValue(11,list);
           //export.insertRowValue(12,list);
           //export.insertRowValue(13,list);
           //export.insertRowValue(14,list);
  
           // InputStream is = new FileInputStream("d:\\2.xls");
           // Workbook readOnlyWorkbook = Workbook.getWorkbook(is);
            //readOnlyWorkbook.getSheet(0);
            File tempFile = new File("d:\\3.xls");
            WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
            WritableSheet sheet1 = workbook.getSheet(0);
            //sheet1.mergeCells();
            //WritableCell cell = sheet1.getWritableCell(5,5);
            //sheet1.addCell(cell.copyTo(12,12));
            //workbook.addNameArea("begin",sheet1,1,20,12,40);
            //sheet1.ad
            workbook.write();
            
            workbook.close();
           // readOnlyWorkbook.close();
            System.out.println("ExcelExport success");
        }
        catch (Exception ex) {
            
           System.out.println("ExcelExport : " + ex);
           ex.printStackTrace();
        }

    }





}