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;
        if (input.IsXlsx)
        {
            wb = new XSSFWorkbook();
        }
        else
        {
            wb = new HSSFWorkbook();
        }
        ISheet sheet = wb.CreateSheet(input.SheetName);
        sheet.DisplayGridlines = false;
        input.SetSheet?.Invoke(sheet);

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

        // 行索引号
        int rowNum = 0;
        int freezeRowCount = 0;

        #region 标题
        if (!(string.IsNullOrEmpty(input.Title?.Trim()) || string.IsNullOrWhiteSpace(input.Title?.Trim())))
        {
            IRow titleRow = sheet.CreateRow(rowNum++);
            titleRow.Height = input.TitleHeight ?? ExportExcelCellStyle.DefaultTitleHeight;
            AddCell(input.Title.Trim(), titleRow, 0, cellStyle.TitleStyle, sheet);
            sheet.AddMergedRegion(new CellRangeAddress(rowNum - 1, rowNum - 1, 0, input.Columns.Count - 1));
            freezeRowCount++;
        }
        #endregion

        #region 摘要
        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 EXCEL公共方法
    /// <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)
    {
        IDataFormat dataformat = wb.CreateDataFormat();

        ExportExcelCellStyle cellStyle = new()
        {
            DataFormat = dataformat,
            TitleStyle = wb.CreateCellStyle(),
            SummaryStyle = wb.CreateCellStyle(),
            ColumnHeaderStyle = wb.CreateCellStyle(),
            ColumnFillHeaderStyle = wb.CreateCellStyle(),
            CenterCellStyle = wb.CreateCellStyle(),
            LeftCellStyle = wb.CreateCellStyle(),
            RightCellStyle = wb.CreateCellStyle(),
            FillCellStyle = wb.CreateCellStyle(),
            PercentCellStyleP2 = wb.CreateCellStyle(),
            NumberCellStyleP2 = wb.CreateCellStyle(),
            NumberCellStyleP4 = wb.CreateCellStyle(),
        };

        // 标题样式
        cellStyle.TitleStyle.WrapText = true;
        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.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.ColumnFillHeaderStyle.CloneStyleFrom(cellStyle.ColumnHeaderStyle);
        if (wb.SpreadsheetVersion.DefaultExtension == "xls")
        {
            HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
            palette.SetColorAtIndex(48, 237, 237, 237);
            cellStyle.ColumnFillHeaderStyle.FillForegroundColor = palette.FindColor(237, 237, 237).Indexed;
        }
        else
        {
            cellStyle.ColumnFillHeaderStyle.FillForegroundColor = 0;
            cellStyle.ColumnFillHeaderStyle.FillPattern = FillPattern.SolidForeground;
            ((XSSFColor)cellStyle.ColumnFillHeaderStyle.FillForegroundColorColor).SetRgb(new byte[] { 237, 237, 237 });
        }


        // 默认居中样式
        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.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.LeftCellStyle.Alignment = HorizontalAlignment.Left;

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

        // 背景样式
        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 });
        }

        cellStyle.PercentCellStyleP2.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.PercentCellStyleP2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

        cellStyle.NumberCellStyleP2.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.NumberCellStyleP2.DataFormat = dataformat.GetFormat("0.00");

        cellStyle.NumberCellStyleP4.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.NumberCellStyleP4.DataFormat = dataformat.GetFormat("0.0000");

        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>
    /// <param name="zeroToBlank">0转为空白</param>
    /// <param name="cellType"></param>
    public ICell AddCell(object value, IRow row, int columnIndex, ICellStyle cellStyle, ISheet sheet = null, int? width = null, bool? zeroToBlank = false, CellType? cellType = null)
    {
        bool isSetBlank = false;
        if (zeroToBlank == true)
        {
            if (double.TryParse(value.ToString(), out double v))
            {
                isSetBlank = v == 0;
            }
        }

        ICell cell = row.CreateCell(columnIndex);
        cell.CellStyle = cellStyle;
        if (value != null && isSetBlank == false)
        {
            var valueTypeName = value.GetType().Name;
            if (cellType.HasValue)
            {
                cell.SetCellType(cellType.Value);
            }
            if (cellType.HasValue && cellType == CellType.Formula)
            {
                cell.CellFormula = value.ToString();
            }
            else 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);
        }

        return cell;
    }
    #endregion
}