• 使用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包不兼容的问题。

    4.     org.apache.poi
    5.     poi
    6.     4.1.2
    10.     org.apache.poi
    11.     poi-ooxml
    12.     4.1.2

    3.3.2、实现代码

    【03版本xls】 测试导出

    1. @GetMapping("/export03xls")
    2. public void export03xls(HttpServletResponse response) throws IOException {
    3.     String fname = "【03版本xls】-测试导出";
    4.     fname = new String(fname.getBytes("GBK"), "iso8859-1");
    5.     OutputStream os = response.getOutputStream();
    6.     response.reset();
    7.     response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xls");
    8.     response.setCharacterEncoding("utf-8");
    9.     response.setContentType("application/msexcel");
    10.  
    11.     long begin = System.currentTimeMillis();
    12.     exportData(new HSSFWorkbook(), os);
    13.     long end = System.currentTimeMillis();
    14.     System.out.println("03版本时间(秒):" + ((double) (end - begin) / 1000));
    15. }

    【07版本xlxs】 测试导出

    1. @GetMapping("/export07xlsx")
    2. public void export07xlsx(HttpServletResponse response) throws IOException {
    3.     String fname = "【07版本xlxs】 测试导出";
    4.     fname = new String(fname.getBytes("GBK"), "iso8859-1");
    5.     OutputStream os = response.getOutputStream();
    6.     response.reset();
    7.     response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
    8.     response.setCharacterEncoding("utf-8");
    9.     response.setContentType("application/msexcel");
    10.  
    11.     long begin = System.currentTimeMillis();
    12.     exportData(new XSSFWorkbook(), os);
    13.     long end = System.currentTimeMillis();
    14.     System.out.println("07版本时间(秒):" + ((double) (end - begin) / 1000));
    15. }

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

    1. @GetMapping("/export07Plusxlsx")
    2. public void export07Plusxlsx(HttpServletResponse response) throws IOException {
    3.     String fname = "【07版本-加强版xlxs】测试导出";
    4.     fname = new String(fname.getBytes("GBK"), "iso8859-1");
    5.     OutputStream os = response.getOutputStream();
    6.     response.reset();
    7.     response.setHeader("Content-disposition", "attachment; filename=" + fname + ".xlsx");
    8.     response.setCharacterEncoding("utf-8");
    9.     response.setContentType("application/msexcel");
    10.  
    11.     long begin = System.currentTimeMillis();
    12.     SXSSFWorkbook workbook = new SXSSFWorkbook();
    13.     exportData(workbook, os);
    14.     //清除临时文件
    15.     workbook.dispose();
    16.     long end = System.currentTimeMillis();
    17.     System.out.println("07加强版时间(秒):" + ((double) (end - begin) / 1000));
    18. }

    公共方法-模拟导出数据

    1. // 模拟数据导出
    2. private void exportData(Workbook workbook, OutputStream os) throws IOException {
    3.     //创建一个工作表
    4.     Sheet sheet = workbook.createSheet("第一个sheet");
    5.     CellStyle cellStyle = getCellStyle(workbook, sheet);
    6.  
    7.     /**
    8.      *  遍历数据-创建单元格
    9.      *  从第五行，第二列开始
    10.      *  列不变，行增加
    11.      */
    12.     // 除去表头，数据从第5行开始。一共65536条数据
    13.     Random random = new Random();
    14.     for (int rowNumber = 4; rowNumber < 65536; rowNumber++) {
    15.         Row sheetRow5 = sheet.createRow(rowNumber);//创建行--创建新行会覆盖旧行
    16.         //创建单元格
    17.         Cell cell5_0 = sheetRow5.createCell(0);//地点
    18.         Cell cell5_1 = sheetRow5.createCell(1);//男
    19.         Cell cell5_2 = sheetRow5.createCell(2);//女
    20.         Cell cell5_3 = sheetRow5.createCell(3);//总数
    21.         //样式
    22.         cell5_0.setCellStyle(cellStyle);
    23.         cell5_1.setCellStyle(cellStyle);
    24.         cell5_2.setCellStyle(cellStyle);
    25.         cell5_3.setCellStyle(cellStyle);
    26.         //赋值
    27.         cell5_0.setCellValue("公司" + (rowNumber - 4));
    28.         int manNum = random.nextInt(100);
    29.         cell5_1.setCellValue(manNum);
    30.         int womanNum = random.nextInt(100);
    31.         cell5_2.setCellValue(womanNum);
    32.         cell5_3.setCellValue(manNum + womanNum);
    33.     }
    34.     try {
    35.         workbook.write(os);
    36.     } catch (IOException e) {
    37.         e.printStackTrace();
    38.     } finally {
    39.         //关闭资源
    40.         os.close();
    41.     }
    42. }

    公共方法-设置表格样式

    1. // 设置表格样式
    2. private CellStyle getCellStyle(Workbook workbook, Sheet sheet) {
    3.     /**
    4.      * 设置页边距
    5.      * 打印前的页边距设置【设置上下左右】
    6.      * 可以用打印预览查看
    7.      */
    8.     sheet.setMargin(HSSFSheet.BottomMargin, 0.1);
    9.     sheet.setMargin(HSSFSheet.LeftMargin, 0.1);
    10.     sheet.setMargin(HSSFSheet.RightMargin, 0.1);
    11.     sheet.setMargin(HSSFSheet.TopMargin, 0.1);
    12.     sheet.setHorizontallyCenter(true);//是否在页面上水平居中
    13.     sheet.setVerticallyCenter(false);//是否在页面上垂直居中
    14.  
    15.     /**
    16.      * 设置列宽-列从0开始
    17.      * 设置约为15个英文字符的宽度【15*256】
    18.      */
    19.     sheet.setColumnWidth(0, 15 * 256);
    20.     sheet.setColumnWidth(1, 15 * 256);
    21.     sheet.setColumnWidth(2, 15 * 256);
    22.     sheet.setColumnWidth(3, 15 * 256);
    23.  
    24.     /**
    25.      * 定义字体
    26.      */
    27.     Font font = workbook.createFont();
    28.     font.setColor(HSSFFont.COLOR_RED);
    29.     font.setFontName("黑体");
    30.  
    31.  
    32.     /**
    33.      *  表头单元格的样式
    34.      */
    35.     CellStyle titleStyle = workbook.createCellStyle(); //创建一个样式
    36.     titleStyle.setAlignment(HorizontalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
    37.     titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
    38.     titleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); //设置背景色-天蓝色
    39.     titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//填充颜色的模式-实心
    40.     titleStyle.setFont(font);
    41.  
    42.     /**
    43.      *  定义其他单元格的样式
    44.      */
    45.     CellStyle cellStyle = workbook.createCellStyle(); //创建一个样式
    46.     cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
    47.     cellStyle.setBorderBottom(BorderStyle.MEDIUM); //底部边框-中等边框
    48.     cellStyle.setBorderLeft(BorderStyle.MEDIUM); //左边框
    49.     cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
    50.  
    51.     cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());//顶部边框颜色
    52.     cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());//底部边框颜色
    53.     cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);//左边框颜色
    54.     cellStyle.setRightBorderColor(IndexedColors.BLACK.index);//右边框颜色
    55.  
    56.     cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平对齐的样式为居中对齐;
    57.     cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直对齐的样式为居中对齐;
    58.  
    59.     cellStyle.setFont(font);//使用字体
    60.  
    61.  
    62.     /**
    63.      * 创建4行
    64.      * 从0开始算
    65.      * 创建出固定需要的行,用于设计表格格式
    66.      */
    67.     Row row1 = sheet.createRow(0);
    68.     Row row3 = sheet.createRow(2);
    69.     Row row4 = sheet.createRow(3);
    70.  
    71.  
    72.     /**
    73.      *  合并单元格
    74.      *  4个参数【起始行，结束行，起始列，结束列】
    75.      *  行和列都是从0开始计数，且起始结束都会合并
    76.      */
    77.     sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 3));
    78.     sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
    79.     sheet.addMergedRegion(new CellRangeAddress(2, 2, 1, 2));
    80.     sheet.addMergedRegion(new CellRangeAddress(2, 3, 3, 3));
    81.  
    82.  
    83.     /**
    84.      * 创建单元格
    85.      * 从0开始算
    86.      */
    87.     Cell cell1 = row1.createCell(0);
    88.     cell1.setCellValue("统计分析");
    89.     cell1.setCellStyle(titleStyle);
    90.  
    91.  
    92.     Cell cell2 = row3.createCell(0);
    93.     cell2.setCellValue("公司");
    94.     cell2.setCellStyle(cellStyle);
    95.  
    96.     Cell cell3 = row3.createCell(1);
    97.     cell3.setCellValue("性别");
    98.     cell3.setCellStyle(cellStyle);
    99.  
    100.     Cell cell4 = row4.createCell(1);
    101.     cell4.setCellValue("男");
    102.     cell4.setCellStyle(cellStyle);
    103.  
    104.     Cell cell5 = row4.createCell(2);
    105.     cell5.setCellValue("女");
    106.     cell5.setCellStyle(cellStyle);
    107.  
    108.     Cell cell6 = row3.createCell(3);
    109.     cell6.setCellValue("总人数");
    110.     cell6.setCellStyle(cellStyle);
    111.     row4.createCell(3).setCellStyle(cellStyle);//把边框补充完整
    112.     return cellStyle;
    113. }

    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

  • 相关阅读:
    今天的码农女孩学习了关于jQuery遍历节点、查询节点以及插件的知识
    氨基酸代谢：从基础到应用，揭示其在健康与疾病的角色
    k8s--基础--07--环境搭建--安装traefik
    华为路由器即做ipsec vpn又能上互联网
    使用jmeter压测nginx支持的最大长连接数
    【QT】Osg开发（一）-- 环境配置
    牛客刷题＜18＞3-8译码器
    Day56-59 进程的状态、进程控制、进程通信方式
    OFDM Tutorial Series-1: 线性分组码
    【数据结构】树与森林
  • 原文地址：https://blog.csdn.net/weimenglala/article/details/126575834