還記得剛曾經因為導入導出不會做而發愁的自己嗎？我見過自己前同事因為一個導出改了好幾天，然後我們發現雖然有開源的庫但是用起來卻不得心應手，主要是因為百度使用方案的時候很多方案並不能解決問題。 尤其是嘗試新技術那些舊的操作還會有所改變，為了節約開發時間，我們把解決方案收入到一個個demo中，方便以後即拿 ...
還記得剛曾經因為導入導出不會做而發愁的自己嗎？我見過自己前同事因為一個導出改了好幾天，然後我們發現雖然有開源的庫但是用起來卻不得心應手，主要是因為百度使用方案的時候很多方案並不能解決問題。
尤其是嘗試新技術那些舊的操作還會有所改變，為了節約開發時間，我們把解決方案收入到一個個demo中，方便以後即拿即用。而且這些demo有博客文檔支持，幫助任何人非常容易上手開發跨平臺的.net core。隨著時間的推移，我們的demo庫會日益強大請及時收藏GitHub。
一、首先在Common公用項目中引用EPPlus.Core類庫和Json序列化的類庫及讀取配置文件的類庫
Install-Package EPPlus.Core -Version 1.5.4 Install-Package Newtonsoft.Json -Version 12.0.3-beta2 Install-Package Microsoft.Extensions.Configuration.Json -Version 3.0.0
二、在Common公用項目中添加相關操作類OfficeHelper和CommonHelper及ConfigHelper
1.OfficeHelper中Excel的操作方法
#region Excel #region EPPlus導出Excel /// <summary> /// datatable導出Excel /// </summary> /// <param name="dt">數據源</param> /// <param name="sWebRootFolder">webRoot文件夾</param> /// <param name="sFileName">文件名</param> /// <param name="sColumnName">自定義列名（不傳預設dt列名）</param> /// <param name="msg">失敗返回錯誤信息，有數據返迴路徑</param> /// <returns></returns> public static bool DTExportEPPlusExcel(DataTable dt, string sWebRootFolder, string sFileName, string[] sColumnName, ref string msg) { try { if (dt == null || dt.Rows.Count == 0) { msg = "數據為空"; return false; } //轉utf-8 UTF8Encoding utf8 = new UTF8Encoding(); byte[] buffer = utf8.GetBytes(sFileName); sFileName = utf8.GetString(buffer); //判斷文件夾，不存在創建 if (!Directory.Exists(sWebRootFolder)) Directory.CreateDirectory(sWebRootFolder); //刪除大於30天的文件，為了保證文件夾不會有過多文件 string[] files = Directory.GetFiles(sWebRootFolder, "*.xlsx", SearchOption.AllDirectories); foreach (string item in files) { FileInfo f = new FileInfo(item); DateTime now = DateTime.Now; TimeSpan t = now - f.CreationTime; int day = t.Days; if (day > 30) { File.Delete(item); } } //判斷同名文件 FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); if (file.Exists) { //判斷同名文件創建時間 file.Delete(); file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); } using (ExcelPackage package = new ExcelPackage(file)) { //添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName.Split('.')[0]); //添加表頭 int column = 1; if (sColumnName.Count() == dt.Columns.Count) { foreach (string cn in sColumnName) { worksheet.Cells[1, column].Value = cn.Trim();//可以只保留這個，不加央視，導出速度也會加快 worksheet.Cells[1, column].Style.Font.Bold = true;//字體為粗體 worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//設置樣式類型 worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//設置單元格背景色 column++; } } else { foreach (DataColumn dc in dt.Columns) { worksheet.Cells[1, column].Value = dc.ColumnName;//可以只保留這個，不加央視，導出速度也會加快 worksheet.Cells[1, column].Style.Font.Bold = true;//字體為粗體 worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//設置樣式類型 worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//設置單元格背景色 column++; } } //添加數據 int row = 2; foreach (DataRow dr in dt.Rows) { int col = 1; foreach (DataColumn dc in dt.Columns) { worksheet.Cells[row, col].Value = dr[col - 1].ToString();//這裡已知可以減少一層迴圈，速度會上升 col++; } row++; } //自動列寬，由於自動列寬大數據導出嚴重影響速度，我這裡就不開啟了，大家可以根據自己情況開啟 //worksheet.Cells.AutoFitColumns(); //保存workbook. package.Save(); } return true; } catch (Exception ex) { msg = "生成Excel失敗：" + ex.Message; CommonHelper.WriteErrorLog("生成Excel失敗：" + ex.Message); return false; } } /// <summary> /// Model導出Excel /// </summary> /// <param name="list">數據源</param> /// <param name="sWebRootFolder">webRoot文件夾</param> /// <param name="sFileName">文件名</param> /// <param name="sColumnName">自定義列名</param> /// <param name="msg">失敗返回錯誤信息，有數據返迴路徑</param> /// <returns></returns> public static bool ModelExportEPPlusExcel<T>(List<T> myList, string sWebRootFolder, string sFileName, string[] sColumnName, ref string msg) { try { if (myList == null || myList.Count == 0) { msg = "數據為空"; return false; } //轉utf-8 UTF8Encoding utf8 = new UTF8Encoding(); byte[] buffer = utf8.GetBytes(sFileName); sFileName = utf8.GetString(buffer); //判斷文件夾，不存在創建 if (!Directory.Exists(sWebRootFolder)) Directory.CreateDirectory(sWebRootFolder); //刪除大於30天的文件，為了保證文件夾不會有過多文件 string[] files = Directory.GetFiles(sWebRootFolder, "*.xlsx", SearchOption.AllDirectories); foreach (string item in files) { FileInfo f = new FileInfo(item); DateTime now = DateTime.Now; TimeSpan t = now - f.CreationTime; int day = t.Days; if (day > 30) { File.Delete(item); } } //判斷同名文件 FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); if (file.Exists) { //判斷同名文件創建時間 file.Delete(); file = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); } using (ExcelPackage package = new ExcelPackage(file)) { //添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName.Split('.')[0]); //添加表頭 int column = 1; if (sColumnName.Count() > 0) { foreach (string cn in sColumnName) { worksheet.Cells[1, column].Value = cn.Trim();//可以只保留這個，不加央視，導出速度也會加快 worksheet.Cells[1, column].Style.Font.Bold = true;//字體為粗體 worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中 worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//設置樣式類型 worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//設置單元格背景色 column++; } } //添加數據 int row = 2; foreach (T ob in myList) { int col = 1; foreach (System.Reflection.PropertyInfo property in ob.GetType().GetRuntimeProperties()) { worksheet.Cells[row, col].Value = property.GetValue(ob);//這裡已知可以減少一層迴圈，速度會上升 col++; } row++; } //自動列寬，由於自動列寬大數據導出嚴重影響速度，我這裡就不開啟了，大家可以根據自己情況開啟 //worksheet.Cells.AutoFitColumns(); //保存workbook. package.Save(); } return true; } catch (Exception ex) { msg = "生成Excel失敗：" + ex.Message; CommonHelper.WriteErrorLog("生成Excel失敗：" + ex.Message); return false; } } #endregion #region EPPluse導入 #region 轉換為datatable public static DataTable InputEPPlusByExcelToDT(FileInfo file) { DataTable dt = new DataTable(); if (file != null) { using (ExcelPackage package = new ExcelPackage(file)) { try { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; dt = WorksheetToTable(worksheet); } catch (Exception ex) { Console.WriteLine(ex.Message); } } } return dt; } /// <summary> /// 將worksheet轉成datatable /// </summary> /// <param name="worksheet">待處理的worksheet</param> /// <returns>返回處理後的datatable</returns> public static DataTable WorksheetToTable(ExcelWorksheet worksheet) { //獲取worksheet的行數 int rows = worksheet.Dimension.End.Row; //獲取worksheet的列數 int cols = worksheet.Dimension.End.Column; DataTable dt = new DataTable(worksheet.Name); DataRow dr = null; for (int i = 1; i <= rows; i++) { if (i > 1) dr = dt.Rows.Add(); for (int j = 1; j <= cols; j++) { //預設將第一行設置為datatable的標題 if (i == 1) dt.Columns.Add(GetString(worksheet.Cells[i, j].Value)); //剩下的寫入datatable else dr[j - 1] = GetString(worksheet.Cells[i, j].Value); } } return dt; } private static string GetString(object obj) { try { return obj.ToString(); } catch (Exception) { return ""; } } #endregion #region 轉換為IEnumerable<T> /// <summary> /// 從Excel中載入數據（泛型） /// </summary> /// <typeparam name="T">每行數據的類型</typeparam> /// <param name="FileName">Excel文件名</param> /// <returns>泛型列表</returns> public static IEnumerable<T> LoadFromExcel<T>(FileInfo existingFile) where T : new() { //FileInfo existingFile = new FileInfo(FileName);//如果本地地址可以直接使用本方法，這裡是直接拿到了文件 List<T> resultList = new List<T>(); Dictionary<string, int> dictHeader = new Dictionary<string, int>(); using (ExcelPackage package = new ExcelPackage(existingFile)) { ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int colStart = worksheet.Dimension.Start.Column; //工作區開始列 int colEnd = worksheet.Dimension.End.Column; //工作區結束列 int rowStart = worksheet.Dimension.Start.Row; //工作區開始行號 int rowEnd = worksheet.Dimension.End.Row; //工作區結束行號 //將每列標題添加到字典中 for (int i = colStart; i <= colEnd; i++) { dictHeader[worksheet.Cells[rowStart, i].Value.ToString()] = i; } List<PropertyInfo> propertyInfoList = new List<PropertyInfo>(typeof(T).GetProperties()); for (int row = rowStart + 1; row <=rowEnd; row++) { T result = new T(); //為對象T的各屬性賦值 foreach (PropertyInfo p in propertyInfoList) { try { ExcelRange cell = worksheet.Cells[row, dictHeader[p.Name]]; //與屬性名對應的單元格 if (cell.Value == null) continue; switch (p.PropertyType.Name.ToLower()) { case "string": p.SetValue(result, cell.GetValue<String>()); break; case "int16": p.SetValue(result, cell.GetValue<Int16>()); break; case "int32": p.SetValue(result, cell.GetValue<Int32>()); break; case "int64": p.SetValue(result, cell.GetValue<Int64>()); break; case "decimal": p.SetValue(result, cell.GetValue<Decimal>()); break; case "double": p.SetValue(result, cell.GetValue<Double>()); break; case "datetime": p.SetValue(result, cell.GetValue<DateTime>()); break; case "boolean": p.SetValue(result, cell.GetValue<Boolean>()); break; case "byte": p.SetValue(result, cell.GetValue<Byte>()); break; case "char": p.SetValue(result, cell.GetValue<Char>()); break; case "single": p.SetValue(result, cell.GetValue<Single>()); break; default: break; } } catch (KeyNotFoundException ex) { } } resultList.Add(result); } } return resultList; } #endregion #endregion #endregion
2.ConfigHelper添加讀取配置文件方法（瞭解更多看我過去的文章）
private static IConfiguration _configuration; static ConfigHelper() { //在當前目錄或者根目錄中尋找appsettings.json文件 var fileName = "Config/ManagerConfig.json"; var directory = AppContext.BaseDirectory; directory = directory.Replace("\\", "/"); var filePath = $"{directory}/{fileName}"; if (!File.Exists(filePath)) { var length = directory.IndexOf("/bin"); filePath = $"{directory.Substring(0, length)}/{fileName}"; } var builder = new ConfigurationBuilder() .AddJsonFile(filePath, false, true); _configuration = builder.Build(); } public static string GetSectionValue(string key) { return _configuration.GetSection(key).Value; }
3.CommonHelper中加入json相關操作
/// <summary> /// 得到一個包含Json信息的JsonResult /// </summary> /// <param name="isOK">伺服器處理是否成功 1.成功 -1.失敗 0.沒有數據</param> /// <param name="msg">報錯消息</param> /// <param name="data">攜帶的額外信息</param> /// <returns></returns> public static string GetJsonResult(int code, string msg, object data = null) { var jsonObj = new { code = code, msg = msg, data = data }; return Newtonsoft.Json.JsonConvert.SerializeObject(jsonObj); }
三、添加OfficeController控制器和ManagerConfig配置文件
1.ManagerConfig配置（瞭解更多看我過去的文章）
{ "FileMap": { "ImgPath": "D:\\myfile\\TemplateCore\\TemplateCore\\wwwroot\\UpImg\\", "ImgWeb": "http://127.0.0.1:1994/UpImg/", "FilePath": "D:\\myfile\\TemplateCore\\TemplateCore\\wwwroot\\UpFile\\", "FileWeb": "http://127.0.0.1:1994/UpFile/", "VideoPath": "D:\\myfile\\TemplateCore\\TemplateCore\\wwwroot\\UpVideo\\", "VideoWeb": "http://127.0.0.1:1994/UpVideo/", "Web": "http://127.0.0.1:1994/" } }
2.OfficeController控制器添加Excel處理相應方法
#region EPPlus導出Excel public string DTExportEPPlusExcel() { string code = "fail"; DataTable tblDatas = new DataTable("Datas"); DataColumn dc = null; dc = tblDatas.Columns.Add("ID", Type.GetType("System.Int32")); dc.AutoIncrement = true;//自動增加 dc.AutoIncrementSeed = 1;//起始為1 dc.AutoIncrementStep = 1;//步長為1 dc.AllowDBNull = false;// dc = tblDatas.Columns.Add("Product", Type.GetType("System.String")); dc = tblDatas.Columns.Add("Version", Type.GetType("System.String")); dc = tblDatas.Columns.Add("Description", Type.GetType("System.String")); DataRow newRow; newRow = tblDatas.NewRow(); newRow["Product"] = "大話西游"; newRow["Version"] = "2.0"; newRow["Description"] = "我很喜歡"; tblDatas.Rows.Add(newRow); newRow = tblDatas.NewRow(); newRow["Product"] = "夢幻西游"; newRow["Version"] = "3.0"; newRow["Description"] = "比大話更幼稚"; tblDatas.Rows.Add(newRow); newRow = tblDatas.NewRow(); newRow["Product"] = "西游記"; newRow["Version"] = null; newRow["Description"] = ""; tblDatas.Rows.Add(newRow); for (int x = 0; x < 100000; x++) { newRow = tblDatas.NewRow(); newRow["Product"] = "西游記"+x; newRow["Version"] = ""+x; newRow["Description"] = x; tblDatas.Rows.Add(newRow); } string fileName = "MyExcel.xlsx"; string[] nameStrs = new string[tblDatas.Rows.Count];//每列名，這裡不賦值則表示取預設 string savePath = "wwwroot/Excel";//相對路徑 string msg = "Excel/"+ fileName;//文件返回地址，出錯就返回錯誤信息。 System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); //開始監視代碼運行時間 bool b = OfficeHelper.DTExportEPPlusExcel(tblDatas, savePath, fileName, nameStrs, ref msg) ; TimeSpan timespan = watch.Elapsed; //獲取當前實例測量得出的總時間 watch.Stop(); //停止監視 if (b) { code = "success"; } return "{\"code\":\"" + code + "\",\"msg\":\"" + msg + "\",\"timeSeconds\":\"" + timespan.TotalSeconds + "\"}"; } public string ModelExportEPPlusExcel() { string code = "fail"; List<Article> articleList = new List<Article>(); for (int x = 0; x < 100000; x++) { Article article = new Article(); article.Context = "內容："+x; article.Id = x + 1; article.CreateTime = DateTime.Now; article.Title = "標題：" + x; articleList.Add(article); } string fileName = "MyModelExcel.xlsx"; string[] nameStrs = new string[4] {"Id", "Title", "Context", "CreateTime" };//按照模型先後順序，賦值需要的名稱 string savePath = "wwwroot/Excel";//相對路徑 string msg = "Excel/" + fileName;//文件返回地址，出錯就返回錯誤信息。 System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); //開始監視代碼運行時間 bool b = OfficeHelper.ModelExportEPPlusExcel(articleList, savePath, fileName, nameStrs, ref msg); TimeSpan timespan = watch.Elapsed; //獲取當前實例測量得出的總時間 watch.Stop(); //停止監視 if (b) { code = "success<
