大数据导出Excel导致内存溢出的解决方案

一、问题描述：
公司之前的项目中客户有一个需求是将业务数据导出到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("<", "&lt;").replace(">", "&gt;"),
                              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

以上测试没有再报内存溢出的问题了，时间有点慢，主要是时间大部是被查询给占用掉了，项目里面的查询性能还有待优化。