小G最近搞一个导出excel项目,但是数据量比较多导出来tomcat内存狂飙,开始使用xssf内存狂升,后来使用sax还好点,但是
行数到5万左右,就2G,
第一步骤定位:liunx上面使用jmap -histo |head -30
可以查到使用内存最多是C和1,2,c是char[] ,1,2为xml的解析,那就可以看到时poi在解析过程中使用
第二步,找到问题后对问题进行分析
分析结果如下:
自测过程中数据问题:
解析方法 | excel行数 | 内存情况 |
---|---|---|
SXSSFWorkbook | 5万行 | 2-4G内存 |
XSSFWorkbook | 几千行 | G级别反正内存消耗很大 |
第三步解决思路:
1、新增多个sheetname效果虽然好点,但是内存还是不怎么样在执行到10万左右,该方不可行;
2、分多个文件,5万-6万到2G,应该可以;
3、调用gc进行垃圾回收;
4、加锁共享锁lock,因为执行多个的话,就会2G的倍数,所以加一个锁,针对服务器加锁,其实在读取的时候内存比较大,但是这个是瞬间的,(如果数据不超过1万条,是可以没有问题,不用加锁),这样就可以同时执行多个文件进行导出操作
5,读取的时候使用sax进行读取数据
public static Lock lockPOItool = new ReentrantLock();// 锁对象
public static void poi(){
Boolean isLockBoolean =false;
if(lockPOItool.tryLock(5l, TimeUnit.MINUTES){
isLockBoolean=true;
//获取锁成功
}
XSSFWorkbook xssfworkbook = null;
SXSSFWorkbook workbook = null;
SXSSFSheet sheet = null;
sheetName = null ;
fileName = null;
sheetName="根据业务去划分生成多excel";
fileName=sheelNameCount.toString()+fileName;
FileInputStream fileInputStream =new FileInputStream(path);//原来的地址
SXSSFCell cell=null;
XSSFSheet xssheet=null;
POIFSFileSystem pfs =null;
try{
//如果有文件说明以前新建过直接进行追加
Boolean isFrist = new File(path+ fileName).exists();
if(i!sFrist){
workbook = new SXSSFWorkbook();
sheet = workbook.createSheet(sheetName);
SXSSFRow row = sheet.createRow((short) 0);
cell = row.createCell((short) 0);
//如果有标题创建标题
rowNum = sheet.getLastRowNum() + 1;//有标题+1从第二行进行
}else{
//追加操作的
pfs = new POIFSFileSystem(fileInputStream);
EncryptionInfo encInfo = new EncryptionInfo(pfs);
Decryptor decryptor = Decryptor.getInstance(encInfo);
decryptor.verifyPassword(pwd);//读取加密的这个是追加的方法 xssf解密,然后读取到sxssf里面
InputStream pfsInputStream=decryptor.getDataStream(pfs);
xssfworkbook = new XSSFWorkbook(pfsInputStream);
pfsInputStream.close();
pfs.close();
xssheet = xssfworkbook.getSheet(sheetName);
rowNum = xssheet.getLastRowNum() + 1;
workbook = new SXSSFWorkbook(xssfworkbook,-1);//-1就是不在使用内存
sheet=workbook.getSheet(sheetName);
}
//
while(rowNum<50){
//数据的存放
SXSSFRow row = sheet.createRow(rowNum);
row.createCell(0).setCellValue(“”“”);//
row.createCell(1).setCellValue(“”“”);//
//官网说这个在(xssfworkbook,-1);有内存中使用,反正这个地方使用也可以
((SXSSFSheet)sheet).flushRows(dataModels.size()-dataModelsInde);
rowNum++;
}
FileOutputStream outBuildE=null;
try{
outBuildE = new FileOutputStream(path + filename);
outBuildE.flush();
workbook.write(outBuildE);
outBuildE.close();
workbook.dispose();
}catch(Exception e){
}
xssfworkbook.close();
workbook.dispose();
}catch(Exception e){
}finally{
workbook=null;
xssfworkbook=null;
sheet=null;
cell=null;
xssheet=null;
pfs=null;
gcPoi();//缓存
if(isLockBoolean){
lockPOItool.unlock();
}catch(Exception e){
}
}
//手动清空下缓存
private void gcPoi(String msg) {
try {
Runtime.getRuntime().gc();
}catch(Exception e){
}
}
官方的接口如下3.8大家可以看下
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
}
官方如下:
public static void main(String[] args) throws Throwable {
SXSSFWorkbook wb = new SXSSFWorkbook(-1); // turn off auto-flushing and accumulate all rows in memory
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
// manually control how rows are flushed to disk
if(rownum % 100 == 0) {
((SXSSFSheet)sh).flushRows(100); // retain 100 last rows and flush all others
// ((SXSSFSheet)sh).flushRows() is a shortcut for ((SXSSFSheet)sh).flushRows(0),
// this method flushes all rows
}
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}