4
.Net Core 如何数据导出 Excel？（EPPlus->OfficeOpenXml 实现固定列和动态列导出...
source link: https://www.cnblogs.com/czzj/p/OfficeOpenXmlDownload.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
特别注意：本文设计的包（OfficeOpenXml.Extension.AspNetCore）依赖于 EPPlus 5.0.3 等更高版本，属于限制商业用途版本，因此只能用作个人或公司小范围内部使用。
对于将数据以 Excel 表格文件输出，还是比较常用的，也存在诸多情况，比如列固定或不固定、数据类型为 List<T>或 Json 对象等。
本文通过包 OfficeOpenXml.Extension.AspNetCore 实现两个方法导出列数固定和不固定的数据。
注意：OfficeOpenXml.Extension.AspNetCore 是一个基于 OfficeOpenXml 拓展，它依赖于 EPPlus，用于根据模板输出 Excel。
包控制台安装：
NuGet> Install-Package OfficeOpenXml.Extension.AspNetCore -Version 1.0.0
一、根据已知类型对象 List 下载
本章节方法适用背景：
数据列数固定，且可罗列。
对于对象 List<T> 的属性，一般不会命名为汉字，那么就需要将列名转换为汉字，以方便数据清晰显示。
如下为一个基于 WebAPI 项目的固定列数，动态行数的下载实例：
// 安装包：OfficeOpenXml.Extension.AspNetCore // 支持 Core 3.1 及以上，Standard 2.0 及以上using OfficeOpenXml;using OfficeOpenXml.Style;using Microsoft.AspNetCore.Mvc; // File 支持 [HttpGet]public FileContentResult DownloadInfo(){ try { string sql_datalist = ""; var resultlist = dapperFactory.Query<ShujuMXModel>(sql_datalist).ToList(); string sFileName = $"{Guid.NewGuid()}.xlsx"; FileInfo fileinfo = new FileInfo(System.IO.Path.Combine("C:/Files", sFileName)); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; using (ExcelPackage package = new ExcelPackage(fileinfo)) { // 添加 worksheet ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("明细表"); // 添加头 worksheet.Cells[1, 1].Value = "序号"; worksheet.Cells[1, 2].Value = "公司"; worksheet.Cells[1, 3].Value = "日期"; // 添加值 for (int i = 0; i < resultlist.Count; i++) { worksheet.Cells["A" + (i + 2)].Value = resultlist[i].xuhao; worksheet.Cells["B" + (i + 2)].Value = resultlist[i].gongsimc; worksheet.Cells["C" + (i + 2)].Value = resultlist[i].riqi.Substring(0,10); } worksheet.Column(1).Width = 10; // 设置列宽，从第 1 列开始 worksheet.Column(2).Width = 30; worksheet.Column(3).Width = 15; worksheet.Row(1).Style.Font.Bold = true; // 给第一行内容设置加粗 worksheet.Cells.Style.WrapText = true; // 自动换行 worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Top; // 给有数据的区域添加边框 using (ExcelRange excelRange = worksheet.Cells[1, 1, resultlist.Count + 1, 3]) // [从第一行开始，从第一列开始，到第几行，到第几列] { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; // 实线 r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); // 黑色 r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } worksheet.Row(1).Style.Font.Bold = true; worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // 第一行内容水平居中 package.Save(); } using (FileStream fs= fileinfo.OpenRead()) { BinaryReader br = new BinaryReader(fs); br.BaseStream.Seek(0, SeekOrigin.Begin); // 将文件指针设置到文件开 byte[] fileBytes = br.ReadBytes((int)br.BaseStream.Length); return File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, sFileName); // 返回文件对象，前端可直接进行下载动作 } } catch (Exception ex) { return null; }}
二、动态列数据库下载
本章节方法适用背景：
数据列数不固定，且可循环取出，表头和对应的数据顺序相同。
动态列就是，列总数不固定，程序根据传入的列数确定第一行表头。
下面是一个根据 json 字符串，以 Excel 文件形式保存数据的实例。其中包含 “tableheader”节点，来指定动态表头；“tablebody”代表全部数据列表。
// 安装包：OfficeOpenXml.Extension.AspNetCore // 支持 Core 3.1 及以上，Standard 2.0 及以上// 由于 Excel 2003 版本 和 2007 之后版本文件结构的差异性，当前扩展无法同时兼容两种模式，仅支持 *.xlsx 文件using OfficeOpenXml;using OfficeOpenXml.Style; public void DownloadByJsonstr(string xiazaisj){ // string jsonstr = "{\"tableheader\":[{\"mingcheng\":\"列名一\",\"daima\":\"bumenx1\",\"shifoutz\":true},{\"mingcheng\":\"列名二\",\"daima\":\"bumenx2\",\"shifoutz\":true}],\"tablebody\":[{\"kemumc\":\"科目一\",\"bumenx1\":0.12,\"bumenx2\":6.0,\"heji\":6.12,\"erjimx\":[{\"kemumc\":\"科目一明细科目1\",\"bumenx1\":0.0,\"bumenx2\":9.82,\"heji\":9.82},{\"kemumc\":\"科目一明细科目2\",\"bumenx1\":0.12,\"bumenx2\":6.18,\"heji\":6.3}]}]}"; var baobiaosj_json = Json_Object.StrToJson(xiazaisj); var tableheader = xiazaisj_json["tableheader"]; // 前两列表头固定 List<string> headersname = new List<string>(){ "科目", "合计" }; // 用于显示 List<string> headerscode = new List<string>(){ "kemumc", "heji" }; // 用于取值 // 从第三列开始，按默认顺序加入后续表头 foreach (var thj in tableheader) { headersname.Add(thj["mingcheng"].ToString()); headerscode.Add(thj["daima"].ToString()); } string sFileName = $"{Guid.NewGuid()}.xlsx"; FileInfo fileinfo = new FileInfo(System.IO.Path.Combine("C:/Log", sFileName)); ExcelPackage.LicenseContext = LicenseContext.NonCommercial; List<TableRowModel> tableRowModels = new List<TableRowModel>(); using (ExcelPackage package = new ExcelPackage(fileinfo)) { ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("数据表"); EpplusHelper.AddHeader(worksheet, headersname.ToArray()); // 添加表头 var tablebody = xiazaisj_json["tablebody"]; TableRowModel tableRowModel = new TableRowModel(); Dictionary<string, string> keyValuePairs = new Dictionary<string, string>(); foreach (var tablebody_first in tablebody) { tableRowModel = new TableRowModel(); keyValuePairs = new Dictionary<string, string>(); tableRowModel.kemumc = tablebody_first["kemumc"].ToString(); // "kemumc": "成本" tableRowModel.heji = tablebody_first["heji"].ToString(); for (int ii = 2; ii < headerscode.Count; ii++) { keyValuePairs.Add(headerscode[ii], tablebody_first[headerscode[ii]].ToString()); // "bumenx1": 0.0 } tableRowModel.dict_lie = keyValuePairs; tableRowModels.Add(tableRowModel); if (tablebody_first["erjimx"] != null) { foreach(var tablebody_second in tablebody_first["erjimx"]) { tableRowModel = new TableRowModel(); keyValuePairs = new Dictionary<string, string>(); tableRowModel.kemumc = tablebody_second["kemumc"].ToString(); // "kemumc": "成本" tableRowModel.heji = tablebody_second["heji"].ToString(); for (int ii = 2; ii < headerscode.Count; ii++) { keyValuePairs.Add(headerscode[ii], tablebody_second[headerscode[ii]].ToString()); // "bumenx1": 0.0 } tableRowModel.dict_lie = keyValuePairs; tableRowModels.Add(tableRowModel); } } } if (tableRowModels.Count > 0) { //添加动态数据 EpplusHelper.AddObjects(worksheet, 2, tableRowModels, headerscode); } worksheet.Column(1).Width = 20; // 设置列宽 worksheet.Column(2).Width = 20; for (int ii = 3; ii <= headerscode.Count; ii++) // 为动态列设置统一列宽 { worksheet.Column(ii).Width = 16; } //worksheet.Cells.Style.WrapText = true; // 自动换行 worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Top; using (ExcelRange r = worksheet.Cells[1, 1, tableRowModels.Count + 1, headersname.Count]) { r.Style.Border.Top.Style = ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; r.Style.Border.Left.Style = ExcelBorderStyle.Thin; r.Style.Border.Right.Style = ExcelBorderStyle.Thin; r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black); r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black); } worksheet.View.FreezePanes(2, 3); // 冻结第一行，以及前两列 worksheet.Row(1).Style.Font.Bold = true; // 第一行加粗 worksheet.Row(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;// 第一行水平居中 package.Save(); }}
EpplusHelper.cs 类，用作循环添加表头和数据。
using System;using System.Collections.Generic;using OfficeOpenXml; public static class EpplusHelper{ /// <summary> /// 通过 名称数组 添加表头 /// </summary> /// <param name="sheet"></param> /// <param name="headertexts"></param> public static void AddHeader(ExcelWorksheet sheet, params string[] headertexts) { for (var i = 0; i < headertexts.Length; i++) { AddHeader(sheet, i + 1, headertexts[i]); } } /// <summary> /// 添加动态数据 /// </summary> /// <param name="worksheet"></param> /// <param name="startrowindex">从第几行开始</param> /// <param name="items">行数据列表</param> /// <param name="headerscode">列名代码列表，用于取数据</param> public static void AddObjects(ExcelWorksheet worksheet, int startrowindex, IList<TableRowModel> items, List<string> headerscode) { for (var i = 0; i < items.Count; i++) { worksheet.Cells[i + startrowindex, 1].Value = items[i].kemumc; // 注意此处为兼容前两列固定列 worksheet.Cells[i + startrowindex, 2].Value = items[i].heji; for (var j = 2; j < headerscode.Count; j++) // headercode:kemumc,heji,bumenx1,bumenx2... { worksheet.Cells [i + startrowindex, j + 1] .Value = items[i].dict_lie[headerscode[j]]; } } }}
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK