当前位置：首页 > news >正文

.NET6使用MiniExcel根据数据源横向导出头部标题及数据

news 来源：原创 2024/4/28 20:54:36

.NET6+MiniExcel根据数据源横向导出头部标题

MiniExcel简单、高效避免OOM的.NET处理Excel查、写、填充数据工具。

特点:
低内存耗用，避免OOM、频繁 Full GC 情况
支持即时操作每行数据
兼具搭配 LINQ 延迟查询特性，能办到低消耗、快速分页等复杂查询
轻量，不需要安装 Microsoft Office、COM+，DLL小于150KB
简便操作的 API 风格

github地址： MiniExcel
gitee地址： MiniExcel

本案例实现的功能是使用Miniexcel横向导出指标编码、指标名称，医院类型及指标对应的数据值，
要求导出效果如下所示：

第一列展示医院
头部两行动态展示指标编码、指标名称，下面展示每家医院所对应指标的值
安装NuGet程序包SqlSugarCore、MiniExcel、Furion

代码如下:
结合实际情况，可以适当改下

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MiniExcelLibs;
using MiniExcelLibs.Attributes;
using MiniExcelLibs.OpenXml;
using SqlSugar;namespace DJPSMS.Application.Service
{[AllowAnonymous]public class MiniexcelTest : IDynamicApiController{private readonly ISqlSugarClient _db;private readonly SqlSugarRepository<TDU_HOSPITALTEST> _tduHospitalRepository;/// <summary>/// 构造函数注入SqlSugar/// </summary>/// <param name="db"></param>public MiniexcelTest(ISqlSugarClient db, SqlSugarRepository<TDU_HOSPITALTEST> tduHospitalRepository){_db = db;_tduHospitalRepository = tduHospitalRepository;}/// <summary>/// Miniexcel导出/// </summary>/// <param name="Chapter">章节（案例中未使用）</param>/// <returns></returns>[HttpGet("DownLoadExcel")]public void DownLoadExcel([FromQuery][Required] string Chapter = "1"){try{// 导出数据源总集合var quotaAssemble = new List<Dictionary<string, object>>();// 构建横向指标标题var quotaPairy = new Dictionary<string, object>();#region 构造excel模板及数据源Log.Information($"构造excel横向指标标题开始--------");// 使用SqlSugar查询数据库// var quotaArray = _db.Queryable<DW_QUOTATEST>()//    .Where(x => x.Q_CODE.StartsWith(Chapter))//    .GroupBy(x => new { x.Q_CODE, x.Q_NAME })//    .OrderBy(x => x.Q_CODE)//    .Select(g => new { g.Q_CODE, g.Q_NAME })//    .ToList();// 所有的指标类型var quotaArray = new List<DW_QUOTATEST>(){new DW_QUOTATEST{ Q_ID = "1", Q_CODE = "1.01.01",Q_NAME ="指标1"},new DW_QUOTATEST{ Q_ID = "2", Q_CODE = "1.01.02",Q_NAME ="指标2"},new DW_QUOTATEST{ Q_ID = "3", Q_CODE = "1.01.03",Q_NAME ="指标3"},new DW_QUOTATEST{ Q_ID = "4", Q_CODE = "1.01.04",Q_NAME ="指标4"},new DW_QUOTATEST{ Q_ID = "5", Q_CODE = "1.01.05",Q_NAME ="指标5"},new DW_QUOTATEST{ Q_ID = "6", Q_CODE = "1.01.06",Q_NAME ="指标6"},new DW_QUOTATEST{ Q_ID = "7", Q_CODE = "1.01.07",Q_NAME ="指标7"},new DW_QUOTATEST{ Q_ID = "8", Q_CODE = "1.01.08",Q_NAME ="指标8"},new DW_QUOTATEST{ Q_ID = "9", Q_CODE = "1.01.09",Q_NAME ="指标9"},new DW_QUOTATEST{ Q_ID = "10", Q_CODE = "1.01.10",Q_NAME ="指标10"},new DW_QUOTATEST{ Q_ID = "11", Q_CODE = "1.01.11",Q_NAME ="指标11"},new DW_QUOTATEST{ Q_ID = "12", Q_CODE = "1.01.12",Q_NAME ="指标12"},new DW_QUOTATEST{ Q_ID = "13", Q_CODE = "1.01.13",Q_NAME ="指标13"}};// 設置列宽var config = new OpenXmlConfiguration{DynamicColumns = CreateDynamicColumns(quotaArray.GroupBy(x => x.Q_CODE).Select(x => x.Key).ToList())};// 构建横向指标标题quotaPairy["医院名称"] = "";for (int i = 0; i < quotaArray.Count; i++){if (quotaPairy.ContainsKey(quotaArray[i].Q_CODE)) continue;var propertyCode = quotaArray[i].Q_CODE;var propertyName = quotaArray[i].Q_NAME;quotaPairy[propertyCode] = propertyName;}quotaAssemble.Add(quotaPairy);Log.Information($"构造excel横向指标标题结束--------");// 构建第一列医院类型var hospitalList = new List<TDU_HOSPITALTEST>(){new TDU_HOSPITALTEST{ FJGDM ="1",FDESC ="测试医院1" },new TDU_HOSPITALTEST{ FJGDM ="2",FDESC ="测试医院2" },new TDU_HOSPITALTEST{ FJGDM ="3",FDESC ="南京第一" },new TDU_HOSPITALTEST{ FJGDM ="3",FDESC ="测试医院4" },new TDU_HOSPITALTEST{ FJGDM ="4",FDESC ="测试医院5" },new TDU_HOSPITALTEST{ FJGDM ="5",FDESC ="测试医院6" },new TDU_HOSPITALTEST{ FJGDM ="6",FDESC ="测试医院7" },new TDU_HOSPITALTEST{ FJGDM ="7",FDESC ="测试医院8" },new TDU_HOSPITALTEST{ FJGDM ="8",FDESC ="测试医院9" },new TDU_HOSPITALTEST{ FJGDM ="9",FDESC ="测试医院10" },new TDU_HOSPITALTEST{ FJGDM ="10",FDESC ="测试医院11" },new TDU_HOSPITALTEST{ FJGDM ="11",FDESC ="测试医院12" },};//每家医院对应的指标的值var quotaValuePairy = new Dictionary<string, object>();// 总数据源，一般来说是从数据库联表中查询的数据，这边是声明的测试数据List<HospitalViewCodeDetailTest> resultList = new List<HospitalViewCodeDetailTest>(){// 测试医院1的数据new HospitalViewCodeDetailTest { Code = "1.01.01", CodeName = "指标1",HospitalCode ="1",HospitalName ="测试医院1" ,QValue = "11.8"},new HospitalViewCodeDetailTest { Code = "1.01.02", CodeName = "指标2",HospitalCode ="1",HospitalName ="测试医院2" ,QValue = "12.8"},// 测试医院2的数据new HospitalViewCodeDetailTest { Code = "1.01.01", CodeName = "指标1",HospitalCode ="2",HospitalName ="测试医院1" ,QValue = "22.6"},new HospitalViewCodeDetailTest { Code = "1.01.02", CodeName = "指标2",HospitalCode ="2",HospitalName ="测试医院1" ,QValue = "23.2"} ,// 测试医院3的数据new HospitalViewCodeDetailTest { Code = "1.01.01", CodeName = "指标1",HospitalCode ="3",HospitalName ="测试医院3" ,QValue = "65.8"} ,new HospitalViewCodeDetailTest { Code = "1.01.02", CodeName = "指标2",HospitalCode ="3",HospitalName ="测试医院4" ,QValue = "25.1"}};// 填充对应的指标值for (int i = 0; i < hospitalList.Count; i++){quotaValuePairy = new Dictionary<string, object>(); // 在每次迭代中创建新的字典对象var hospitalCodeDetails = resultList.Where(x => x.HospitalCode == hospitalList[i].FJGDM).Select(x => new{x.Code,x.QValue}).ToList();quotaValuePairy["医院名称"] = hospitalList[i].FDESC;for (int o = 0; o < quotaArray.Count; o++){if (quotaValuePairy.ContainsKey(quotaArray[o].Q_CODE)) continue;quotaValuePairy[quotaArray[o].Q_CODE] = hospitalCodeDetails.FirstOrDefault(x => x.Code == quotaArray[o].Q_CODE)?.QValue; //指标值}quotaAssemble.Add(quotaValuePairy);}#endregion#region 导出excelif (quotaAssemble.Count > 0){Log.Information("正在导出......");// 读取json文件中的自定义保存路径// App.GetConfig官网介绍地址：http://furion.baiqian.ltd/docs/global/app?_highlight=getconfig#12-%E8%8E%B7%E5%8F%96%E9%85%8D%E7%BD%AE%E5%AF%B9%E8%B1%A1//可以改成自己地址string savePath = $"{App.GetConfig<string>("GenerateExcelOfHospitalFillingJobConfig:SavePath")}\\DownLoadExcel\\";if (!Directory.Exists(savePath)){Directory.CreateDirectory(savePath);}string filename = $"{DateTime.Now:yyyyMMddHHmmss}.xlsx";var absoluteFilePath = Path.Combine(savePath, filename);// 保存MiniExcel.SaveAs(absoluteFilePath, quotaAssemble.ToArray(),configuration: config);Log.Information($"{filename}导出成功！");}#endregion}catch (Exception ex){// 异常处理逻辑Log.Error($"发生异常: {ex.Message}");}}/// <summary>/// 设置行宽/// </summary>/// <returns></returns>private DynamicExcelColumn[] CreateDynamicColumns(List<string> dwQuota){var dynamicColumns = new List<DynamicExcelColumn>{new DynamicExcelColumn("医院名称") { Index = 0, Width = 30 }};dynamicColumns.AddRange(dwQuota.Select((codeTitle, codeIndex) =>{if (string.IsNullOrEmpty(codeTitle)){// 处理空值的情况，例如使用默认列名或跳过该列return null; // 返回 null 或者其他处理方式}else{return new DynamicExcelColumn(codeTitle) { Index = codeIndex + 1, Width = 25 };}}).Where(c => c != null).ToArray());return dynamicColumns.ToArray();}}/// <summary>/// 指标实体/// </summary>public class DW_QUOTATEST{/// <summary>/// 主键guid/// </summary>[SugarColumn(ColumnDescription = "主键id", Length = 32, IsPrimaryKey = true)]public string Q_ID { get; set; }/// <summary>/// 编码/// </summary>public string Q_CODE { get; set; }/// <summary>/// 指标名称/// </summary>public string Q_NAME { get; set; }}/// <summary>/// 医院实体/// </summary>public class TDU_HOSPITALTEST{public string FJGDM { get; set; }public string FSEQ { get; set; }public string FDESC { get; set; }}public class HospitalViewCodeDetailTest{/// <summary>/// 医院编码/// </summary>public string HospitalCode { get; set; }/// <summary>/// 医院名称/// </summary>public string HospitalName { get; set; }/// <summary>/// 指标编码/// </summary>public string Code { get; set; }/// <summary>/// 指标名称/// </summary>public string CodeName { get; set; }/// <summary>/// QValue指标值/// </summary>public string QValue { get; set; }}
}