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

本文主要是介绍基于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; }}
}