NET Core 使用EPPlus导出
EPPlus的基本介绍
EPPlus是一个使用Open Office XML(xlsx)文件格式,能读写Excel 2007/2010 文件的开源组件,在导出Excel的时候不需要电脑上安装office。官网地址:http://epplus.codeplex.com/
使用的话直接NuGet上获取对应的dll即可。
但有一点注意,EPPlus不支持2003版本的Excel。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
单元格赋值 单元格赋值很简单,指定对应的单元格就可以直接赋值,价格遍历循环就可以进行批量的操作了 worksheet.Cells[1, 1].Value = "测试" ; //直接指定行列数进行赋值 worksheet.Cells[ "A1" ].Value = "赋值" ; //直接指定单元格进行赋值 设置单元格样式 worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中 worksheet.Cells[1, 4, 1, 5].Merge = true ; //合并单元格 worksheet.Cells.Style.WrapText = true ; //自动换行 设置字体 worksheet.Cells[1, 1].Style.Font.Bold = true ; //字体为粗体 worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White); //字体颜色 worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑" ; //字体 worksheet.Cells[1, 1].Style.Font.Size = 12; //字体大小 设置单元格边框 worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); //设置单元格所有边框 worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //单独设置单元格底部边框样式和颜色(上下左右均可分开设置) worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); 设置单元格高和宽 worksheet.Cells.Style.ShrinkToFit = true ; //单元格自动适应大小 worksheet.Row(1).Height = 15; //设置行高 worksheet.Row(1).CustomHeight = true ; //自动调整行高 worksheet.Column(1).Width = 15; //设置列宽 设置单元格格式 worksheet.Cells[1, 1].Style.Numberformat.Format = "#,##0.00" ; //这是保留两位小数 设置sheet背景 worksheet.View.ShowGridLines = false ; //去掉sheet的网格线 worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray); //设置背景色 worksheet.BackgroundImage.Image = Image.FromFile( @"firstbg.jpg" ); //设置背景图片 隐藏sheet worksheet.Hidden = eWorkSheetHidden.Hidden; //隐藏sheet worksheet.Column(1).Hidden = true ; //隐藏某一列 worksheet.Row(1).Hidden = true ; //隐藏某一行 图片操作 有时候需求会将某个图片保存至Excel中,代码如下: ExcelPicture picture = worksheet.Drawings.AddPicture( "picture" , Image.FromFile( @"firstbg.jpg" )); //插入图片 picture.SetPosition(100, 100); //设置图片的位置 picture.SetSize(100, 100); //设置图片的大小 Excel加密和锁定 有时候导出的Excel不希望别人修改,可对Excel进行加密,代码如下: worksheet.Protection.IsProtected = true ; //设置是否进行锁定 worksheet.Protection.SetPassword( "yk" ); //设置密码 worksheet.Protection.AllowAutoFilter = false ; //下面是一些锁定时权限的设置 worksheet.Protection.AllowDeleteColumns = false ; worksheet.Protection.AllowDeleteRows = false ; worksheet.Protection.AllowEditScenarios = false ; worksheet.Protection.AllowEditObject = false ; worksheet.Protection.AllowFormatCells = false ; worksheet.Protection.AllowFormatColumns = false ; worksheet.Protection.AllowFormatRows = false ; worksheet.Protection.AllowInsertColumns = false ; worksheet.Protection.AllowInsertHyperlinks = false ; worksheet.Protection.AllowInsertRows = false ; worksheet.Protection.AllowPivotTables = false ; worksheet.Protection.AllowSelectLockedCells = false ; worksheet.Protection.AllowSelectUnlockedCells = false ; worksheet.Protection.AllowSort = false ; |
public async Task<ActionResult> ExportLog() { //string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Excel"; //if (!Directory.Exists(sWebRootFolder)) //{ // Directory.CreateDirectory(sWebRootFolder);//不存在则创建 //} string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; //FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); //fileinfo.Delete(); var strem = new MemoryStream(); using (ExcelPackage package = new ExcelPackage(strem)) { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName); //添加头 DailySignInfo cell = new DailySignInfo() { Date = "日期", Name = "姓名", Attendancetime = "考勤时段", Time = "时间", Status = "状态" }; worksheet.Cells[1, 1].Value = cell.Date; worksheet.Cells[1, 2].Value = cell.Name; worksheet.Cells[1, 3].Value = cell.Attendancetime; worksheet.Cells[1, 4].Value = cell.Time; worksheet.Cells[1, 5].Value = cell.Status; //添加值 int cellnum = 2; foreach (var item in query) { worksheet.Cells["A" + cellnum].Value = item.date+ item.create_time; worksheet.Cells["B" + cellnum].Value = item.realname; worksheet.Cells["C" + cellnum].Value = item.period_name; //worksheet.Cells["D" + cellnum].Value = item.create_time; worksheet.Cells["D" + cellnum].Value = item.status_name; cellnum++; } var arr= package.GetAsByteArray(); strem.Dispose(); string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取响应头里面的Content-Disposition //return Ok("/Excel/" + sFileName); //return this.File(sFileName, type, sFileName); return new FileContentResult(arr, type) { FileDownloadName = sFileName }; } }
2.
private IHostingEnvironment _hostingEnvironment; public XlsxController(IHostingEnvironment hostingEnvironment)//名称对应控制器名 { _hostingEnvironment = hostingEnvironment; }
string sWebRootFolder = _hostingEnvironment.WebRootPath; string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); fileinfo.Delete(); using (ExcelPackage package = new ExcelPackage(fileinfo)) { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName); //添加头 DailySignInfo cell = new DailySignInfo() { Date = "日期", Name = "姓名", Attendancetime = "时段", Time = "时间", Status = "状态" }; worksheet.Cells[1, 1].Value = cell.Date; worksheet.Cells[1, 2].Value = cell.Name; worksheet.Cells[1, 3].Value = cell.dancetime; worksheet.Cells[1, 4].Value = cell.Time; worksheet.Cells[1, 5].Value = cell.Status; //添加值 int cellnum = 2; foreach (var item in query) { worksheet.Cells["A" + cellnum].Value = item.date; worksheet.Cells["B" + cellnum].Value = item.realname; worksheet.Cells["C" + cellnum].Value = item.period_name; worksheet.Cells["D" + cellnum].Value = item.create_time; worksheet.Cells["E" + cellnum].Value = item.status_name; cellnum++; } package.Save(); string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; return this.File(sFileName, type, sFileName); }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
单元格赋值 单元格赋值很简单,指定对应的单元格就可以直接赋值,价格遍历循环就可以进行批量的操作了 worksheet.Cells[1, 1].Value = "测试" ; //直接指定行列数进行赋值 worksheet.Cells[ "A1" ].Value = "赋值" ; //直接指定单元格进行赋值 设置单元格样式 worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中 worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中 worksheet.Cells[1, 4, 1, 5].Merge = true ; //合并单元格 worksheet.Cells.Style.WrapText = true ; //自动换行 设置字体 worksheet.Cells[1, 1].Style.Font.Bold = true ; //字体为粗体 worksheet.Cells[1, 1].Style.Font.Color.SetColor(Color.White); //字体颜色 worksheet.Cells[1, 1].Style.Font.Name = "微软雅黑" ; //字体 worksheet.Cells[1, 1].Style.Font.Size = 12; //字体大小 设置单元格边框 worksheet.Cells[1, 1].Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.FromArgb(191, 191, 191)); //设置单元格所有边框 worksheet.Cells[1, 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; //单独设置单元格底部边框样式和颜色(上下左右均可分开设置) worksheet.Cells[1, 1].Style.Border.Bottom.Color.SetColor(Color.FromArgb(191, 191, 191)); 设置单元格高和宽 worksheet.Cells.Style.ShrinkToFit = true ; //单元格自动适应大小 worksheet.Row(1).Height = 15; //设置行高 worksheet.Row(1).CustomHeight = true ; //自动调整行高 worksheet.Column(1).Width = 15; //设置列宽 设置单元格格式 worksheet.Cells[1, 1].Style.Numberformat.Format = "#,##0.00" ; //这是保留两位小数 设置sheet背景 worksheet.View.ShowGridLines = false ; //去掉sheet的网格线 worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.LightGray); //设置背景色 worksheet.BackgroundImage.Image = Image.FromFile( @"firstbg.jpg" ); //设置背景图片 隐藏sheet worksheet.Hidden = eWorkSheetHidden.Hidden; //隐藏sheet worksheet.Column(1).Hidden = true ; //隐藏某一列 worksheet.Row(1).Hidden = true ; //隐藏某一行 图片操作 有时候需求会将某个图片保存至Excel中,代码如下: ExcelPicture picture = worksheet.Drawings.AddPicture( "picture" , Image.FromFile( @"firstbg.jpg" )); //插入图片 picture.SetPosition(100, 100); //设置图片的位置 picture.SetSize(100, 100); //设置图片的大小 Excel加密和锁定 有时候导出的Excel不希望别人修改,可对Excel进行加密,代码如下: worksheet.Protection.IsProtected = true ; //设置是否进行锁定 worksheet.Protection.SetPassword( "yk" ); //设置密码 worksheet.Protection.AllowAutoFilter = false ; //下面是一些锁定时权限的设置 worksheet.Protection.AllowDeleteColumns = false ; worksheet.Protection.AllowDeleteRows = false ; worksheet.Protection.AllowEditScenarios = false ; worksheet.Protection.AllowEditObject = false ; worksheet.Protection.AllowFormatCells = false ; worksheet.Protection.AllowFormatColumns = false ; worksheet.Protection.AllowFormatRows = false ; worksheet.Protection.AllowInsertColumns = false ; worksheet.Protection.AllowInsertHyperlinks = false ; worksheet.Protection.AllowInsertRows = false ; worksheet.Protection.AllowPivotTables = false ; worksheet.Protection.AllowSelectLockedCells = false ; worksheet.Protection.AllowSelectUnlockedCells = false ; worksheet.Protection.AllowSort = false ; |
public async Task<ActionResult> ExportLog() { //string sWebRootFolder = _hostingEnvironment.WebRootPath + "/Excel"; //if (!Directory.Exists(sWebRootFolder)) //{ // Directory.CreateDirectory(sWebRootFolder);//不存在则创建 //} string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; //FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); //fileinfo.Delete(); var strem = new MemoryStream(); using (ExcelPackage package = new ExcelPackage(strem)) { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName); //添加头 DailySignInfo cell = new DailySignInfo() { Date = "日期", Name = "姓名", Attendancetime = "考勤时段", Time = "时间", Status = "状态" }; worksheet.Cells[1, 1].Value = cell.Date; worksheet.Cells[1, 2].Value = cell.Name; worksheet.Cells[1, 3].Value = cell.Attendancetime; worksheet.Cells[1, 4].Value = cell.Time; worksheet.Cells[1, 5].Value = cell.Status; //添加值 int cellnum = 2; foreach (var item in query) { worksheet.Cells["A" + cellnum].Value = item.date+ item.create_time; worksheet.Cells["B" + cellnum].Value = item.realname; worksheet.Cells["C" + cellnum].Value = item.period_name; //worksheet.Cells["D" + cellnum].Value = item.create_time; worksheet.Cells["D" + cellnum].Value = item.status_name; cellnum++; } var arr= package.GetAsByteArray(); strem.Dispose(); string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.Headers.Add("Access-Control-Expose-Headers", "Content-Disposition");//允许前端获取响应头里面的Content-Disposition //return Ok("/Excel/" + sFileName); //return this.File(sFileName, type, sFileName); return new FileContentResult(arr, type) { FileDownloadName = sFileName }; } }
2.
private IHostingEnvironment _hostingEnvironment; public XlsxController(IHostingEnvironment hostingEnvironment)//名称对应控制器名 { _hostingEnvironment = hostingEnvironment;
}
string sWebRootFolder = _hostingEnvironment.WebRootPath; string sFileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; FileInfo fileinfo = new FileInfo(Path.Combine(sWebRootFolder, sFileName)); fileinfo.Delete(); using (ExcelPackage package = new ExcelPackage(fileinfo)) { // 添加worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(sFileName); //添加头 DailySignInfo cell = new DailySignInfo() { Date = "日期", Name = "姓名", Attendancetime = "时段", Time = "时间", Status = "状态" }; worksheet.Cells[1, 1].Value = cell.Date; worksheet.Cells[1, 2].Value = cell.Name; worksheet.Cells[1, 3].Value = cell.dancetime; worksheet.Cells[1, 4].Value = cell.Time; worksheet.Cells[1, 5].Value = cell.Status; //添加值 int cellnum = 2; foreach (var item in query) { worksheet.Cells["A" + cellnum].Value = item.date; worksheet.Cells["B" + cellnum].Value = item.realname; worksheet.Cells["C" + cellnum].Value = item.period_name; worksheet.Cells["D" + cellnum].Value = item.create_time; worksheet.Cells["E" + cellnum].Value = item.status_name; cellnum++; } package.Save(); string type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; return this.File(sFileName, type, sFileName); }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库