基于MiniExcel的三种常用导出Excel方法(固定列导出、动态列导出、按模板导出)

为了方便代码编写和测试，把很多代码都放在一个class里面，实际开发根据需要放到对应的目录下即可。

1.使用nuget下载安装miniexcel；

2.编写对应的测试接口，具体代码如下:

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.IO;
using System.Threading.Tasks;
using System;
using AutoMapper;
using MiniExcelLibs.Attributes;
using MiniExcelLibs;
using MiniExcelLibs.OpenXml;
using System.Linq;namespace YY.Webapi.Controllers
{/// <summary>/// miniexcel测试/// </summary>[Route("api/[controller]")][ApiController][Produces("application/json")][AllowAnonymous]public class MiniExcelController : ControllerBase{private readonly IMapper _mapper;public MiniExcelController(IMapper mapper){_mapper = mapper;}/// <summary>/// 固定列导出/// </summary>/// <param name="input"></param>/// <returns></returns>/// <exception cref="Exception"></exception>[HttpPost("Export")]public async Task<IActionResult> Export(){try{var models = new Custome().GetProducts();var exportDtos = _mapper.Map<List<CustomeExportDto>>(models);var memoryStream = new MemoryStream();memoryStream.SaveAs(exportDtos);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){FileDownloadName = $"固定列报表导出-{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"};}catch (Exception ex){throw new Exception($"固定列报表导出出现错误:{ex.Message}");}}/// <summary>/// 动态列导出(指定列导出)/// </summary>/// <param name="columnParams"></param>/// <returns></returns>/// <exception cref="Exception"></exception>[HttpPost("ExportByAssignColumn")][AllowAnonymous]public async Task<IActionResult> ExportByAssignColumn(List<CustomeParam> columnParams){try{if (columnParams == null || !columnParams.Any()) throw new Exception("请选择需要导出的列!");var dtos = new Custome().GetProducts();#region 配置var config = new OpenXmlConfiguration { };List<DynamicExcelColumn> objs = new List<DynamicExcelColumn>();int index = 0;foreach (var columnParam in columnParams){objs.Add(new DynamicExcelColumn(columnParam.ColumnDisplayName) { Index = index++, Width = columnParam.ColumnWidth });}config.DynamicColumns = objs.ToArray();#endregion#region 获取值var values = new List<Dictionary<string, object>>();foreach (var dto in dtos){var dic = new Dictionary<string, object>();foreach (var columnParam in columnParams){dic.Add(columnParam.ColumnDisplayName, GetModelValue(columnParam.ColumnName, dto));}values.Add(dic);}#endregionvar memoryStream = new MemoryStream();memoryStream.SaveAs(values, configuration: config);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){FileDownloadName = $"动态列报表导出-{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"};}catch (Exception ex){throw new Exception($"动态列报表导出错误:{ex.Message}");}}/// <summary>/// 按模板导出/// </summary>/// <param name="id"></param>/// <returns></returns>/// <exception cref="Exception"></exception>[HttpPost("{id}/ExportByTemplate")]public async Task<IActionResult> ExportByTemplate([FromRoute] int id){try{var entity = new Custome() { Id = 1, Code = "Code", Name = "Test", Price = 12, CreateTime = DateTime.Now };string templatePath = $@"C:\Users\Administrator\Desktop\报表模板.xlsx";var value = new{Code = entity.Code,Name = entity.Name};byte[] bytes = System.IO.File.ReadAllBytes(templatePath);var memoryStream = new MemoryStream();await memoryStream.SaveAsByTemplateAsync(bytes, value);memoryStream.Seek(0, SeekOrigin.Begin);return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"){FileDownloadName = $"按模板报表导出-{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx"};}catch (Exception ex){throw new Exception($"按模板报表导出错误:{ex.Message}");}}#region 私有方法/// <summary>/// 根据字段名获取对应的值/// </summary>/// <param name="fieldName"></param>/// <param name="obj"></param>/// <returns></returns>private string GetModelValue(string fieldName, object obj){try{object o = obj.GetType().GetProperty(fieldName).GetValue(obj, null);string Value = Convert.ToString(o);if (string.IsNullOrEmpty(Value)) return "";return Value;}catch{return "";}}#endregion}public class Custome{/// <summary>/// 产品Id/// </summary>public int Id { get; set; }/// <summary>/// 产品编码/// </summary>public string Code { get; set; }/// <summary>/// 产品名称/// </summary>public string Name { get; set; }/// <summary>/// 价格/// </summary>public int Price { get; set; }/// <summary>/// 创建时间/// </summary>public DateTime CreateTime { get; set; }public List<Custome> GetProducts(){var products = new List<Custome>();for (int i = 0; i < 1000; i++){products.Add(new Custome{Id = i + 1,Code = $"Code-{(i + 1).ToString()}",Name = $"Name-{(i + 1).ToString()}",Price = Random.Shared.Next(10, 100),CreateTime = DateTime.Now});}return products;}}public class CustomeExportDto{/// <summary>/// 产品编码/// </summary>[ExcelColumn(Name = "产品编码", Width = 12)]public string Code { get; set; }/// <summary>/// 产品名称/// </summary>[ExcelColumn(Name = "产品名称", Width = 12)]public string Name { get; set; }/// <summary>/// 价格/// </summary>[ExcelColumn(Name = "价格", Width = 12)]public int Price { get; set; }/// <summary>/// 创建时间/// </summary>[ExcelColumn(Name = "创建时间", Width = 12, Format = "yyyy-MM-dd HH:mm:ss")]public DateTime CreateTime { get; set; }}public class CustomeParam{/// <summary>/// 列名/// </summary>public string ColumnName { get; set; }/// <summary>/// 列显示名/// </summary>public string ColumnDisplayName { get; set; }/// <summary>/// 列宽/// </summary>public double ColumnWidth { get; set; }}
}

3.固定列和按模板导出Excel都比较常规，，其中的动态列导出是根据前端传进来的参数进行选择性的动态列导出,swagger测试效果如下:

导出的文件如下: