c#导出百万级别数据到Excel速度优化到一分钟之内

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; using ICSharpCode.SharpZipLib.Core;   namespace FolyerERP {       public class ExportBaseList : Jeez.Runtime.Base.General.frmList     {         public DataTable GetExDataTable(DataTable dt)         {             return RefreshDataTable(dt);         }          protected override DataTable RefreshDataTable(DataTable dt)         {             return base.RefreshDataTable(dt);         }           protected override void Export()         {                System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();              sfd.Filter = "Excel文件(*.xlsx)|*.xlsx";              if (sfd.ShowDialog() == System.Windows.Forms.DialogResult.OK)             {                  string filePath = sfd.FileName;                 ExcelExport.Export(this, filePath                     );             }               //base.Export();         }     }      public static class frmListExtend     {          private static Dictionary<string, bool> dicCol = new Dictionary<string, bool>();         public static bool GetEntityAnnosDt(this ExportBaseList list, int pIndex, out DataTable result)         {             result = new DataTable();             pIndex = (pIndex == 0 ? 1 : pIndex);             int beginIdx = (pIndex == 1 ? 1 : (pIndex - 1) * 500 + 1);             Jeez.Common.UI.MainStatusBar.ProgressBarValue += 500;             if (beginIdx > list.CurrentGrid.RowCount)             {                 result = new DataTable();                 return true;             }             try             {                 if (list.SQLTableName != "")                 {                     result = list.ReportListData.GetRptListData(beginIdx, beginIdx + 499);                 }                 else                     result = list.GetExDataTable(list.ReportListData.GetRptListData(beginIdx, beginIdx + 499));                  foreach (DataColumn col in result.Columns)                 {                     if (!dicCol.ContainsKey(col.ColumnName))                     {                         dicCol.Add(col.ColumnName, false);                     }                 }                  foreach (KeyValuePair<string, bool> kv in dicCol)                 {                     if (result.Columns.Contains(kv.Key) && !kv.Value)                     {                         result.Columns.Remove(kv.Key);                     }                   }                  if (list.SQLTableName == "")                 {                     DataTable dt = new DataTable();                     foreach (Jeez.Control.JeezGrid.Column col in list.CurrentGrid.Columns)                     {                         if (col.Visible)                         {                             if (col.EntityCol != null && col.EntityCol.Value != null)                             {                                 dt.Columns.Add(col.Name, col.EntityCol.Value.SystemType);//增加缓存列。                             }                             else                             {                                 dt.Columns.Add(col.Name, GetColType(col.ColDataType));                             }                         }                     }                     foreach (DataRow dr in result.Rows)                     {                         DataRow r = dt.NewRow();                         foreach (DataColumn dc in result.Columns)                         {                             if (dt.Columns.Contains(dc.ColumnName))                             {                                 r[dc.ColumnName] = dr[dc.ColumnName];                             }                         }                         dt.Rows.Add(r);                      }                     dt.AcceptChanges();                     result = dt;                 }              }             catch             {                 result = null;                 return false;             }             return true;         }           private static Type GetColType(Jeez.Control.JeezGrid.ColumnDataType coltype)         {             switch (coltype)             {                 case Jeez.Control.JeezGrid.ColumnDataType.Boolean:                     return typeof(bool);                 case Jeez.Control.JeezGrid.ColumnDataType.Date:                 case Jeez.Control.JeezGrid.ColumnDataType.Time:                 case Jeez.Control.JeezGrid.ColumnDataType.DateTime:                     return typeof(DateTime);                 case Jeez.Control.JeezGrid.ColumnDataType.Decimal:                 case Jeez.Control.JeezGrid.ColumnDataType.Money:                     return typeof(decimal);                 case Jeez.Control.JeezGrid.ColumnDataType.Integer:                     return typeof(int);                  case Jeez.Control.JeezGrid.ColumnDataType.Text:                     return typeof(string);                 case Jeez.Control.JeezGrid.ColumnDataType.Byte:                     return typeof(byte[]);                 default:                     return typeof(string);                }         }         public static List<string> GetColName(this ExportBaseList list)         {             dicCol = new Dictionary<string, bool>();             List<string> lsCols = new List<string>();             foreach (Jeez.Control.JeezGrid.Column ccol in list.CurrentGrid.Columns)             {                 if (ccol.Visible)                 {                     lsCols.Add(ccol.Caption);                 }                 dicCol.Add(ccol.Name, ccol.Visible);               }              return lsCols;         }      }      public class ExcelExport     {         public static void Export(ExportBaseList list, string strFileName)         {             Jeez.Common.UI.MainStatusBar.ProgressBarMaxValue = list.CurrentGrid.RowCount;             Jeez.Common.UI.MainStatusBar.ProgressBarValue = 1;             Jeez.Common.UI.MainStatusBar.ProgressBarVisible = true;             Jeez.Common.UI.MessageTip tip = new Jeez.Common.UI.MessageTip("正在导出，请稍侯...");              try             {                                  XlsxOutputHelper xlsxOutput = new XlsxOutputHelper(strFileName);                  //更新进度条                  xlsxOutput.BeginGenerate(new List<string> { "sheet1" });                    DataPackage QureyService(int pIndex, int flag)                 {                     if (!list.GetEntityAnnosDt(pIndex, out DataTable result))                     {                         return new DataPackage(false, null);                     }                     return new DataPackage(true, result);                 }                 xlsxOutput.TotalCnt = 0;                 if (!xlsxOutput.GenerateSheet(list.GetColName(), "sheet1", QureyService))                 {                     tip.CloseMessageTip();                     //导出失败                     Jeez.Common.UI.MsgBox.Inform("导出失败");                 }                 else                 {                     tip.CloseMessageTip();                     Jeez.Common.UI.MsgBox.Inform("导出成功");                 }                 var isOk = xlsxOutput.EndGenerate((endmsg) =>                 {                      Jeez.Common.UI.MainStatusBar.StatusBarMainText = endmsg;                 });             }             catch (Exception ex)             {                 Jeez.Common.UI.MsgBox.Inform("导出失败:" + ex.Message);             }             finally             {                 tip.CloseMessageTip();                 Jeez.Common.UI.MainStatusBar.ProgressBarVisible = false;             }         }     }       public class XlsxOutputHelper     {         private const int FlushCnt = 2000;         private static readonly string _tempFilePath = Jeez.Common.UI.FileIO.FileOperate.GetAppTempPath();         public int TotalCnt = 0;         public Action<int> ProgressShow = null;         private readonly string _batchId;         private List<EntryPackage> _sheetFileList = new List<EntryPackage>();         private readonly string _filePath;         private readonly string _tempFile;         private Dictionary<string, XSSFCellStyle> _styles;         public XlsxOutputHelper(string filePath)         {             var ext = Path.GetExtension(filePath);             if (ext != ".xlsx")             {                 _filePath = Path.GetFileNameWithoutExtension(filePath) + ".xlsx";             }             else             {                 _filePath = filePath;             }             File.Create(_filePath).Close();             _batchId = Guid.NewGuid().ToString("N");             _tempFile = _tempFilePath + "/" + _batchId + ".xlsx";          }         public void BeginGenerate(List<string> sheetNames)         {             XSSFWorkbook wb = new XSSFWorkbook();             _styles = CreateStyles(wb);             foreach (var sheetName in sheetNames)             {                 wb.CreateSheet(sheetName);             }             using (var os = new FileStream(_tempFile, FileMode.Create, FileAccess.ReadWrite))             {                 wb.Write(os);             }         }          /// <summary>         ///  生成Excel,多个sheet文件外部调用方法         /// </summary>         /// <param name="headList">标题列表</param>         /// <param name="sheetName">工作薄sheet名称</param>         /// <param name="querySerivce">查询服务</param>         public bool GenerateSheet(List<string> headList, string sheetName, Func<int/*页码*/, int/*数据标识*/, DataPackage/*返回数据*/> querySerivce)         {             if (!File.Exists(_tempFile)) throw new Exception("请先执行BeginGenerate方法");             XSSFWorkbook wb = new XSSFWorkbook(_tempFile);             XSSFSheet sheet = (XSSFSheet)wb.GetSheet(sheetName);             string sheetRef = sheet.GetPackagePart().PartName.Name;             string sheetRefList = sheetRef.Substring(1);             wb.Close();             if (!Directory.Exists(_tempFilePath))             {                 Directory.CreateDirectory(_tempFilePath);             }             string guid = Guid.NewGuid().ToString("N");             string sheetFileListFile = _tempFilePath + "/sheet_" + guid + ".xml";              bool isOk = true;             using (var s = File.OpenWrite(sheetFileListFile))             {                 using (StreamWriter fw = new StreamWriter(s, Encoding.UTF8))                 {                     //生成sheet                     if (!GenerateExcelSheet(headList, fw, _styles, querySerivce))                     {                         isOk = false;                     }                 }              }             if (!isOk)             {                 Jeez.Common.UI.FileIO.FileOperate.DeleteFile(sheetFileListFile);                 return false;             }             _sheetFileList.Add(new EntryPackage() { EntryPath = sheetRefList, XmlFile = sheetFileListFile });             return true;         }         /// <summary>         /// 结束生成Excel写入文件到本地         /// </summary>         /// <param name="writefileConsole"></param>         /// <returns></returns>         public bool EndGenerate(Action<string> writefileConsole)         {             if (!File.Exists(_tempFile)) throw new Exception("请先执行BeginGenerate方法");             if (_sheetFileList == null || _sheetFileList.Count == 0) return false;             writefileConsole("正在写入文件，请耐心等待....");             //将临时文件压缩替换             using (var output = File.OpenWrite(_filePath))             {                 SubstituteAll(_tempFile, _sheetFileList, output);             }             // 删除临时文件             Jeez.Common.UI.FileIO.FileOperate.DeleteFile(_tempFile);             foreach (var entryPackage in _sheetFileList)             {                 Jeez.Common.UI.FileIO.FileOperate.DeleteFile(entryPackage.XmlFile);             }             writefileConsole("就绪");             return true;         }           /// <summary>         ///  生成sheet         /// </summary>         /// <param name="headList"></param>         /// <param name="output"></param>         /// <param name="styles"></param>         /// <param name="querySerivce"></param>         private bool GenerateExcelSheet(List<string> headList, StreamWriter output,             Dictionary<string, XSSFCellStyle> styles, Func<int/*页码*/, int/*数据标识*/, DataPackage/*返回数据*/> querySerivce)         {             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] + 1);             }             sw.EndSetColWidth();              sw.BeginSheet();             // 表头             sw.InsertRowWithheight(0, headList.Count, 15);             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;// 是否还有数据，循环条件             int flag = 0;//用于多批数据的处理             int rownum = 1;//总行数             do             {// 开始分页查询              // 导出查询改为分页查询方式，替代原有queryExportResult()方法                 DataPackage data = querySerivce(pageIndex, flag);                 if (!data.IsSucess) return false;                 if (flag == 0 || data.Flag == 0) flag = data.Flag;                 if (flag != 0 && flag != data.Flag)                 {                     flag = data.Flag;                     pageIndex = 1;                     hasNextRow = true;                     continue;                 }                  var dt = data.Table;                 if (dt != null && dt.Rows.Count > 0)                 {                     int cellIndex;                      foreach (DataRow row in dt.Rows)                     {                         cellIndex = 0;                         sw.InsertRow(rownum);                         #region 填充内容                          foreach (DataColumn column in dt.Columns)                         {                             string drValue = row[column].ToString();                             if (string.IsNullOrWhiteSpace(drValue))                             {                                 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.00");             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");             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 = 700;             style5.SetFont(font);             stylesMap.Add("sheet_title", (XSSFCellStyle)style5);             return stylesMap;         }          /// <summary>         /// 打包压缩         /// </summary>         /// <param name="zipfile"></param>         /// <param name="sheetList"></param>         /// <param name="output"></param>         private void SubstituteAll(string zipfile, List<EntryPackage> sheetList, 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 (!sheetList.Exists(e => e.EntryPath.Contains(ze.Name)))                         {                             zos.PutNextEntry(new ZipEntry(ze.Name));                             Stream tis = zip.GetInputStream(ze);                             var length = ze.Size;                              byte[] buffer = new byte[2048];                             StreamUtils.Copy(tis, zos, buffer);                          }                     }                      foreach (var sheetEntry in sheetList)                     {                         zos.PutNextEntry(new ZipEntry(sheetEntry.EntryPath));                          using (Stream lis = new FileStream(sheetEntry.XmlFile, FileMode.Open, FileAccess.ReadWrite))                         {                             var length = lis.Length;                              byte[] buffer = new byte[2048];                             StreamUtils.Copy(lis, zos, buffer);                         }                     }                 }             }         }         /// <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);                         }                     }                     zos.PutNextEntry(new ZipEntry(entryList));                     using (Stream lis = new FileStream(xmlfile, FileMode.Open, FileAccess.ReadWrite))                     {                         var length = lis.Length;                         StreamUtils.Copy(lis, zos, null);                     }                 }             }         }     }      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" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"> <dimension ref="A1"/>" +             "<sheetViews><sheetView showRuler="1" showOutlineSymbols ="1" defaultGridColor ="1" colorId ="64" zoomScale ="100" workbookViewId ="0" ></sheetView></sheetViews><sheetFormatPr baseColWidth="8" defaultRowHeight ="15" />");         }          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; }         /// <summary>         /// 数据标识         /// </summary>         public int Flag { get; set; }         public DataTable Table { get; set; }         public DataPackage(bool isSucess) : this(isSucess, null, 0)         {         }         public DataPackage(bool isSucess, DataTable table) : this(isSucess, table, 0)         {         }         public DataPackage(bool isSucess, DataTable table, int flag)         {             IsSucess = isSucess;             Table = table;             Flag = flag;         }     }      public class EntryPackage     {         public string EntryPath { get; set; }         public string XmlFile { get; set; }     } }