EPPlus导出两千万记录的测试代码

采用导入100w条记录一个文件，然后合并的方式

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<string> zlist = new List<string>();
 
            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<string> _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;
        }
    }
}