EPPlus主頁(yè)
EPPlus源碼
需求：將xml文件中的數(shù)據(jù)導(dǎo)出成Excel
數(shù)據(jù)量在幾十萬(wàn)吧
xml的數(shù)據(jù)大致如下結(jié)構(gòu)

<?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>

導(dǎo)出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>
        /// 導(dǎo)出EXCEL,可以導(dǎo)出多個(gè)sheet .xsl
        /// </summary>
        /// <param name="dtSources">原始數(shù)據(jù)數(shù)</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;
            }
        }

這段代碼其實(shí)有很大的性能消耗問(wèn)題，因?yàn)镾aveAs寫(xiě)在了循環(huán)的內(nèi)部，一次SaveAs就是一次IO，IO是特別耗能的一種操作。
其次，F(xiàn)ile對(duì)象本身就已經(jīng)可以判斷指定路徑的文件是否存在，無(wú)需創(chuàng)建FileInfo對(duì)象后再去判斷。
修改后的代碼如下：

        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>
        /// 導(dǎo)出EXCEL,可以導(dǎo)出多個(gè)sheet .xsl
        /// </summary>
        /// <param name="dtSources">原始數(shù)據(jù)</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);
                }
            }
        }