我所知道报表之POI百万数据导入测试、分析、原理、解决、总结

一、传统方式上进行百万导入

---

根据前面我们的方式解决百万导出，那么就要做百万次循环

//获取数据
private static Object getValue(Cell cell) {
    Object value = null;
    switch (cell.getCellType()) {
        case STRING: //字符串类型
            value = cell.getStringCellValue();
            break;
        case BOOLEAN: //boolean类型
            value = cell.getBooleanCellValue();
            break;
        case NUMERIC: //数字类型（包含日期和普通数字）
            if(DateUtil.isCellDateFormatted(cell)) {
                value = cell.getDateCellValue();
            }else{
                value = cell.getNumericCellValue();
            }
            break;
        case FORMULA: //公式类型
            value = cell.getCellFormula();
            break;
        default:
            break;
    }
    return value;
}

//1.根据上传流信息创建工作簿
Workbook workbook = WorkbookFactory.create(attachment.getInputStream());
//2.获取第一个sheet
Sheet sheet = workbook.getSheetAt(0);
List<User> users = new ArrayList<>();
//3.从第二行开始获取数据
for (int rowNum = 1; rowNum <sheet.getLastRowNum(); rowNum++) {
    //获取行对象
    Row row = sheet.getRow(rowNum);
    //获取该行的所有列单元格数量
    Object objs[] = new Object[row.getLastCellNum()];
    //从第二列获取数据
    for(int cellNum = 0; cellNum < row.getLastCellNum();cellNum++) {
        Cell cell = row.getCell(cellNum);
        objs[cellNum] = getValue(cell);
    }
    //根据每一列构造用户对象
    User user = new User(objs);
    users.add(user);
}
//第一个参数：用户列表，第二个参数：部门编码
userService.save(users);
return Result.SUCCESS();

这种方式去执行百万数据的读取，那么我们在Jvisualvm看看效率

观看如图所示，我们发现会不断的占用内存，直到吃满跑出oom异常

那么为什么会这样呢？

二、传统方式上问题分析

---

那么为什么会占用那么多内存呢？

加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容。

当Excel数据量较大时，由于不同的运行环境可能会造成内存不足甚至OOM异常

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

• 用户模式：用户模式有许多封装好的方法操作简单，但创建太多的对象，非常耗内存（之前使用的方法）
• 事件模式：基于SAX方式解析XML，SAX全称Simple API for XML，它是一个接口，也是一个软件包。它是一种XML解析的替代方法，不同于DOM解析XML文档时把所有内容一次性加载到内存中的方式，它逐行扫描文档，一边扫描，一边解析。
• SXSSF对象：是用来生成海量excel数据文件，主要原理是借助临时存储空间生成excel

Apache POI官方提供有一张图片，描述了基于用户模式，事件模式，以及使用SXSSF三种方式操作Excel的特性以及CUP和内存占用情况

三、解决思路分析

---

我们刚刚进行问题分析知道当我们加载并读取Excel时，是通过一次性的将所有数据加载到内存中再去解析每个单元格内容

当百万数据级别的Excel导入时，随着单元格的不断读取，内存中对象越来越多，直至内存溢出。

上面提到POI提供刚给excel大数据时读取有两种模式：
1.用户模式：使用系列封装好的API操作Excel，使得操作简单但占用内存
2.事件驱动：基于sax的读取方式，逐行扫描文档，一边扫描，一边解析

那么为什么使用事件驱动就可以解决这个问题呢？

事件驱动原理解析

事件驱动一般来说会有一个事件监听的主线程，而所有要处理的事件则需要注册意思指发生某件事情的时候，委托给事件处理器进行处理

比如说：有一个事件处理器当每个月发工资时准时存款一半工资

我们刚刚提到过SAX是基于事件驱动的一种xml解析方案

那么事件处理器在解析excel的时候是如何去使用的呢？

其实就是指定xml里的一些节点，在指定的节点解析后触发事件

按照这样的思路方法：

我们在解析excel时每一行完成后进行触发事件，事件做的事情就是将解析的当前行的内存进行销毁

这样我们进行百万数据处理的时候，则处理完一行就释放一行

但是也有缺点：因为我们是处理完一行就释放一行，则用完就销毁了不可在用

四、使用事件驱动来优化传统方式

---

那么根据SAX事件驱动进行读取数据主要分几种步骤

1.设置POI的事件模式，指定使用事件驱动去解析EXCEL来做

• 根据Excel 获取文件流
• 根据文件流创建OPCPackage
• 创建XSSFReader对象

2.使用Sax进行解析

• 自定义Sheet处理器
• 创建Sax的XMlReader对象
• 设置Sheet的事件处理器
• 逐行读取

接下来我们根据步骤思路，去实现百万数据的读取，首先我们将excel对应的数据实体类创建

public class PoiEntity {
    
    private String id;
    private String name;
    private String tel;
    
    //省略get、set方法
}

接着创建我们的自定义Sheet基于Sax的解析处理器SheetHandler

/**
 *    自定义Sheet基于Sax的解析处理器
 *    处理每一行数据读取
 *    实现接口org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler
 */
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    //封装实体对象
    private PoiEntity entity;

    /**
     * 解析行开始
     */
    @Override
    public void startRow(int rowNum) {
        if (rowNum >0 ) {
            entity = new PoiEntity();
       }
   }
    
    /**
     * 解析每一个单元格
     * cellReference       单元格名称
     * formattedValue      单元格数据值
     * comment             单元格批注
     */
     @Override
    public void cell(String cellReference, String formattedValue, XSSFComment comment){
        if(entity != null) {
            //因为单元格名称比较长，所以截取首字母
            switch (cellReference.substring(0, 1)) {
                case "A":
                    entity.setId(formattedValue);
                    break;
                case "B":
                    entity.setName(formattedValue);
                    break;
                case "C":
                    entity.setTel(formattedValue);
                    break;
                default:
                    break;
           }
       }
    }

    /**
     * 解析每一行结束时触发
     */
    public void endRow(int rowNum) {
        System.out.println(entity);
        //一般进行使用对象进行业务处理.....
   }
}   

接下来我们使用事件模型来解析百万数据excel报表

//excel文档路径
String path = "e:\\demo.xlsx";

//============设置POI的事件模式，指定使用事件驱动去解析EXCEL来做============
//1.根据Excel获取OPCPackage对象
OPCPackage pkg = OPCPackage.open(path, PackageAccess.READ);

//2.创建XSSFReader对象
XSSFReader reader = new XSSFReader(pkg);

//3.获取String类型表格SharedStringsTable对象
SharedStringsTable sst = reader.getSharedStringsTable();

//4.获取样式表格StylesTable对象
StylesTable styles = reader.getStylesTable();

//============使用Sax进行解析============
//5.创建Sax的XmlReader对象
XMLReader parser = XMLReaderFactory.createXMLReader();

//6.设置Sheet的事件处理器
parser.setContentHandler(new XSSFSheetXMLHandler(styles,sst,new SheetHandler(), false));

//7.逐行读取(因为有多个sheet所以需要迭代读取)
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator)reader.getSheetsData();
while (sheets.hasNext()) {
    InputStream sheetstream = sheets.next();//每一个sheet的数据流
    InputSource sheetSource = new InputSource(sheetstream);
    try {
        parser.parse(sheetSource);
   } finally {
        sheetstream.close();
   }
}

让我们使用Jvisualvm 看看事件驱动的方式是什么效率吧

通过简单的分析以及运行两种模式进行比较，可以看到用户模式下使用更简单的代码实现了Excel读取。

但是在读取大文件时CPU和内存都不理想；而事件模式虽然代码写起来比较繁琐，但是在读取大文件时CPU和内存更加占优。