2,679
社区成员
发帖
与我相关
我的任务
分享C#快速导出百万级别的数据到Excel
近日做项目过程中，遇到一个客户要求将每个月产生的交易明细数据快速导出到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!
大家还有更好的方法欢迎推荐！
...全文
请发表友善的回复…
发表回复
wahaha2223 2021-11-11
- 打赏
- 举报
6666
低代码开发平台 2021-11-09
- 打赏
- 举报
需要引用的组件可在低代码开发平台下载
试用链接：
https://pan.baidu.com/s/1CoCu31HZb9u3bXFwd_CvUQ
提取码：1234
中获取！
低代码开发平台 2021-11-29
- 举报
账号admin密码admin或者820419或者sasa
