C#core使用EPPLUS存取EXCEL檔案

標籤：儲存   oid   count   XML   file   roo   ceo   命名   summary   

1：安裝EPPLUS的NUGET包

實現原始碼：

 1 using OfficeOpenXml; 2 using System; 3 using System.Collections.Generic; 4 using System.IO; 5 using System.Linq; 6  7 namespace BigBeer.Core.Excel 8 { 9     public class Excel10     {11         /// <summary>12         /// 只針對2003以上版本13         /// </summary>14         /// <param name="data">需要儲存的資料</param>15         /// <param name="path">儲存地址</param>16         /// <param name="filename">檔案名稱即可，尾碼.xlsx</param>17         /// <param name="worksheetname">EXCEL文檔下方的命名</param>18         public static void SaveExcel(IList<object> data,string path = null, string filename = null, string worksheetname = null)19         {20             int count = data[0].ToString().Split(‘,‘).Count();21             string sWebRootFolder = "D://Excel/";22             if (!string.IsNullOrEmpty(path)) sWebRootFolder = path;23             if (!Directory.Exists(sWebRootFolder))24             {25                 Directory.CreateDirectory(sWebRootFolder);26             }27             string sFileName = $"{Guid.NewGuid()}.xlsx";28             if (!string.IsNullOrEmpty(filename)) sFileName = $"{filename}.xlsx";29             FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));30             string worksheetName = "sheet";31             if (!string.IsNullOrEmpty(worksheetname)) worksheetName = worksheetname;32 33             using (ExcelPackage package = new ExcelPackage(file))34             {35                 // 添加worksheet36                 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetName);37                 //添加頭38                 for (int i = 0; i < count; i++)39                 {40                     var key = data.ToList()[i].ToString().Split(‘,‘)[i].Split(‘=‘)[0].Replace("{", "").Replace("}", "");41                     worksheet.Cells[1, i + 1].Value = key;42                 }43                 //添加值44                 for (int i = 0; i < data.Count; i++)45                 {46                     var list = data[i];47                     var shu = i + 2;48                     for (int t = 0; t < count; t++)49                     {50                         var c = Convert.ToChar(‘A‘ + t);51                         var value = list.ToString().Split(‘,‘)[t].Split(‘=‘)[1].Replace("{", "").Replace("}", "");52                         worksheet.Cells[$"{c}{shu}"].Value = value;53                     }54                 }55                 package.Save();56                 GC.Collect();57             }58         }59     }60 }

3：使用方法：

 1  IList<object> Good = new List<object>(); 2  3 var temp = result.ToList(); //result為查詢出來的資料 4  5 for (int i = 0; i < temp.Count(); i++) 6 { 7 Good.Add(temp[i]); 8 } 9 Excel.SaveExcel(Good);

 

C#core使用EPPLUS存取EXCEL檔案