使用EasyExcel导出百万条数据

本文主要是介绍使用EasyExcel导出百万条数据，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

使用EasyExcel导出百万条数据

应用是基于100W条数据进行的测试
首先：导入相关需要的依赖：

		<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.16</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.16</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.10</version><exclusions><exclusion><groupId>org.apache.poi</groupId><artifactId>poi</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId></exclusion><exclusion><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId></exclusion></exclusions></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.16.20</version></dependency><!-- junit --><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-simple</artifactId><version>1.7.25</version><scope>compile</scope></dependency>

创建所需要的实体类，如下：

@Accessors(chain = true)
@Data
public class ExcelBean {@ExcelProperty("主键id")private String id;@ExcelProperty("姓名")private String name;@ExcelProperty("地址")private String address;@ExcelProperty("年龄")private Integer age;@ExcelProperty("数量")private Integer number;@NumberFormat("#.##")@ExcelProperty("身高")private Double high;@ExcelProperty("距离")private Double distance;@DateTimeFormat("yyyy-MM-dd HH:mm:ss")@ExcelProperty("开始时间")private Date startTime;@ExcelProperty("结束时间")private Date endTime;
}

创建所用到的测试类，如下：

@Slf4j
public class writeExcelByApi {public static final String FILE_NAME = "C:\\Users\\861123001\\Desktop\\mqtt压测软件\\自造数据\\test_04.xlsx";// 每个 sheet 写入的数据public static final int NUM_PER_SHEET = 300000;// 每次向 sheet 中写入的数据（分页写入）public static final int NUM_BY_TIMES = 50000;@Testpublic void writeExcelByApi(){String fileName = FILE_NAME;log.info("导出excel名称={}",fileName);long startTime = System.currentTimeMillis();//调用apiList<ExcelBean> date = getDate();EasyExcel.write(fileName,ExcelBean.class).sheet().doWrite(date);log.info("导出excel结束,数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);}/*** 获取excel 导出的数据** @return list 集合*/public static List<ExcelBean> getDate(){log.info("开始生成数据");//创建返回数据集合List<ExcelBean> list = new ArrayList<>();Date date = new Date();long startTime = System.currentTimeMillis();for (int i = 0; i < 2000000; i++) {//创建数据对象ExcelBean excelBean = new ExcelBean();ExcelBean excel = excelBean.setId(UUID.randomUUID().toString()).setName("小明" + (10000 + i)).setAddress("浙江省杭州市西湖").setAge(i).setNumber(i + 10000).setHigh(1.234 * i).setDistance(3.14 * i).setStartTime(date).setEndTime(date);list.add(excel);}log.info("数据生成结束，数据量={}，耗时={}ms", list.size(), System.currentTimeMillis() - startTime);return list;}
}

EasyExcel 导出 excel 应用优化一：可以通过分sheet来解决超出100万的数据

	@Testpublic void writeExcelByMulSheet() {String fileName = FILE_NAME;log.info("导出excel名称={}",fileName);long startTime = System.currentTimeMillis();//获取数据List<ExcelBean> date = getDate();//获取sheet的个数int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1;//指定写入的文件ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();for (int i = 0; i < sheetNum; i++) {long l = System.currentTimeMillis();//设置sheet的名字，每个sheet名称不能相同String sheetName = "sheet" + i;WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();//开始根结束行数int startNum = i * NUM_PER_SHEET;int endNum = i == sheetNum - 1 ? date.size() : (i + 1) *  NUM_PER_SHEET;excelWriter.write(date.subList(startNum, endNum), writeSheet);log.info("写入sheet={}，数据量{}-{}={}，耗时={}ms", sheetName, endNum, startNum, endNum - startNum, System.currentTimeMillis() - l);}//最好放在finally中excelWriter.finish();log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);}

EasyExcel 导出 excel 应用优化二：数据源 list 太大，直接读取全部的 list 数据导致 OOM
将 list 数据进行分页读取，并进行分页写入到 excel。这样还有个好处，每次每页读取部分数据，然后写入到 excel 中（相当于该批数据已经从内存刷到了磁盘），也增加了写入的效率；poi 中的导出excel，为此专门提供了一个刷新磁盘的 api，具体代码如下

	@Testpublic void writeExcelByMulWrite() {String fileName = FILE_NAME;log.info("导出excel名称={}",fileName);long startTime = System.currentTimeMillis();//获取数据List<ExcelBean> date = getDate();ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();//适用于针对100万数据以下的写法//WriteSheet writeSheet = EasyExcel.writerSheet("testSheet" ).build();//计算需要写的次数int times = date.size() % NUM_BY_TIMES == 0 ? date.size() / NUM_BY_TIMES : date.size() / NUM_BY_TIMES + 1;for (int i = 0; i < times; i++) {long l = System.currentTimeMillis();WriteSheet writeSheet = EasyExcel.writerSheet("testSheet" + i).build();//开始跟结束行数int startNum = i * NUM_BY_TIMES;int endNum = i == times - 1 ? date.size() : (i + 1) * NUM_BY_TIMES;excelWriter.write(date.subList(startNum, endNum), writeSheet);log.info("写入数量{}-{}={}，耗时={}ms", endNum, startNum, endNum - startNum, startTime - l);}//最好写在finally里if (excelWriter != null) {excelWriter.finish();}}

EasyExcel 导出 excel 应用优化三：结合前面两种方案
将 list 数据进行分页读取，并且每个 sheet 分多次写入，且写入到多个 sheet 中

	@Testpublic void writeExcelByMulSheetAndMulWrite() {String fileName = FILE_NAME;log.info("导出excel名称={}", fileName);long startTime = System.currentTimeMillis();//获取数据List<ExcelBean> date = getDate();//获取sheet表数int sheetNum = date.size() % NUM_PER_SHEET == 0 ? date.size() / NUM_PER_SHEET : date.size() / NUM_PER_SHEET + 1;//获取每个sheet导入的次数int writeNumPerSheet = NUM_PER_SHEET % NUM_BY_TIMES == 0 ? NUM_PER_SHEET / NUM_BY_TIMES : NUM_PER_SHEET / NUM_BY_TIMES + 1;// 最后一个 sheet 写入的数量int writeNumLastSheet = date.size() - (sheetNum - 1) * NUM_PER_SHEET;// 最后一个 sheet 写入的次数int writeNumPerLastSheet = writeNumLastSheet % NUM_BY_TIMES == 0 ? writeNumLastSheet / NUM_BY_TIMES : writeNumLastSheet / NUM_BY_TIMES + 1;// 指定写入的文件ExcelWriter excelWriter = EasyExcel.write(fileName, ExcelBean.class).build();for (int i = 0; i < sheetNum; i++) {String sheetName = "sheet" + i;WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).build();int writeNum = i == sheetNum - 1 ? writeNumPerLastSheet : writeNumPerSheet; // 每个sheet 写入的次数int endEndNum = i == sheetNum - 1 ? date.size() : (i + 1) * NUM_PER_SHEET; // 每个sheet 最后一次写入的最后行数for (int j = 0; j < writeNum; j++) {long l = System.currentTimeMillis();int startNum = i * NUM_PER_SHEET + j * NUM_BY_TIMES;int endNum = j == writeNum - 1 ? endEndNum : i * NUM_PER_SHEET + (j + 1) * NUM_BY_TIMES;excelWriter.write(date.subList(startNum, endNum), writeSheet);log.info("写入sheet={}，数据量={}-{}={}，耗时={}", sheetName, endNum, startNum, endNum - startNum, startTime - l);}}// 需要放入 finally 中if (excelWriter != null) {excelWriter.finish();}log.info("导出excel结束,总数据量={}，耗时={}ms", date.size(), System.currentTimeMillis() - startTime);}

这篇关于使用EasyExcel导出百万条数据的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---