目前本文章中分配导出的相关代码已更新至本人github的练习项目中 https://github.com/kimikudo/practice-back
前两天项目中有个将数据导出为Excel的功能,一开始用的是Excel4J进行导出,但是由于数据量较大导出很慢,而且会出现内存溢出的问题.所以准备进行优化.首先想到的是分批查询数据进行导出.搜索了一些资料也准备开始做了.但是同事说可以直接使用阿里的EasyExcel,导出速度和内存占用方面确实提升明显.但是分批导出的方式还是顺便自己业余时间实现一下并且做个记录吧!
目前暂时总结了三种数据导出为Excel的方式,分别是Excel4J方式,POI原生方式,和阿里EasyExcel方式,其中Excel4J的效果不太理想,我也暂时不考虑使用该方法进行大数据量导出,只总结了该方式的普通导出用法,大数据量仅使用另外两种方式进行实现.
Excel4J
添加Excel4J依赖 <!-- Excel4j导出 --> <!-- https://mvnrepository.com/artifact/com.github.crab2died/Excel4J --> <dependency> <groupId>com.github.crab2died</groupId> <artifactId>Excel4J</artifactId> <version>3.0.0</version> </dependency>
使用注解标识导出表头 这里使用一张IMSI数据表进行演示,使用注解方式,在数据实体类的属性上,使用@ExcelField(title = "IMSI", order = 1)注解将属性指定为表头,并可添加列序号, @Data @ToString @ApiModel(value = "IMSI源数据") public class ImsiSourceData { /** * 源数据表主键 */ @ApiModelProperty("源数据表主键") private Integer id; /** * 设备编号 */ @ApiModelProperty("设备编号") @ExcelField(title = "设备编号", order = 2) private String deviceid; /** * imei */ @ApiModelProperty("imei") private String imei; /** * imsi */ @ApiModelProperty("imsi") @ExcelField(title = "IMSI", order = 1) private String imsi; /** * 上报时间 */ @ApiModelProperty("上报时间") @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") @ExcelField(title = "采集时间", order = 3, writeConverter = DateTimeConverter.class) private Long reportTime; /** * 归属地 */ @ApiModelProperty("归属地") @ExcelField(title = "归属地", order = 4) private String homeLocation; /** * 运营商 */ @ApiModelProperty("运营商") @ExcelField(title = "运营商", order = 5) private String networkOperator; /** * 网络制式 */ @ApiModelProperty("网络制式") @ExcelField(title = "网络制式", order = 6) private String terminalNetworkInformation; }
数据导出 通过查询数据库获得数据,再直接调用自带方法即可实现导出; 其中,exportObjects2Excel()方法的参数,第三个可以指定为输出流,为了处理输出流 ,可能需要对响应头的属性进行设置. public void exportByExcel4J(HttpServletResponse response) throws IOException { List<ImsiSourceData> list = this.list( new QueryWrapper<ImsiSourceData>() .orderByDesc("report_time") .last(" LIMIT 50") ); //Excel4J方式导出 try { ExcelUtils.getInstance().exportObjects2Excel(list,ImsiSourceData.class,"IMSI导出测试.xlsx"); } catch (Excel4JException e) { e.printStackTrace(); } }
效果测试 我这里直接指定的文件名,即在项目根目录成功导出文件.
POI原生方式
这里只是简单的记录一下POI原生的导出方式,与本次博客内容关系不大,本次主要是记录分批导出的设计方案.
添加POI依赖 <!-- POI依赖 --> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency>
创建Excel对象,创建标题行 //HSSFWorkbook是处理.xls文件的,XSSFWorkbook处理.xlsx文件 HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("sheet1"); //标题行 HSSFRow headRow = sheet.createRow(0); headRow.createCell(0).setCellValue("ID"); headRow.createCell(1).setCellValue("IMSI"); headRow.createCell(2).setCellValue("采集时间"); headRow.createCell(3).setCellValue("运营商");
遍历数据,创建内容行,并写入文件/输出流 //写数据 List<ImsiSourceData> list = this.listData(); int i = 1; for (ImsiSourceData imsiSourceData : list) { HSSFRow row = sheet.createRow(i); row.createCell(0).setCellValue(imsiSourceData.getId()); row.createCell(1).setCellValue(imsiSourceData.getImsi()); //时间戳字段的处理 SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); row.createCell(2).setCellValue(format.format(new Date(imsiSourceData.getReportTime()))); row.createCell(3).setCellValue(imsiSourceData.getNetworkOperator()); i++; } FileOutputStream os = new FileOutputStream("POI导出_" + System.currentTimeMillis() + ".xls"); workbook.write(os); os.flush(); os.close();
生成的文件效果
大数据量分批导出
由于POI的工作簿对象会存在内存溢出的问题,所以在进行大数据量导出是,考虑使用SXSSFWorkbook对象,这是一个基于XSSF的工作簿对象,适用于大对象的创建和大数据量的导出,同时支持.xlsx后缀名. POI的分批导出参考的是这篇博客: 自己参照这篇博客的方式尝试实现了一遍,该说不说原博主写的还不错!这里记录一下自己的实现过程.
POI 5.2.2 用于excel写入 MyBatisPlus 3.4 用于数据查询
在贴代码之前先说一下这个功能的实现思路 数据量大的情况下导出数据,需将数据划分为不同的sheet,再根据不同sheet进行写入,所以在写入前需先确定单个sheet保存多少条数据,单次写入多少条数据,从而可以计算出单个sheet的写入次数.再根据次数进行循环写入.数据查询也是在每次写入时完成,可以直接理解为分页查询,根据当次的写入量查询对应数量的数据来完成写入,从而尽可能不发生内存溢出的问题. 规定数据条数的常量如下
public class ExcelConstant {
* 每次写入的记录条数
public static final Integer WRITE_ROW_COUNT = 100000;
* 单个sheet保存的记录数
public static final Integer SHEET_ROW_COUNT = 500000;
* 每个sheet的写入次数: 总数/单次写入
public static final Integer WRITE_TIMES = SHEET_ROW_COUNT / WRITE_ROW_COUNT;
POI工具类
该工具类包含根据数据量来初始化Workbook,创建对应数量的sheet,并设置表头;将Workbook保存到文件或输出流;分批将数据写入Workbook等功能
import javax.servlet.http.HttpServletResponse;
import com.kay.practiceback.practice.excel.poi.constant.ExcelConstant;
import com.kay.practiceback.practice.excel.poi.delegate.WriteDataDelegated;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.*;
import java.nio.charset.StandardCharsets;
* POI工具类
* @author Kay
* @date 2022-05-19
@Slf4j
public class PoiUtil {
* 初始化表格
* @param totalRow 数据总行数
* @param titles 列名集合
* @return workbook
public static SXSSFWorkbook init(Integer totalRow, String[] titles) {
//定义窗口行数,即进行读写时,内存中存在100条数据
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
//计算需要多少个sheet
int sheetCount = (totalRow % ExcelConstant.SHEET_ROW_COUNT == 0) ?
(totalRow / ExcelConstant.SHEET_ROW_COUNT) : (totalRow / ExcelConstant.SHEET_ROW_COUNT + 1);
log.info("初始化excel,共计 {} 条数据,共计 {} 个sheet", totalRow, sheetCount);
for (int i = 0; i < sheetCount; i++) {
//初始化sheet
SXSSFSheet sheet = wb.createSheet("sheet" + (i + 1));
//创建表头行,并写入表头
SXSSFRow headRow = sheet.createRow(0);
for (int j = 0; j < titles.length; j++) {
SXSSFCell headCell = headRow.createCell(j);
headCell.setCellValue(titles[j]);
log.info("Excel文件初始化完成,共计{}个sheet", sheetCount);
return wb;
* 将excel保存到本地路径
* @param wb excel对象
* @param path 保存路径
public static void saveExcelToPath(SXSSFWorkbook wb, String path) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(path);
wb.write(fos);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != wb) {
wb.dispose();
if (null != fos) {
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
* 将excel转为输出流
* @param wb excel
* @param response response
* @param fileName 文件名
public static void saveExcelToStream(SXSSFWorkbook wb, HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
response.setHeader("Content-disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
OutputStream outputStream = null;
try {
outputStream = response.getOutputStream();
wb.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
if (null != outputStream) {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
* 导出Excel到指定路径
* @param totalRow 总记录数
* @param titles 标题
* @param exportPath 导出路径
* @param writeDataDelegated 数据写入委托
public static void exportToPath(Integer totalRow, String[] titles, String exportPath, WriteDataDelegated writeDataDelegated) {
log.info("开始导出excel文件...");
//初始化工作簿
SXSSFWorkbook workbook = PoiUtil.writeWorkbook(totalRow, titles, writeDataDelegated);
//写入成功后将excel保存到指定路径
PoiUtil.saveExcelToPath(workbook, exportPath);
log.info("Excel文件导出完成");
* 导出Excel到指定路径
* @param totalRow 总记录数
* @param titles 标题
* @param response 导出的输出流
* @param fileName 文件名
* @param writeDataDelegated 数据写入委托
public static void exportToStream(Integer totalRow, String[] titles, HttpServletResponse response, String fileName, WriteDataDelegated writeDataDelegated) throws UnsupportedEncodingException {
log.info("开始导出excel到流...");
SXSSFWorkbook workbook = PoiUtil.writeWorkbook(totalRow, titles, writeDataDelegated);
//写入成功后将excel保存到指定路径
PoiUtil.saveExcelToStream(workbook, response, fileName);
log.info("Excel输出流导出完成");
* 数据写入,创建工作簿并写入数据
* @param totalRow 总数据条数
* @param titles 表头
* @param writeDataDelegated 写数据委托
* @return workbook
public static SXSSFWorkbook writeWorkbook(Integer totalRow, String[] titles, WriteDataDelegated writeDataDelegated) {
log.info("写入数据开始...");
//初始化工作簿
SXSSFWorkbook workbook = PoiUtil.init(totalRow, titles);
//获取sheet数目,遍历写入sheet
int sheetCount = workbook.getNumberOfSheets();
for (int i = 0; i < sheetCount; i++) {
log.info("开始第{}次写入", i + 1);
SXSSFSheet sheet = workbook.getSheetAt(i);
//根据单个sheet和单次写入数量计算的写入次数,来进行多次写入
for (int j = 0; j < ExcelConstant.WRITE_TIMES; j++) {
int pageNum = i * ExcelConstant.WRITE_TIMES + j + 1;
int pageSize = ExcelConstant.WRITE_ROW_COUNT;
int startRow = j * ExcelConstant.WRITE_ROW_COUNT + 1;
writeDataDelegated.writeData(sheet, startRow, pageNum, pageSize);
return workbook;
参考原博客的委托方式,这里也创建了一个委托接口,可在接口的实现中自行查询数据并将数据逐行写入sheet,我这里创建了一个订单表的写数据委托实现,如果需要其他内容的导出,再创建一个接口实现即可 接口
import org.apache.poi.xssf.streaming.SXSSFSheet;
* Excel数据写入委托类
* @author Kay
* @date 2022-07-13
public interface WriteDataDelegated {
* 分页查询并将数据写入excel
* @param sheet 写入的数据表
* @param startRow 写入开始行
* @param pageNum 数据查询分页页数
* @param pageSize 数据查询记录条数
void writeData(SXSSFSheet sheet, Integer startRow, Integer pageNum, Integer pageSize);
实现 这里仅作练习和测试使用,所以数据比较简单只是单表查询,导出的字段也随便填了几个,实际应用中可以根据实际需求进行增减和格式化
import cn.hutool.core.util.NumberUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.kay.practiceback.db.order.entity.OrderRecord;
import com.kay.practiceback.db.order.service.OrderRecordService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.util.List;
* 订单记录的Excel数据写入委托
* @author Kay
* @date 2022-07-13
@Slf4j
@Component
public class OrderWriteDataDelegated implements WriteDataDelegated {
@Autowired
private OrderRecordService orderRecordService;
@Override
public void writeData(SXSSFSheet sheet, Integer startRow, Integer pageNum, Integer pageSize) {
log.info("开始分批查询并写入数据...");
//根据条件查询待写入的数据
List<OrderRecord> orderList = orderRecordService.page(new Page<OrderRecord>(pageNum, pageSize),
new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime)).getRecords();
log.info("本次写入起始行号为:{},共计 {} 条数据", startRow, orderList.size());
if (orderList.size() > 0) {
for (int i = startRow; i < orderList.size() + startRow; i++) {
OrderRecord data = orderList.get(i - startRow);
SXSSFRow row;
if (null != sheet.getRow(i)) {
row = sheet.getRow(i);
} else {
row = sheet.createRow(i);
//写入单行数据,根据实际需要写入所需字段,这里仅写入几列进行测试
row.createCell(0).setCellValue(data.getId().toString());
row.createCell(1).setCellValue(data.getClientId().toString());
row.createCell(2).setCellValue(data.getAccount().toString());
row.createCell(3).setCellValue(NumberUtil.div(data.getDeno().toString(), "100",0).toString());
row.createCell(4).setCellValue(NumberUtil.decimalFormat("#.##", NumberUtil.div(data.getPrice().toString(), "100")));
row.createCell(5).setCellValue(data.getState());
row.createCell(6).setCellValue(data.getCreateTime().toString());
log.info("本次写入完成,当前sheet总行数为:{}", sheet.getLastRowNum());
创建服务类,定义文件名,进行到处之前先计算一下要导出的数据总数,之后执行导出即可
@Service
public class PoiExportServiceImpl implements PoiExportService {
@Autowired
private OrderRecordService orderRecordService;
* 写数据委托类,使用注解注入所需的实现
@Autowired
@Qualifier("orderWriteDataDelegated")
private OrderWriteDataDelegated orderWriteDataDelegated;
@Override
public void exportOrder(String fileName) {
//定义表头,去委托中相同
String[] titles = {
"订单号", "渠道编号", "充值账号", "面额", "售价", "状态", "创建时间"};
String path = "D:\Kay\export\";
File pathFile = new File(path);
if (!pathFile.exists()) {
pathFile.mkdirs();
//计算总条数,与委托类中的查询相同
Long totalCount = orderRecordService.count(new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime));
PoiUtil.exportToPath(totalCount.intValue(), titles, path + fileName, orderWriteDataDelegated);
一个小意外
代码刚完成开始测试时候,第二次写入总是报错 Attempting to write a row[100001] in the range [0,661376] that is already written to disk.,开始以为是循环时候写入的行号不正确,仔细审阅了代码发现也没什么问题…于是开始逐行debug…最后发现了…是第一次写入时候数据查询的分页没有生效!单次就像给我写入总条数了!..赶紧加上了MyBatisPlus的分页配置,问题解决
@Configuration
public class MyBatisPlusConfig {
@Bean
public MybatisPlusInterceptor paginationInnerInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
EasyExcel
因为这个博客坑了太久了,期间其实EasyExcel也更新了,所以这里就按我现在填坑时候的操作来写吧!
普通导出参考官方文档的步骤即可: 这里的版本是3.1.1
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
在实体类的属性上添加@ExcelProperty注解规定需要导出的列,注解可以规定index指定顺序,无需导出的添加@ExcelIgnore即可,对于日期或数值类型的字段可以使用 @DateTimeFormat或@NumberFormat进行格式化,对于需要自定义输出格式的可以自行实现Converter<T>接口进行格式化 对于Long型的字段,可以使用EasyExcel自带的LongStringConverter将Long转为String
@ApiModelProperty("订单号")
@ExcelProperty(value = "订单号", index = 0, converter = LongStringConverter.class)
private Long id;
@ExcelProperty(value = "渠道编码", index = 1)
private Integer clientId;
@ExcelProperty(value = "支付金额", index = 6, converter = AmountConverter.class)
private Integer price;
@ExcelIgnore
private Integer payType;
@ExcelProperty(value = "充值金额", index = 5, converter = AmountConverter.class)
private Integer deno;
@ExcelProperty(value = "创建时间", index = 8)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private LocalDateTime createTime;
Converter:
import cn.hutool.core.util.NumberUtil;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.WriteConverterContext;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.data.WriteCellData;
public class AmountConverter implements Converter<Integer> {
@Override
public Class<Integer> supportJavaTypeKey() {
return Integer.class;
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
@Override
public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {
return new WriteCellData<String>(NumberUtil.div(context.getValue().toString(), "100", 2).toString());
之后直接进行写Excel操作即可,其他写Excel方式参考文档就好了~
@Override
public void simpleWrite() {
String fileName = "D:\Kay\export\订单导出_" + System.currentTimeMillis() + ".xlsx";
EasyExcel.write(fileName, OrderRecord.class).sheet("订单记录").doWrite(() -> {
return orderRecordService.lambdaQuery().orderByDesc(OrderRecord::getCreateTime).last(" LIMIT 100").list();
最后的效果
大数据量分批导出
@Override
public void batchExport() {
log.info("开始导出数据...");
String fileName = "D:\Kay\export\订单导出_批量_" + System.currentTimeMillis() + ".xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName, OrderRecord.class).build();
//数据总量
long totalCount = orderRecordService.count(new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime));
//单个sheet保存的数据量
int perSheetCount = 100000;
//sheet总数
long sheetCount = totalCount % perSheetCount == 0 ? (totalCount / perSheetCount) : (totalCount / perSheetCount + 1);
log.info("总数据量:{},共计{}个sheet", totalCount, sheetCount);
for (int i = 0; i < sheetCount; i++) {
log.info("开始第{}个sheet写入", i);
//分页查询写入数据
List<OrderRecord> orderList = orderRecordService.page(new Page<OrderRecord>(i + 1, perSheetCount),
new LambdaQueryWrapper<OrderRecord>().orderByDesc(OrderRecord::getCreateTime)).getRecords();
//创建sheet
WriteSheet sheet = EasyExcel.writerSheet(i, "订单记录-" + i).build();
excelWriter.write(orderList, sheet);
excelWriter.finish();
log.info("数据导出完成");
EasyExcel我导出的列相较于POI多几个,为了测试自定义格式转换的功能,而且sheet数量也不同,最终导出66W数据耗时1分钟16s,文件大小45.5M
