EPPlus导出Excel

EPPlus主页
EPPlus源码
需求：将xml文件中的数据导出成Excel
数据量在几十万吧
xml的数据大致如下结构

<?xml version="1.0" standalone="yes"?>
<newDataSet>
  <DataTable1>
    <datacolumn1>001</datacolumn1>
    <datacolumn2>a1</datacolumn2>
    <datacolumn3>b1</datacolumn3>
    <datacolumn4>c1</datacolumn4>
  </DataTable1>
...
  <DataTable1>
    <datacolumn1>001</datacolumn1>
    <datacolumn2>an</datacolumn2>
    <datacolumn3>bn</datacolumn3>
    <datacolumn4>cn</datacolumn4>
  </DataTable1>

<DataTable2>
    <datacolumn1>001</datacolumn1>
    <datacolumn2>a1</datacolumn2>
    <datacolumn3>b1</datacolumn3>
    <datacolumn4>c1</datacolumn4>
    <datacolumn5>d1</datacolumn5>
    <datacolumn6>e1</datacolumn6>
  </DataTable2>
...
  <DataTable2>
    <datacolumn1>001</datacolumn1>
    <datacolumn2>an</datacolumn2>
    <datacolumn3>bn</datacolumn3>
    <datacolumn4>cn</datacolumn4>
    <datacolumn5>dn</datacolumn5>
    <datacolumn6>en</datacolumn6>
  </DataTable2>
...
</newDataSet>

导出Excel的代码如下：

        static void Main(string[] args)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            ds.ReadXml("D:\\Data_20180525170650.xml");
            ExportEasy(ds, "D:\\Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            Console.Write("OK");
            Console.ReadKey();
        }

        /// <summary>
        /// 导出EXCEL,可以导出多个sheet .xsl
        /// </summary>
        /// <param name="dtSources">原始数据数</param>
        /// <param name="strFileName">路径</param>
        public static void ExportEasy(System.Data.DataSet dtSources, string strFileName)
        {            
            FileInfo file = new FileInfo(strFileName);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(strFileName);
            }
            if (dtSources != null && dtSources.Tables != null && dtSources.Tables.Count > 0)
            {
                using (var package = new ExcelPackage(file))
                {
                    int count = dtSources.Tables.Count;
                    for (int i = 0; i < count; i++)
                    {
                        DataTable dt = dtSources.Tables[i];
                        string sheetName = string.IsNullOrEmpty(dt.TableName) ? string.Format("sheet{0}", i + 1) : dt.TableName;
                        Stream s =  DataTableToExcel(dt,sheetName);
                        ExcelPackage p=new ExcelPackage(s);
                        p.SaveAs(file);
                    }
                }
            }
        }
       
        
        public static Stream DataTableToExcel(FileInfo file, DataTable dt, string sheetName)
        {
            try
            {
                using (var package = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);                    
                    worksheet.Cells["A1"].LoadFromDataTable(dt, true);
                    MemoryStream ms = new MemoryStream();
                    package.SaveAs(ms);
                    ms.Flush();
                    ms.Position = 0;
                    return ms;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

这段代码其实有很大的性能消耗问题，因为SaveAs写在了循环的内部，一次SaveAs就是一次IO，IO是特别耗能的一种操作。
其次，File对象本身就已经可以判断指定路径的文件是否存在，无需创建FileInfo对象后再去判断。
修改后的代码如下：

        static void Main(string[] args)
        {
            System.Data.DataSet ds = new System.Data.DataSet();
            ds.ReadXml("D:\\Data_20180525170650.xml");
            ExportEasy(ds, "D:\\Excel" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
            Console.Write("OK");
            Console.ReadKey();
        }

        /// <summary>
        /// 导出EXCEL,可以导出多个sheet .xsl
        /// </summary>
        /// <param name="dtSources">原始数据</param>
        /// <param name="strFileName">路径</param>
        public static void ExportEasy(System.Data.DataSet dtSources, string strFileName)
        {
            if (File.Exists(strFileName)) File.Delete(strFileName);
            FileInfo file = new FileInfo(strFileName);           
            if (dtSources != null && dtSources.Tables != null && dtSources.Tables.Count > 0)
            {
                using (var package = new ExcelPackage(file))
                {
                    int count = dtSources.Tables.Count;
                    for (int i = 0; i < count; i++)
                    {
                        DataTable dt = dtSources.Tables[i];
                        string sheetName = string.IsNullOrEmpty(dt.TableName) ? string.Format("sheet{0}", i + 1) : dt.TableName;
                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sheetName);
                        worksheet.Cells["A1"].LoadFromDataTable(dt, true);                        
                    }
                    package.SaveAs(file);
                }
            }
        }