excel java 导出优化 java导出excel很慢

转载

bigrobin 2023-07-21 18:47:19

文章标签 excel java 导出优化 poi导出excel 损坏数据 css 临时文件 文章分类 Java 后端开发

前言

众所周知，导Excel分为两步：

抓取数据(查数据)

写数据到Excel文件

这两步都比较耗时间，一般我们从数据库查数据，然后组装数据，最后写数据。

查数据不是本节的重点，主要是SQL，索引这一块，此处不讨论。本节重点是写数据。

问题

当数据量小(比如，几千几万条)的时候可以采用同步的方式，不用考虑别的。

而当数据量大的时候(比如，几十上百万)的时候问题就暴露出来了。

首先，慢是肯定的了。少则几十秒，多则几十分钟都是有可能的。

这还是小问题，最要命的因为一个导出把系统搞挂了。。。

笔者曾经见过，因为一个导出，系统直接挂了，还严重拖慢了同一台机器上的其它应用，最终宕机了。。。

究其原因，大量数据堆积在内存中，可能会造成内存溢出。夸张一点，几百万条数据每条数据几十个字段都放到内存中，要等到全部写完这些内存才会释放。

方案

针对单个工作表(sheet)的行数限制，可以分多个工作表

针对单个文件太大不容易打开，可以分多个文件，最终打成压缩包

针对内存溢出，可以分批导，每次导一批数据，分多次导

建议

异步下载！异步！异步！异步！

如果对样式没什么要求，也不用公式的话，强烈推荐导出CSV格式

可以采用多线程的方式，先查总数，然后分一下看需要多少个线程，每个线程读取一部数据并写入单独Excel文件；当然，也可以多线程读，单线程写

分批导，这一点跟上一步类似

思路

客户端发起下载请求以后，服务端异步执行下载任务并生成下载文件，客户端读取这个文件下载。

那么问题来了，客户端怎么知道服务端下载文件已经生成好了呢？

有一个方案是：WebSocket

客户端发起下载请求并收到服务端的响应以后和服务端建立一个WebSocket连接，这样服务端生成完文件以后就可以主动通知客户端了。

组件

关于导Excel的组件，笔者用过以下4种：

CSV

POI

JXLS

EasyPoi

其中，POS就不用说了，CSV真的很快，不熟悉CSV的请参考《Java导出CSV文件》，JXLS用模板的方式也很方便，可以预先定义好样式格式，easypoi是在poi基础上做了封装，使用注解就能轻松完成导出。

Apache POI

HSSF与XSSF基本用法

@Testpublic void testHSSF() throwsException {//创建一个工作簿
HSSFWorkbook wb = newHSSFWorkbook();//创建一个工作表
HSSFSheet sheet =wb.createSheet();//创建字体
HSSFFont font1 =wb.createFont();
HSSFFont font2=wb.createFont();
font1.setFontHeightInPoints((short) 14);
font1.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
font2.setFontHeightInPoints((short) 12);
font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//创建单元格样式
HSSFCellStyle css1 =wb.createCellStyle();
HSSFCellStyle css2=wb.createCellStyle();
HSSFDataFormat df=wb.createDataFormat();//设置单元格字体及格式
css1.setFont(font1);
css1.setDataFormat(df.getFormat("#,##0.0"));
css2.setFont(font2);
css2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));//创建行
for (int i = 0; i < 20; i++) {
HSSFRow row=sheet.createRow(i);for (int j = 0; j < 10; j = j + 2) {
HSSFCell cell=row.createCell(j);
cell.setCellValue("Spring");
cell.setCellStyle(css1);
HSSFCell cell2= row.createCell(j+1);
cell2.setCellValue(new HSSFRichTextString("Hello! " +j));
cell2.setCellStyle(css2);
}
}//写文件
FileOutputStream fos = new FileOutputStream("G:/wb.xls");
wb.write(fos);
fos.close();
}
@Testpublic void testSS() throwsIOException {
Workbook[] wbs= {new HSSFWorkbook(), newXSSFWorkbook()};for (int i = 0; i < wbs.length; i++) {
Workbook wb=wbs[i];
CreationHelper creationHelper=wb.getCreationHelper();
Sheet sheet=wb.createSheet();for (int j = 0; j < 10; j++) {
Row row=sheet.createRow(j);
Cell cell= row.createCell(0);
cell.setCellValue(creationHelper.createRichTextString("ABC"));
}
String filename= "G:/workbook.xls";if (wb instanceofXSSFWorkbook) {
filename= filename + "x";
}
wb.write(newFileOutputStream(filename));
wb.close();
}
}

JXLS基本用法

@Testpublic void abc() throwsIOException {long t1 =System.currentTimeMillis();
List userList = new ArrayList<>();for (int i = 0; i < 100000; i++) {
userList.add(new User("zhangsan", "10001"));
}
InputStream is= new FileInputStream("G:/object_collection_template.xlsx");
OutputStream os= new FileOutputStream("G:/object_collection_out.xlsx");
Context context= newContext();
context.putVar("users", userList);
JxlsHelper.getInstance().processTemplate(is, os, context);long t2 =System.currentTimeMillis();
System.out.println(t2-t1);
}

SXSSF

SXSSF扩展自XSSF，用于当非常大的工作表要导出且内存受限制的时候。SXSSF占用很少的内存是因为它限制只能访问滑动窗口中的数据，而XSSF可以访问文档中所有数据。那些不在滑动窗口中的数据是不能访问的，因为它们已经被写到磁盘上了。

你可以通过new SXSSFWorkbook(int windowSize)来指定窗口的大小，也可以通过SXSSFSheet#setRandomAccessWindowSize(int windowSize)来设置每个工作表的窗口大小。

当通过createRow()创建一个新行的时候，总的行数可能会超过窗口大小，这个时候行号最低的那行会被刷新到磁盘而且不能通过getRow()访问。

默认的窗口大小是100。如果设置为-1，则表示不限，这就意味着没有记录会被自动刷新到磁盘，除非你手动调用flushRow()刷新。

注意，SXSSF会产生临时文件，你必须总是明确地清理它们，通过调用dispose方法。

/*** 写一个工作表，窗口大小是100
* 当达到101行的时候，行号为0的行(rownum=0)被刷新到磁盘，并从内存中删除
* 当行号达到102的时候，rownum=1的行被刷新到磁盘，并从内存中删除
* 也就是说内存中最多保存100行，就是一个滑动窗口*/@Testpublic void testWindow() throwsIOException {//在内存中保存100行，当行数超过100时将其刷新到磁盘
System.out.println(Runtime.getRuntime().freeMemory());
SXSSFWorkbook wb= new SXSSFWorkbook(100);
SXSSFSheet sheet=wb.createSheet();for (int i = 0; i < 1000; i++) {
SXSSFRow row=sheet.createRow(i);for (int j = 0; j < 10; j++) {
SXSSFCell cell=row.createCell(j);
cell.setCellValue(newCellReference(cell).formatAsString());
}
}//行号小于900的行已经被刷新到磁盘，无法访问
for (int rownum = 0; rownum < 900; rownum++) {
Assert.assertNull(sheet.getRow(rownum));
}//最后100行仍然在内存中
for (int rownum = 900; rownum < 1000; rownum++) {
Assert.assertNotNull(sheet.getRow(rownum));
}
FileOutputStream fos= new FileOutputStream("G:/sxssf.xlsx");
wb.write(fos);
fos.close();//处理工作表在磁盘上产生的临时文件
wb.dispose();
}/*** 关闭自动刷新，并且手动控制哪些数据被写到磁盘*/@Testpublic void testAutoFlush() throwsIOException {//关闭自动刷新，并且在内存中累积所有的行
SXSSFWorkbook wb = new SXSSFWorkbook(-1);
SXSSFSheet sheet=wb.createSheet();for (int rownum = 0; rownum < 1000; rownum++) {
Row row=sheet.createRow(rownum);for (int cellnum = 0; cellnum < 10; cellnum++) {
Cell cell=row.createCell(cellnum);
cell.setCellValue(newCellReference(cell).formatAsString());
}//手动控制刷新多少行到磁盘
if (rownum % 100 == 0) {//保留最后100行，其余的刷新到磁盘
sheet.flushRows(100);//sheet.flushRows();//所有行，全部刷新到磁盘
}
}
FileOutputStream fos= new FileOutputStream("G:/sxssf2.xlsx");
wb.write(fos);
fos.close();//删除产生的临时文件
wb.dispose();
}/*** SXSSF刷新工作表数据到磁盘(每个工作表一个临时文件)，而且，临时文件可能会增长到非常大。
* 例如，对于一个20M的csv数据它的临时xml数据有可能会变得超过1G
* 如果你任务临时文件的的大小是一个问题的话，那么你可以告诉SXSSF用gzip来压缩它。
* SXSSFWorkbook wb = new SXSSFWorkbook();
* wb.setCompressTempFiles(true); // temp files will be gzipped*/

Maven依赖

org.apache.commons
commons-csv
1.5
org.apache.poi
poi
3.17
org.apache.poi
poi-ooxml
3.17
org.jxls
jxls
2.4.5
org.jxls
jxls-poi
1.0.15
junit
junit
4.12
test

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。