在开发过程中经常会有需要将数据导出到 excel 的需求,当数据量很大,达到几万甚至几十万、几百万级别的时候,如何加快生成 excel 的速度呢?
首先普及一下知识背景:
Excel2003 及以下版本一张表最多支持 65536 行、256 列数据,所以要生成十万、百万级别数据就不能用 Excel2003 了;
Excel2007 版本一张表最多支持1048576行,16384 列,基本可以满足百万级别以下的数据量级。
一般通过 poi 生成 excel 的方式如下:(原谅我以前没有研究过poi,就只会用这种方式,而且网上的demo好多也是这种方式)
1 public static void exportDataToExcel1(String[] header, List<String[]> datas, String path) {
2 File file = new File(path);
3 Workbook workbook = null;
4 if (path.endsWith(EXCEL_2003)) {
5 workbook = new HSSFWorkbook();
6 }
7 if (path.endsWith(EXCEL_2007)) {
8 workbook = new XSSFWorkbook();
9 }
10 Sheet sheet = workbook.createSheet();
11 Row firstRow = sheet.createRow(0); //第一行
12 for (int i = 0; i < header.length; i++) {
13 Cell cell = firstRow.createCell(i);
14 cell.setCellValue(header[i]);
15 }
16 if (datas != null && datas.size() > 0) {
17 for (int i = 0; i < datas.size(); i++) {
18 Row row = sheet.createRow(i + 1);
19 String[] d = datas.get(i);
20 for (int j = 0; j < d.length; j++) {
21 Cell cell = row.createCell(j);
22 cell.setCellValue(d[j]);
23 }
24 }
25 }
26 try {
27 OutputStream outputStream = new FileOutputStream(file);
28 workbook.write(outputStream);
29 outputStream.flush();
30 outputStream.close();
31 } catch (FileNotFoundException e) {
32 e.printStackTrace();
33 } catch (IOException e) {
34 e.printStackTrace();
35 }
36 }
利用上述方式生成一张 10万 行、30 列的 excel 表在我的电脑上耗时大约40多秒
数据准备了 1731 ms
导出花费了 46795 ms
查看 poi 官网http://poi.apache.org/spreadsheet/index.html发现从 3.8 beta3 版本开始新增 SXSSF api 用于解决大数据量场景
这种方式新增了自动刷入数据的机制,可以设置一个数据量阈值,达到这个阈值后会将数据刷入到磁盘,缓解了运行时的压力。
改后的代码如下:
public static void exportDataToExcel(String[] header, List<String[]> datas, String path) {
File file = new File(path);
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(100);
Sheet sheet = sxssfWorkbook.createSheet();
Row firstRow = sheet.createRow(0); //第一行
for (int i = 0; i < header.length; i++) {
Cell cell = firstRow.createCell(i);
cell.setCellValue(header[i]);
}
if (datas != null && datas.size() > 0) {
for (int i = 0; i < datas.size(); i++) {
Row row = sheet.createRow(i + 1);
String[] d = datas.get(i);
for (int j = 0; j < d.length; j++) {
Cell cell = row.createCell(j);
cell.setCellValue(d[j]);
}
}
}
try {
OutputStream outputStream = new FileOutputStream(file);
sxssfWorkbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
sxssfWorkbook.dispose();
}
}
使用这种方式测试相同量级的数据,导出excel缩短到了6、7秒,可见这个提升幅度还是很明显的。
数据准备了 1096 ms
导出花费了 6784 ms