实际场景中我们可能会遇到大报表的录入,比如将一张具有几十万或者百万的数据表导出为excel报表,这种情况我们我们不可能一次性将数据库中的数据都读取出来然后再写入excel,这样会导致程序的OOM。
如何解决这个问题呢?
我们可以用分页的方式查询数据,然后将查到的数据逐步追加写入到单个sheet,如果数据量过大可以考虑将数据分别写入到多个sheet。
1、easyexcel的依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>
2、代码实现
2.1 Excel导出工具类
package com.bbs.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.RowWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.handler.context.RowWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.bbs.bean.DemoData;
import java.io.File;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
/**
* @Date: 2023/02/10/ 22:35
* @description
*/
public class EasyExcelUtil {
public static <T> void writeExcel(File file, Class<T> head, Collection<T> data) {
EasyExcel.write(file, head).sheet(0).doWrite(data);
}
/**
* 分页查询数据库并写入excel
*
* @param file 数据输出对象
* @param head 表头
* @param offset 分页偏移量
* @param pageHandler 分页处理器
* @param <T>
*/
public static <T> void writeExcel(File file, Class<T> head, int offset, PageHandler<T> pageHandler) {
ExcelWriter excelWriter = EasyExcel.write(file, head).build();
//当前页码
int page = 0;
while (true) {
//分页读取数据
Collection<T> data = pageHandler.page(page, offset);
if (data.isEmpty()) {
//数据为0的时候也需要写入,防止excel文件异常
excelWriter.write(data, EasyExcel.writerSheet(0).head(head).build());
break;
}
//读取的而数据写入excel
excelWriter.write(data, EasyExcel.writerSheet(0).head(head).build());
//更新页码
++page;
}
//数据写入完毕关闭资源
excelWriter.finish();
}
//测试,用两个列表模拟分页
public static void main(String[] args) {
List<DemoData> list1 = new ArrayList<>();
for (int i = 0; i < 20; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list1.add(data);
}
List<DemoData> list2 = new ArrayList<>();
for (int i = 0; i < 20; i++) {
DemoData data = new DemoData();
data.setString("字符串---" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list2.add(data);
}
writeExcel(new File("C:\\3.xlsx"), DemoData.class, 10, (page, offset) -> {
if(page==0){
return list1;
}
if(page==1){
return list2;
}
return new ArrayList<>();
});
}
}
2.2 PageHandler为一个接口,用于分页查询数据
package com.bbs.util;
import java.util.Collection;
/**
* @Date: 2023/02/11/ 0:34
* @description
*/
public interface PageHandler<T> {
Collection<T> page(int page,int offset);
}
2.3 Excel数据载体bean
package com.bbs.bean;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import java.util.Date;
/**
* @Date: 2023/02/10/ 22:57
* @description
*/
public class DemoData {
@ExcelProperty("字符串标题")
private String string;
@ExcelProperty("日期标题")
private Date date;
@ExcelProperty("数字标题")
private Double doubleData;
/**
* 忽略这个字段
*/
@ExcelIgnore
private String ignore;
public String getString() {
return string;
}
public void setString(String string) {
this.string = string;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public Double getDoubleData() {
return doubleData;
}
public void setDoubleData(Double doubleData) {
this.doubleData = doubleData;
}
public String getIgnore() {
return ignore;
}
public void setIgnore(String ignore) {
this.ignore = ignore;
}
}
3、导出数据效果
EasyExcel工具类
package com.bbs.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.File;
import java.util.*;
import java.util.function.Supplier;
/**
* @Date: 2023/02/10/ 22:35
* @description
*/
public class EasyExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);
/**
* 批量读取excel并写入数据库
* @param file
* @param sheet
* @param chunkSize
* @param clazz
* @param handler
* @param <T>
*/
public static <T> void chunkRead2DB(File file,int sheet,int chunkSize,Class<T> clazz,Handler<T> handler){
EasyExcel.read(file, clazz, new ReadListener<T>() {
private List<T> cache = ListUtils.newArrayListWithExpectedSize(chunkSize);
private int batch = 0;
@Override
public void invoke(T data, AnalysisContext context) {
cache.add(data);
if(cache.size()>=chunkSize){
LOGGER.info("第{}批次,读取数据{}条,开始插入数据库",batch,cache.size());
handler.handle(cache);
LOGGER.info("第{}批次,插入数据库成功",batch);
cache = ListUtils.newArrayListWithExpectedSize(chunkSize);
++batch;
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("第{}批次,读取数据{}条,开始插入数据库",batch,cache.size());
handler.handle(cache);
LOGGER.info("第{}批次,插入数据库成功",batch);
}
}).sheet(sheet).doRead();
}
public static <T> void writeExcel(File file, Class<T> head, Collection<T> data) {
EasyExcel.write(file, head).sheet(0).doWrite(data);
}
/**
* 分页查询数据库并写入excel
*
* @param file 数据输出对象
* @param head 表头
* @param offset 分页偏移量
* @param pageHandler 分页处理器
* @param <T>
*/
public static <T> void writeExcel(File file, Class<T> head, int offset, Handler<T> pageHandler) {
ExcelWriter excelWriter = EasyExcel.write(file, head).build();
//当前页码
int page = 0;
while (true) {
//分页读取数据
Collection<T> data = pageHandler.page(page, offset);
if (data.isEmpty()) break;
//读取的而数据写入excel
excelWriter.write(data, EasyExcel.writerSheet(0).head(head).build());
//更新页码
++page;
}
//数据写入完毕关闭资源
excelWriter.finish();
}
/**
*机构数据排序并导出为excel
* @param file
* @param head
* @param deptId
* @param handler
* @param <T>
*/
public static <T> void deptSort2Excel(File file, Class<T> head, Dept deptId,Handler<Dept> handler) {
int BATCH_COUNT = 5000;
ExcelWriter excelWriter = EasyExcel.write(file, head).build();
Stack<Dept> stack = new Stack<>();
stack.add(deptId);
List<Dept> data = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
while (!stack.isEmpty()){
Dept pop = stack.pop();
data.add(pop);
if(data.size()>=BATCH_COUNT){
//读取的而数据写入excel
excelWriter.write(data, EasyExcel.writerSheet(0).head(Dept.class).build());
//清理数据
data =ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
Collection<Dept> child = handler.child(pop);
if(!child.isEmpty()){
stack.addAll(child);
}
}
//数据写入完毕关闭资源
excelWriter.finish();
}
/**
* 机构数据排序并导出为excel 这种方式比较慢
* @param file
* @param head
* @param deptId
* @param handler
* @param <T>
*/
public static <T> void deptSort2Excel(File file, Class<T> head, Dept deptId,Supplier<List<Dept>> handler) {
List<Dept> depts = handler.get();
if(depts == null || depts.isEmpty()){
LOGGER.warn("机构数据为空,无法进行机构树转换");
return;
}
Stack<Dept> stack = new Stack<>();
stack.add(deptId);
List<Dept> data = ListUtils.newArrayListWithExpectedSize(depts.size());
while (!stack.isEmpty()){
Dept pop = stack.pop();
data.add(pop);
for (Dept dept : depts) {
if(pop.getDeptId().equals(dept.getDeptId2())){
stack.add(dept);
}
}
}
EasyExcel.write(file, head).sheet(0).doWrite(data);
}
}
Handler接口
package com.bbs.util;
import java.util.Collection;
/**
* @Date: 2023/02/11/ 0:34
* @description
*/
public interface Handler<T> {
Collection<T> page(int page,int offset);
void handle(Collection<T> data);
Collection<T> child(T t);
}