Excel导出大数据，导致内存溢出的解决方案（NPOI，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 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();
        }
      }

}

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;
using ICSharpCode.SharpZipLib.Zip;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

    /// <summary>
    /// Xlsx输出
    /// editor:571115139@qq.com
    /// </summary>
    public class XlsxOutputHelper
    {
        private const int FlushCnt = 2000;
        private readonly Func<int, DataPackage> _querySerivce;
        private readonly string _tempFilePath = LocalStorge.TempDirectory;
        public int TotalCnt=0;
        public Action<int> ProgressShow = null;
        public XlsxOutputHelper(Func<int/*页码*/, DataPackage/*返回数据*/> querySerivce) 
        {
            _querySerivce = querySerivce;
        }

        /// <summary>
        ///  生成Excel文件外部调用方法
        /// </summary>
        /// <param name="headList">标题列表</param>
        /// <param name="sheetName">工作薄sheet名称</param>
        /// <param name="filePath">目标文件</param>
        /// <param name="writefileConsole">进度输出</param>
        public bool GenerateExcel(List<string> headList, string sheetName, string filePath, Action<string> writefileConsole)
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            Dictionary<string, XSSFCellStyle> styles = CreateStyles(wb);
            XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(sheetName);
            string sheetRef = sheet.GetPackagePart().PartName.Name;
            string sheetRefList = sheetRef.Substring(1);
            if (!Directory.Exists(_tempFilePath))
            {
                Directory.CreateDirectory(_tempFilePath);
            }
            string guid = Guid.NewGuid().ToString("N");
            string sheetFileListFile = _tempFilePath + "/sheet_" + guid + ".xml";
            string tmpFile = _tempFilePath + "/" + guid + ".xlsx";
            using (var os = new FileStream(tmpFile, FileMode.Create, FileAccess.ReadWrite))
            {
                wb.Write(os);
            }
            using (var s = File.OpenWrite(sheetFileListFile))
            {
                using (StreamWriter fw = new StreamWriter(s, Encoding.UTF8))
                {
                    //生成sheet
                   if(!GenerateExcelSheet(headList, fw, styles))
                   {
                        return false;
                   }
                }
             
            }
            writefileConsole("正在写入文件，请耐心等待....");
            //将临时文件压缩替换
            using (var output = File.OpenWrite(filePath))
            {
                SubstituteAll(tmpFile, sheetFileListFile, sheetRefList, output);
            }
            // 删除临时文件
            File.Delete(tmpFile); 
            File.Delete(sheetFileListFile);
            return true;
        }
        /// <summary>
        ///  生成sheet
        /// </summary>
        /// <param name="headList"></param>
        /// <param name="output"></param>
        /// <param name="styles"></param>
        private bool GenerateExcelSheet(List<string> headList, StreamWriter output,
            Dictionary<string, XSSFCellStyle> styles)
        {
            XSSFCellStyle stringStyle = styles["cell_string"];
            XSSFCellStyle longStyle = styles["cell_long"];
            XSSFCellStyle doubleStyle = styles["cell_double"];
            XSSFCellStyle dateStyle = styles["cell_date"];

            SpreadsheetWriter sw = new SpreadsheetWriter(output);
            int[] arrColWidth = new int[headList.Count];
            for (int i = 0; i < headList.Count; i++)
            {
                arrColWidth[i] =Math.Max(Encoding.GetEncoding(936).GetBytes(headList[i]).Length,10);
            }
           
            sw.BeginWorkSheet();
            sw.BeginSetColWidth();
            for (int i = 0; i < headList.Count; i++)
            {
                sw.SetColWidthBeforeSheet(i, arrColWidth[i]);
            }
            sw.EndSetColWidth();

            sw.BeginSheet();
            // 表头
            sw.InsertRowWithheight(0, headList.Count, 25);
            int styleIndex = styles["sheet_title"].Index;
            for (int i = 0, len = headList.Count; i < len; i++)
            {

                sw.CreateCell(i, headList[i], styleIndex);
            }
            sw.EndWithheight();

            //
            int pageIndex = 1;// 查询起始页
            bool hasNextRow;// 是否还有数据，循环条件
            do
            {// 开始分页查询
             // 导出查询改为分页查询方式，替代原有queryExportResult()方法
                DataPackage data = this._querySerivce(pageIndex);
                if (!data.IsSucess) return false;
                var dt = data.Table;
                if (dt != null && dt.Rows.Count > 0)
                {
                    int cellIndex;
                    int rownum = 1;
                    foreach (DataRow row in dt.Rows)
                    {
                        cellIndex = 0;
                        sw.InsertRow((pageIndex - 1) * LocalStorge.MaxPageSize + rownum);
                        #region 填充内容

                        foreach (DataColumn column in dt.Columns)
                        {
                            string drValue = row[column].ToString();
                            if (drValue.IsNullOrWiteSpace())
                            {
                                sw.CreateCell(cellIndex, "", stringStyle.Index);
                            }
                            else
                            {
                                switch (column.DataType.ToString())
                                {
                                    case "System.DateTime"://日期类型
                                        DateTime.TryParse(drValue, out DateTime dateV);
                                        sw.CreateCell(cellIndex, dateV, dateStyle.Index);
                                        break;

                                    case "System.Int16"://整型
                                    case "System.Int32":
                                    case "System.Int64":
                                    case "System.Byte":
                                        int.TryParse(drValue, out int intV);
                                        sw.CreateCell(cellIndex, intV, longStyle.Index);
                                        break;
                                    case "System.Decimal"://浮点型
                                    case "System.Double":
                                        double.TryParse(drValue, out double doubV);
                                        sw.CreateCell(cellIndex, doubV, doubleStyle.Index);
                                        break;
                                    case "System.DBNull"://空值处理
                                        sw.CreateCell(cellIndex, "", stringStyle.Index);
                                        break;
                                    default:
                                        sw.CreateCell(cellIndex, drValue.Replace("<", "&lt;").Replace(">", "&gt;"),
                                            stringStyle.Index);
                                        break;
                                }
                            }
                            cellIndex++;
                        }
                        #endregion

                        sw.EndRow();
                        if (rownum % FlushCnt == 0)
                        {
                            output.Flush();
                        }
                        rownum++;

                    }
                    ProgressShow?.Invoke(TotalCnt+= rownum-1);
                    hasNextRow = true;
                    pageIndex++;
                }
                else
                {
                    hasNextRow = false;
                }
                GC.Collect();
            } while (hasNextRow);

            sw.EndSheet();
            sw.EndWorkSheet();
            return true;
        }

        /// <summary>
        /// 创建Excel样式
        /// </summary>
        /// <param name="wb"></param>
        /// <returns></returns>
        private static Dictionary<string, XSSFCellStyle> CreateStyles(XSSFWorkbook wb)
        {
            Dictionary<string, XSSFCellStyle> stylesMap = new Dictionary<string, XSSFCellStyle>();
            IDataFormat fmt = wb.CreateDataFormat();
            ICellStyle style = wb.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;
            stylesMap.Add("cell_string", (XSSFCellStyle)style);
            ICellStyle style2 = wb.CreateCellStyle();
            style2.DataFormat = fmt.GetFormat("0");
            style2.Alignment = HorizontalAlignment.Center;
            style2.VerticalAlignment = VerticalAlignment.Center;
            stylesMap.Add("cell_long", (XSSFCellStyle)style2);
            ICellStyle style3 = wb.CreateCellStyle();
            style3.DataFormat = fmt.GetFormat("0");
            style3.Alignment = HorizontalAlignment.Center;
            style3.VerticalAlignment = VerticalAlignment.Center;
            stylesMap.Add("cell_double", (XSSFCellStyle)style3);
            ICellStyle style4 = wb.CreateCellStyle();
            style4.DataFormat = fmt.GetFormat("yyyy-MM-dd HH:mm");
            style4.Alignment = HorizontalAlignment.Center;
            style4.VerticalAlignment = VerticalAlignment.Center;
            stylesMap.Add("cell_date", (XSSFCellStyle)style4);
            ICellStyle style5 = wb.CreateCellStyle();
            style5.FillForegroundColor = IndexedColors.Grey25Percent.Index;
            style5.FillPattern = FillPattern.SolidForeground;
            style5.Alignment = HorizontalAlignment.Center;
            style5.VerticalAlignment = VerticalAlignment.Center;
            IFont font = wb.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 600;
            style5.SetFont(font);
            stylesMap.Add("sheet_title", (XSSFCellStyle)style5);
            return stylesMap;
        }


        /// <summary>
        /// 打包压缩
        /// </summary>
        /// <param name="zipfile"></param>
        /// <param name="xmlfile"></param>
        /// <param name="entryList"></param>
        /// <param name="output"></param>
        private void SubstituteAll(string zipfile, string xmlfile,string entryList, Stream output)
        {
            using (ZipOutputStream zos = new ZipOutputStream(output))
            {
                using (ZipFile zip = new ZipFile(zipfile))
                {
                    IEnumerator en = zip.GetEnumerator();
                    while (en.MoveNext())
                    {
                        if(en.Current==null) continue;
                        ZipEntry ze = (ZipEntry)en.Current;
                        if (!entryList.Contains(ze.Name))
                        {
                            zos.PutNextEntry(new ZipEntry(ze.Name));
                            Stream tis = zip.GetInputStream(ze);
                            var length = ze.Size;
                            StreamUtils.Copy(tis, zos,null,(position) =>
                            {
                                ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100));
                            });
                        }
                    }
                    zos.PutNextEntry(new ZipEntry(entryList));
                    using (Stream lis = new FileStream(xmlfile, FileMode.Open, FileAccess.ReadWrite))
                    {
                        var length = lis.Length;
                        StreamUtils.Copy(lis, zos, null, (position) =>
                        {
                            ProgressShow?.Invoke(Convert.ToInt32((position / (length + 0M)) * 100));
                        });
                    }
                }
            }
        }


        public class SpreadsheetWriter
        {
            private StreamWriter _out;
            private int _rownum;

            public SpreadsheetWriter(StreamWriter output)
            {
                this._out = output;
            }

            public void BeginWorkSheet()
            {
                this._out
                    .Write("<?xml version=\"1.0\" encoding=\"UTF-8\"?><worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
            }

            public void BeginSheet()
            {
                this._out.Write("<sheetData>\n");
            }

            public void EndSheet()
            {
                this._out.Write("</sheetData>");
                // 合并单元格
            }

            public void EndWorkSheet()
            {
                this._out.Write("</worksheet>");
            }

            //插入行 不带高度
            public void InsertRow(int rownum)
            {
                this._out.Write("<row r=\"" + (rownum + 1) + "\">\n");
                this._rownum = rownum;
            }

            public void EndRow()
            {
                this._out.Write("</row>\n");
            }

            //插入行且设置高度
            public void InsertRowWithheight(int rownum, int columnNum, double height)

            {
                this._out.Write("<row r=\"" + (rownum + 1) + "\" spans=\"1:"
                    + columnNum + "\" ht=\"" + height
                    + "\" customHeight=\"1\">\n");
                this._rownum = rownum;
            }

            public void EndWithheight()
            {
                this._out.Write("</row>\n");
            }

            public void BeginSetColWidth()
            {
                this._out.Write("<cols>\n");
            }

            // 设置列宽 下标从0开始
            public void SetColWidthBeforeSheet(int columnIndex, double columnWidth)

            {
                this._out.Write("<col min=\"" + (columnIndex + 1) + "\" max=\""
                    + (columnIndex + 1) + "\" width=\"" + columnWidth
                    + "\" customWidth=\"1\"/>\n");
            }

            public void EndSetColWidth()
            {
                this._out.Write("</cols>\n");
            }

            public void BeginMergerCell()
            {
                this._out.Write("<mergeCells>\n");
            }

            public void EndMergerCell()
            {
                this._out.Write("</mergeCells>\n");
            }

            // 合并单元格 下标从0开始
            public void SetMergeCell(int beginColumn, int beginCell, int endColumn,
                int endCell)
            {
                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)

            {
                string cellref = new CellReference(this._rownum, columnIndex)
                          .FormatAsString();
                this._out.Write("<c r=\"" + cellref + "\" 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)

            {
                CreateCell(columnIndex, value, -1);
            }

            public void CreateCell(int columnIndex, double value, int styleIndex)

            {
                string cellref = new CellReference(this._rownum, columnIndex)
                          .FormatAsString();
                this._out.Write("<c r=\"" + cellref + "\" 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)

            {
                CreateCell(columnIndex, value, -1);
            }

            public void CreateCell(int columnIndex, DateTime value, int styleIndex)

            {
                CreateCell(columnIndex, DateUtil.GetExcelDate(value, false),
                          styleIndex);
            }

            //10 进制转26进制
            private string GetExcelName(int i)
            {
                char[] allChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".ToCharArray();
                List<char> sb = new List<char>();
                while (i > 0)
                {
                    sb.Add(allChar[i % 26 - 1]);
                    i /= 26;
                }
                sb.Reverse();
                return string.Join("", sb);
            }
        }

    }

    public class DataPackage
    {
        public bool IsSucess { get; set; }
        public DataTable Table { get; set; }
    }