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

using NPOI.HSSF.UserModel;
using NPOI.OpenXmlFormats.Dml.Chart;
using NPOI.SS.UserModel;
using NPOI.SS.UserModel.Charts;
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++);
                if (!input.NotSetRowHeight)
                {
                    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++);
        if (!input.NotSetRowHeight)
        {
            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++);
            if (!input.NotSetRowHeight)
            {
                row.Height = input.RowHeight ?? ExportExcelCellStyle.DefaultRowHeight;
            }
            for (int i = 0; i < input.Columns.Count; i++)
            {
                var col = input.Columns[i];
                ICellStyle cstyle = col.WrapText ? cellStyle.CenterWrapCellStyle : cellStyle.CenterCellStyle;
                switch (col.Align)
                {
                    case ExportExcelCellAlign.LEFT:
                        cstyle = col.WrapText ? cellStyle.LeftWrapCellStyle : cellStyle.LeftCellStyle;
                        break;
                    case ExportExcelCellAlign.RIGHT:
                        cstyle = col.WrapText ? cellStyle.RightWrapCellStyle : 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(),
            CenterWrapCellStyle = wb.CreateCellStyle(),
            LeftWrapCellStyle = wb.CreateCellStyle(),
            RightWrapCellStyle = 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 });
        }

        #region 内容样式
        // 居中样式
        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;
        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;
        #endregion

        #region 自动换行
        // 居中样式（自动换行）
        cellStyle.CenterWrapCellStyle.CloneStyleFrom(cellStyle.CenterCellStyle);
        cellStyle.CenterWrapCellStyle.WrapText = true;
        // 居左样式（自动换行）
        cellStyle.LeftWrapCellStyle.CloneStyleFrom(cellStyle.LeftCellStyle);
        cellStyle.LeftWrapCellStyle.WrapText = true;
        // 居右样式（自动换行）
        cellStyle.RightWrapCellStyle.CloneStyleFrom(cellStyle.RightCellStyle);
        cellStyle.RightWrapCellStyle.WrapText = true;
        #endregion

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

    #region 图表
    /// <summary>
    /// 导出柱状图
    /// </summary>
    /// <param name="sheet"></param>
    /// <param name="drawing"></param>
    /// <param name="anchor"></param>
    /// <param name="startDataRow"></param>
    /// <param name="endDataRow"></param>
    /// <param name="columnIndex"></param>
    /// <param name="title"></param>
    /// <param name="serieTitle"></param>
    /// <param name="catalogTitle"></param>
    /// <param name="valueTile"></param>
    public void CreateBarChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, int startDataRow, int endDataRow, int columnIndex, string title = null, string serieTitle = null, string catalogTitle = null, string valueTile = null)
    {
        XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
        if (!string.IsNullOrEmpty(title))
        {
            chart.SetTitle(title);
            chart.GetCTChart().title.tx.rich.p[0].pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties
            {
                defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 1400 }
            };
        }

        IBarChartData<string, double> barChartData = chart.ChartDataFactory.CreateBarChartData<string, double>();
        IChartLegend legend = chart.GetOrCreateLegend();
        legend.Position = LegendPosition.TopRight;
        legend.IsOverlay = true;

        IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
        bottomAxis.MajorTickMark = AxisTickMark.None;
        IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
        leftAxis.Crosses = AxisCrosses.AutoZero;
        leftAxis.SetCrossBetween(AxisCrossBetween.Between);

        IChartDataSource<string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0));
        IChartDataSource<double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex));
        var serie = barChartData.AddSeries(categoryAxis, valueAxis);
        if (!string.IsNullOrEmpty(serieTitle))
        {
            serie.SetTitle(serieTitle);
        }

        chart.Plot(barChartData, bottomAxis, leftAxis);

        var plotArea = chart.GetCTChart().plotArea;
        plotArea.catAx[0].txPr = new CT_TextBody();
        plotArea.catAx[0].txPr.AddNewP().pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties()
        {
            defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 900 }
        };
        plotArea.catAx[0].majorTickMark = new CT_TickMark() { val = ST_TickMark.@out };
        plotArea.valAx[0].txPr = new CT_TextBody();
        plotArea.valAx[0].txPr.AddNewP().pPr = new NPOI.OpenXmlFormats.Dml.CT_TextParagraphProperties()
        {
            defRPr = new NPOI.OpenXmlFormats.Dml.CT_TextCharacterProperties() { sz = 900 }
        };
        plotArea.valAx[0].majorTickMark = new CT_TickMark() { val = ST_TickMark.@out };

        var barChart = plotArea.barChart.First();
        barChart.barDir = new CT_BarDir { val = ST_BarDir.col };

        if (!string.IsNullOrEmpty(catalogTitle))
        {
            var aTitle = new CT_Title
            {
                tx = new CT_Tx()
            };
            aTitle.tx.rich = new CT_TextBody();
            aTitle.tx.rich.AddNewP().AddNewR().t = catalogTitle;
            plotArea.valAx[0].title = aTitle;
        }
        if (!string.IsNullOrEmpty(valueTile))
        {
            var aTitle = new CT_Title
            {
                tx = new CT_Tx()
            };
            aTitle.tx.rich = new CT_TextBody();
            aTitle.tx.rich.AddNewP().AddNewR().t = valueTile;
            plotArea.catAx[0].title = aTitle;
        }
    }
    #endregion
}