使用POI生成Excel報表

先把報表模板截圖貼上來

下面是POI編寫的報表生成類ExcelReport.java

package com.jadyer.report;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.ss.usermodel.WorkbookFactory;

/**
 * 使用POI生成Excel報表
 * @see 它所生成的報表是根據Excel模塊文件生成的
 * @see 這里要用到poi-3.9-20121203.jar和poi-ooxml-3.9-20121203.jar
 * @see 另外模板文件<>下載地址為http://download.csdn.net/detail/jadyer/5736263
 * @create Jul 5, 2013 9:54:46 PM
 * @author 玄玉
 */
public enum ExcelReport {
    //實現單例模式的唯一實例
    INSTANCE;
    /**報表模板文件的存儲位置*/
    private static final String REPORT_TEMPLATE_FILE_PATH = "/ReportTemplate.xls";
    /**本列開始填充序號的標識*/
    private static final String SERIAL_NO = "serialNo";
    /**本行開始填充數據的標識*/
    private static final String DATA_BEGIN = "dataBegin";
    /**表格采用同列樣式的標識*/
    private static final String USE_STYLES = "useStyles";
    /**表格樣式采用的默認樣式*/
    private static final String DEFAULT_STYLES = "defaultStyles";
    /**初始行的下標(指的是填充數據的第一個單元格下標)*/
    private int initRowIndex;
    /**初始列的下標(指的是填充數據的第一個單元格下標)*/
    private int initColIndex;
    /**當前行的下標(指的是填充數據的當前單元格下標)*/
    private int currRowIndex;
    /**當前列的下標(指的是填充數據的當前單元格下標)*/
    private int currColIndex;
    /**最后一行的下標*/
    private int lastRowIndex;
    /**序號列的第一個單元格的下標*/
    private int serialColIndex;
    /**默認行高(指的是填充數據的第一個單元格的行高)*/
    private float defaultRowHeight;
    /**存放模板中所有表格樣式(鍵為99表示表格的默認樣式)*/
    private Map allCellStyle = new HashMap();
    private Row currRow;
    private Sheet sheet;
    private Workbook wb;


    /**
     * 基礎數據初始化
     */
    private ExcelReport(){
        try {
            //從指定目錄中讀取
            //wb = WorkbookFactory.create(new File(REPORT_TEMPLATE_FILE_PATH));
            //從classpath中讀取模板文檔
            wb = WorkbookFactory.create(ExcelReport.class.getResourceAsStream(REPORT_TEMPLATE_FILE_PATH));
            //獲取模板中的第一個Sheet
            sheet = wb.getSheetAt(0);
        } catch (InvalidFormatException e) {
            throw new RuntimeException("模板文件格式無效", e);
        } catch (IOException e) {
            throw new RuntimeException("模板文件不存在", e);
        }
        for(Row row : sheet){
            for(Cell cell : row){
                //報表模板文件default.xls中約定序號和SERIAL_NO和DATA_BEGIN都是String類型的
                if(Cell.CELL_TYPE_STRING != cell.getCellType()){
                    continue;
                }
                String str = cell.getStringCellValue().trim();
                //收集默認的表格樣式
                if(DEFAULT_STYLES.equals(str)){
                    this.allCellStyle.put(99, cell.getCellStyle());
                }
                //收集除默認表格樣式以外的所有表格樣式
                if(USE_STYLES.equals(str)){
                    this.allCellStyle.put(cell.getColumnIndex(), cell.getCellStyle());
                }
                //定位序號列的第一個單元格下標
                if(SERIAL_NO.equals(str)){
                    this.serialColIndex = cell.getColumnIndex();
                }
                //定位開始填充數據的第一個單元格的下標
                if(DATA_BEGIN.equals(str)){
                    this.initColIndex = cell.getColumnIndex();
                    this.initRowIndex = row.getRowNum();
                    this.currColIndex = this.initColIndex;
                    this.currRowIndex = this.initRowIndex;
                    this.lastRowIndex = sheet.getLastRowNum();
                    this.defaultRowHeight = row.getHeightInPoints();
                }
            }
        }
    }


    /**
     * 創建行
     */
    public void createNewRow(){
        //下移行的條件有2個:當前行非初始行,且當前行沒有超過最后一行
        if(this.currRowIndex!=this.initRowIndex && this.lastRowIndex>this.currRowIndex){
            //將指定的幾行進行下移一行
            sheet.shiftRows(this.currRowIndex, this.lastRowIndex, 1, true, true);
            //既然下移了那么最后一行下標就也要增大了
            this.lastRowIndex++;
        }
        //在指定的行上創建一個空行(如果此行原本有單元格和數據,那么也會被空行覆蓋,且創建出來的空行是沒有單元格的)
        this.currRow = sheet.createRow(this.currRowIndex);
        this.currRow.setHeightInPoints(this.defaultRowHeight);
        this.currRowIndex++;
        this.currColIndex = this.initColIndex;
    }


    /**
     * 構造單元格(包括創建單元格和填充數據)
     */
    public void buildCell(String value){
        Cell cell = this.currRow.createCell(this.currColIndex);
        if(this.allCellStyle.containsKey(this.currColIndex)){
            cell.setCellStyle(this.allCellStyle.get(this.currColIndex));
        }else{
            cell.setCellStyle(this.allCellStyle.get(99));
        }
        cell.setCellValue(value);
        this.currColIndex++;
    }


    /**
     * 插入序號
     */
    private void insertSerialNo(){
        int index = 1;
        Row row = null;
        Cell cell = null;
        for(int i=this.initRowIndex; i constantData = new HashMap();
        constantData.put("title", "優秀學生名單");
        constantData.put("date", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        constantData.put("developer", "玄玉博客");
        for(Row row : sheet){
            for(Cell cell : row){
                if(Cell.CELL_TYPE_STRING != cell.getCellType()){
                    continue;
                }
                String str = cell.getStringCellValue().trim();
                if(str.startsWith("#")){
                    if(constantData.containsKey(str.substring(1))){
                        cell.setCellValue(constantData.get(str.substring(1)));
                    }
                }
            }
        }
    }


    /**
     * 將生成的excel文件寫到輸出流中
     * @see 適用于文件下載
     */
    public void writeToStream(OutputStream os){
        this.insertSerialNo();
        this.replaceConstantData();
        try {
            wb.write(os);
        } catch (IOException e) {
            throw new RuntimeException("寫入流失敗", e);
        }
    }


    /**
     * 將生成的excel文件寫到指定的文件中
     * @see 適用于硬盤保存
     */
    public void writeToFile(String filepath){
        this.insertSerialNo();
        this.replaceConstantData();
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream(filepath);
            wb.write(fos);
        } catch (FileNotFoundException e) {
            throw new RuntimeException("寫入的文件[" + filepath + "]不存在", e);
        } catch (IOException e) {
            throw new RuntimeException("寫入數據失敗", e);
        } finally {
            if(null != fos){
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

package com.jadyer.report;

import java.io.File;

import org.junit.Assert;
import org.junit.Test;

import com.jadyer.report.ExcelReport;

public class ExcelReportTest {
    @Test
    public void testExcelReportUtil(){
        ExcelReport eru = ExcelReport.INSTANCE;
        eru.createNewRow();
        eru.buildCell("aa");
        eru.buildCell("玄玉");
        eru.buildCell("cc");
        eru.buildCell("dd");
        eru.createNewRow();
        eru.buildCell("aa");
        eru.buildCell("http://blog.csdn.net/jadyer");
        eru.buildCell("cc");
        eru.buildCell("dd");
        eru.createNewRow();
        eru.buildCell("aa");
        eru.buildCell("蓄機而動");
        eru.buildCell("cc");
        eru.buildCell("dd");
        eru.writeToFile("D:/test.xls");
        Assert.assertTrue(new File("D:/test.xls").exists());
    }
}