ExportExcel.java

ExportExcel.java

package com.runchain.arch.util.export;

import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springside.modules.utils.reflection.ReflectionUtils;

/**
 * 导出Excel，封装了POI
 *
 * @author HenryYan
 */
public class ExportExcel {

    private static Logger logger = LoggerFactory.getLogger(ExportExcel.class);

    private String sheetName;
    private Map<String, String> columns = null;
    private List<? extends Object> datas;

    private String fileName;

    private HSSFWorkbook workbook;
    private HSSFSheet sheet;
    private HSSFRow titleRow;

    /**
     * 创建ExportExcel对象
     * @param fileName	Excel文件名称
     * @param sheetName	Sheet名称
     * @param columns	中英文名称对照
     * @param datas		数据源
     */
    public ExportExcel(String fileName, String sheetName, Map<String, String> columns, List<? extends Object> datas) {
        super();
        this.fileName = fileName;
        this.sheetName = sheetName;
        this.columns = columns;
        this.datas = datas;

        init();
    }

    /**
     * @param sheetName
     */
    private void init() {
        //1 创建一个webbook，对应一个Excel文件
        workbook = new HSSFWorkbook();

        //2，在webbook中添加一个sheet,对应Excel文件中的sheet
        sheet = workbook.createSheet(sheetName);
        sheet.autoSizeColumn(columns.size());

        //3 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        titleRow = sheet.createRow((int) 0);

        //4，创建单元格，并设置值表头  设置表头居中
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    }

    public ExportExcel export() throws Exception {
        Set<String> keySet = columns.keySet();
        int indexOf = 0;
        // 创建表头
        for (String columnName : keySet) {
            logger.debug("列：{}，索引：{}", columnName, indexOf);
            HSSFCell cell = titleRow.createCell(indexOf);
            cell.setCellValue(columns.get(columnName));
            cell.setCellType(Cell.CELL_TYPE_STRING);
            indexOf++;
        }

        // 填充数据
        long lasttime = System.currentTimeMillis();
        int rowCounter = 1;
        for (Object obj : datas) {
            HSSFRow row = sheet.createRow(rowCounter++);
            System.out.println("create row: " + rowCounter + ", time= " + (System.currentTimeMillis() - lasttime) + "ms");
            lasttime = System.currentTimeMillis();
            indexOf = 0;
            for (String columnName : keySet) {
                sheet.autoSizeColumn(indexOf);
                String[] columnNames = columnName.split("\\.");
                Object property = null;
                if (columnNames.length == 1) {
                    property = ReflectionUtils.invokeGetterMethod(obj, columnName);
                    row.createCell(indexOf++).setCellValue(ObjectUtils.toString(property));
                } else {
                    property = ReflectionUtils.getFieldValue(obj, columnNames[0]);
                    if (property == null) {
                        row.createCell(indexOf++).setCellValue(StringUtils.EMPTY);
                        continue;
                    }

                    for (int i = 1; i < columnNames.length; i++) {
                        property = ReflectionUtils.getFieldValue(property, columnNames[i]);
                    }

                    row.createCell(indexOf++).setCellValue(ObjectUtils.toString(property));
                }

            }
        }

        FileOutputStream fo = new FileOutputStream(fileName);
        workbook.write(fo);
        fo.close();
        return this;
    }

    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    public HSSFSheet getSheet() {
        return sheet;
    }

    public HSSFRow getTitleRow() {
        return titleRow;
    }

}