declaratrions-automatization/DeclarationAutomatization/Services/ExcelExportService.cs

using System.Collections.Generic;
using ClosedXML.Excel;
using DeclarationAutomatization.Models;

namespace DeclarationAutomatization.Services;

public class ExcelExportService
{
    public void Export(
        string outputPath,
        IEnumerable<DeclarationItem> declarationItems,
        IEnumerable<Sheet3Row> sheet3Rows)
    {
        using var workbook = new XLWorkbook();

        WriteSheet2(workbook, declarationItems);
        WriteSheet3(workbook, sheet3Rows);

        workbook.SaveAs(outputPath);
    }

    private static void WriteSheet2(XLWorkbook workbook, IEnumerable<DeclarationItem> items)
    {
        var ws = workbook.Worksheets.Add("Лист2");

        var headers = new[]
        {
            "п/п",
            "Наименование, характеристика, сорт, артикул товара",
            "ТН ВЭД",
            "Страна ID",
            "Количество,шт.",
            "Сумма с учетом НДС, руб. РФ",
            "ВЕС брутто, КГ",
            "Масса нетто, КГ",
            "Код декларации",
            "Регистрационный номер",
            "Дата регистрационного номера",
        };

        for (int c = 0; c < headers.Length; c++)
        {
            var cell = ws.Cell(1, c + 1);
            cell.Value = headers[c];
            cell.Style.Font.Bold = true;
            cell.Style.Fill.BackgroundColor = XLColor.LightGray;
        }

        int row = 2;
        decimal totalAmount = 0, totalGross = 0, totalNet = 0, totalQty = 0;

        foreach (var item in items)
        {
            ws.Cell(row, 1).Value = item.SequentialNumber;
            ws.Cell(row, 2).Value = item.Description;
            ws.Cell(row, 3).Value = item.TnVed;
            ws.Cell(row, 4).Value = item.CountryId;
            ws.Cell(row, 5).Value = item.Quantity;
            ws.Cell(row, 6).Value = item.AmountWithVat;
            ws.Cell(row, 7).Value = item.GrossWeight;
            ws.Cell(row, 8).Value = item.NetWeight;
            ws.Cell(row, 9).Value = item.DeclarationCode;

            if (item.RegEntries.Count == 1)
            {
                ws.Cell(row, 10).Value = item.RegEntries[0].Number;
                ws.Cell(row, 11).Value = item.RegEntries[0].Date;
            }
            else if (item.RegEntries.Count > 1)
            {
                // Несколько партий — жёлтая строка целиком, подробности в Лист3
                ws.Row(row).Style.Fill.BackgroundColor = XLColor.Yellow;
            }

            totalAmount += item.AmountWithVat;
            totalGross  += item.GrossWeight;
            totalNet    += item.NetWeight;
            totalQty    += item.Quantity;
            row++;
        }

        // Итоговая строка
        ws.Cell(row, 2).Value = "ИТОГО:";
        ws.Cell(row, 5).Value = totalQty;
        ws.Cell(row, 6).Value = totalAmount;
        ws.Cell(row, 7).Value = totalGross;
        ws.Cell(row, 8).Value = totalNet;
        ws.Row(row).Style.Font.Bold = true;

        ApplyBorders(ws, 1, row, headers.Length);

        ws.Columns().AdjustToContents();
        ws.Column(2).Width = 50;
    }

    private static void WriteSheet3(XLWorkbook workbook, IEnumerable<Sheet3Row> rows)
    {
        var ws = workbook.Worksheets.Add("Лист3");

        var headers = new[]
        {
            "п/п",
            "ТН ВЭД",
            "Регистрационный номер",
            "Дата регистрационного номера",
        };

        for (int c = 0; c < headers.Length; c++)
        {
            var cell = ws.Cell(1, c + 1);
            cell.Value = headers[c];
            cell.Style.Font.Bold = true;
            cell.Style.Fill.BackgroundColor = XLColor.LightGray;
        }

        int row = 2;
        foreach (var r in rows)
        {
            ws.Cell(row, 1).Value = r.SequentialNumber;
            ws.Cell(row, 2).Value = r.TnVed;
            ws.Cell(row, 3).Value = r.RegNumber;
            ws.Cell(row, 4).Value = r.RegDate;
            row++;
        }

        ApplyBorders(ws, 1, row - 1, headers.Length);

        ws.Columns().AdjustToContents();
    }

    private static void ApplyBorders(IXLWorksheet ws, int fromRow, int toRow, int colCount)
    {
        var range = ws.Range(fromRow, 1, toRow, colCount);
        range.Style.Border.InsideBorder = XLBorderStyleValues.Thin;
        range.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
    }
}