還記得剛曾經因為匯入匯出不會做而發愁的自己嗎？我見過自己前同事因為一個匯出改了好幾天，然后我們發現雖然有開源的庫但是用起來卻不得心應手，主要是因為百度使用方案的時候很多方案并不能解決問題，
尤其是嘗試新技術那些舊的操作還會有所改變，為了節約開發時間，我們把解決方案收入到一個個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 = https://www.cnblogs.com/jiyuwu/p/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 = https://www.cnblogs.com/jiyuwu/p/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 = https://www.cnblogs.com/jiyuwu/p/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 =https://www.cnblogs.com/jiyuwu/p/= 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 = https://www.cnblogs.com/jiyuwu/p/null) { var jsonObj = new { code = code, msg = msg, data =https://www.cnblogs.com/jiyuwu/p/ 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"; } return "{\"code\":\"" + code + "\",\"msg\":\"" + msg + "\",\"timeSeconds\":\"" + timespan.TotalSeconds + "\"}"; } #endregion #region EPPlus匯出資料 public async Task<string> ExcelImportEPPlusDTJsonAsync() { IFormFileCollection files = Request.Form.Files; DataTable articles = new DataTable(); int code = 0; string msg = "失敗！"; var file = files[0]; string path = ConfigHelper.GetSectionValue("FileMap:FilePath") + files[0].FileName; using (FileStream fs = System.IO.File.Create(path)) { await file.CopyToAsync(fs); fs.Flush(); } System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); //開始監視代碼運行時間 FileInfo fileExcel = new FileInfo(path); articles = OfficeHelper.InputEPPlusByExcelToDT(fileExcel); TimeSpan timespan = watch.Elapsed; //獲取當前實體測量得出的總時間 watch.Stop(); //停止監視 code = 1; msg = "成功！"; string json = CommonHelper.GetJsonResult(code, msg, new { articles, timespan }); return json; } public async Task<string> ExcelImportEPPlusModelJsonAsync() { IFormFileCollection files = Request.Form.Files; List<Article> articles = new List<Article>(); int code = 0; string msg = "失敗！"; var file = files[0]; string path = ConfigHelper.GetSectionValue("FileMap:FilePath")+files[0].FileName; using (FileStream fs = System.IO.File.Create(path)) { await file.CopyToAsync(fs); fs.Flush(); } System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch(); watch.Start(); //開始監視代碼運行時間 FileInfo fileExcel = new FileInfo(path); articles=OfficeHelper.LoadFromExcel<Article>(fileExcel).ToList(); TimeSpan timespan = watch.Elapsed; //獲取當前實體測量得出的總時間 watch.Stop(); //停止監視 code = 1;msg = "成功！"; string json = CommonHelper.GetJsonResult(code, msg, new { articles, timespan }); return json; } #endregion
四、前端請求設計
<script type="text/javascript"> function DTEPPlusExport() { $.ajax({ type: "post", contentType: 'application/json', url: '/Office/DTExportEPPlusExcel', dataType: 'json', async: false, success: function (data) { if (data.code == "success") { window.open("../" + data.msg); $("#DTEPPlusExcel").text("匯出10w條耗時"+data.timeSeconds+"秒"); } console.log(data.code); }, error: function (xhr) { console.log(xhr.responseText); } }); } function ModelEPPlusExport() { $.ajax({ type: "post", contentType: 'application/json', url: '/Office/ModelExportEPPlusExcel', dataType: 'json', async: false, success: function (data) { if (data.code == "success") { window.open("../" + data.msg); $("#ModelEPPlusExcel").text("匯出10w條耗時"+data.timeSeconds+"秒"); } console.log(data.code); }, error: function (xhr) { console.log(xhr.responseText); } }); } function ExcelToModel() { $("#filename").text(document.getElementById("ExcelToModel").files[0].name); var formData = https://www.cnblogs.com/jiyuwu/p/new FormData(); formData.append('file',document.getElementById("ExcelToModel").files[0]); $.ajax({ url: "../Office/ExcelImportEPPlusModelJson", type: "POST", data: formData, contentType: false, processData: false, dataType: "json", success: function(result){ if (result.code == 1) { console.log(result.data.articles); $("#ToModel").text("匯入10w條耗時"+result.data.timespan+"秒"); } } }); } function ExcelToDT() { $("#filename1").text(document.getElementById("ExcelToDT").files[0].name); var formData = https://www.cnblogs.com/jiyuwu/p/new FormData(); formData.append('file',document.getElementById("ExcelToDT").files[0]); $.ajax({ url: "../Office/ExcelImportEPPlusDTJson", type: "POST", data: formData, contentType: false, processData: false, dataType: "json", success: function(result){ if (result.code == 1) { console.log(result.data.articles); $("#ToDT").text("匯入10w條耗時"+result.data.timespan+"秒"); } } }); } </script> <style> body { margin:auto; text-align:center; } </style> <div style="margin-top:30px;"> <h3>EPPlus匯出案例</h3> <button type="button" class="btn btn-default" id="DTEPPlusExcel" onclick="DTEPPlusExport();">Excel匯出測驗（Datatable）</button> <button type="button" class="btn btn-default" id="ModelEPPlusExcel" onclick="ModelEPPlusExport();">Excel匯出測驗（Model）</button> <h3>EPPlus匯入案例</h3> <div class="col-xs-12 col-sm-4 col-md-4" style="width:100%;"> <div class="file-container" style="display:inline-block;position:relative;overflow: hidden;vertical-align:middle"> <label>Model:</label> <button class="btn btn-success fileinput-button" type="button">上傳</button> <input type="file" accept=".xls,.xlsx" id="ExcelToModel" onchange="ExcelToModel(this.files[0])" style="position:absolute;top:0;left:0;font-size:34px; opacity:0"> </div> <span id="filename" style="vertical-align: middle">未上傳檔案</span> <span id="ToModel"></span> </div><br /><br /> <div class="col-xs-12 col-sm-4 col-md-4" style="width:100%;"> <div class="file-container" style="display:inline-block;position:relative;overflow: hidden;vertical-align:middle"> <label>DataTable:</label> <button class="btn btn-success fileinput-button" type="button">上傳</button> <input type="file" accept=".xls,.xlsx" id="ExcelToDT" onchange="ExcelToDT(this.files[0])" style="position:absolute;top:0;left:0;font-size:34px; opacity:0"> </div> <span id="filename1" style="vertical-align: middle">未上傳檔案</span> <span id="ToDT"></span> </div> </div>
上面的上傳為了美觀用了bootstrap的樣式，如果你喜歡原生可以把兩個匯入案例通過如下代碼替換，其它條件無需改變，
<label>Model:</label><input accept=".xls,.xlsx" type="file" id="ExcelToModel" style="display:initial;" /><button id="ToModel" onclick="ExcelToModel();" >上傳</button><br /> <label>DataTable:</label><input accept=".xls,.xlsx" type="file" id="ExcelToDT" style="display:initial;" /><button id="ToDT" onclick="ExcelToDT();">上傳</button>
五、那么看下效果吧，我們這里模擬了10w條資料進行了簡單實驗，一般要求滿足應該沒什么問題，
開源地址 動動小手，點個推薦吧！
注意：我們機遇屋該專案將長期為大家提供asp.net core各種好用demo，旨在幫助.net開發者提升競爭力和開發速度，建議盡早收藏該模板集合專案，
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/96237.html
標籤：.NET Core