使用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、文件表头

 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

4、总结

对于导出数据量不大的情况下,推荐使用HSSF导出xls文件,效率上更高一些。对于导出大数据量的场景,推荐使用SXSSF,加强版的SXSSF导出效率比XSSF高出很多,但是会产生临时文件,记得用dispose()清除生成的临时文件。

参考资料

1、https://blog.csdn.net/qq_44413835/article/details/124174996

2、cnblogs.com/swordfall/p/8298386.html

  • 2
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值