readexcelmodelutils.java

利用jxl和jdom到报表用的,可以自定义excel模板

/**
* 读取Excel表格,拷贝、更新Excel工作薄 </p>
* @author tombo
* @version 1.0
*/
package com.glf.reportIE.commons;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Vector;
import jxl.CellType;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ReadExcelModelUtils{
    /**
     * 创建Excel
     * @param source 是excel模板存放的地方
     * @param newPath 是拷贝过后的excel文件存放的地方
     * @param dataList 是要填充excel表的【列、行、数据】的集合
     */
	@SuppressWarnings("unused")
	public void exportExcelWithData(String source,String newPath,ArrayList dataList){
        Workbook workbook = null;
        try{
            // 构建Workbook对象, 只读Workbook对象
            //直接从本地文件创建Workbook, 从输入流创建Workbook
            InputStream ins = new FileInputStream(source);
            workbook = Workbook.getWorkbook(ins);
            //利用已经创建的Excel工作薄创建新的可写入的Excel工作薄
            File outFile = new File(newPath);
            WritableWorkbook wwb = Workbook.createWorkbook(outFile, workbook);
            //FileOutputStream fstream = new FileOutputStream(newPath);
            //WritableWorkbook wwb = Workbook.createWorkbook(fstream, workbook, new WorkbookSettings());
            //读取第一张工作表
            WritableSheet dataSheet = wwb.getSheet(0);
            //WritableSheet dataSheet = wwb.createSheet("Sheet0",0);
            //设置冻结单元格
            //dataSheet.getSettings().setVerticalFreeze(7);
            //dataSheet.getSettings().setHorizontalFreeze(2);   
            // 插入数据
            insertData(wwb, dataSheet, dataList);            
            // 插入模拟图像数据
            
            // 插入图表

            //写入Excel对象
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            //YTLogger.logDebug(e);
        } finally {
            // 操作完成时，关闭对象，释放占用的内存空间
            workbook.close();
        }
    }
    
    /**
     * 插入数据
     * @param wwb WritableWorkbook : 工作簿
     * @param dataSheet WritableSheet : 工作表
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings("unused")
	private void insertData(WritableWorkbook wwb, WritableSheet dataSheet, ArrayList dataList) throws RowsExceededException, WriteException {
        // 修改数据单元格数据
    	for(int i=0; i<dataList.size(); i++){
    		//int col = Integer.parseInt(dataList.get(i).toString());
    		//int row = Integer.parseInt(dataList.get(i+1).toString());
    		//String cellValue = dataList.get(i+2).toString();
    		modiStrCell(dataSheet, Integer.parseInt(dataList.get(i).toString()), Integer.parseInt(dataList.get(i+1).toString()), dataList.get(i+2).toString(), null);
    		i+=2;
    	}  
//        // 删除空行
//        for (int j = vecData.size() + TITLE_LENGTH; j < SHEET_HEIGHT; j++) {
//            dataSheet.removeRow(vecData.size() + TITLE_LENGTH);
//        }        
//        // 插入公式
//        for(int i = 2; i < SHEET_WIDTH; i ++) {
//            modiFormulaCell(dataSheet, i, vecData.size() + TITLE_LENGTH, 8, vecData.size() + TITLE_LENGTH, null);
//        }        
    }

    /**
     * 修改字符单元格的值
     * @param dataSheet WritableSheet : 工作表
     * @param col int : 列
     * @param row int : 行
     * @param str String : 字符
     * @param format CellFormat : 单元格的样式
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings({ "unused", "deprecation" })
	private void modiStrCell(WritableSheet dataSheet, int col, int row, String str, CellFormat format) throws RowsExceededException, WriteException {
        // 获得单元格对象
        WritableCell cell = dataSheet.getWritableCell(col, row);
        // 判断单元格的类型, 做出相应的转化
        //Label lbl = new Label(col, row-1, str, cell.getCellFormat());
        //WritableCellFormat fm = (WritableCellFormat) cell.getCellFormat();
        Label lbl = new Label(col, row-1, str, dataSheet.getCell(col, row-1).getCellFormat());
        dataSheet.addCell(lbl);
        //lbl.setString(str);
//        if (cell.getType() == CellType.EMPTY) {
//            Label lbl = new Label(col, row, str);
//            if(null != format) {
//                lbl.setCellFormat(format);
//            } else {
//                lbl.setCellFormat(cell.getCellFormat());
//            }
//            dataSheet.addCell(lbl);
//        } else if (cell.getType() == CellType.LABEL) {
//            //Label lbl = (Label)cell;
//            //lbl.setString(str);
//        	Label lbl = new Label(col, row-1, str);
//        	dataSheet.addCell(lbl);
//        } else if (cell.getType() == CellType.NUMBER) {
//            // 数字单元格修改
//        	Number lbl = (Number)cell;
//            lbl.setValue(Double.valueOf(str));
//        }
    }
    
    /**
     * 修改数字单元格的值
     * @param dataSheet WritableSheet : 工作表
     * @param col int : 列
     * @param row int : 行
     * @param num double : 数值
     * @param format CellFormat : 单元格的样式
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings("unused")
	private void modiNumCell(WritableSheet dataSheet, int col, int row, double num, CellFormat format) throws RowsExceededException, WriteException {
        // 获得单元格对象
        WritableCell cell = dataSheet.getWritableCell(col, row);
        // 判断单元格的类型, 做出相应的转化
        if (cell.getType() == CellType.EMPTY) {
            Number lbl = new Number(col, row, num);
            if(null != format) {
                lbl.setCellFormat(format);
            } else {
                lbl.setCellFormat(cell.getCellFormat());
            }
            dataSheet.addCell(lbl);
        } else if (cell.getType() == CellType.NUMBER) {
            // 数字单元格修改
            Number lbl = (Number)cell;
            lbl.setValue(num);
        } else if (cell.getType() == CellType.LABEL) {
            Label lbl = (Label)cell;
            lbl.setString(String.valueOf(num));
        }
    }
    
    /**
     * 修改公式单元格的值
     * @param dataSheet WritableSheet : 工作表
     * @param col int : 列
     * @param row int : 行
     * @param startPos int : 开始位置
     * @param endPos int : 结束位置
     * @param format
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings("unused")
	private void modiFormulaCell(WritableSheet dataSheet, int col, int row, int startPos, int endPos, CellFormat format) throws RowsExceededException, WriteException {
        String f = getFormula(col, row, startPos, endPos);
        // 插入公式（只支持插入，不支持修改）
        WritableCell cell = dataSheet.getWritableCell(col, row);
        if (cell.getType() == CellType.EMPTY) {                    
            // 公式单元格
            Formula lbl = new Formula(col, row, f);
            if(null != format) {
                lbl.setCellFormat(format);
            } else {
                lbl.setCellFormat(cell.getCellFormat());
            }
            dataSheet.addCell(lbl);
        } else if (cell.getType() == CellType.STRING_FORMULA){
            //YTLogger.logWarn("Formula modify not supported!");
        }
    }
    
    /**
     * 得到公式
     * @param col int : 列
     * @param row int : 行
     * @param startPos int : 开始位置
     * @param endPos int : 结束位置
     * @return String
     * @throws RowsExceededException
     * @throws WriteException
     */
    private String getFormula(int col, int row, int startPos, int endPos)
            throws RowsExceededException, WriteException {
        char base = 'A';
        char c1 = base;
        StringBuffer formula = new StringBuffer(128);
        // 组装公式
        formula.append("SUM(");
        if (col <= 25) {
            c1 = (char) (col % 26 + base);
            formula.append(c1).append(startPos).append(":")
                   .append(c1).append(endPos).append(")");
        } else if (col > 25) {
            char c2 = (char) ((col - 26) / 26 + base);
            c1 = (char) ((col - 26) % 26 + base);
            formula.append(c2).append(c1).append(startPos).append(":")
                   .append(c2).append(c1).append(endPos).append(")");
        }

        return formula.toString();
    }
    
    /**
     * 插入图表工作表
     * @param wwb WritableWorkbook : 工作簿
     * @param vecImg Vector : 图像链表
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings("unused")
	private void insertImgsheet(WritableWorkbook wwb, Vector vecImg)
            throws RowsExceededException, WriteException {
        // 插入图像
//        WritableSheet imgSheet;
//        if((wwb.getSheets()).length < 2){
//            imgSheet = wwb.createSheet("图表", 1);
//        } else {
//            imgSheet = wwb.getSheet(1);
//        }
//        
//        for (int i = 0; i < vecImg.size(); i++){
//            ChartImg chart = (ChartImg) vecImg.get(i);
//            // 插入图像标题
//            Label lbl = new Label(0, 2 + 20 * i, chart.getImgTitle());
//            WritableFont font = new WritableFont(WritableFont.ARIAL,
//                    WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false,
//                    UnderlineStyle.NO_UNDERLINE, Colour.DARK_BLUE2);
//            WritableCellFormat background = new WritableCellFormat(font);
//            background.setWrap(true);
//            background.setBackground(Colour.GRAY_25);
//            imgSheet.mergeCells(0, 2 + 20 * i, 9, 2 + 20 * i);
//            lbl.setCellFormat(background);
//            imgSheet.addCell(lbl);
//            // 插入图像单元格
//            insertImgCell(imgSheet, 2, 4 + 20 * i, 8, 15, chart.getImgName());
//        }
    }

    /**
     * 插入图像到单元格（图像格式只支持png）
     * @param dataSheet WritableSheet : 工作表
     * @param col int : 列
     * @param row int : 行
     * @param width int : 宽
     * @param height int : 高
     * @param imgName String : 图像的全路径
     * @throws RowsExceededException
     * @throws WriteException
     */
    @SuppressWarnings("unused")
	private void insertImgCell(WritableSheet dataSheet, int col, int row, int width,
            int height, String imgName) throws RowsExceededException, WriteException{
        File imgFile = new File(imgName);
        WritableImage img = new WritableImage(col, row, width, height, imgFile);
        dataSheet.addImage(img);
    }
    
    /**
     * 测试
     * @param args
     */
    public static void main(String[] args){
    	
    }
}