使⽤NPOI或POI导出Excel⼤数据（百万级以上），导致内存溢出的解决⽅案
（NPOI，POI）
使⽤⼯具：POI(JAVA)，NPOI(.Net)
致谢博主 Crazy_Jeff 提供的思路
⼀、问题描述：
导出任务数据量近100W甚⾄更多，导出的项⽬就会内存溢出，挂掉。
⼆、原因分析：
1、每个进程在写Excel⽂件时，都是先将数据加载到内存，然后再将内存⾥⾯的数据⽣成⽂件；因此单个进程任务的数据量过⼤，将⽆法及时回收系统内存，最终导致系统内存耗尽⽽宕机。
2、导出中查询结果是⼀次性全部查询出来，占⽤⼤量系统内存资源。
三、优化⽅案思路：
1、将所有导出查询全部改成分页的⽅式查询；
2、将写Excel⽂件使⽤IO流来实现，采⽤POI，或NPOI拼接xml字符串完成，迭代⼀批数据就flush进硬盘，同时把list，⼤对象赋值为空，显式调⽤垃圾回收器，及时回收内存。
⾸先提供Java版代码POI实现，来⾃：
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import flect.Method;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipOutputStream;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import lobal.service.ITaskService;
import onfig.GlobalConfig;
/**
* 功能描述：⽣成Excel⽂件类
* @author Jeff
* @version 1.0
* @date 2015-08-03
*/
@Service("xlsxOutPutService")
public class XlsxOutPutService {
@Autowired
private ITaskService taskService;
/**
* 导出每个sheet⾏数
*/
public int pageSize = Integer.parseInt(GlobalConfig
.getPropertyValue("prt.wnum"));
/**
* 根据传⼊的不同serviceName来执⾏不同的查询语句
* @param serviceName
* @param execMethod
* @param params
* @param pageIndex
* @return
*/
public List<?> queryBySerivceName(String serviceName,String execMethod, Map<String, Object> params,int pageIndex)throws Exception{
List<?> resultList = null;
if("taskService".equals(serviceName)){
resultList = taskService.queryExportResultPage(execMethod,params, pageIndex, pageSize);
}
return resultList;
}
/**
* ⽣成Excel⽂件外部调⽤⽅法
* @param headList 标题列表
* @param fieldName 字段列表
* @param sheetName ⼯作薄sheet名称
* @param tempFilePath 临时⽂件⽬录
* @param filePath ⽬标⽂件
* @param execMethod 执⾏sql
* @param params 查询参数
* @param serviceName 执⾏service⽅法对象名称
* @throws Exception
*/
public void generateExcel(List<String> headList,List<String> fieldName,String sheetName, String tempFilePath,String filePath,String execMethod, Map<String, O throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, XSSFCellStyle> styles = createStyles(wb);
XSSFSheet sheet = wb.createSheet(sheetName);
String sheetRef = PackagePart().getPartName().getName();
String sheetRefList = sheetRef.substring(1);
File tempFiledir = new File(tempFilePath);
if(!ists()){
tempFiledir.mkdirs();
}
String uuid = UUID.randomUUID().toString();
uuid = place("-", "");
File sheetFileList = new File(tempFilePath + "/sheet_" + uuid + ".xml");
File tmpFile = new File(tempFilePath + "/"+uuid+".xlsx");
FileOutputStream os = new FileOutputStream(tmpFile);
wb.write(os);
os.close();
Writer fw = new OutputStreamWriter(new FileOutputStream(
sheetFileList), "UTF-8");
//⽣成sheet
generateExcelSheet(headList,fieldName, fw, styles,execMethod,params,serviceName);
fw.close();
//将临时⽂件压缩替换
FileOutputStream out = new FileOutputStream(filePath);
substituteAll(tmpFile, sheetFileList, sheetRefList, out);
out.close();
/
/ 删除临时⽂件
tmpFile.delete();
sheetFileList.delete();
tmpFile = null;
sheetFileList = null;
os = null;
fw = null;
out = null;
}
/**
* ⽣成sheet
* @param headList
* @param fields
* @param out
* @param styles
* @param execMethod
* @param params
* @throws Exception
*/
private void generateExcelSheet(List<String> headList,List<String> fields,Writer out,
Map<String, XSSFCellStyle> styles,String execMethod, Map<String, Object> params,String serviceName) throws Exception { XSSFCellStyle stringStyle = ("cell_string");
XSSFCellStyle longStyle = ("cell_long");
XSSFCellStyle doubleStyle = ("cell_double");
XSSFCellStyle dateStyle = ("cell_date");
Calendar calendar = Instance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginWorkSheet();
sw.beginSetColWidth();
for (int i = 10, len = headList.size() - 2; i < len; i++) {
sw.setColWidthBeforeSheet(i, 13);
}
sw.setColWidthBeforeSheet(headList.size() - 1, 16);
sw.beginSheet();
// 表头
sw.insertRowWithheight(0, headList.size(), 25);
int styleIndex = ((XSSFCellStyle) ("sheet_title")).getIndex();
for (int i = 0, len = headList.size(); i < len; i++) {
}
//
int pageIndex = 1;// 查询起始页
Boolean isEnd = false;// 是否是最后⼀页，循环条件
do {// 开始分页查询
// 导出查询改为分页查询⽅式，替代原有queryExportResult()⽅法
long startTimne = System.currentTimeMillis();
List<?> dataList = this.queryBySerivceName(serviceName, execMethod, params, pageIndex);
long endTime = System.currentTimeMillis();
System.out.println("查询"+pageIndex+"完成⽤时="+((endTime-startTimne))+"毫秒");
if (dataList != null && dataList.size() > 0) {
//写⽅法-------
int cellIndex = 0;
for (int rownum = 1, len = dataList.size() + 1; rownum < len; rownum++) {
cellIndex = 0;
sw.insertRow((pageIndex-1)*pageSize+rownum);
Object data = (rownum-1);
Object val = null;
Method fieldMethod = null;
for (int k = 0, len2 = fields.size(); k < len2; k++) {
fieldMethod = (Method) Class().getMethod("get"+ (k));
fieldMethod.setAccessible(true);// 不进⾏安全检测
val = fieldMethod.invoke(data);
if(val == null){
}else{
String typeName = GenericReturnType().toString();
if (dsWith("int") || dsWith("nteger")) {
} else if (dsWith("ong")) {
} else if (dsWith("ouble")) {
} else if (dsWith("util.Date")) {
calendar.setTime((java.util.Date) val);
} else if (dsWith("sql.Date")) {
calendar.setTime((java.sql.Date) val);
} else {
}
}
cellIndex++;
}
if (rownum % 2000 == 0) {
out.flush();
}
}
//------------
isEnd = true;
pageIndex++;
} else {
isEnd = false;
}
dataList = null;
} while (isEnd);
// 合并单元格
// sw.beginMergerCell();
// for (int i = 0, len = dataList.size() + 1; i < len; i++) {
// sw.setMergeCell(i, 8, i, 9);
// }
// sw.endMergerCell();
}
/**
* 创建Excel样式
* @param wb
* @return
*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) {
Map<String, XSSFCellStyle> stylesMap = new HashMap<String, XSSFCellStyle>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style = wb.createCellStyle();
style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_string", style);
XSSFCellStyle style2 = wb.createCellStyle();
style2.Format("0"));
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_long", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.Format("0.00"));
style3.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_double", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.Format("yyyy-MM-dd HH:mm:ss"));
style4.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style4.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
style5.setFillForegroundColor(Index());
style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style5.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style5.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("sheet_title", style5);
return stylesMap;
}
/**
* 打包压缩
* @param zipfile
* @param tmpfileList
* @param entryList
* @param out
* @throws IOException
*/
private void substituteAll(File zipfile,File tmpfileList,
spring framework runtimeString entryList, OutputStream out) throws IOException {
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>)ies();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if (!Name())) {
zos.putNextEntry(new Name()));
InputStream is = InputStream(ze);
copyStream(is, zos);
is.close();
is = null;
<();
}
}
InputStream is = null;
zos.putNextEntry(new ZipEntry(entryList));
is = new FileInputStream(tmpfileList);
copyStream(is, zos);
is.close();
zos.close();
zip.close();
is = null;
zos = null;
zip = null;
<();
}
private static void copyStream(InputStream in, OutputStream out)
throws IOException {
byte[] chunk = new byte[1024*10];
int count;
while ((count = in.read(chunk)) >= 0)
out.write(chunk, 0, count);
}
public int getTrueColumnNum(String address) {
address = placeAll("[^a-zA-Z]", "").toLowerCase();
char[] adds = CharArray();
int base = 1;
int total = 0;
for (int i = adds.length - 1; i >= 0; i--) {
total += (adds[i] - 'a' + 1) * base;
base = 26 * base;
}
return total;
}
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out) {
this._out = out;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论