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

28640

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


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

//获取数据
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看看效率

image.png

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

那么为什么会这样呢?

二、传统方式上问题分析


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

image.png

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

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

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

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

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

image.png

三、解决思路分析


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

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

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

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

事件驱动原理解析

image.png

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

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

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

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

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

image.png

按照这样的思路方法:

我们在解析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 看看事件驱动的方式是什么效率吧

image.png

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

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

阅读 1.4k

心有多大,舞台就有多大

95 声望
20 粉丝
0 条评论
你知道吗?

心有多大,舞台就有多大

95 声望
20 粉丝
宣传栏