使用POI导出数据以及性能比较-Toy模板网

这篇具有很好参考价值的文章主要介绍了使用POI导出数据以及性能比较。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1、背景

工作中难免会遇到一些导入导出数据的需求，寻找一个好用的工具是很重要的，找一款合适的工具要考虑多种因素，比如导出效率、导出过程占用内存的大小。由于Apache POI在使用过程中问题比较多，所以个人不推荐使用，常见好用的工具有EasyPOI、EasyExcel。这二者都是基于Apache POI实现的，所以本文先从Apache POI进行讲解。

2、POI介绍

2.1、POI概念

poi其英文全称“Poor Obfuscation Implementation”，意思是“简易的模糊实现”，它是Apache提供的一款免费开源跨平台的Java API，其提供的API应用于Java程序，实现对Microsoft Office格式的文档读和写功能。

官网地址

2.2、POI相关API

poi的几个重要的API：

HSSF －提供读写Microsoft Excel格式档案的功能(.xls后缀)。优点是过程中写入缓存，不操作磁盘，最后一次性写入磁盘，导出数据速度快，但是导出数据最大行数是65536行，最大列数是256列。

XSSF －提供读写Microsoft Excel OOXML格式档案的功能(.xlsx后缀)。XSSF支持的2007版的xlsx文件是基于XML的，因此处理它们的内存占用比HSSF支持的2003版的xls文件(基于二进制文件)要高。优点是可以导出较大的数据量，缺点导出速度慢，非常消耗内存，也会发生内存溢出，如100万条数据。

SXSSF - 是 XSSF API的兼容流式扩展，主要解决当使用 XSSF 方式导出大数据量时，内存溢出的问题，采用缓存方式进行大批量写文件。优点是可以写较大的数据量，写数据速度快，占用更少的内存。

扩展说明：

（1）过程中会产生临时文件，需要清理临时文件

（2）默认有100条记录被保存在内存中，如果超过这个数量，则最前面的数据被写入临时文件

（3）如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook（数量）

HWPF(.doc后缀)－提供读写Microsoft Word格式档案的功能(XWPF(.docx后缀)：Microsoft Word OOXML格式)。

HSLF(.ppt后缀) －提供读写Microsoft PowerPoint格式档案的功能(XSLF(.pptx后缀)：Microsoft Word OOXML格式)。

HDGF(.vsd后缀) －提供读写Microsoft Visio格式档案的功能(XDGF(.vsdx后缀)：Microsoft Word OOXML格式)。

2.3、Excel2003与Excel2007

两个版本的最大行数和列数不同，2003版最大行数是65536行，最大列数是256列，2007版及以后的版本最大行数是1048576行，最大列数是16384列。
excel2003是以二进制的方式存储，这种格式不易被其他软件读取使用；而excel2007采用了基于XML的ooxml开放文档标准，ooxml使用XML和ZIP技术结合进行文件存储，XML是一个基于文本的格式，而且ZIP容器支持内容的压缩，所以其一大优势是可以大大减小文件的尺寸。

2.3、POI读取excel的两种模式

一种是userModel，即用户模式，一种是eventModel，即sax事件驱动模式。

（1）userModel

用户模式的API接口丰富，平时使用最多的像用的HSSFWorkBook、XSSFWorkBook、SXSSFWorkBook。但是这种模式消耗内存很大，当遇到很大sheet、大数据网格、假空行、公式等问题时，很容易导致内存溢出。

POI官方推荐解决内存溢出的方式使用CVS格式解析，即SAX事件驱动模式。

（2）eventModel

这种模式将xlsx格式的文档转换成CSV格式后进行读取。

3、POI实现Excel文件导出

3.1、操作流程

（1）创建HSSFWorkbook/XSSFWorkbook/SXSSFWorkbook对象，一个excel对应一个workbook。

（2）用实例化好的Workbook创建sheet对象（一个workbook中有多个Sheet组成）。

（3）用实例化好的Sheet对象创建Row(行)对象，用Row对象创建Cell(单元格)对象（一个sheet是由多个行(row)和列(cell)组成）。

（4）对创建的cell实例对象设置要导出的数据。

（5）将生成的HSSFWorkbook放入HttpServletResponse中响应到前端页面。

3.2、文件表头

poi导出,文件相关,java,excel导出

3.3、代码实现

3.3.1、pom核心依赖

最新版本是5.2.2，建议不要用最新的，容易引起jar包不兼容的问题。

<!--poi-->
<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

3.3.2、实现代码

【03版本xls】测试导出

@GetMapping("/export03xls")
public void export03xls(HttpServletResponse response) throws IOException {
    String fname = "【03版本xls】-测试导出";
    fname = new String(fname.getBytes("GBK"), "iso8859-1");
    OutputStream os = response.getOutputStream();
    response.reset();
    response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls");
    response.setCharacterEncoding("utf-8");
    response.setContentType("application/msexcel");

    long begin = System.currentTimeMillis();
    exportData(new HSSFWorkbook(), os);
    long end = System.currentTimeMillis();
    System.out.println("03版本时间(秒):" + ((double) (end - begin) / 1000));
}

【07版本xlxs】测试导出

@GetMapping("/export07xlsx")
public void export07xlsx(HttpServletResponse response) throws IOException {
    String fname = "【07版本xlxs】 测试导出";
    fname = new String(fname.getBytes("GBK"), "iso8859-1");
    OutputStream os = response.getOutputStream();
    response.reset();
    response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
    response.setCharacterEncoding("utf-8");
    response.setContentType("application/msexcel");

    long begin = System.currentTimeMillis();
    exportData(new XSSFWorkbook(), os);
    long end = System.currentTimeMillis();
    System.out.println("07版本时间(秒):" + ((double) (end - begin) / 1000));
}

【07版本-加强版xlxs】测试导出

@GetMapping("/export07Plusxlsx")
public void export07Plusxlsx(HttpServletResponse response) throws IOException {
    String fname = "【07版本-加强版xlxs】测试导出";
    fname = new String(fname.getBytes("GBK"), "iso8859-1");
    OutputStream os = response.getOutputStream();
    response.reset();
    response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
    response.setCharacterEncoding("utf-8");
    response.setContentType("application/msexcel");

    long begin = System.currentTimeMillis();
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    exportData(workbook, os);
    //清除临时文件
    workbook.dispose();
    long end = System.currentTimeMillis();
    System.out.println("07加强版时间(秒):" + ((double) (end - begin) / 1000));
}

公共方法-模拟导出数据

// 模拟数据导出
private void exportData(Workbook workbook, OutputStream os) throws IOException {
    //创建一个工作表
    Sheet sheet = workbook.createSheet("第一个sheet");
    CellStyle cellStyle = getCellStyle(workbook, sheet);

    /**
     *  遍历数据-创建单元格
     *  从第五行，第二列开始
     *  列不变，行增加
     */
    // 除去表头，数据从第5行开始。一共65536条数据
    Random random = new Random();
    for (int rowNumber = 4; rowNumber < 65536; rowNumber++) {
        Row sheetRow5 = sheet.createRow(rowNumber);//创建行--创建新行会覆盖旧行
        //创建单元格
        Cell cell5_0 = sheetRow5.createCell(0);//地点
        Cell cell5_1 = sheetRow5.createCell(1);//男
        Cell cell5_2 = sheetRow5.createCell(2);//女
        Cell cell5_3 = sheetRow5.createCell(3);//总数
        //样式
        cell5_0.setCellStyle(cellStyle);
        cell5_1.setCellStyle(cellStyle);
        cell5_2.setCellStyle(cellStyle);
        cell5_3.setCellStyle(cellStyle);
        //赋值
        cell5_0.setCellValue("公司" + (rowNumber - 4));
        int manNum = random.nextInt(100);
        cell5_1.setCellValue(manNum);
        int womanNum = random.nextInt(100);
        cell5_2.setCellValue(womanNum);
        cell5_3.setCellValue(manNum + womanNum);
    }
    try {
        workbook.write(os);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        //关闭资源
        os.close();
    }
}

公共方法-设置表格样式

// 设置表格样式
private CellStyle getCellStyle(Workbook workbook, Sheet sheet) {
    /**
     * 设置页边距
     * 打印前的页边距设置【设置上下左右】
     * 可以用打印预览查看
     */
    sheet.setMargin(HSSFSheet.BottomMargin, 0.1);
    sheet.setMargin(HSSFSheet.LeftMargin, 0.1);
    sheet.setMargin(HSSFSheet.RightMargin, 0.1);
    sheet.setMargin(HSSFSheet.TopMargin, 0.1);
    sheet.setHorizontallyCenter(true);//是否在页面上水平居中
    sheet.setVerticallyCenter(false);//是否在页面上垂直居中

    /**
     * 设置列宽-列从0开始
     * 设置约为15个英文字符的宽度【15*256】
     */
    sheet.setColumnWidth(0, 15 * 256);
    sheet.setColumnWidth(1, 15 * 256);
    sheet.setColumnWidth(2, 15 * 256);
    sheet.setColumnWidth(3, 15 * 256);

    /**
     * 定义字体
     */
    Font font = workbook.createFont();
    font.setColor(HSSFFont.COLOR_RED);
    font.setFontName("黑体");


    /**
     *  表头单元格的样式
     */
    CellStyle titleStyle = workbook.createCellStyle(); //创建一个样式
    titleStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
    titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
    titleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); //设置背景色-天蓝色
    titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充颜色的模式-实心
    titleStyle.setFont(font);

    /**
     *  定义其他单元格的样式
     */
    CellStyle cellStyle = workbook.createCellStyle(); //创建一个样式
    cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
    cellStyle.setBorderBottom(BorderStyle.MEDIUM); //底部边框-中等边框
    cellStyle.setBorderLeft(BorderStyle.MEDIUM); //左边框
    cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框

    cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());//顶部边框颜色
    cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//底部边框颜色
    cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);//左边框颜色
    cellStyle.setRightBorderColor(IndexedColors.BLACK.index);//右边框颜色

    cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;

    cellStyle.setFont(font);//使用字体


    /**
     * 创建4行
     * 从0开始算
     * 创建出固定需要的行,用于设计表格格式
     */
    Row row1 = sheet.createRow(0);
    Row row3 = sheet.createRow(2);
    Row row4 = sheet.createRow(3);


    /**
     *  合并单元格
     *  4个参数【起始行，结束行，起始列，结束列】
     *  行和列都是从0开始计数，且起始结束都会合并
     */
    sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 3));
    sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
    sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));


    /**
     * 创建单元格
     * 从0开始算
     */
    Cell cell1 = row1.createCell(0);
    cell1.setCellValue("统计分析");
    cell1.setCellStyle(titleStyle);


    Cell cell2 = row3.createCell(0);
    cell2.setCellValue("公司");
    cell2.setCellStyle(cellStyle);

    Cell cell3 = row3.createCell(1);
    cell3.setCellValue("性别");
    cell3.setCellStyle(cellStyle);

    Cell cell4 = row4.createCell(1);
    cell4.setCellValue("男");
    cell4.setCellStyle(cellStyle);

    Cell cell5 = row4.createCell(2);
    cell5.setCellValue("女");
    cell5.setCellStyle(cellStyle);

    Cell cell6 = row3.createCell(3);
    cell6.setCellValue("总人数");
    cell6.setCellStyle(cellStyle);
    row4.createCell(3).setCellStyle(cellStyle);//把边框补充完整
    return cellStyle;
}

3.3.3、使用三种方式导出测试结果

导出1000条数据

版本	测试10次用时时长(单位：秒)	平均用时时间(单位：秒)
03版本xls	0.089、0.012、0.013、0.012、0.012、0.012、0.012、0.021、0.013、0.013	0.0209
07版本xlxs	0.366、0.055、0.05、0.051、0.051、0.053、0.054、0.052、0.058、0.05	0.084
07版本-加强版xlxs	0.103、0.027、0.023、0.021、0.026、0.027、0.021、0.024、0.022、0.022	0.0316

导出65536条数据

版本	测试10次用时时长(单位：秒)	平均用时时间(单位：秒)
03版本xls	1.175、2.12、0.749、0.676、0.654、1.947、1.943、1.348、1.952、1.993	1.4557
07版本xlxs	2.711、2.486、2.537、2.481、2.558、2.431、2.548、2.6、2.233、2.268	2.4853
07版本-加强版xlxs	0.47、0.486、0.356、0.352、0.366、0.364、0.352、0.353、0.366、0.352	0.381