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

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

excel_1">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);
                }
                #endregion

                var 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; }
    }
}