建议收藏：.net core 使用EPPlus导入导出Excel详细案例，精心整理源码已更新至开源模板
- 2019 年 11 月 8 日
- 筆記
还记得刚曾经因为导入导出不会做而发愁的自己吗？我见过自己前同事因为一个导出改了好几天，然后我们发现虽然有开源的库但是用起来却不得心应手，主要是因为百度使用方案的时候很多方案并不能解决问题。
尤其是尝试新技术那些旧的操作还会有所改变，为了节约开发时间，我们把解决方案收入到一个个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"; } 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 = 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 = 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开发者提升竞争力和开发速度，建议尽早收藏该模板集合项目。
