Excel导入导出
文章参考：百万数据的导入导出解决方案_liu.kai的博客-CSDN博客_百万数据导出
大量百万级数据导入导出场景：
在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。
传统POI的的版本优缺点比较
想到数据的导入导出,会想到apache的poi技术,以及Excel的版本问题
首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口，我们的POI版本也在更新的同时对这个几个实现类做了更新；
HSSFWorkbook :
这个实现类是我们早期使用最多的对象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后缀还是.xls
XSSFWorkbook :
这个实现类现在在很多公司都可以发现还在使用，它是操作的Excel2003–Excel2007之间的版本，Excel的扩展名是.xlsx
SXSSFWorkbook :
这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx
知道导入导出操作的时候会用到这样三个实现类以及他们可以操作的Excel版本和后缀,从优缺点分析
-
HSSFWorkbook
它是POI版本中最常用的方式,不过:
它的缺点是 最多只能导出 65535行,也就是导出的数据函数超过这个数据就会报错;
它的优点是 不会报内存溢出。（因为数据量还不到7w所以内存一般都够用，首先你得明确知道这种方式是将数据先读取到内存中，然后再操作）
-
XSSFWorkbook
优点：这种形式的出现是为了突破HSSFWorkbook的65535行局限，是为了针对Excel2007版本的1048576行，16384列，最多可以导出104w条数据；
缺点：伴随的问题来了，虽然导出数据行数增加了好多倍，但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book，Sheet，row，cell等在写入到Excel之前，都是存放在内存中的（这还没有算Excel的一些样式格式等等），可想而知，内存不溢出就有点不科学了！！！ -
SXSSFWorkbook
从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式;
优点：
这种方式不会一般不会出现内存溢出（它使用了硬盘来换取内存空间，
也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来，而内存中存的都是最新的数据），
并且支持大型Excel文件的创建（存储百万条数据绰绰有余）。
缺点：
既然一部分数据持久化到了硬盘中，且不能被查看和访问那么就会导致，
在同一时间点我们只能访问一定数量的数据，也就是内存中存储的数据;
sheet.clone()方法将不再支持,还是因为持久化的原因;
不再支持对公式的求值，还是因为持久化的原因，在硬盘中的数据没法读取到内存中进行计算；
在使用模板方式下载数据的时候，不能改动表头，还是因为持久化的问题，写到了硬盘里就不能改变了；
使用方式哪种看情况
-
当我们经常导入导出的数据不超过7w的情况下，
可以使用 HSSFWorkbook 或者 XSSFWorkbook都行
-
当数据量查过7w并且导出的Excel中不牵扯对Excel的样式，公式，格式等操作的情况下，
推荐使用SXSSFWorkbook;
-
当数据量查过7w，并且我们需要操做Excel中的表头，样式，公式等，
使用 XSSFWorkbook 配合进行分批查询，分批写入Excel的方式来做；
百万数据导入导出
铺垫也做了不少，那么现在开始讲讲我在工作中遇到的超百万数据的导入导出解决方案：
想要解决问题我们首先要明白自己遇到的问题是什么？
1、 我遇到的数据量超级大，使用传统的POI方式来完成导入导出很明显会内存溢出，并且效率会非常低；
2、 数据量大直接使用select * from tableName肯定不行，一下子查出来300w条数据肯定会很慢；
3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中，这样效率会非常低；估计打开都得几分钟；
4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行；
5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行；
6、导入时300w数据如果使用Mybatis的批量插入肯定不行，因为Mybatis的批量插入其实就是SQL的循环；一样很慢。
解决思路：
针对1 ：
其实问题所在就是内存溢出，我们只要使用对上面介绍的POI方式即可，主要问题就是原生的POI解决起来相当麻烦。
经过查阅资料翻看到阿里的一款POI封装工具EasyExcel，上面问题等到解决；
针对2：
不能一次性查询出全部数据，我们可以分批进行查询，只不过时多查询几次的问题，况且市面上分页插件很多。此问题好解决。
针对3：
可以将300w条数据写到不同的Sheet中，每一个Sheet写一百万即可。
针对4：
不能一行一行的写入到Excel上，我们可以将分批查询的数据分批写入到Excel中。
针对5：
导入到DB时我们可以将Excel中读取的数据存储到集合中，到了一定数量，直接批量插入到DB中。
针对6：
不能使用Mybatis的批量插入，我们可以使用JDBC的批量插入，配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。
JAVA解析Excel工具EasyExcel
官网： alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具 (github.com)
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便
导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
300w数据导出
使用EasyExcel 参考官网实例
实现思路：
- 查询数据的时候，分批进行分页查询
- 查询结束，批量写入数据
- 设置每个sheet的最大容量，超过最大容量后自动调到下一个sheet
注意：
-
需要计算sheet的个数，还有循环写入的次数，最后一个sheet可能不是满的，也要计算写的次数
-
相当于：
-
300w的数据在数据库里，每次分页查询20w，每次写入20w（查出来就写进去）
每个sheet最大存100w行数据，那就是有3个sheet，每个sheet写 100/20 = 5 次，最后一个sheet因为是整除，可以一样的5次
-
310w的数据，每次查写20w，sheet存100w，需要4个sheet，前3个sheet写5次，最后一个写1次
-
导出逻辑：
@GetMapping("/export")
public void dataExport(HttpServletResponse response) {
{
OutputStream outputStream = null;
try {
long startTime = System.currentTimeMillis();
System.out.println("导出开始时间:" + startTime);
outputStream = response.getOutputStream();
//指定输出流 和对应的 head 实体
// ExcelWriter excelWriter = EasyExcel.write(outputStream,TestExcel.class).build();
ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
String fileName = new String(("excelTest").getBytes(), "UTF-8");
//记录总数:实际中需要根据查询条件进行统计即可
Integer totalCount = testExcelService.count();
//剩余
Integer surplusCount = totalCount;
//每一个Sheet存放100w条数据
Integer sheetDataRows = ExcelConstant.PER_SHEET_ROW_COUNT;
//每次写入的数据量10w
Integer writeDataRows = ExcelConstant.PER_WRITE_ROW_COUNT;
//计算需要的Sheet数量
Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
//计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
//计算最后一个sheet需要写入的次数
Integer lastSheetWriteCount = totalCount % sheetDataRows == 0
? oneSheetWriteCount :
(
totalCount % sheetDataRows % writeDataRows == 0
? (totalCount / sheetDataRows / writeDataRows)
: (totalCount / sheetDataRows / writeDataRows + 1)
);
//开始分批查询分次写入
//注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
List<TestExcel> reslultList = new ArrayList<>();
for (int i = 0; i < sheetNum; i++) {
// 循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数
// 如果是最后一个就需要使用计算的次数lastSheetWriteCount
for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
//分页查询一次5w
PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
reslultList = testExcelService.idNotNullList();
//不需要导出的字段userId，如果没有不需要导出的字段，可以忽略这个方法
//只需要导入的字段，用includeColumnFiledNames()方法，写法是一样的
Set<String> excludeColumnFiledNames = new HashSet<String>();
excludeColumnFiledNames.add("userId");
//设置sheet
WriteSheet writeSheet = EasyExcel.writerSheet(i + 1, "sheet" + (i + 1))
.head(TestExcel.class)//指定的head表头,可以给定每个sheet都不一样的表
.excludeColumnFiledNames(excludeColumnFiledNames)//忽略的字段表头
.build();
//写数据
excelWriter.write(reslultList, writeSheet);
int size = reslultList.size();
surplusCount = surplusCount - size;
System.out.println("当前进度：" + (1 - surplusCount / totalCount) * 100 + "%");
//集合复用,便于GC清理
reslultList.clear();
}
}
// 下载EXCEL 设置响应头
response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
response.setContentType("multipart/form-data");
response.setCharacterEncoding("utf-8");
excelWriter.finish();
outputStream.flush();
//导出时间结束
long endTime = System.currentTimeMillis();
System.out.println("导出结束时间:" + endTime + "ms");
System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
} catch (Exception e) {
e.printStackTrace();
} finally {
if (outputStream != null) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
实体类
package com.ung.excelTest.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.io.Serializable;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
/**
* @author ung
* @since 2022-10-14
* 导入、导出共用实体类
*/
@Data
@EqualsAndHashCode(callSuper = false)
@TableName("test_excel")
@NoArgsConstructor
@AllArgsConstructor
public class TestExcel implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 表主键id
*/
@TableId(value = "user_id", type = IdType.AUTO)
private Integer userId;
/**
* 用户姓名，不能为空
* 可以通过index和name去匹配excel里的列
* 注意name,index值不要重复
*/
@ExcelProperty(value = "姓名", index = 0)
private String userName;
/**
* 用户年龄，允许为空
*/
@ExcelProperty(value = "用户年龄", index = 1)
private String userAge;
/**
* 身份证号，不能为空，不允许重复
*/
@ExcelProperty(value = "身份证号", index = 2)
private String userCardid;
public TestExcel(String userName) {
this.userName = userName;
this.userAge = userName;
this.userCardid = userName;
}
}
300w数据导入
每次写入5w数据到数据库
1、首先是分批读取读取Excel中的数据，这一点EasyExcel有自己的解决方案，我们可以参考Demo即可，只需要把它分批的参数调大即可。
2、其次就是往DB里插入，怎么去插入这5w条数据，当然不能一条一条的循环，应该批量插入这5w条数据，同样也不能使用Mybatis的批量插入语，因为效率也低。使用自定义批量插入
3、使用JDBC+事务的批量操作将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）
创建一个读取类
package com.ung.excelTest.utils;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.ung.excelTest.entity.TestExcel;
import com.ung.excelTest.service.TestExcelService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.util.List;
/**
* @author: wenyi
* @create: 2022/10/18
* @Description: 读取类
*/
@Slf4j
public class ExcelDataListener implements ReadListener<TestExcel> {
/**
* 最大5w条，然后清理list ，方便内存回收
*/
private static final int BATCH_COUNT = 50000;
/**
* 缓存的数据
* 指定创建size大小的list
*/
private List<TestExcel> cachedDataListTrue = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private List<TestExcel> cachedDataListFalse = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
/**
* 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
*/
private TestExcelService testExcelService;
public ExcelDataListener() {
}
/**
* 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
*/
public ExcelDataListener(TestExcelService testExcelService) {
this.testExcelService = testExcelService;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context
*/
@Override
public void invoke(TestExcel data, AnalysisContext context) {
// log.info("解析到一条数据:{}", JSON.toJSONString(data));
//可以获取每一行数据，进行简单的判断校验
if (data == null) {
//对象为空直接跳出
return;
} else if (StringUtils.isBlank(data.getUserName())) {//判断名字是否为空
cachedDataListFalse.add(data);//放入错误集合列表
return;
} else if (StringUtils.isBlank(data.getUserCardid())) {//判断身份证是否为空
/**
* 身份证号的判断，以前碰到过一个需求，就是不能和数据库已有的数据重复，数据库存在这个身份证号则表示数据已导入/不能再次导入
* 我使用的方式是，先把那个表的”身份证号“字段全部查询出来加载到内存里，然后这里直接和查询出来的身份证号进行对比，存在的就不
* 导入，并记录到错误集合列表标注为”重复导入“，不存在的才存入正确的集合列表，并把这个身份证号也存入内存，给后面的数据校验
* 是否有重复的数据，这样所有的校验都在内存里进行，优点是：速度会很快、数据库压力也会很小，但是缺点也很明显：很占内存。
* 不过通过测试：数据在百万级的，只查询身份证号的话，内存的占用是很少的，即使是微型服务器也能满足需求，而如果是千万级数据，相信
* 能有这个数据量的，服务器也差不了，上亿数据量的还没处理过，以后有机会碰到了再进行测试吧，这里不进行身份证号相同的校验
*/
cachedDataListFalse.add(data);//放入错误集合列表
return;
}
cachedDataListTrue.add(data);
// 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
if (cachedDataListTrue.size() >= BATCH_COUNT) {
saveData();
}
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// 这里也要保存数据，确保最后遗留的数据也存储到数据库
saveData();
log.info("所有数据解析完成！");
}
/**
* 加上存储数据库
*/
private void saveData() {
// log.info("{}条数据，开始存储数据库！", cachedDataListTrue.size());
testExcelService.insertBatchSomeColumn(cachedDataListTrue);
// 存储完成清理 list
cachedDataListTrue.clear();
// log.info("存储数据库成功！");
if (cachedDataListFalse.size() > 0) {
log.info("{}条数据有问题!", cachedDataListFalse.size());
log.info("问题数据:", cachedDataListFalse);
cachedDataListFalse.clear();
}
}
}
导出逻辑：
@PostMapping("/import")
public void importTest(MultipartFile file) {
//记录开始读取Excel时间,也是导入程序开始时间
long startReadTime = System.currentTimeMillis();
System.out.println("------开始读取Excel(包括导入数据过程)------");
//读取所有Sheet的数据
try {
// DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
EasyExcel.read(file.getInputStream(), TestExcel.class, new ExcelDataListener(testExcelService)).doReadAll();
} catch (IOException e) {
e.printStackTrace();
}
long endReadTime = System.currentTimeMillis();
System.out.println("------结束读取Excel(包括导入数据过程)总时间花费:" + (endReadTime - startReadTime) / 1000 + "s------");
}
8811
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
