AGEHAB.RelatorioSeadIpasgo/Infrastructure/Excel/ExcelRelatorioWriter.cs

using AGEHAB.RelatorioSeadIpasgo.Application.Abstractions;
using ClosedXML.Excel;

namespace AGEHAB.RelatorioSeadIpasgo.Infrastructure.Excel;

public class ExcelRelatorioWriter : IExcelRelatorioWriter
{
    public void Escrever(string tituloRelatorio, IReadOnlyList<dynamic> linhas, string caminhoArquivo, CancellationToken cancellationToken)
    {
        cancellationToken.ThrowIfCancellationRequested();
        using var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add(tituloRelatorio);

        var primeiraLinha = linhas.FirstOrDefault();
        if (primeiraLinha != null)
        {
            var dict = (IDictionary<string, object>)primeiraLinha;
            var colunas = dict.Keys.ToList();

            for (int i = 0; i < colunas.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = colunas[i];
                worksheet.Cell(1, i + 1).Style.Font.Bold = true;
                worksheet.Cell(1, i + 1).Style.Fill.BackgroundColor = XLColor.LightGray;
            }

            int row = 2;
            foreach (var linha in linhas)
            {
                cancellationToken.ThrowIfCancellationRequested();
                var linhaDict = (IDictionary<string, object>)linha;
                for (int i = 0; i < colunas.Count; i++)
                {
                    var valor = linhaDict[colunas[i]];
                    if (valor is decimal decimalValue)
                    {
                        worksheet.Cell(row, i + 1).Value = decimalValue;
                        worksheet.Cell(row, i + 1).Style.NumberFormat.Format = "#,##0.00";
                    }
                    else if (valor is int intValue)
                    {
                        worksheet.Cell(row, i + 1).Value = intValue;
                    }
                    else
                    {
                        worksheet.Cell(row, i + 1).Value = valor?.ToString() ?? "";
                    }
                }

                row++;
            }
        }

        worksheet.Columns().AdjustToContents();
        workbook.SaveAs(caminhoArquivo);
    }
}