一、问题描述:
公司之前的项目中客户有一个需求是将业务数据导出到Excel表中,方便他们对账,单个导出任务数据量近100W,每当月初任务量多时,导出的项目就会内存溢出,挂掉。
二、原因分析:
1、每个进程在写Excel文件时,都是先将数据加载到内存,然后再将内存里面的数据生成文件;因此单个进程任务的数据量过大,将无法及时回收系统内存,最终导致系统内存耗尽而宕机。
2、导出中oracle查询结果是一次性全部查询出来,占用大量系统内存资源。
三、优化方案思路:
1、将所有导出查询全部改成分页的方式查询;
2、将写Excel文件使用java的基础技术IO流来实现,采用POI拼接xml字符串完成,迭代一批数据就flush进硬盘,同时把list,大对象赋值为空,显式调用垃圾回收器,及时回收内存。
四、具体优化改造方案:
1、改造导出查询方法:
这里省略,数据分页查询及SQL优化请自行度娘,这里不深入分析;
2、工程中增加生成Excel文件实现类
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 java.lang.reflect.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 com.chengfeng.ne.global.service.ITaskService;
import com.thinkjf.core.config.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("common.exoprt.Worksheet.max.rownum"));
/**
* 根据传入的不同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, Object> params,String serviceName)
throws Exception {
XSSFWorkbook wb = new XSSFWorkbook();
Map<String, XSSFCellStyle> styles = createStyles(wb);
XSSFSheet sheet = wb.createSheet(sheetName);
String sheetRef = sheet.getPackagePart().getPartName().getName();
String sheetRefList = sheetRef.substring(1);
File tempFiledir = new File(tempFilePath);
if(!tempFiledir.exists()){
tempFiledir.mkdirs();
}
String uuid = UUID.randomUUID().toString();
uuid = uuid.replace("-", "");
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;
Runtime.getRuntime().gc();
}
/**
* 生成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 = styles.get("cell_string");
XSSFCellStyle longStyle = styles.get("cell_long");
XSSFCellStyle doubleStyle = styles.get("cell_double");
XSSFCellStyle dateStyle = styles.get("cell_date");
Calendar calendar = Calendar.getInstance();
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.endSetColWidth();
sw.beginSheet();
// 表头
sw.insertRowWithheight(0, headList.size(), 25);
int styleIndex = ((XSSFCellStyle) styles.get("sheet_title")).getIndex();
for (int i = 0, len = headList.size(); i < len; i++) {
sw.createCell(i, headList.get(i), styleIndex);
}
sw.endWithheight();
//
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 = dataList.get(rownum-1);
Object val = null;
Method fieldMethod = null;
for (int k = 0, len2 = fields.size(); k < len2; k++) {
fieldMethod = (Method) data.getClass().getMethod("get"+ fields.get(k));
fieldMethod.setAccessible(true);// 不进行安全检测
val = fieldMethod.invoke(data);
if(val == null){
sw.createCell(cellIndex,"",stringStyle.getIndex());
}else{
String typeName = fieldMethod.getGenericReturnType().toString();
if (typeName.endsWith("int") || typeName.endsWith("nteger")) {
sw.createCell(cellIndex, (Integer) val,
longStyle.getIndex());
} else if (typeName.endsWith("ong")) {
sw.createCell(cellIndex, (Long) val, longStyle.getIndex());
} else if (typeName.endsWith("ouble")) {
sw.createCell(cellIndex, (Double) val,
doubleStyle.getIndex());
} else if (typeName.endsWith("util.Date")) {
calendar.setTime((java.util.Date) val);
sw.createCell(cellIndex, calendar, dateStyle.getIndex());
} else if (typeName.endsWith("sql.Date")) {
calendar.setTime((java.sql.Date) val);
sw.createCell(cellIndex, calendar, dateStyle.getIndex());
} else {
sw.createCell(cellIndex, val==null?"":val.toString().replace("<", "<").replace(">", ">"),
stringStyle.getIndex());
}
}
cellIndex++;
}
sw.endRow();
if (rownum % 2000 == 0) {
out.flush();
}
}
//------------
isEnd = true;
pageIndex++;
} else {
isEnd = false;
}
dataList = null;
Runtime.getRuntime().gc();
} while (isEnd);
sw.endSheet();
// 合并单元格
// sw.beginMergerCell();
// for (int i = 0, len = dataList.size() + 1; i < len; i++) {
// sw.setMergeCell(i, 8, i, 9);
// }
// sw.endMergerCell();
sw.endWorkSheet();
}
/**
* 创建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.setDataFormat(fmt.getFormat("0"));
style2.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_long", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setDataFormat(fmt.getFormat("0.00"));
style3.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style3.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
stylesMap.put("cell_double", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setDataFormat(fmt.getFormat("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(IndexedColors.AQUA.getIndex());
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,
String entryList, OutputStream out) throws IOException {
ZipFile zip = new ZipFile(zipfile);
ZipOutputStream zos = new ZipOutputStream(out);
@SuppressWarnings("unchecked")
Enumeration<ZipEntry> en = (Enumeration<ZipEntry>)zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if (!entryList.contains(ze.getName())) {
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
is = null;
System.gc();
}
}
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;
System.gc();
}
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 = address.replaceAll("[^a-zA-Z]", "").toLowerCase();
char[] adds = address.toCharArray();
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;
}
public void beginWorkSheet() throws IOException {
this._out
.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
}
public void beginSheet() throws IOException {
this._out.write("<sheetData>\n");
}
public void endSheet() throws IOException {
this._out.write("</sheetData>");
// 合并单元格
}
public void endWorkSheet() throws IOException {
this._out.write("</worksheet>");
}
//插入行 不带高度
public void insertRow(int rownum) throws IOException {
this._out.write("<row r=\"" + (rownum + 1) + "\">\n");
this._rownum = rownum;
}
public void endRow() throws IOException {
this._out.write("</row>\n");
}
//插入行且设置高度
public void insertRowWithheight(int rownum, int columnNum, double height)
throws IOException {
this._out.write("<row r=\"" + (rownum + 1) + "\" spans=\"1:"
+ columnNum + "\" ht=\"" + height
+ "\" customHeight=\"1\">\n");
this._rownum = rownum;
}
public void endWithheight() throws IOException {
this._out.write("</row>\n");
}
public void beginSetColWidth() throws IOException {
this._out.write("<cols>\n");
}
// 设置列宽 下标从0开始
public void setColWidthBeforeSheet(int columnIndex, double columnWidth)
throws IOException {
this._out.write("<col min=\"" + (columnIndex + 1) + "\" max=\""
+ (columnIndex + 1) + "\" width=\"" + columnWidth
+ "\" customWidth=\"1\"/>\n");
}
public void endSetColWidth() throws IOException {
this._out.write("</cols>\n");
}
public void beginMergerCell() throws IOException {
this._out.write("<mergeCells>\n");
}
public void endMergerCell() throws IOException {
this._out.write("</mergeCells>\n");
}
// 合并单元格 下标从0开始
public void setMergeCell(int beginColumn, int beginCell, int endColumn,
int endCell) throws IOException {
this._out.write("<mergeCell ref=\"" + getExcelName(beginCell + 1)
+ (beginColumn + 1) + ":" + getExcelName(endCell + 1)
+ (endColumn + 1) + "\"/>\n");// 列行:列行
}
public void createCell(int columnIndex, String value, int styleIndex)
throws IOException {
String ref = new CellReference(this._rownum, columnIndex)
.formatAsString();
this._out.write("<c r=\"" + ref + "\" t=\"inlineStr\"");
if (styleIndex != -1)
this._out.write(" s=\"" + styleIndex + "\"");
this._out.write(">");
this._out.write("<is><t>" + value + "</t></is>");
this._out.write("</c>");
}
public void createCell(int columnIndex, String value)
throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex)
throws IOException {
String ref = new CellReference(this._rownum, columnIndex)
.formatAsString();
this._out.write("<c r=\"" + ref + "\" t=\"n\"");
if (styleIndex != -1)
this._out.write(" s=\"" + styleIndex + "\"");
this._out.write(">");
this._out.write("<v>" + value + "</v>");
this._out.write("</c>");
}
public void createCell(int columnIndex, double value)
throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex)
throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false),
styleIndex);
}
//10 进制转26进制
private String getExcelName(int i) {
char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();
StringBuilder sb = new StringBuilder();
while (i > 0) {
sb.append(allChar[i % 26 - 1]);
i /= 26;
}
return sb.reverse().toString();
}
}
}
在外部类调用时:
String tempFilePath = GlobalConfig
.getPropertyValue("common.attach.upload_dir") + "/task/tmp/";
//调用新的生成方法 xlsxOutPutService.generateExcel(Arrays.asList(cellName), fieldName,MessageUtils.getMessage(exportDateType.toString()),tempFilePath, expFilePath, execMethod, params, "taskService");
五、性能测试
1、测试一:多线程写文件
描述:22个线程,都同时导出35个字段, 35万数据,耗时16分钟,每个文件48M
2、测试二:多线程写文件
描述:10个线程,都同时导出35个字段, 75万数据,耗时16分钟,每个文件102M
以上测试没有再报内存溢出的问题了,时间有点慢,主要是时间大部是被查询给占用掉了,项目里面的查询性能还有待优化。