ASP.NET Core使用EPPlus操作Excel
1.前言
本篇文章通过ASP.NET Core的EPPlus包去操作Excel(导入导出),其使用原理与NPOI类似,导出Excel的时候不需要电脑上安装office,非常好用
2.使用
新建一个ASP.NET Core Web应用程序(模型视图控制器),还有一个类库,SDK2.1版本,解决方案如下
3.在EPPlusCommon类库中创建一个EPPlusHelper类,包括两个方法,导入和读取数据
1 using OfficeOpenXml; 2 using OfficeOpenXml.Style; 3 using System; 4 using System.Collections.Generic; 5 using System.Data; 6 using System.Drawing; 7 using System.IO; 8 using System.Text; 9 namespace EPPlusCommon 10 { 11 public class EPPlusHelper 12 { 13 private static int i; 14 15 /// <summary> 16 /// 导入数据到Excel中 17 /// </summary> 18 /// <param name="fileName"></param> 19 /// <param name="ds"></param> 20 public static bool ImportExcel(string fileName, DataSet ds) 21 { 22 if (ds == null || ds.Tables.Count == 0) 23 { 24 return false; 25 } 26 FileInfo file = new FileInfo(fileName); 27 if (file.Exists) 28 { 29 file.Delete(); 30 file = new FileInfo(fileName); 31 } 32 //在using语句里面我们可以创建多个worksheet，ExcelPackage后面可以传入路径参数 33 //命名空间是using OfficeOpenXml 34 using (ExcelPackage package = new ExcelPackage(file)) 35 { 36 foreach (DataTable dt in ds.Tables) 37 { 38 //创建工作表worksheet 39 ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName); 40 //给单元格赋值有两种方式 41 //worksheet.Cells[1, 1].Value = "单元格的值";直接指定行列数进行赋值 42 //worksheet.Cells["A1"].Value = "单元格的值";直接指定单元格进行赋值 43 worksheet.Cells.Style.Font.Name = "微软雅黑"; 44 worksheet.Cells.Style.Font.Size = 12; 45 worksheet.Cells.Style.ShrinkToFit = true;//单元格自动适应大小 46 for (int i = 0; i < dt.Rows.Count; i++) 47 { 48 for (int j = 0; j < dt.Columns.Count; j++) 49 { 50 worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString(); 51 } 52 } 53 using (var cell = worksheet.Cells[1, 1, 1, dt.Columns.Count]) 54 { 55 //设置样式:首行居中加粗背景色 56 cell.Style.Font.Bold = true; //加粗 57 cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 58 cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中 59 cell.Style.Font.Size = 14; 60 cell.Style.Fill.PatternType = ExcelFillStyle.Solid; //背景颜色 61 cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(128, 128, 128));//设置单元格背景色 62 } 63 } 64 //保存 65 package.Save(); 66 } 67 return true; 68 } 69 70 /// <summary> 71 /// 读取Excel数据 72 /// </summary> 73 /// <param name="fileName"></param> 74 public static string ReadExcel(string fileName) 75 { 76 StringBuilder sb = new StringBuilder(); 77 FileInfo file = new FileInfo(fileName); 78 try 79 { 80 using (ExcelPackage package = new ExcelPackage(file)) 81 { 82 var count = package.Workbook.Worksheets.Count; 83 for (int k = 1; k <= count; k++) //worksheet是从1开始的 84 { 85 var workSheet = package.Workbook.Worksheets[k]; 86 sb.Append(workSheet.Name); 87 sb.Append(Environment.NewLine); 88 int row = workSheet.Dimension.Rows; 89 int col = workSheet.Dimension.Columns; 90 for (int i = 1; i <= row; i++) 91 { 92 for (int j = 1; j <= col; j++) 93 { 94 sb.Append(workSheet.Cells[i, j].Value.ToString() + "\t"); 95 } 96 sb.Append(Environment.NewLine); 97 } 98 sb.Append(Environment.NewLine); 99 sb.Append(Environment.NewLine); 100 } 101 } 102 } 103 catch (Exception ex) 104 { 105 return "An error had Happen"; 106 } 107 return sb.ToString(); 108 } 109 } 110 }
代码片段已经给出了一些注释,对于Excel的更多样式设置可以参考博客
4.新建一个ExcelController(用于读取和导入Excel),代码如下
using System; using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using EPPlusCommon; using Microsoft.AspNetCore.Hosting; using System.IO; using EPPlusWeb.Models; namespace EPPlusWeb.Controllers { public class ExcelController : Controller { private readonly IHostingEnvironment _hosting; public ExcelController(IHostingEnvironment hosting) { _hosting = hosting; } public IActionResult Import() { string folder = _hosting.WebRootPath; string fileName = Path.Combine(folder, "Excel", "Test.xlsx"); bool result = EPPlusHelper.ImportExcel(fileName, ExcelData.GetExcelData()); string str = result ? "导入Excel成功:" + fileName : "导入失败"; return Content(str); } public IActionResult Read() { string folder = _hosting.WebRootPath; string fileName = Path.Combine(folder, "Excel", "Test.xlsx"); string result = EPPlusHelper.ReadExcel(fileName); return Content(result); } } }
using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Threading.Tasks; namespace EPPlusWeb.Models { public class ExcelData { public static DataSet GetExcelData() { DataSet ds = new DataSet(); string[,] infos = { { "151100310001","刘备","男","计算机科学与工程学院","计算机科学与技术"}, { "151100310002","关羽","男","计算机科学与工程学院","通信工程"}, { "151100310003","张飞","男","数学与统计学院","信息与计算科学"}, { "151100310004","小乔","女","文学院","汉语言文学"} }; string[,] scores = { { "151100310001","刘备","88","90","80"}, { "151100310002","关羽","86","70","75"}, { "151100310003","张飞","67","75","81"}, { "151100310004","小乔","99","89","92"} }; DataTable stuInfoTable = new DataTable { TableName = "学生信息表" }; stuInfoTable.Columns.Add("学号", typeof(string)); stuInfoTable.Columns.Add("姓名", typeof(string)); stuInfoTable.Columns.Add("性别", typeof(string)); stuInfoTable.Columns.Add("学院", typeof(string)); stuInfoTable.Columns.Add("专业", typeof(string)); stuInfoTable.Rows.Add("学号", "姓名", "性别", "学院", "专业"); for (int i = 0; i < infos.GetLength(0); i++) { DataRow row = stuInfoTable.NewRow(); for (int j = 0; j < infos.GetLength(1); j++) { row[j] = infos[i, j]; } stuInfoTable.Rows.Add(row); } ds.Tables.Add(stuInfoTable); DataTable stuScoreTable = new DataTable { TableName = "学生成绩表" }; stuScoreTable.Columns.Add("学号", typeof(string)); stuScoreTable.Columns.Add("姓名", typeof(string)); stuScoreTable.Columns.Add("语文", typeof(string)); stuScoreTable.Columns.Add("数学", typeof(string)); stuScoreTable.Columns.Add("英语", typeof(string)); stuScoreTable.Rows.Add("学号", "姓名", "语文", "数学", "英语"); for (int i = 0; i < scores.GetLength(0); i++) { DataRow row = stuScoreTable.NewRow(); for (int j = 0; j < scores.GetLength(1); j++) { row[j] = scores[i, j]; } stuScoreTable.Rows.Add(row); } ds.Tables.Add(stuScoreTable); return ds; } } }
5.相关结果如下
本文章代码已经放在github:https://github.com/xs0910/.NET-Core-EPPlus
