feixian_weifajianguan/Infrastructure/Utilities/Excel/ExcelExporter.cs

using System.Reflection;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace Infrastructure.Utilities.Excel;

public class ExcelExporter
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="data"></param>
    /// <param name="headerTitle"></param>
    /// <exception cref="ArgumentException"></exception>
    public static Response<MemoryStream> ExportToExcel<T>(IEnumerable<T> data, string headerTitle) where T : class
    {
        var workbook = new XSSFWorkbook();
        var sheet = workbook.CreateSheet("Sheet1");

        // 获取实体类的属性信息，并筛选出标记了ExcelColumnAttribute的特性
        PropertyInfo[] properties = typeof(T).GetProperties()
            .Where(p => p.GetCustomAttribute<ExcelColumnAttribute>() != null)
            .ToArray();

        var rowIndex = 0;
        if (!string.IsNullOrEmpty(headerTitle))
        {
            var headerTitleRow = sheet.CreateRow(rowIndex++);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
            var cell = headerTitleRow.CreateCell(0);
            cell.SetCellValue(headerTitle);
            var style = workbook.CreateCellStyle();
            var font = workbook.CreateFont();
            font.FontName = "方正小标宋简体";
            font.FontHeightInPoints = 36;
            font.IsBold = true;
            style.SetFont(font);
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;
            style.WrapText = true;
            cell.CellStyle = style;
        }

        // 写入表头
        var headerRow = sheet.CreateRow(rowIndex++);
        for (var i = 0; i < properties.Length; i++)
        {
            // 自动调节列宽
            sheet.AutoSizeColumn(i);
            var cell = headerRow.CreateCell(i);
            cell.SetCellValue(properties[i].GetCustomAttribute<ExcelColumnAttribute>()?.HeaderName);
            // 应用表头样式
            var headerStyleAttr = properties[i].GetCustomAttribute<ExcelHeaderStyleAttribute>();
            var style = workbook.CreateCellStyle();
            if (headerStyleAttr != null)
            {
                var font = workbook.CreateFont();
                font.FontName = headerStyleAttr.FontName;
                font.FontHeightInPoints = (short)headerStyleAttr.FontSize;
                font.IsBold = true;
                style.SetFont(font);
                // 填充模式
                style.FillPattern = FillPattern.SolidForeground;
                style.WrapText = true;
                // 设置边框样式
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
            }
            else
            {
                var font = workbook.CreateFont();
                font.FontName = "Microsoft YaHei";
                font.FontHeightInPoints = 12f;
                font.IsBold = true;
                style.SetFont(font);
                // 填充模式
                style.FillPattern = FillPattern.SolidForeground;
                style.WrapText = true;
                // 设置边框样式
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderTop = BorderStyle.Thin;
                style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                style.VerticalAlignment = VerticalAlignment.Center;
            }
            cell.CellStyle = style;
        }

        foreach (var item in data)
        {
            var row = sheet.CreateRow(rowIndex++);
            for (var i = 0; i < properties.Length; i++)
            {
                var cell = row.CreateCell(i);
                cell.SetCellValue(properties[i].GetValue(item)?.ToString());
            }
        }

        var response = new Response<MemoryStream>
        {
            Result = new MemoryStream()
        };
        // MemoryStream中写入数据
        workbook.Write(response.Result);
        workbook.Close();
        response.Result.Close();
        response.Result.Dispose();
        response.Code = 200;
        response.Message = "获取成功";
        return response;
    }
}