epplus保存为流_EPPlus导出两千万记录的测试代码

using System;

using System.IO;

using OfficeOpenXml;

using System.Data;

using System.Diagnostics;

using System.ComponentModel;

using System.Collections.Generic;

namespace ConsoleApp2

{

class Program

{

static void Main(string[] args)

{

Stopwatch watch = new Stopwatch();

watch.Start();

List zlist = new List();

using (DataTable sourceTable = new DataTable())

{

sourceTable.TableName = "test";

sourceTable.Columns.Add("NO", typeof(string));

sourceTable.Columns.Add("a", typeof(int));

sourceTable.Columns.Add("b", typeof(float));

for (int i = 0; i < 40000000 / 1; i++)

{

DataRow dr = sourceTable.NewRow();

dr["NO"] = "#" + (i + 1).ToString(); ;

dr["a"] = i;

dr["b"] = i;

sourceTable.Rows.Add(dr);

dr = null;

}

int dd = 1000000 / 1;

int pages = (int)sourceTable.Rows.Count / dd + 1;

if (sourceTable.Rows.Count % dd == 0)

pages = pages - 1;

for (int i = 1; i <= pages; i++)

{

string zfilename = Guid.NewGuid().ToString() + ".xlsx";

zlist.Add(zfilename);

FileInfo zfile = new FileInfo(AppDomain.CurrentDomain.BaseDirectory + zfilename);

using (ExcelPackage excel = new ExcelPackage(zfile))

{

ExcelWorksheet ws = excel.Workbook.Worksheets.Add(sourceTable.TableName + i.ToString());

for (int zcol = 0; zcol < sourceTable.Columns.Count; zcol++)

{

ws.Cells[1, zcol + 1].Value = sourceTable.Columns[zcol].ColumnName;

}

int zrow = 0;

while (sourceTable.Rows.Count > 0)

{

for (int zcol = 0; zcol < sourceTable.Columns.Count; zcol++)

{

ws.Cells[zrow + 2, zcol + 1].Value = sourceTable.Rows[0][zcol];

}

sourceTable.Rows.RemoveAt(0);

zrow++;

if (zrow % dd == 0)

break;

}

Console.WriteLine(string.Format("pageindex:{0}" ,i));

excel.Save();

}

}

sourceTable.Rows.Clear();

sourceTable.Columns.Clear();

sourceTable.Clear();

sourceTable.Reset();

}

GC.Collect();

GC.WaitForFullGCComplete();

string zpath = AppDomain.CurrentDomain.BaseDirectory;

zpath = zpath.Substring(0, zpath.Length - 1);

MergeExcel(zpath, zlist, string.Format("{0}.xlsx" ,Guid.NewGuid()));

foreach (string item in zlist)

{

File.Delete(zpath + "\\" + item);

}

watch.Stop();

Console.Write("用时:" + watch.Elapsed.ToString());

Console.ReadLine();

Console.ReadLine();

}

public static DataTable DtSelectTop(int from, int to, DataTable oDT)

{

if (oDT.Rows.Count < from) return oDT;

DataTable NewTable = oDT.Clone();

DataRow[] rows = oDT.Select("1=1");

for (int i = from; i < to; i++)

{

((DataRow)rows[i])["NO"] = "#" + (i + 1).ToString();

NewTable.ImportRow((DataRow)rows[i]);

}

return NewTable;

}

private static bool MergeExcel(string _stFilePath, List _listFiles, string _stSaveFileName)

{

ExcelPackage epMergeFile = new ExcelPackage();

bool result = true;

try

{

string stSheetName = string.Empty;

int zi = 0;

foreach (string item in _listFiles)

{

zi++;

FileInfo newFile = new FileInfo(_stFilePath + "\\" + item);

using (ExcelPackage pck = new ExcelPackage(newFile))

{

ExcelWorkbook workBook = pck.Workbook;

if (workBook != null)

{

if (workBook.Worksheets.Count > 0)

{

stSheetName = workBook.Worksheets[1].Name;

epMergeFile.Workbook.Worksheets.Add(stSheetName, workBook.Worksheets[1]);

}

}

}

}

}

catch (Exception ex)

{

result = false;

Debug.WriteLine("合并文件失败:" + ex.Message);

throw new Exception("合并文件失败！");

}

if (result)

{

string stFile = _stFilePath + "\\" + _stSaveFileName;

epMergeFile.SaveAs(new FileInfo(stFile));

epMergeFile.Dispose();

}

return result;

}

}

}