ASP.NET 8 访问控制器导出Excel附件

1、安装NPOI

NuGet\Install-Package NPOI -Version 2.7.0

2、准备Excel操作相关类

新建类库项目，添加项目被引用，新建文件夹Exceloperation，添加3个类文件

TitleAttribute.cs

[AttributeUsage(AttributeTargets.Property)]
public class TitleAttribute : Attribute
{
    public string? Titile;

    public HSSFCellStyle? CellType;
}

ExcelDataResource.cs

public class ExcelDataResource
{
    /// <summary>
    /// 页签名称
    /// </summary>
    public string? SheetName { get; set; }
    /// <summary>
    /// 标题所在行
    /// </summary>
    public int TitleIndex { get; set; }

    /// <summary>
    /// 每一行的数据
    /// </summary>
    public List<object>? SheetDataResource { get; set; }
}


public class UserInfo
{
    [Title(Titile = "用户Id")]
    public int UserId { get; set; }

    [Title(Titile = "用户名称")]
    public string? UserName { get; set; }

    [Title(Titile = "用户年龄")]
    public int Age { get; set; }

    [Title(Titile = "用户类型")]
    public int UserType { get; set; }

    [Title(Titile = "描述")]
    public string? Description { get; set; }
}

public class ClassInfo
{
    [Title(Titile = "学生Id")]
    public int UserId { get; set; }


    [Title(Titile = "学生名称")]
    public string? UserName { get; set; }

    [Title(Titile = "学生年龄")]
    public int Age { get; set; }

    [Title(Titile = "学生类型")]
    public int UserType { get; set; }


    [Title(Titile = "学生描述1")]
    public string? Description1 { get; set; }

    [Title(Titile = "学生描述2")]
    public string? Description2 { get; set; }

    [Title(Titile = "学生描述3")]
    public string? Description3 { get; set; }

    [Title(Titile = "学生描述4")]
    public string? Description4 { get; set; }
}

ExcelOperationHelper.cs

public class ExcelOperationHelper
{
    /// <summary>
    /// 创建一个ExcelWorkbook
    /// </summary>
    /// <returns></returns>
    public static IWorkbook CreateExcelWorkbook()
    {
        HSSFWorkbook _Workbook = new HSSFWorkbook();
        ISheet sheet = _Workbook.CreateSheet("Sheet1");
        IRow titleRow = sheet.CreateRow(0);
        ICell cell = titleRow.CreateCell(1);
        cell.SetCellValue("你好");
        return _Workbook;
    }

    /// <summary>
    /// 导出
    /// </summary>
    public static IWorkbook DataToHSSFWorkbook(List<ExcelDataResource>? dataResources)
    {
        HSSFWorkbook _Workbook = new HSSFWorkbook();
        if (dataResources == null && dataResources?.Count == 0)
        {
            return _Workbook;
        }
        foreach (var sheetResource in dataResources)
        {
            if (sheetResource.SheetDataResource != null && sheetResource.SheetDataResource.Count == 0)
            {
                break;
            }
            ISheet sheet = _Workbook.CreateSheet(sheetResource.SheetName);
            object obj = sheetResource.SheetDataResource[0];

            Type type = obj.GetType();
            List<PropertyInfo> propList = type.GetProperties().Where(c => c.IsDefined(typeof(TitleAttribute), true)).ToList();

            IRow titleRow = sheet.CreateRow(0);

            ICellStyle style = _Workbook.CreateCellStyle();
            style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            style.FillPattern = FillPattern.SolidForeground;
            style.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Automatic.Index;

            style.Alignment = HorizontalAlignment.CenterSelection;
            style.VerticalAlignment = VerticalAlignment.Center;
            titleRow.Height = 100 * 4;

            for (int i = 0; i < propList.Count(); i++)
            {
                TitleAttribute propertyAttribute = propList[i].GetCustomAttribute<TitleAttribute>();
                ICell cell = titleRow.CreateCell(i);
                cell.SetCellValue(propertyAttribute.Titile);
                cell.CellStyle = style;
            }

            for (int i = 0; i < sheetResource.SheetDataResource.Count(); i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                object objInstance = sheetResource.SheetDataResource[i];
                for (int j = 0; j < propList.Count; j++)
                {
                    ICell cell = row.CreateCell(j);
                    cell.SetCellValue(propList[j].GetValue(objInstance).ToString());
                }
            }
        }
        return _Workbook;
    }

    /// <summary>
    /// 生成Excel的内存流-MemoryStream
    /// </summary>
    /// <param name="dataResources"></param>
    /// <returns></returns>
    public static MemoryStream ToExcelMemoryStream(List<ExcelDataResource> dataResources)
    {
        IWorkbook _Workbook = DataToHSSFWorkbook(dataResources);
        using (MemoryStream stream = new MemoryStream())
        {
            _Workbook.Write(stream, true);
            return stream;
        }

    }

    /// <summary>
    ///通过数据生成Excel  然后转换成byte[]
    /// </summary>
    /// <param name="dataResources"></param>
    /// <returns></returns>
    public static byte[] ToExcelByteArray(List<ExcelDataResource> dataResources)
    {
        IWorkbook _Workbook = DataToHSSFWorkbook(dataResources);
        using (MemoryStream stream = new MemoryStream())
        {
            _Workbook.Write(stream, true);
            byte[] bt = stream.ToArray();
            stream.Write(bt, 0, (int)stream.Length);
            return bt;
        }
    }

    /// <summary>
    ///Excel转换成DataTable 
    /// </summary>
    /// <param name="hSSFWorkbook"></param>
    /// <returns></returns>
    public static List<DataTable> ToExcelDateTable(IWorkbook hSSFWorkbook)
    {
        List<DataTable> datatableList = new List<DataTable>();
        for (int sheetIndex = 0; sheetIndex < hSSFWorkbook.NumberOfSheets; sheetIndex++)
        {
            ISheet sheet = hSSFWorkbook.GetSheetAt(sheetIndex);
            //获取表头 FirstRowNum 第一行索引 0
            IRow header = sheet.GetRow(sheet.FirstRowNum);//获取第一行
            if (header == null)
            {
                break;
            }
            int startRow = 0;//数据的第一行索引

            DataTable dtNpoi = new DataTable();
            startRow = sheet.FirstRowNum + 1;
            for (int i = header.FirstCellNum; i < header.LastCellNum; i++)
            {
                ICell cell = header.GetCell(i);
                if (cell != null)
                {
                    string cellValue = $"Column{i + 1}_{cell.ToString()}";
                    if (cellValue != null)
                    {
                        DataColumn col = new DataColumn(cellValue);
                        dtNpoi.Columns.Add(col);
                    }
                    else
                    {
                        DataColumn col = new DataColumn();
                        dtNpoi.Columns.Add(col);
                    }
                }

            }
            //数据    LastRowNum 最后一行的索引 如第九行---索引 8
            for (int i = startRow; i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);//获取第i行
                if (row == null)
                {
                    continue;
                }
                DataRow dr = dtNpoi.NewRow();
                //遍历每行的单元格
                for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
                {
                    if (row.GetCell(j) != null)
                        dr[j] = row.GetCell(j).ToString();
                }
                dtNpoi.Rows.Add(dr);
            }

            datatableList.Add(dtNpoi);
        }
        return datatableList;
    }

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="stream"></param>
    /// <returns></returns>
    public static List<DataTable> ExcelStreamToDateTable(Stream stream)
    {
        IWorkbook hSSFWorkbook = WorkbookFactory.Create(stream);
        return ToExcelDateTable(hSSFWorkbook);
    }
}

3、Utility文件夹下新建ResultExt文件夹，新建类ExcelResult.cs

/// <summary>
/// 这个返回值类型，只能适用在mvc返回值，他就是专门用来返回Excel文件的
/// </summary>
public class ExcelResult : IActionResult
{
    private string _ExcelName;
    private List<ExcelDataResource> _ExcelDataResources;

    /// <summary>
    /// 如果没有时间就默认以当前时间为文件名称
    /// </summary>
    /// <param name="excelDataResources"></param>
    public ExcelResult(List<ExcelDataResource> excelDataResources) : this(DateTime.Now.ToString("yyyyMMddHHmmssffffff"), excelDataResources)
    {
    }

    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="excelName">文件名称</param>
    /// <param name="excelDataResources">数据源</param>
    public ExcelResult(string excelName, List<ExcelDataResource> excelDataResources)
    {
        _ExcelName = excelName;
        _ExcelDataResources = excelDataResources;
    }

    public async Task ExecuteResultAsync(ActionContext context)
    {
        //给定输出的结果，浏览器以什么方式来解析
        context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
        //设置响应的头信息
        context.HttpContext.Response.Headers.Add("Content-Disposition", $"attachment;filename={_ExcelName}.xls");

        //得到需要响应给浏览器的数据
        byte[] bt = ExcelOperationHelper.ToExcelByteArray(_ExcelDataResources);

        //各种不同类型的数据其实最终都是要写到这儿来；
        await context.HttpContext.Response.BodyWriter.WriteAsync(bt);
    }
}

4、Controller中获取数据返回数据

//扩展返回 
public IActionResult ReturnExcelFile()
{
    // 获取导出Excel需要的数据源
    List<ExcelDataResource> list = GetExcelSheetData(1);
    return new ExcelResult(list);  //调用IActionResult的扩展返回Excel
}

//新建数据的方式准备两个sheet的数据
private List<ExcelDataResource> GetExcelSheetData(int id)
{

    List<object> objlist = new List<object>();
    for (int i = 0; i < 100; i++)
    {
        objlist.Add(new UserInfo()
        {
            UserId = i + 1,
            UserName = $"名称-{i}",
            Age = i + i + 1,
            UserType = i + 1,
            Description = $"Description_{i}"
        });
    }
    List<object> Classobjlist = new List<object>();

    for (int i = 0; i < 200; i++)
    {
        Classobjlist.Add(new ClassInfo()
        {
            UserId = i + 1,
            UserName = $"名称-{i}",
            Age = i + i + 1,
            UserType = i + 1,
            Description1 = $"Description_{i}",
            Description2 = $"Description_{i}",
            Description3 = $"Description_{i}",
            Description4 = $"Description_{i}"
        });
    }
    List<ExcelDataResource> datalist = new List<ExcelDataResource>()
            {
                 new ExcelDataResource(){
                        SheetName="Sheet1",
                        TitleIndex=1,
                        SheetDataResource=objlist
                 },
                 new ExcelDataResource(){
                        SheetName="Sheet2",
                        TitleIndex=1,
                        SheetDataResource=Classobjlist
                 }
            };

    return datalist;
}