YBEE.EQM/YBEE.EQM.Application/ExportExcel/Services/ExportExcelService.cs

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace YBEE.EQM.Application;

/// <summary>
/// EXCEL导出服务
/// </summary>
public class ExportExcelService : IExportExcelService, ISingleton
{
    /// <summary>
    /// 导出 Excel 文件
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="input"></param>
    /// <returns></returns>
    public byte[] ExportExcel<T>(ExportExcelDto<T> input)
    {
        IWorkbook wb = new XSSFWorkbook();
        ISheet sheet = wb.CreateSheet();
        sheet.DisplayGridlines = false;
        input.SetSheet?.Invoke(sheet);

        var cellStyle = GetCellStyle(wb, input.FontSize, input.TitleFontSize);

        // 行索引号
        int rowNum = 0;

        #region 标题
        IRow titleRow = sheet.CreateRow(rowNum++);
        titleRow.Height = input.TitleHeight ?? ExportExcelCellStyle.DefaultTitleHeight;
        AddCell(input.Title, titleRow, 0, cellStyle.TitleStyle, sheet);
        sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, input.Columns.Count - 1));
        #endregion

        #region 摘要
        int freezeRowCount = 1;
        if (input.BuildSummary == null)
        {
            string summary = "";
            if (input.IncludeExportTime) { summary = $"导出时间：{DateTime.Now:yyyy-MM-dd HH:mm:ss}    "; };
            if (!(string.IsNullOrEmpty(input.Summary) || string.IsNullOrWhiteSpace(input.Summary))) { summary = $"{summary}{input.Summary}"; }
            if (summary != "")
            {
                IRow summaryRow = sheet.CreateRow(rowNum++);
                summaryRow.Height = ExportExcelCellStyle.DefaultRowHeight;
                AddCell(summary, summaryRow, 0, cellStyle.SummaryStyle, sheet);
                sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, input.Columns.Count - 1));
                freezeRowCount++;
            }
        }
        else
        {
            int rc = input.BuildSummary(sheet, cellStyle, rowNum);
            freezeRowCount += rc;
            rowNum += rc;
        }
        #endregion

        #region 列头
        IRow headerRow = sheet.CreateRow(rowNum++);
        headerRow.Height = input.HeaderHeight;
        for (int i = 0; i < input.Columns.Count; i++)
        {
            var col = input.Columns[i];
            AddCell(col.Name, headerRow, i, cellStyle.ColumnHeaderStyle, sheet, col.Width);
        }
        sheet.CreateFreezePane(0, ++freezeRowCount);
        #endregion

        #region 数据
        foreach (var item in input.Items)
        {
            IRow row = sheet.CreateRow(rowNum++);
            row.Height = input.RowHeight ?? ExportExcelCellStyle.DefaultRowHeight;
            for (int i = 0; i < input.Columns.Count; i++)
            {
                var col = input.Columns[i];
                ICellStyle cstyle = cellStyle.CenterCellStyle;
                switch (col.Align)
                {
                    case ExportExcelCellAlign.LEFT:
                        cstyle = cellStyle.LeftCellStyle;
                        break;
                    case ExportExcelCellAlign.RIGHT:
                        cstyle = cellStyle.RightCellStyle;
                        break;
                }
                AddCell(col.GetCellValue(item), row, i, cstyle, sheet, col.Width);
            }
        }
        #endregion

        MemoryStream ms = new();
        wb.Write(ms, false);
        ms.Flush();
        return ms.ToArray();
    }


    #region 静态方法
    /// <summary>
    /// 获取单元格表头和单元格样式
    /// </summary>
    /// <param name="wb"></param>
    /// <param name="fontSize">正文字体大小</param>
    /// <param name="titleFontSize">标题字体大小</param>
    /// <returns></returns>
    public ExportExcelCellStyle GetCellStyle(IWorkbook wb, double fontSize = 10, double titleFontSize = 16)
    {
        ExportExcelCellStyle cellStyle = new()
        {
            TitleStyle = wb.CreateCellStyle(),
            SummaryStyle = wb.CreateCellStyle(),
            ColumnHeaderStyle = wb.CreateCellStyle(),
            CenterCellStyle = wb.CreateCellStyle(),
            LeftCellStyle = wb.CreateCellStyle(),
            RightCellStyle = wb.CreateCellStyle(),
            FillCellStyle = wb.CreateCellStyle(),
        };

        // 标题样式
        cellStyle.TitleStyle.Alignment = HorizontalAlignment.Center;
        cellStyle.TitleStyle.VerticalAlignment = VerticalAlignment.Center;
        IFont titleFont = wb.CreateFont();
        titleFont.IsBold = true;
        titleFont.FontName = cellStyle.TitleFontName;
        titleFont.FontHeightInPoints = titleFontSize;
        cellStyle.TitleStyle.SetFont(titleFont);

        // 摘要样式
        cellStyle.SummaryStyle.Alignment = HorizontalAlignment.Left;
        cellStyle.SummaryStyle.VerticalAlignment = VerticalAlignment.Center;
        IFont summaryFont = wb.CreateFont();
        summaryFont.FontName = cellStyle.FontName;
        summaryFont.FontHeightInPoints = fontSize;
        cellStyle.SummaryStyle.SetFont(summaryFont);

        // 列头样式
        cellStyle.ColumnHeaderStyle = wb.CreateCellStyle();
        cellStyle.ColumnHeaderStyle.Alignment = HorizontalAlignment.Center;
        cellStyle.ColumnHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
        cellStyle.ColumnHeaderStyle.BorderTop = BorderStyle.Thin;
        cellStyle.ColumnHeaderStyle.BorderLeft = BorderStyle.Thin;
        cellStyle.ColumnHeaderStyle.BorderRight = BorderStyle.Thin;
        cellStyle.ColumnHeaderStyle.BorderBottom = BorderStyle.Thin;
        cellStyle.ColumnHeaderStyle.WrapText = true;
        IFont headerFont = wb.CreateFont();
        headerFont.IsBold = true;
        headerFont.FontName = cellStyle.TitleFontName;
        headerFont.FontHeightInPoints = fontSize;
        cellStyle.ColumnHeaderStyle.SetFont(headerFont);

        // 默认居中样式
        cellStyle.CenterCellStyle = wb.CreateCellStyle();
        cellStyle.CenterCellStyle.Alignment = HorizontalAlignment.Center;
        cellStyle.CenterCellStyle.VerticalAlignment = VerticalAlignment.Center;
        cellStyle.CenterCellStyle.BorderTop = BorderStyle.Thin;
        cellStyle.CenterCellStyle.BorderLeft = BorderStyle.Thin;
        cellStyle.CenterCellStyle.BorderRight = BorderStyle.Thin;
        cellStyle.CenterCellStyle.BorderBottom = BorderStyle.Thin;
        cellStyle.CenterCellStyle.WrapText = true;
        IFont cellFont = wb.CreateFont();
        cellFont.FontName = cellStyle.FontName;
        cellFont.FontHeightInPoints = fontSize;
        cellStyle.CenterCellStyle.SetFont(cellFont);

        // 居左样式
        cellStyle.LeftCellStyle = wb.CreateCellStyle();
        cellStyle.LeftCellStyle.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.LeftCellStyle.Alignment = HorizontalAlignment.Left;

        // 居右样式
        cellStyle.RightCellStyle = wb.CreateCellStyle();
        cellStyle.RightCellStyle.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.RightCellStyle.Alignment = HorizontalAlignment.Right;

        // 背景样式
        cellStyle.FillCellStyle = wb.CreateCellStyle();
        cellStyle.FillCellStyle.CloneStyleFrom(cellStyle.CenterCellStyle);
        if (wb.SpreadsheetVersion.DefaultExtension == "xls")
        {
            HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
            palette.SetColorAtIndex(48, 245, 245, 245);
            cellStyle.FillCellStyle.FillForegroundColor = palette.FindColor(245, 245, 245).Indexed;
        }
        else
        {
            cellStyle.FillCellStyle.FillForegroundColor = 0;
            cellStyle.FillCellStyle.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)cellStyle.FillCellStyle.FillForegroundColorColor).SetRgb(new byte[] { 245, 245, 245 });
        }

        return cellStyle;
    }

    /// <summary>
    /// 添加单元格
    /// </summary>
    /// <param name="value"></param>
    /// <param name="row"></param>
    /// <param name="columnIndex"></param>
    /// <param name="cellStyle"></param>
    /// <param name="sheet"></param>
    /// <param name="width"></param>
    public void AddCell(object value, IRow row, int columnIndex, ICellStyle cellStyle, ISheet sheet = null, int? width = 8)
    {
        ICell cell = row.CreateCell(columnIndex);
        cell.CellStyle = cellStyle;
        if (value != null)
        {
            var valueTypeName = value.GetType().Name;
            if (valueTypeName.Contains("Int") || valueTypeName == "Double" || valueTypeName == "Decimal")
            {
                cell.SetCellValue(Convert.ToDouble(value));
            }
            else if (valueTypeName == "String")
            {
                cell.SetCellValue(value.ToString());
            }
            else if (valueTypeName == "Boolean")
            {
                cell.SetCellValue((bool)value);
            }
        }

        if (sheet != null && width.HasValue)
        {
            sheet.SetColumnWidth(columnIndex, width.Value * 256);
        }
    }
    #endregion
}