EasyExcel

参考：EasyExcel

easyexcel：快速、简单避免OOM的java处理Excel工具

easyexcel--解决poi大文件发生OOM问题

xlsx和xls的区别

1. XLS是复合文档类型结构；XLSX是XML类型结构。最大只能23M左右。
2. XLS是03年及以前的文件格式，XLSX是07年及以后的。
3. XLSX基于XML的zip压缩方式，占用的空间更小（实际实验时大小只要XLS的三分之一）。
4. CSV，是纯文本文件，也可以被Excel打开，格式简单，解析起来和解析文本文件一样。大小上XLSX文件的两倍。

Apache POI 的员工数据的导入导出

1、导出数据

• 查询数据库，返回员工对象集合
• 创建POI工具类导出员工数据，类型需要是ResponseEntity<byte[]>
  • 创建一个Excel文档，可以是xlsx或者xls（Excel97）
  • 创建文档摘要信息（文档类型、文档管理员、公司信息、文档标题、文档表单名、作者、备注）
  • 创建样式，主要是第一行的样式颜色，与数据区别开，以及日期格式
  • 设置每一列的列宽，
  • 设置第一行每一列的列名，例如编号、姓名、工号、性别、出生日期、身份证、民族、电话、地址、职称
  • 遍历员工对象集合，导出数据到Excel表
  • 文件下载配置：将Excel表转为字节输出流，设置Excel的导出文件名等属性。

2、导入数据

（1） Excel上传

两种方法：

• Ajax上传
• ElementUI中Upload组件上传，其实也是Ajax形式

注：ajax，异步请求

样例：

$("button").click(function(){
    $.ajax({url:"demo_test.txt",success:function(result){
        $("#div1").html(result);
    }});
});

（2）Excel解析

将Excel文件形式解析成员工对象集合list

• 创建一个workbook对象，传入参数是文件输入流
• 获取workbook中表单数量，因为一个Excel中可能不止一个表单
• 遍历表单，在循环中获取表单行数，然后遍历每一行
• 遍历每一行时，跳过标题行，判断行是否为空，放在有空行
• 获取每一行的列数，遍历这一行，将数据保存在对象中
• 数据保存时，首先分两种情况，一类数据是string类型，一类是日期类型的，需要判断，分开存储。
• 需要注意的是在数据库中对象的民族，部门，政治面貌，职位，职称保存的是ID，所以在设置对象属性时，需要处理。以部门为例，先查询数据库，得到所有部门的信息，放在list集合中，利用indexof得到该部门索引（需要重写equals和hashcode方法，只判断name是否相等），通过索引得到这个部门的信息，然后就有了部门的ID，再将这个ID存入当前对象中。

（3）插入数据库

将员工对象集合list，插入数据库，一个SQL语句，insert中用forEach遍历。

最后返回一个respbean对象，告诉前端上传成功或者失败

Excel导入大量数据，如果做校验，提高性能

参考：关于EXCEL大批量数据导入校验，效率太慢的性能优化

1、将Excel表数据按照某一个数据，例如部门ID，归类处理，分为n张子表，这样在导入时就只要判断当前部门内是否重复就行。

2、用Redis将数据库数据缓存下来，就不用每次到数据库查询数据

3、用map，查看是否map中已经有数据，判断是否重复。

4、重复的情况有多种：首先是Excel中可能有，然后是数据库本身是否已经有重复，然后就是导入时Excel和数据库中是否存在重复。

解决POI大文件发生OOM问题

1、问题

导入50M文件出错，复现时是20M左右内存基本就已经被占满了，但是还没报OOM问题（4核8G），当到40M文件时，报异常：OfficeXmlFileException，提示：You need to call a different part of POI to process this data (eg XSSF instead of HSSF)。

2、原因

使用的Apache POI 中使用的是workbook这个类处理文件，它会将文件按流的形式读到内存中，非常消耗内存，当数据量比较大的时候产生OOM。

注：FileUploadBase$SizeLimitExceededException这个是因为springboot上传文件的限制，可以修改

spring:
    servlet:
      multipart:
        max-file-size: 100MB
        max-request-size: 100MB

注：在Apache POI中应对大数据，有一套sax模式，可以一定程度的解决内存溢出问题。采用sax模式，原理是一行一行解析，并将一行解析结果以观察者模式通知处理。但是POI在对xlsx文件处理时，解压文件和解压后存储以备sax模式处理都是在内存中完成的，内存消耗依然很大。

3、解决方案

EasyExcel在处理大数据量上操作效率高于easyPOI，小数据量上是差不多的效率。

EasyExcel重写了POI对07版xlsx文件的解析，将3M文件解析原本需要100M内存降低到KB级别，03版依赖POI的sax模式，在上层做了模型转换的封装。

• 文件解压和文件读取通过文件形式

• 避免将去不数据一次加载到内存，参考sax模式
• 抛弃不重要的数据

Excel解析时候会包含样式，字体，宽度等数据，但这些数据是我们不关心的，抛弃这些数据打打降低了内存使用。

（1）Excel导出

1. 定义方法，配置Excel基本属性，利用输出流。

public static void excelExport(HttpServletResponse response,List<Employee> list)

list可以传入员工对象集合

2. 让实体类继承BaseRowModel，并加上@ExcelProperty(value=" ",index= 0)注解，其中有些数据可能和数据库中存储的不同，需要自定义转换器Converter,例如部门名，职称，职位等，在数据库中是ID，让在Excel表中是名称。注意导入包时是 com.alibaba.excel.convertes.Converter。convertToJavaData是转为java模型的，在Excel导入中使用。convertToExcelData是转为Excel的格式。具体实现的话，可以查询数据库，然后缓存，或者重写equals方法和hashcode方法。

public class NationConverter implements Converter<Integer> 

3. 在需要自定义格式的属性的ExcelProperty中加上Converter

（2）Excel导出

1. 因为在Converter转换器中已经实现了，只需要配置导出属性等。
2. 配置监听器，应用了观察者模式。主要是配置BATCH_COUNT，当解析的Excel数量达到BATCH_COUNT时，需要去存储一次数据库，存储完成就好清理data。防止大量数据在内存中，容易出现OOM。BATCH_COUNT一般为3000；还有一些其他实现，当数据全部解析完提示等。注意不要注入到spring中，

public class NoModelDataListener extends AnalysisEventListener<Map<Integer, String>>