Java POI 百万规模数据的导入和导出

目录

• 1、百万数据导入 💻
• • 1.1 需求分析 🐧
  • 1.2 思路分析 📹
  • 1.3 代码实现 📓
  • • 1.3.1 步骤分析 🎨
    • 1.3.2 自定义处理器 🔊
    • 1.3.3 自定义解析 📖
    • 1.3.4 测试 🌍
• 2、百万数据导出 🎯
• • 2.1、概述 💂
  • 2.2、解决方案分析 🚄
  • 2.3、原理分析 🏡
  • 2.4、百万数据的导出 📱
  • • 2.4.1、模拟数据 ⭐️
    • 2.4.2、思路分析 ☘️
    • 2.4.3、代码实现 📡
    • 2.4.4、测试结果 🚀

1、百万数据导入 💻

1.1 需求分析 🐧

使用POI基于事件模式解析案例提供的excel文件

1.2 思路分析 📹

**用户模式：**加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常。

例如读取我们刚刚导出的百万数据：

package com.itheima.test;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;//测试百万数据的导入public class POIDemo5 {    public static void main(String[] args) throws Exception {        XSSFWorkbook workbook = new XSSFWorkbook("C:\\Users\\syl\\Desktop\\百万用户数据的导出.xlsx");        XSSFSheet sheetAt = workbook.getSheetAt(0);        String strinGCellValue = sheetAt.getRow(0).getCell(0).getStringCellValue();        System.out.println(stringCellValue);    }}

会直接报内存溢出的错误：

事件模式： 它逐行扫描文档，一边扫描一边解析。由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。

1.3 代码实现 📓

1.3.1 步骤分析 🎨

（1）设置POI的事件模式
根据Excel获取文件流
根据文件流创建OPCPackage 用来组合读取到的xml 组合出来的数据占用的空间更小
创建XSSFReader对象
（2）Sax解析
自定义Sheet处理器
创建Sax的XmlReader对象
设置Sheet的事件处理器
逐行读取

1.3.2 自定义处理器 🔊

package com.itheima.test;import com.itheima.pojo.User;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.usermodel.XSSFComment;public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {//    编号 用户名  手机号  入职日期 现住址    private User user=null;    @Override    public void startRow(int rowIndex) { //每一行的开始   rowIndex代表的是每一个sheet的行索引        if(rowIndex==0){            user = null;        }else{            user = new User();        }    }    @Override  //处理每一行的所有单元格    public void cell(String cellName, String cellValue, XSSFComment comment) {        if(user!=null){            String letter = cellName.substring(0, 1);  //每个单元名称的首字母 A  B  C            switch (letter){                case "A":{                    user.setId(Long.parseLong(cellValue));                    break;                }                case "B":{                    user.setUserName(cellValue);                    break;                }            }        }    }    @Override    public void endRow(int rowIndex) { //每一行的结束        if(rowIndex!=0){            System.out.println(user);        }    }}

1.3.3 自定义解析 📖

package com.itheima.test;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.openxml4j.opc.PackageAccess;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.model.SharedStringsTable;import org.apache.poi.xssf.model.StylesTable;import org.xml.sax.InputSource;import org.xml.sax.XMLReader;import org.xml.sax.helpers.XMLReaderFactory;import java.io.InputStream;public class ExcelParser {    public void parse (String path) throws Exception {        //1.根据Excel获取OPCPackage对象        OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);        try {            //2.创建XSSFReader对象            XSSFReader reader = new XSSFReader(pkg);            //3.获取SharedStringsTable对象            SharedStringsTable sst = reader.getSharedStringsTable();            //4.获取StylesTable对象            StylesTable styles = reader.getStylesTable();            XMLReader parser = XMLReaderFactory.createXMLReader();            // 处理公共属性：Sheet名，Sheet合并单元格            parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst, new SheetHandler(), false));            XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) reader.getSheetsData();            while (sheets.hasNext()) {                InputStream sheetstream = sheets.next();                InputSource sheetSource = new InputSource(sheetstream);                try {                    parser.parse(sheetSource);                } finally {                    sheetstream.close();                }            }        } finally {            pkg.close();        }    }}

1.3.4 测试 🌍

用户模式下读取测试Excel文件直接内存溢出，测试Excel文件映射到内存中还是占用了不少内存；事件模式下可以流畅的运行。

使用事件模型解析

public class POIDemo5 {    public static void main(String[] args) throws Exception{        new ExcelParser().parse("C:\\Users\\syl\\Desktop\\百万用户数据的导出.xlsx");    }}

2、百万数据导出 🎯

2.1、概述 💂

​ 我们都知道Excel可以分为早期的Excel2003版本（使用POI的HSSF对象操作）和Excel2007版本（使用POI的XSSF操作），两者对百万数据的支持如下：
​ Excel 2003：在POI中使用HSSF对象时，excel 2003最多只允许存储65536条数据，一般用来处理较少的数据量。这时对于百万级别数据，Excel肯定容纳不了。
​ Excel 2007：当POI升级到XSSF对象时，它可以直接支持excel2007以上版本，因为它采用ooxml格式。这时excel可以支持1048576条数据，单个sheet表就支持近百万条数据。但实际运行时还可能存在问题，原因是执行POI报表所产生的行对象，单元格对象，字体对象，他们都不会销毁，这就导致OOM的风险。

2.2、解决方案分析 🚄

​ 对于百万数据量的Excel导入导出，只讨论基于Excel2007的解决方法。在ApachePoi 官方提供了对操作大数据量的导入导出的工具和解决办法，操作Excel2007使用XSSF对象，可以分为三种模式：

java代码解析xml

dom4j:一次性加载xml文件再解析

SAX:逐行加载，逐行解析

**用户模式：**用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）

**事件模式：**基于SAX方式解析XML，SAX全称Simple api for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。

SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel

2.3、原理分析 🏡

在实例化SXSSFWorkBook这个对象时，可以指定在内存中所产生的POI导出相关对象的数量（默认100），一旦内存中的对象的个数达到这个指定值时，就将内存中的这些对象的内容写入到磁盘中（XML的文件格式），就可以将这些对象从内存中销毁，以后只要达到这个值，就会以类似的处理方式处理，直至Excel导出完成。

2.4、百万数据的导出 📱

2.4.1、模拟数据 ⭐️

第一步、创建表

CREATE TABLE `tb_user2` (  `id` bigint(20) NOT NULL  COMMENT '用户ID',  `user_name` varchar(100) DEFAULT NULL COMMENT '姓名',  `phone` varchar(15) DEFAULT NULL COMMENT '手机号',  `province` varchar(50) DEFAULT NULL COMMENT '省份',  `city` varchar(50) DEFAULT NULL COMMENT '城市',  `salary` int(10) DEFAULT NULL,  `hire_date` datetime DEFAULT NULL COMMENT '入职日期',  `dept_id` bigint(20) DEFAULT NULL COMMENT '部门编号',  `birthday` datetime DEFAULT NULL COMMENT '出生日期',  `photo` varchar(200) DEFAULT NULL COMMENT '照片路径',  `address` varchar(300) DEFAULT NULL COMMENT '现在住址' ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

第二步、创建存储过程，来插入数据

DELIMITER $$    -- 重新定义“;”分号DROP PROCEDURE IF EXISTS test_insert $$   -- 如果有test_insert这个存储过程就删除CREATE PROCEDURE test_insert()  -- 创建存储过程BEGIN   DECLARE n int DEFAULT 1;    -- 定义变量n=1   SET AUTOCOMMIT=0;    -- 取消自动提交      while n <= 5000000 do   INSERT INTO `tb_user2` VALUES ( n, CONCAT('测试', n), '13800000001', '北京市', '北京市', '11000', '2001-03-01 21:18:29', '1', '1981-03-02 00:00:00', '\\static\\user_photos\\1.jpg', '北京市西城区宣武大街1号院');   SET n=n+1;   END while;   COMMIT;END $$

第三步、在新建查询处，点击开始执行

CALL test_insert();

插入500W数据大概需要300至600秒左右（当然，这个要根据电脑的配置而定）

查看插入结果

2.4.2、思路分析 ☘️

导出时使用的是SXSSFWorkBook这个类，一个工作表sheet最多只能放1048576行数据， 当我们的业务数据已超过100万了，一个sheet就不够用了，必须拆分到多个工作表。

导出百万数据时有两个弊端：

不能使用模板

不能使用太多的样式

也就是说导出的数据太多时必须要放弃一些。

2.4.3、代码实现 📡

UserController代码

@GetMapping(value = "/downLoadMillion",name = "导出用户百万数据的导出")public void downLoadMillion(Long id,httpservletRequest request,HttpServletResponse response) throws Exception{    userService.downLoadMillion(request,response);}

UserService代码

public void downLoadMillion(HttpServletRequest request, HttpServletResponse response) throws Exception {//        创建一个空的工作薄        Workbook workbook = new SXSSFWorkbook();        int page = 1;        int pageSize=200000;        int rowIndex = 1; //每一个工作页的行数        int num = 0; //总数据量        Row row = null;        Cell cell = null;        Sheet sheet = null;        while (true){  //不停地查询             List<User> userList = this.findPage(page,pageSize);             if(CollectionUtils.isEmpty(userList)){  //如果查询不到就不再查询了                 break;             }            if(num%1000000==0){  //每100W个就重新创建新的sheet和标题                rowIndex = 1;                 //        在工作薄中创建一个工作表                 sheet = workbook.createSheet("第"+((num/1000000)+1)+"个工作表");//        设置列宽                 sheet.setColumnWidth(0,8*256);                 sheet.setColumnWidth(1,12*256);                 sheet.setColumnWidth(2,15*256);                 sheet.setColumnWidth(3,15*256);                 sheet.setColumnWidth(4,30*256);                 //            处理标题                 String[] titles = new String[]{"编号","姓名","手机号","入职日期","现住址"};                //        创建标题行                Row titleRow = sheet.createRow(0);                for (int i = 0; i < titles.length; i++) {                    cell = titleRow.createCell(i);                    cell.setCellValue(titles[i]);                }             }//        处理内容        for (User user : userList) {            row = sheet.createRow(rowIndex);            cell = row.createCell(0);            cell.setCellValue(user.getId());            cell = row.createCell(1);            cell.setCellValue(user.getUserName());            cell = row.createCell(2);            cell.setCellValue(user.getPhone());            cell = row.createCell(3);            cell.setCellValue(simpleDateFORMat.format(user.getHireDate()));            cell = row.createCell(4);            cell.setCellValue(user.getAddress());            rowIndex++;            num++;        }          page++;// 继续查询下一页        }//            导出的文件名称        String filename="百万数据.xlsx";//            设置文件的打开方式和mime类型        ServletOutputStream outputStream = response.getOutputStream();        response.setHeader( "Content-Disposition", "attachment;filename="  + new String(filename.getBytes(),"ISO8859-1"));        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");        workbook.write(outputStream);    }

2.4.4、测试结果 🚀

导出的这个文档大概需要3-5分钟的时间，有105 MB，内容如下

来源地址：https://blog.csdn.net/weixin_49171365/article/details/132088848