前言
众所周知,导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基本用法
@TestpublicvoidtestHSSF()throwsException{//创建一个工作簿
HSSFWorkbookwb=newHSSFWorkbook();//创建一个工作表
HSSFSheetsheet=wb.createSheet();//创建字体
HSSFFontfont1=wb.createFont();
HSSFFontfont2=wb.createFont();
font1.setFontHeightInPoints((short)14);
font1.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
font2.setFontHeightInPoints((short)12);
font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());//创建单元格样式
HSSFCellStylecss1=wb.createCellStyle();
HSSFCellStylecss2=wb.createCellStyle();
HSSFDataFormatdf=wb.createDataFormat();//设置单元格字体及格式css1.setFont(font1);
css1.setDataFormat(df.getFormat("#,##0.0"));
css2.setFont(font2);
css2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));//创建行
for(inti=0;i
HSSFRowrow=sheet.createRow(i);for(intj=0;j
HSSFCellcell=row.createCell(j);
cell.setCellValue("Spring");
cell.setCellStyle(css1);
HSSFCellcell2=row.createCell(j+1);
cell2.setCellValue(newHSSFRichTextString("Hello!"+j));
cell2.setCellStyle(css2);
}
}//写文件
FileOutputStreamfos=newFileOutputStream("G:/wb.xls");
wb.write(fos);
fos.close();
}
@TestpublicvoidtestSS()throwsIOException{
Workbook[]wbs={newHSSFWorkbook(),newXSSFWorkbook()};for(inti=0;i
Workbookwb=wbs[i];
CreationHelpercreationHelper=wb.getCreationHelper();
Sheetsheet=wb.createSheet();for(intj=0;j
Rowrow=sheet.createRow(j);
Cellcell=row.createCell(0);
cell.setCellValue(creationHelper.createRichTextString("ABC"));
}
Stringfilename="G:/workbook.xls";if(wbinstanceofXSSFWorkbook){
filename=filename+"x";
}
wb.write(newFileOutputStream(filename));
wb.close();
}
}
JXLS基本用法
@Testpublicvoidabc()throwsIOException{longt1=System.currentTimeMillis();
ListuserList=newArrayList<>();for(inti=0;i
userList.add(newUser("zhangsan","10001"));
}
InputStreamis=newFileInputStream("G:/object_collection_template.xlsx");
OutputStreamos=newFileOutputStream("G:/object_collection_out.xlsx");
Contextcontext=newContext();
context.putVar("users",userList);
JxlsHelper.getInstance().processTemplate(is,os,context);longt2=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行,就是一个滑动窗口*/@TestpublicvoidtestWindow()throwsIOException{//在内存中保存100行,当行数超过100时将其刷新到磁盘System.out.println(Runtime.getRuntime().freeMemory());
SXSSFWorkbookwb=newSXSSFWorkbook(100);
SXSSFSheetsheet=wb.createSheet();for(inti=0;i
SXSSFRowrow=sheet.createRow(i);for(intj=0;j
SXSSFCellcell=row.createCell(j);
cell.setCellValue(newCellReference(cell).formatAsString());
}
}//行号小于900的行已经被刷新到磁盘,无法访问
for(intrownum=0;rownum
Assert.assertNull(sheet.getRow(rownum));
}//最后100行仍然在内存中
for(intrownum=900;rownum
Assert.assertNotNull(sheet.getRow(rownum));
}
FileOutputStreamfos=newFileOutputStream("G:/sxssf.xlsx");
wb.write(fos);
fos.close();//处理工作表在磁盘上产生的临时文件wb.dispose();
}/**
*关闭自动刷新,并且手动控制哪些数据被写到磁盘*/@TestpublicvoidtestAutoFlush()throwsIOException{//关闭自动刷新,并且在内存中累积所有的行
SXSSFWorkbookwb=newSXSSFWorkbook(-1);
SXSSFSheetsheet=wb.createSheet();for(intrownum=0;rownum
Rowrow=sheet.createRow(rownum);for(intcellnum=0;cellnum
Cellcell=row.createCell(cellnum);
cell.setCellValue(newCellReference(cell).formatAsString());
}//手动控制刷新多少行到磁盘
if(rownum%100==0){//保留最后100行,其余的刷新到磁盘
sheet.flushRows(100);//sheet.flushRows();//所有行,全部刷新到磁盘}
}
FileOutputStreamfos=newFileOutputStream("G:/sxssf2.xlsx");
wb.write(fos);
fos.close();//删除产生的临时文件wb.dispose();
}/**
*SXSSF刷新工作表数据到磁盘(每个工作表一个临时文件),而且,临时文件可能会增长到非常大。
*例如,对于一个20M的csv数据它的临时xml数据有可能会变得超过1G
*如果你任务临时文件的的大小是一个问题的话,那么你可以告诉SXSSF用gzip来压缩它。
*SXSSFWorkbookwb=newSXSSFWorkbook();
*wb.setCompressTempFiles(true);//tempfileswillbegzipped*/
Maven依赖
mons
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
参考