大量数据导出到XLS ,内存溢出 ,期待高手解决 数据至少有10W条,导出到一个xls,分成8个SHEET。用POI 或者JXL 导出,数据量到一定的时候就会报内存溢出.问问各位老鸟有没有好的解决方法. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 我们程序一个 sheet最多6万条你的程序可能是 需要优化,不然爆是正常的。贴代码 分批导出给每个sheet做一次,每次做1000条给启动参数加大内存 一个SHEET 6万条? 你用什么工具导出的 jxl,不过代码做了很多优化, 呵呵 老大写的, 是嘛,那要请教下你了,我QQ 270578113 加下 QQ聊 先谢谢了 你怎么导出的?串行导出还是并行导出,如果是串行导出应该不会有这样的问题,如果是并行的话可能是并行的时候对xls的读写太频繁,队列太长,所以很多都积累在内存中,然后就内存出错,用串行方式吧,写完一张sheet再写另一张 我是写完一张在写另一张的,创建一个SHEET ,写完,在创建另一个SHEET在写的,关键是POI或者JXL 都会把前面的所有写的东西放在内存,数据大过 6W左右就会内存个溢出了 差不多,你说的情况是一个xls 一个sheet 可以, 现在 一个xls 多个sheet 不行 我的意思是你的数据可以分批写excel第一次写一部分,等第一个sheet满了,重新读这个文件在此基础上create new sheet 你从新读,POI也会把第一次写进去的东西加载到内存里的 public void exportMsc(HttpServletRequest request,HttpServletResponse response){ Long regionId = RequestUitls.parseLong(request, "regionId", true); String[] filePathArr = { Utility.getRealFile(importConfigMap.get("BSCFORXLS")), Utility.getRealFile(importConfigMap.get("BSCLINKFORXLS")), Utility.getRealFile(importConfigMap.get("BSCPCMFORXLS")), Utility.getRealFile(importConfigMap.get("BSCCELLFORXLS")), Utility.getRealFile(importConfigMap.get("RNCFORXLS")), Utility.getRealFile(importConfigMap.get("RNCLINKFORXLS")), Utility.getRealFile(importConfigMap.get("RNCATMFORXLS")) }; String filename = Utility.getRealFile("/upload/"+regionService.loadById(regionId).getName()+"-MSC"+".xls"); try { HSSFWorkbook book = new HSSFWorkbook(); for(int i = 0;i<filePathArr.length;i++) { HashMap<String,Object> map = xmlLoader(filePathArr[i]); String[] titleName = (String[])map.get("titleName"); if(i == 0) { HSSFSheet sheet = book.createSheet("BSC基本信息"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<Bsc> bscList = bscService.loadByRegionId(String.valueOf(regionId)); if(bscList != null) { int j = 1; for(Bsc bsc : bscList){ HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(bsc.getMsc().getName()); row.createCell(1).setCellValue(bsc.getName()); row.createCell(2).setCellValue(bsc.getBscId()); row.createCell(3).setCellValue(bsc.getSignalCode()); row.createCell(4).setCellValue(bsc.getDpc()); row.createCell(5).setCellValue(bsc.getLaiForExcel()); row.createCell(6).setCellValue(bsc.getBscNo()); row.createCell(7).setCellValue(bsc.getPcmNo()); j++; } bscList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); }else if(i ==1) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("BSC链路基本信息"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<BscLink> bscLinkList = bscLinkService.loadByRegionId(String.valueOf(regionId)); if(bscLinkList != null) { int j = 1; for(BscLink bscLink : bscLinkList){ HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(bscLink.getBsc().getMsc().getName()); row.createCell(1).setCellValue(bscLink.getBsc().getName()); row.createCell(2).setCellValue(bscLink.getSlc()); row.createCell(3).setCellValue(bscLink.getPcmId()); row.createCell(4).setCellValue(bscLink.getLinkChoose()); j++; } bscLinkList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); } else if(i ==2) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("闭塞电路信息"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<BscPcm> bscPcmList = bscPcmService.loadByRegionId(String.valueOf(regionId)); if(bscPcmList != null) { int j = 1; for(BscPcm bscPcm : bscPcmList){ HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(bscPcm.getBsc().getMsc().getName()); row.createCell(1).setCellValue(bscPcm.getBsc().getName()); row.createCell(2).setCellValue(bscPcm.getPcmId()); row.createCell(3).setCellValue(bscPcm.getOffsetStart()); row.createCell(4).setCellValue(bscPcm.getOffsetEnd()); j++; } bscPcmList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); }else if(i ==3) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("BSC_CELL"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<Cell> bscCellList = cellService.loadByRegionId(String.valueOf(regionId)); if(bscCellList != null) { int j = 1; for(Cell cell : bscCellList) { HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(cell.getBsc().getMsc().getName()); row.createCell(1).setCellValue(cell.getBsc().getName()); row.createCell(2).setCellValue(cell.getLaiForExcel()); row.createCell(3).setCellValue(cell.getCellIdForExcel()); System.out.println(j); j++; } bscCellList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); }else if (i == 4) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("RNC基本信息"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<Rnc> rncList = rncService.loadByRegionId(String.valueOf(regionId)); if(rncList != null) { int j = 1; for(Rnc rnc : rncList) { HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(rnc.getMsc().getName()); row.createCell(1).setCellValue(rnc.getName()); row.createCell(2).setCellValue(rnc.getRncId()); row.createCell(3).setCellValue(rnc.getDpc()); row.createCell(4).setCellValue(rnc.getRncNsap()); row.createCell(5).setCellValue(rnc.getLaiForExcel()); row.createCell(6).setCellValue(rnc.getRncNoId()); row.createCell(7).setCellValue(rnc.getRncNo()); j++; } rncList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); }else if (i == 5) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("RNC链路"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<RncLink> rncLinkList = rncLinkService.loadByRegionId(String.valueOf(regionId)); if(rncLinkList != null) { int j = 1; for(RncLink rncLink : rncLinkList) { HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(rncLink.getRnc().getMsc().getName()); row.createCell(1).setCellValue(rncLink.getRnc().getName()); row.createCell(2).setCellValue(rncLink.getRnc().getName()+"_"+(j-1)); row.createCell(3).setCellValue(rncLink.getSlc()); row.createCell(4).setCellValue(rncLink.getStfn()); row.createCell(5).setCellValue(rncLink.getStsn()); row.createCell(6).setCellValue(rncLink.getE1t1n()); row.createCell(7).setCellValue(rncLink.getVpi()); row.createCell(8).setCellValue(rncLink.getVci()); row.createCell(9).setCellValue(rncLink.getPvcpcr()); j++; } rncLinkList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); }else if (i == 6) { FileInputStream fis = new FileInputStream(filename); book = new HSSFWorkbook(fis); HSSFSheet sheet = book.createSheet("ATM承载信息"); HSSFRow head = sheet.createRow(0); for (int b=0; b < titleName.length; b++) { head.createCell(b).setCellValue(titleName[b]); } List<RncPvc> rncAtmList = rncPvcService.loadByRegionId(String.valueOf(regionId)); if(rncAtmList != null) { int j = 1; for(RncPvc rncPvc : rncAtmList) { HSSFRow row = sheet.createRow(j); row.createCell(0).setCellValue(rncPvc.getRnc().getMsc().getName()); row.createCell(1).setCellValue(rncPvc.getRnc().getName()); row.createCell(2).setCellValue(rncPvc.getRnc().getName()+"_BEARER_"+(j-1)); row.createCell(3).setCellValue(rncPvc.getPvcId()); row.createCell(4).setCellValue(rncPvc.getStfn()); row.createCell(5).setCellValue(rncPvc.getStsn()); row.createCell(6).setCellValue(rncPvc.getE1t1n()); row.createCell(7).setCellValue(rncPvc.getVpi()); row.createCell(8).setCellValue(rncPvc.getVci()); j++; } rncAtmList.clear(); } FileOutputStream fos = new FileOutputStream(filename); book.write(fos); fos.flush(); fos.close(); } } DownloadUtil.download(null, response, filename); } catch (Exception e) { e.printStackTrace(); } }附上代码 明白了你在sheet的for循环结尾前加一个System.gc()启动内存加到1G实在不行,你一个sheet一个sheet的做,然后手动合并 加内存指标不治本,我也在想 一个一个导出,然后在合并XLS 了 JXL 更加会内存溢出,还不如 POI POI 比JXL 溢出快http://www.javaeye.com/topic/422117 写完一个sheet之后你有没有close掉, 你写一个就close,你后面的数据就保存不进去了 你的book.write(fos);然后呢????book.close();都没的,当然要溢出啦, 既然你现在因为数据量太大无法全部保存到内存里,那就就必须考虑分块的写,写好就close掉,然后再打开再写再close 我用的是fos导出到 fos.close(), book 是没有close() 这个方法的 如果没有这个方法,应该有类似的吧,我用的jxl,有close,poi的不知道,但是方法应该类似吧 其实JXL也是一样,你写一次关闭一次,然后在打开,他照样会把你前面写好的东西加载到内存个. 看完前边的评论发现相当一部分人没用过jxl或poi然后把excel想当然的当成txt 好吧 有高手出现的时候求指点 qq1048745542 基础问题 难到N个人 java新手,简单问题 有关jimi的问题,gif与tif格式图片。。。 各位帮我看看这个程序出错在哪?我按照书上检察了n遍了。 [求助]请教JBuilder引用包的问题 接口是如何实现多重继承的? 请各位指点,矫正错误,我找不出来什么地方错了 jbuild安装问题 websphere的client问题?最多的分了..多谢.. 如何为数字加千分位 怎么用批处理导入csv文件到sqlite中 jquery1.4.2 升级为jquery1.7.1,并使用lhgdialog4.x后,ajax有问题
给每个sheet做一次,每次做1000条给启动参数加大内存
一个SHEET 6万条? 你用什么工具导出的
是嘛,那要请教下你了,我QQ 270578113 加下 QQ聊 先谢谢了
差不多,你说的情况是一个xls 一个sheet 可以, 现在 一个xls 多个sheet 不行
在此基础上create new sheet
你从新读,POI也会把第一次写进去的东西加载到内存里的
public void exportMsc(HttpServletRequest request,HttpServletResponse response){
Long regionId = RequestUitls.parseLong(request, "regionId", true);
String[] filePathArr = {
Utility.getRealFile(importConfigMap.get("BSCFORXLS")),
Utility.getRealFile(importConfigMap.get("BSCLINKFORXLS")),
Utility.getRealFile(importConfigMap.get("BSCPCMFORXLS")),
Utility.getRealFile(importConfigMap.get("BSCCELLFORXLS")),
Utility.getRealFile(importConfigMap.get("RNCFORXLS")),
Utility.getRealFile(importConfigMap.get("RNCLINKFORXLS")),
Utility.getRealFile(importConfigMap.get("RNCATMFORXLS"))
};
String filename = Utility.getRealFile("/upload/"+regionService.loadById(regionId).getName()+"-MSC"+".xls");
try {
HSSFWorkbook book = new HSSFWorkbook();
for(int i = 0;i<filePathArr.length;i++) {
HashMap<String,Object> map = xmlLoader(filePathArr[i]);
String[] titleName = (String[])map.get("titleName");
if(i == 0) {
HSSFSheet sheet = book.createSheet("BSC基本信息");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<Bsc> bscList = bscService.loadByRegionId(String.valueOf(regionId));
if(bscList != null) {
int j = 1;
for(Bsc bsc : bscList){
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(bsc.getMsc().getName());
row.createCell(1).setCellValue(bsc.getName());
row.createCell(2).setCellValue(bsc.getBscId());
row.createCell(3).setCellValue(bsc.getSignalCode());
row.createCell(4).setCellValue(bsc.getDpc());
row.createCell(5).setCellValue(bsc.getLaiForExcel());
row.createCell(6).setCellValue(bsc.getBscNo());
row.createCell(7).setCellValue(bsc.getPcmNo());
j++;
}
bscList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}else if(i ==1) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("BSC链路基本信息");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<BscLink> bscLinkList = bscLinkService.loadByRegionId(String.valueOf(regionId));
if(bscLinkList != null) {
int j = 1;
for(BscLink bscLink : bscLinkList){
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(bscLink.getBsc().getMsc().getName());
row.createCell(1).setCellValue(bscLink.getBsc().getName());
row.createCell(2).setCellValue(bscLink.getSlc());
row.createCell(3).setCellValue(bscLink.getPcmId());
row.createCell(4).setCellValue(bscLink.getLinkChoose());
j++;
}
bscLinkList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}
else if(i ==2) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("闭塞电路信息");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<BscPcm> bscPcmList = bscPcmService.loadByRegionId(String.valueOf(regionId));
if(bscPcmList != null) {
int j = 1;
for(BscPcm bscPcm : bscPcmList){
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(bscPcm.getBsc().getMsc().getName());
row.createCell(1).setCellValue(bscPcm.getBsc().getName());
row.createCell(2).setCellValue(bscPcm.getPcmId());
row.createCell(3).setCellValue(bscPcm.getOffsetStart());
row.createCell(4).setCellValue(bscPcm.getOffsetEnd());
j++;
}
bscPcmList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}else if(i ==3) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("BSC_CELL");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<Cell> bscCellList = cellService.loadByRegionId(String.valueOf(regionId));
if(bscCellList != null) {
int j = 1;
for(Cell cell : bscCellList) {
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(cell.getBsc().getMsc().getName());
row.createCell(1).setCellValue(cell.getBsc().getName());
row.createCell(2).setCellValue(cell.getLaiForExcel());
row.createCell(3).setCellValue(cell.getCellIdForExcel());
System.out.println(j);
j++;
}
bscCellList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}else if (i == 4) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("RNC基本信息");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<Rnc> rncList = rncService.loadByRegionId(String.valueOf(regionId));
if(rncList != null) {
int j = 1;
for(Rnc rnc : rncList) {
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(rnc.getMsc().getName());
row.createCell(1).setCellValue(rnc.getName());
row.createCell(2).setCellValue(rnc.getRncId());
row.createCell(3).setCellValue(rnc.getDpc());
row.createCell(4).setCellValue(rnc.getRncNsap());
row.createCell(5).setCellValue(rnc.getLaiForExcel());
row.createCell(6).setCellValue(rnc.getRncNoId());
row.createCell(7).setCellValue(rnc.getRncNo());
j++;
}
rncList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}else if (i == 5) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("RNC链路");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<RncLink> rncLinkList = rncLinkService.loadByRegionId(String.valueOf(regionId));
if(rncLinkList != null) {
int j = 1;
for(RncLink rncLink : rncLinkList) {
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(rncLink.getRnc().getMsc().getName());
row.createCell(1).setCellValue(rncLink.getRnc().getName());
row.createCell(2).setCellValue(rncLink.getRnc().getName()+"_"+(j-1));
row.createCell(3).setCellValue(rncLink.getSlc());
row.createCell(4).setCellValue(rncLink.getStfn());
row.createCell(5).setCellValue(rncLink.getStsn());
row.createCell(6).setCellValue(rncLink.getE1t1n());
row.createCell(7).setCellValue(rncLink.getVpi());
row.createCell(8).setCellValue(rncLink.getVci());
row.createCell(9).setCellValue(rncLink.getPvcpcr());
j++;
}
rncLinkList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}else if (i == 6) {
FileInputStream fis = new FileInputStream(filename);
book = new HSSFWorkbook(fis);
HSSFSheet sheet = book.createSheet("ATM承载信息");
HSSFRow head = sheet.createRow(0);
for (int b=0; b < titleName.length; b++) {
head.createCell(b).setCellValue(titleName[b]);
}
List<RncPvc> rncAtmList = rncPvcService.loadByRegionId(String.valueOf(regionId));
if(rncAtmList != null) {
int j = 1;
for(RncPvc rncPvc : rncAtmList) {
HSSFRow row = sheet.createRow(j);
row.createCell(0).setCellValue(rncPvc.getRnc().getMsc().getName());
row.createCell(1).setCellValue(rncPvc.getRnc().getName());
row.createCell(2).setCellValue(rncPvc.getRnc().getName()+"_BEARER_"+(j-1));
row.createCell(3).setCellValue(rncPvc.getPvcId());
row.createCell(4).setCellValue(rncPvc.getStfn());
row.createCell(5).setCellValue(rncPvc.getStsn());
row.createCell(6).setCellValue(rncPvc.getE1t1n());
row.createCell(7).setCellValue(rncPvc.getVpi());
row.createCell(8).setCellValue(rncPvc.getVci());
j++;
}
rncAtmList.clear();
}
FileOutputStream fos = new FileOutputStream(filename);
book.write(fos);
fos.flush();
fos.close();
}
}
DownloadUtil.download(null, response, filename);
} catch (Exception e) {
e.printStackTrace();
}
}
附上代码
加内存指标不治本,我也在想 一个一个导出,然后在合并XLS 了
POI 比JXL 溢出快
http://www.javaeye.com/topic/422117
写完一个sheet之后你有没有close掉,
你写一个就close,你后面的数据就保存不进去了
然后呢????
book.close();都没的,当然要溢出啦,
我用的是fos导出到 fos.close(), book 是没有close() 这个方法的
其实JXL也是一样,你写一次关闭一次,然后在打开,他照样会把你前面写好的东西加载到内存个.