POI-导出Excel文件并自定义设置单元格

之前使用JXL导出Excel文件（仅支持2003版本之前的Excel），但随着现在业务数据量的增多，并为了适应新版本Excel文件的导出和读取，以及提高内存的使用效率和导出时间，使用POI导出Excel文件。

JXL和POI的比较：
JXL：
支持比较低版本的excel，比如Excel 95 ,97 ,2000，2003
由于Excel版本比较低，导致最大行有限制，无法导出65535以上量级的数据
对于内存，和时间的花费也比POI基于内存+磁盘的方式高。
1 读取Excel公式（可以读取Excel 97以后的公式）
2 生成Excel数据表（格式为Excel 97）
3 支持字体、数字、日期的格式化
4 支持单元格的阴影操作，以及颜色操作
5 修改已经存在的数据表
6 是最基础的excel api
7 小文件读取效率比较高
8 跨平台
POI：
1 能保持Excel里原有的宏（但不能用它写新的宏）。
2 不支持跨平台（主要就是Java语言）
3 在一些业务场景中代码相对复杂，但是API丰富，支持多种模式的读写。
4 支持比较新版本的excel.
5 读写的时候比较占内存。
6 读写的时候比较占内存。
7 支持大数量大文件的读写操作。但是需要熟悉API。

总体来说，对于简单的单表excel导入导出的需求，建议使用JXL。数据量稍微小点，占用内存少，速度快。
对于报表类的，涉及月份数据量，多表数据聚合在一起建议使用POI。

自己写了个测试类，支持导出Excel，自定义设置单元格格式。
示例代码如下：

package poi;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.util.CellRangeAddress;

public class TestPoi
{

    public static void main(String[] args)
    {
        try
        {
            // reportXls(getListmap());
            reportMergeXls(getListmap(), "test2", "sunck");
            System.out.println("导出完成");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

    }

    /**
     * 模拟数据库数据集合
     * @return
     */
    @SuppressWarnings({ "unchecked", "rawtypes"
    }) public static List<Map> getListmap()
    {
        List<Map> list = new ArrayList<Map>();
        Map map1 = new HashMap();
        map1.put("行", 1);
        map1.put("仓库名称", "a");
        map1.put("商品编号", "1a");
        map1.put("商品名称", "2a");
        map1.put("规格型号", "3a");
        map1.put("小单位数量", "3a");
        map1.put("成本价格", "3a");
        map1.put("成本金额", "3a");

        Map map2 = new HashMap();
        map2.put("行", 2);
        map2.put("仓库名称", "a");
        map2.put("商品编号", "1a");
        map2.put("商品名称", "2a");
        map2.put("规格型号", "3a");
        map2.put("小单位数量", "3a");
        map2.put("成本价格", "3a");
        map2.put("成本金额", "3a");

        Map map3 = new HashMap();
        map3.put("行", 3);
        map3.put("仓库名称", "a");
        map3.put("商品编号", "1a");
        map3.put("商品名称", "2a");
        map3.put("规格型号", "3a");
        map3.put("小单位数量", "3a");
        map3.put("成本价格", "3a");
        map3.put("成本金额", "3a");

        list.add(map1);
        list.add(map2);
        list.add(map3);

        return list;

    }

    /**
     * 模拟表头1
     */
    static String[] excelHeader = { "行", "仓库名称", "商品编号", "商品编号", "商品名称", "规格型号", "今日库存", "今日库存", "今日库存", "今日库存"
    };

    /**
     * 模拟表头2
     */
    static String[] excelHeader2 = { "行", "仓库名称", "商品编号", "商品编号", "商品名称", "规格型号", "小单位数量", "小单位数量", "成本价格", "成本金额"
    };

    @SuppressWarnings("rawtypes")
    public static void reportXls(List<Map> list)
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet("test1");// 创建一个sheet-test1

        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFRow row = sheet.createRow((int) 0);
        for (int i = 0; i < excelHeader.length; i++)
        {// 设置表头-标题
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);// 自动设宽
        }

        for (int i = 0; i < list.size(); i++)
        {// 设置列值-内容
            row = sheet.createRow(i + 1);
            Map map = list.get(i);
            row.createCell(0).setCellValue((int) map.get("id"));
            row.createCell(1).setCellValue((String) map.get("name"));
            row.createCell(2).setCellValue((String) map.get("value1"));
            row.createCell(3).setCellValue((String) map.get("value2"));
            row.createCell(4).setCellValue((String) map.get("value3"));
        }

        try
        {
            FileOutputStream fileOut = new FileOutputStream("E:/test.xls");
            wb.write(fileOut);
            fileOut.close();
            System.out.println("输出完成");
        }
        catch (Exception e)
        {
            System.out.println("文件输出失败!");
            e.printStackTrace();
        }

    }

    /**
     * 导出xls文件(合并单元格) 
     * @param list
     * @param xlsFileName
     * @param sheetName
     */
    @SuppressWarnings("rawtypes")
    public static void reportMergeXls(List<Map> list, String xlsFileName,
            String sheetName) throws Exception
    {
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet(sheetName);// 创建一个sheet

        // 设置单元格风格，居中对齐。
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFCellStyle cs1 = wb.createCellStyle();
        cs1.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        HSSFCellStyle cs4 = wb.createCellStyle();
        cs4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cs4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFCellStyle cs5 = wb.createCellStyle();
        cs5.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cs5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cs5.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        cs5.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        cs5.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        cs5.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

        // 设置字体:
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 15);// 设置字体大小
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
        HSSFFont font1 = wb.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 11);// 设置字体大小
        HSSFFont font2 = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 11);// 设置字体大小
        font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示

        cs.setFont(font);// 要用到的字体格式
        cs1.setFont(font1);// 要用到的字体格式
        cs2.setFont(font2);// 要用到的字体格式
        cs4.setFont(font2);// 要用到的字体格式
        cs5.setFont(font1);// 要用到的字体格式

        // sheet.setColumnWidth(0, 3766); //第一个参数代表列下标(从0开始),第2个参数代表宽度值
        // cs.setWrapText(true);//设置字体超出宽度自动换行

        // 设置背景颜色
        // cs.setFillBackgroundColor(HSSFColor.BLUE.index);
        // cs.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);

        // 创建第一行
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;

        // 设置表头-标题
        cell = row.createCell(0);
        cell.setCellValue("sunck老板商贸有限公司库存报表-商品明细");
        cell.setCellStyle(cs);
        cell = row.createCell(10);
        cell.setCellValue("错误信息");
        cell.setCellStyle(cs);
        sheet.setColumnWidth(0, "unck老板商贸有限公司库存报表-商品明细".getBytes().length * 2 * 256);// 自动设宽
        sheet.setColumnWidth(10, "错误信息".getBytes().length * 2 * 256);// 自动设宽

        // 创建第二行
        row = sheet.createRow(1);
        // 设置表头-标题
        cell = row.createCell(0);
        cell.setCellValue("库存日期：2018-09-06 | 仓库：主仓库 ");
        cell.setCellStyle(cs1);
        sheet.setColumnWidth(0, "库存日期：2018-09-06 | 仓库：主仓库 ".getBytes().length * 2 * 256);// 自动设宽

        // 创建第三行
        row = sheet.createRow(2);
        for (int i = 0; i < excelHeader.length; i++)
        {// 设置表头-标题
            cell = row.createCell(i);
            cell.setCellValue(excelHeader[i]);
            cell.setCellStyle(cs2);
            if (i == 2 || i == 3)
            {
                sheet.setColumnWidth(i, excelHeader[i].getBytes().length * 128);// 自动设宽
            }
            else
            {
                sheet.setColumnWidth(i, excelHeader[i].getBytes().length * 2 * 256);// 自动设宽
            }
        }

        // 创建第四行
        row = sheet.createRow(3);
        for (int j = 0; j < excelHeader2.length; j++)
        {
            cell = row.createCell(j);
            cell.setCellStyle(cs2);
            cell.setCellValue(excelHeader2[j]);
            if (j == 2 || j == 3)
            {
                sheet.setColumnWidth(j, excelHeader[j].getBytes().length * 128);// 自动设宽
            }
            else if (j == 6 || j == 7)
            {
                sheet.setColumnWidth(j, excelHeader[j].getBytes().length * 256);// 自动设宽
            }
            else
            {
                sheet.setColumnWidth(j, excelHeader[j].getBytes().length * 2 * 256);// 自动设宽
            }
        }

        // 创建第五行
        row = sheet.createRow(4);
        for (int j = 3; j < 6; j++)
        {
            cell = row.createCell(j);
            cell.setCellStyle(cs4);
            cell.setCellValue("主仓库库存明细列表");
            sheet.setColumnWidth(j, "主仓库库存明细列表".getBytes().length * 128);// 自动设宽
        }

        // 设置合并的标题头(注意：横向合并的时候，标题头单元格必须长度和内容单元格一致否则合并时会把其他标题头单元格内容挤掉)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));// 横向：合并第一行的第1列到第10列
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 9));// 横向：合并第二行的第1列到第10列
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 3));// 横向：合并第三行的第3列到第4列
        sheet.addMergedRegion(new CellRangeAddress(2, 2, 6, 9));// 横向：合并第三行的第7列到第10列
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 3));// 横向：合并第四行的第3列到第4列
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 6, 7));// 横向：合并第四行的第7列到第8列
        sheet.addMergedRegion(new CellRangeAddress(4, 4, 3, 6));// 横向：合并第五行的第4列到第7列
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));// 纵向：合并第一列的第3行和第4行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));// 纵向：合并第二列的第3行和第4行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));// 纵向：合并第三列的第3行和第4行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));// 纵向：合并第四列的第3行和第4行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 4, 4));// 纵向：合并第五列的第3行和第4行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 5, 5));// 纵向：合并第六列的第3行和第4行

        // 加错误信息
        ExcelImportResult result = new ExcelImportResult();
        // 错误行数据
        List<XRow> errorList = result.getErrorList();
        // 错误行提示信息
        Map<Integer, List<String>> errorInfoMap = result.getErrorInfoMap();
        // 设置列值-内容
        for (int i = 0; i < list.size(); i++)
        {
            row = sheet.createRow(i + 5);
            Map map = list.get(i);
            for (int j = 0; j < excelHeader2.length; j++)
            {
                cell = row.createCell(j);
                if (j == 0)
                {
                    cell.setCellValue((int) map.get(excelHeader2[j]));
                }
                else
                {
                    cell.setCellValue((String) map.get(excelHeader2[j]));
                }
                cell.setCellStyle(cs5);
            }
            sheet.addMergedRegion(new CellRangeAddress(i + 5, i + 5, 2, 3));// 横向：合并第i+6行的第3列到第4列
            sheet.addMergedRegion(new CellRangeAddress(i + 5, i + 5, 6, 7));// 横向：合并第i+6行的第7列到第8列
            for (int x = 0; x < errorList.size(); x++)
            {
                XRow errorRow = errorList.get(x);
                int rowIndex = errorRow.getRowIndex();
                if (rowIndex == i + 5)
                {
                    cell = row.createCell(10);
                    cell.setCellValue(StringUtils.join(errorInfoMap.get(errorRow.getRowIndex()), "；"));
                    cell.setCellStyle(cs1);
                    sheet.setColumnWidth(10,
                            StringUtils.join(errorInfoMap.get(errorRow.getRowIndex()), "；").getBytes().length * 256);
                }
            }
            // 模拟测试加错误信息
            int rowIndex = 5;
            if (rowIndex == i + 5)
            {
                cell = row.createCell(10);
                cell.setCellValue("此行存在错误信息；ssssss");
                cell.setCellStyle(cs1);
                sheet.setColumnWidth(10, "此行存在错误信息；ssssss".getBytes().length * 256);
            }
        }

        // 加合计行
        int dataLen = list.size();
        row = sheet.createRow(5 + dataLen);
        cell = row.createCell(0);
        cell.setCellValue("主仓库合计");
        cell.setCellStyle(cs2);
        sheet.setColumnWidth(0, "主仓库合计".getBytes().length * 2 * 256);
        cell = row.createCell(6);
        cell.setCellValue("rrrr");
        cell.setCellStyle(cs4);
        sheet.addMergedRegion(new CellRangeAddress(5 + dataLen, 5 + dataLen, 6, 7));// 横向：合并第4+dataLen行的第7列到第8列
        cell = row.createCell(9);
        cell.setCellValue("bbbb");
        cell.setCellStyle(cs4);
        row = sheet.createRow(6 + dataLen);
        cell = row.createCell(0);
        cell.setCellValue("总合计");
        cell.setCellStyle(cs2);
        sheet.setColumnWidth(0, "总合计".getBytes().length * 2 * 256);
        cell = row.createCell(6);
        cell.setCellValue("rrrr");
        cell.setCellStyle(cs4);
        sheet.addMergedRegion(new CellRangeAddress(6 + dataLen, 6 + dataLen, 6, 7));// 横向：合并第5+dataLen行的第7列到第8列
        cell = row.createCell(9);
        cell.setCellValue("bbbb");
        cell.setCellStyle(cs4);

        try
        {
            FileOutputStream fileOut = new FileOutputStream("E:/code_test_local/" + xlsFileName + ".xls");
            wb.write(fileOut);
            fileOut.close();
            System.out.println("输出完成");
        }
        catch (Exception e)
        {
            System.out.println("文件输出失败!");
            e.printStackTrace();
        }

    }
}

package poi;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 导入返回类
 */
public class ExcelImportResult
{

    /**
     * 标题行
     */
    private XRow headRow;

    /**
     * 错误结果集
     */
    private List<XRow> errorList = new ArrayList<XRow>();

    /**
     * 成功的行数
     */
    private Integer successTotal = 0;

    /**
     * 错误行的错误信息
     */
    private Map<Integer, List<String>> errorInfoMap = new HashMap<Integer, List<String>>();

    public List<XRow> getErrorList()
    {
        return errorList;
    }

    public void addErrorRow(XRow row)
    {
        this.errorList.add(row);
    }

    public void addErrorRow(XRow row, String errorInfo)
    {
        this.errorList.add(row);
        List<String> errList = errorInfoMap.get(row.getRowIndex());
        if (errList == null)
        {
            errList = new ArrayList<String>();
        }
        errList.add(errorInfo);
        errorInfoMap.put(row.getRowIndex(), errList);
    }

    public Integer getSuccessTotal()
    {
        return successTotal;
    }

    public void addSuccessTotal(Integer successCount)
    {
        this.successTotal += successCount;
    }

    public Integer getFailedTotal()
    {
        return errorList.size();
    }

    public Map<Integer, List<String>> getErrorInfoMap()
    {
        return errorInfoMap;
    }

    public void addErrorInfo(int rowIndex, String errorInfo)
    {
        List<String> errList = errorInfoMap.get(rowIndex);
        if (errList == null)
        {
            errList = new ArrayList<String>();
        }
        errList.add(errorInfo);
        errorInfoMap.put(rowIndex, errList);
    }

    public XRow getHeadRow()
    {
        return headRow;
    }

    public void setHeadRow(XRow headRow)
    {
        this.headRow = headRow;
    }

}

package poi;

/**
 * Excel单元格对象封装
 */
public class XCell
{
    private int rowIndex;
    private int columnIndex;
    private String value;

    public int getRowIndex()
    {
        return rowIndex;
    }

    public void setRowIndex(int rowIndex)
    {
        this.rowIndex = rowIndex;
    }

    public int getColumnIndex()
    {
        return columnIndex;
    }

    public void setColumnIndex(int columnIndex)
    {
        this.columnIndex = columnIndex;
    }

    public String getValue()
    {
        return value;
    }

    public void setValue(String value)
    {
        this.value = value;
    }
}

package poi;

import java.util.ArrayList;
import java.util.List;

/**
 * Excel行对象封装
 */
public class XRow
{
    private int rowIndex;
    private List<XCell> cells = new ArrayList<XCell>();

    public int getRowIndex()
    {
        return rowIndex;
    }

    public void setRowIndex(int rowIndex)
    {
        this.rowIndex = rowIndex;
    }

    public int getCellsSize()
    {
        return cells.size();
    }

    public XRow addCell(XCell cell)
    {
        this.cells.add(cell);
        return this;
    }

    public XCell getCell(int cellIndex)
    {
        return cells.get(cellIndex);
    }
}