easyexcel多个sheet导入_EasyExcel写入百万级数据到多sheet---非注解方式

EasyExcel是什么？

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

一、项目需求

从mongo库中查询数据，导出到excel文件中。但是动态导出的excel有多少列、列名是什么、有多少sheet页都需要动态获取。所以生成的excel也必须是动态生成，不能通过注解配置对象映射。而且写入的数据量，有可能达到100W级，使用传统的POI工具，需要把excel数据全部加载到内存空间，内存空间很容易OOM。所以选择了阿里的EasyExcel，据说可以高效的解决POI的OOM问题。

二、测试Demo

1、引入的pom依赖

com.alibaba

easyexcel

1.1.2-beta5

org.apache.poi

poi-ooxml

3.17

2、测试代码

packagecom.movitech.product.datahub.util;

importcom.alibaba.excel.EasyExcelFactory;

importcom.alibaba.excel.ExcelReader;

importcom.alibaba.excel.ExcelWriter;

importcom.alibaba.excel.context.AnalysisContext;

importcom.alibaba.excel.context.WriteContext;

importcom.alibaba.excel.event.AnalysisEventListener;

importcom.alibaba.excel.event.WriteHandler;

importcom.alibaba.excel.metadata.Sheet;

importcom.alibaba.excel.metadata.Table;

importcom.alibaba.excel.support.ExcelTypeEnum;

importcom.alibaba.excel.write.ExcelBuilderImpl;

import org.apache.poi.ss.usermodel.*;

import java.io.*;

importjava.lang.reflect.Field;

importjava.util.ArrayList;

importjava.util.List;

/**

* @Author JAY

* @Date 2019/8/29 11:00

* @Description TODO

**/

public classEasyExcelUtil {

public static String excelFilePath = "C:\\Users\\lenovo\\Desktop\\Jay01-(jay01)-v5自定义导入数据.xls";

public static voidmain(String[] args) {

try{

writeExcel(excelFilePath);

} catch(IOException e) {

e.printStackTrace();

}

}

public static void writeExcel(String excelFile) throwsIOException {

// 文件输出位置

OutputStream out = newFileOutputStream(excelFile);

ExcelWriter writer =EasyExcelFactory.getWriter(out);

// 动态添加表头，适用一些表头动态变化的场景

Sheet sheet1 = new Sheet(1, 0);

sheet1.setSheetName("第一个sheet");

// 创建一个表格，用于 Sheet 中使用

Table table1 = new Table(1);

// 无注解的模式，动态添加表头

table1.setHead(createTestListStringHead());

// 写数据

writer.write1(new ArrayList<>(), sheet1, table1);

// 动态添加表头，适用一些表头动态变化的场景

Sheet sheet2 = new Sheet(2, 0);

sheet2.setSheetName("第2个sheet");

/*

添加TableStyle属性会使内存OOM，没办法满足分批插入100W条数据

TableStyle tableStyle = new TableStyle();

com.alibaba.excel.metadata.Font font = new com.alibaba.excel.metadata.Font();

font.setBold(true);

tableStyle.setTableContentFont(font);

sheet2.setTableStyle(tableStyle);

*/

// 创建一个表格，用于 Sheet 中使用

Table table2 = new Table(2);

// 无注解的模式，动态添加表头

table2.setHead(createTestListStringHead());

writer.write1(new ArrayList<>(), sheet2, table2);

int x = 0;

while (x < 1000000) {

// 模拟分批写入数据到excel，每次写入100条

System.out.println("x = " +x);

Table tableX = new Table(1);

// 每次从sheet的第几行开始写入

sheet1.setStartRow(x);

writer.write1(createDynamicModelList(x), sheet1, tableX);

Table tableX2 = new Table(1);

sheet2.setStartRow(x);

writer.write1(createDynamicModelList(x), sheet2, tableX2);

x = x + 100;

}

// 将上下文中的最终 outputStream 写入到指定文件中

writer.finish();

// 关闭流

out.close();

}

private static List> createDynamicModelList(intx) {

List> rows = new ArrayList<>();

for (int i= x; i < 100 + x; i++){

List row = new ArrayList<>();

row.add("字符串-" +i);

row.add(Long.valueOf(187837834L) +i);

row.add(Integer.valueOf(2233 +i));

row.add("宁-" +i);

row.add("微信公众号： demo");

rows.add(row);

}

returnrows;

}

private static List>createTestListStringHead() {

// 模型上没有注解，表头数据动态传入

List> head = new ArrayList>();

List headCoulumn1 = new ArrayList();

List headCoulumn2 = new ArrayList();

List headCoulumn3 = new ArrayList();

List headCoulumn4 = new ArrayList();

List headCoulumn5 = new ArrayList();

headCoulumn1.add("第1列");

headCoulumn2.add("第2列");

headCoulumn3.add("第3列");

headCoulumn4.add("第4列");

headCoulumn5.add("第5列");

head.add(headCoulumn1);

head.add(headCoulumn2);

head.add(headCoulumn3);

head.add(headCoulumn4);

head.add(headCoulumn5);

returnhead;

}

}

3、执行结果

总结：

此测试代码可以直接运行测试查看结果。

我配置的jvm运行参数，

我只给了10M空间，但是往excel中写入100W数据，程序并没有出现OOM。可以看到，使用EasyExcel，确实解决了OOM问题。

但是实际情况，EasyExcel不足以满足我的业务需求。因为除了百万级的数据导出之外，还需要进行sheet页隐藏、行隐藏、列隐藏等操作。目前EasyExcel的API，还没有那么多的功能变化。不过，easyExcel提供了自定义拦截器的功能，貌似可以给excel做样式处理。大致测试了一下，可以隐藏列和sheet，但是不知道怎么隐藏行。测试代码如下：

(1)隐藏列，通过自定义拦截器

public static void writeExcelToSheet(String excelFile, Sheet sheet) throwsIOException {//文件输出位置

OutputStream out = newFileOutputStream(excelFile);

ExcelWriter writer= EasyExcelFactory.getWriterWithTempAndHandler(null, out, ExcelTypeEnum.XLS, true, newWriteHandler() {

@Overridepublic void sheet(inti, org.apache.poi.ss.usermodel.Sheet sheet) {

sheet.setColumnHidden(0,true);

sheet.setColumnHidden(1,true);

}

@Overridepublic void row(inti, Row row) {

System.out.println("row : " +row.getRowNum());

}

@Overridepublic void cell(inti, Cell cell) {

System.out.println("cell : " +i);

}

});

Table table1= new Table(1);

table1.setHead(createTestListStringHead());//写数据

writer.write1(createDynamicModelList(0), sheet, table1);//将上下文中的最终 outputStream 写入到指定文件中

writer.finish();//关闭流

out.close();

}

(2)隐藏sheet页，通过反射获取Workbook，用wb来设置隐藏sheet页

/*** **获取workbook**

* 因为EasyExcel这个库设计的原因

* 只能使用反射获取workbook

*

*@paramwriter

*@return

*/

private staticWorkbook getWorkbook(ExcelWriter writer) {

Workbook workbook= null;try{

Class> clazz1 = Class.forName("com.alibaba.excel.ExcelWriter");

Field[] fs=clazz1.getDeclaredFields();for(Field field : fs) {//要设置属性可达，不然会抛出IllegalAccessException异常

field.setAccessible(true);if ("excelBuilder".equals(field.getName())) {

ExcelBuilderImpl excelBuilder=(ExcelBuilderImpl) field.get(writer);

Class> clazz2 = Class.forName("com.alibaba.excel.write.ExcelBuilderImpl");

Field[] fs2=clazz2.getDeclaredFields();for(Field field2 : fs2) {

field2.setAccessible(true);if ("context".equals(field2.getName())) {

WriteContext context=(WriteContext) field2.get(excelBuilder);

workbook=context.getWorkbook();

}

}

}

}

}catch(ClassNotFoundException e) {

e.printStackTrace();

}catch(IllegalAccessException e) {

e.printStackTrace();

}returnworkbook;

}

public static void writeExcel(String excelFile) throwsIOException {//文件输出位置

OutputStream out = newFileOutputStream(excelFile);

ExcelWriter writer=EasyExcelFactory.getWriter(out);//动态添加表头，适用一些表头动态变化的场景

Sheet sheet1 = new Sheet(1, 0);

sheet1.setSheetName("第一个sheet");//创建一个表格，用于 Sheet 中使用

Table table1 = new Table(1);//无注解的模式，动态添加表头

table1.setHead(createTestListStringHead());//写数据

writer.write1(new ArrayList<>(), sheet1, table1);//动态添加表头，适用一些表头动态变化的场景

Sheet sheet2 = new Sheet(2, 0);

sheet2.setSheetName("第2个sheet");/*添加TableStyle属性会使内存OOM

TableStyle tableStyle = new TableStyle();

com.alibaba.excel.metadata.Font font = new com.alibaba.excel.metadata.Font();

font.setBold(true);

tableStyle.setTableContentFont(font);

sheet2.setTableStyle(tableStyle);*/

//创建一个表格，用于 Sheet 中使用

Table table2 = new Table(2);//无注解的模式，动态添加表头

table2.setHead(createTestListStringHead());

writer.write1(new ArrayList<>(), sheet2, table2);int x = 0;while (x < 10000) {

System.out.println("x = " +x);

Table tableX= new Table(1);

sheet1.setStartRow(x);

writer.write1(createDynamicModelList(x), sheet1, tableX);

Table tableX2= new Table(1);

sheet2.setStartRow(x);

writer.write1(createDynamicModelList(x), sheet2, tableX2);

x= x + 100;

}//获取workbook，隐藏第2页sheet

Workbook workbook = getWorkbook(writer);

workbook.setSheetHidden(1,true);//将上下文中的最终 outputStream 写入到指定文件中

writer.finish();//关闭流

out.close();

}

参考资源 https://segmentfault.com/a/1190000019472781，https://github.com/alibaba/easyexcel