- A+
所属分类：.NET技术
近日做项目过程中，遇到一个客户要求将每个月产生的交易明细数据快速导出到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 = 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("<", "<").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!
大家还有更好的方法欢迎推荐！
