近日做專案程序中，遇到一個客戶要求將每個月產生的交易明細資料快速匯出到excel，方便他們財務人員在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 =https://www.cnblogs.com/Jeez_JBF/archive/2021/10/24/ 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 =https://www.cnblogs.com/Jeez_JBF/archive/2021/10/24/ 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("<", "<").Replace(">", ">"), 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; } } }
經過測驗原來匯出10萬條資料要10分鐘左右，現在只要10s!
大家還有更好的方法歡迎推薦！
快速開發，隨需而變，將互聯網時代的企業管理軟體做到極致轉載請註明出處，本文鏈接：https://www.uj5u.com/net/335033.html
標籤：.NET技术
上一篇：WCF 動態呼叫