Una libreria C# .NET 5 per gestire l'export in Excel di dati in forma tabellare tramite il pacchetto NuGet - DocumentFormat.OpenXml
C#: ExcelSimpleExport.cs
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Sgart.Net5.WebReactApp.Services
{
    /// <summary>
    /// Crea un file excel base per l'export dei dati in forma tabellare
    /// si può aggiungere un header e supporta date e numeri
    /// richiede il pacchetto NuGet DocumentFormat.OpenXml Version="2.15.0"
    /// </summary>
    public class SimpleExcelService : IDisposable
    {
        private readonly ILogger<SimpleExcelService> _logger;
        private readonly System.Globalization.CultureInfo _ciEN = new("en-US");

        public const string CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        public const string FILE_EXTENSION = "xlsx";
        private const string CELL_BASE_NAMES = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        private SpreadsheetDocument _package;
        private SheetData _sheetData;
        private Row _row;
        private int _rowNumber = 1;
        private int _cellNumber = 1;

        // stili grafici
        private int _styleFormatDate = 0;
        private int _styleFormatDateTime = 0;

        /// <summary>
        /// usato per la DI
        /// </summary>
        /// <param name="logger"></param>
        public SimpleExcelService(ILogger<SimpleExcelService> logger)
        {
            _logger = logger;
            _logger.LogTrace("Export Excel");
        }

        /// <summary>
        /// usato se richiamato direttamente senza logger
        /// </summary>
        public SimpleExcelService()
        {
        }


        /// <summary>
        /// inizializza un nuovo documento/package
        /// </summary>
        /// <param name="fileName"></param>
        public void Create(string fileName)
        {
            _package = SpreadsheetDocument.Create(fileName, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            Init();
        }

        /// <summary>
        /// inizializza un nuovo documento/package
        /// </summary>
        /// <param name="fileName"></param>
        public void Create(System.IO.Stream stream)
        {
            stream.Position = 0;
            _package = SpreadsheetDocument.Create(stream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);
            Init();
        }

        private void Init()
        {
            _sheetData = null;
            _row = null;
            _rowNumber = 1;
            _cellNumber = 1;
            _styleFormatDate = 0;
            _styleFormatDateTime = 0;

            var workbookPart = _package.AddWorkbookPart();
            workbookPart.Workbook = new Workbook
            {
                Sheets = new Sheets()
            };


            // Add Stylesheet.
            var workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
            workbookStylesPart.Stylesheet = GetStylesheet();
            workbookStylesPart.Stylesheet.Save();
        }

        /// <summary>
        /// abilita il formato date per office 2003/7
        /// </summary>
        public bool Dateformat2007 { get; set; }

        private Stylesheet GetStylesheet()
        {
            //https://stackoverflow.com/questions/7089745/openxml-writing-a-date-into-excel-spreadsheet-results-in-unreadable-content/31829959#31829959
            var styleSheet = new Stylesheet();

            // Create "fonts" node.
            var fonts = new Fonts();
            fonts.Append(new Font()
            {
                FontName = new FontName() { Val = "Calibri" },
                FontSize = new FontSize() { Val = 11 },
                FontFamilyNumbering = new FontFamilyNumbering() { Val = 2 },
            });

            fonts.Count = (uint)fonts.ChildElements.Count;

            // Create "fills" node.
            var fills = new Fills();
            fills.Append(new Fill()
            {
                PatternFill = new PatternFill() { PatternType = PatternValues.None }
            });
            fills.Append(new Fill()
            {
                PatternFill = new PatternFill() { PatternType = PatternValues.Gray125 }
            });

            fills.Count = (uint)fills.ChildElements.Count;

            // Create "borders" node.
            var borders = new Borders();
            borders.Append(new Border()
            {
                LeftBorder = new LeftBorder(),
                RightBorder = new RightBorder(),
                TopBorder = new TopBorder(),
                BottomBorder = new BottomBorder(),
                DiagonalBorder = new DiagonalBorder()
            });
            borders.Count = (uint)borders.ChildElements.Count;

            // Create "cellStyleXfs" node.
            var cellStyleFormats = new CellStyleFormats();
            cellStyleFormats.Append(new CellFormat()
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0
            });

            cellStyleFormats.Count = (uint)cellStyleFormats.ChildElements.Count;

            // Create "cellXfs" node.
            var cellFormats = new CellFormats();

            // A default style that works for everything but DateTime
            cellFormats.Append(new CellFormat()
            {
                BorderId = 0,
                FillId = 0,
                FontId = 0,
                NumberFormatId = 0,
                FormatId = 0,
                ApplyNumberFormat = true
            });

            // Date only
            cellFormats.Append(new CellFormat()
            {
                BorderId = 0,
                FillId = 0,
                FontId = 0,
                NumberFormatId = 14,    // Date only
                FormatId = 0,
                ApplyNumberFormat = true
            });

            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            _styleFormatDate = cellFormats.ChildElements.Count - 1;

            // Date + Time
            cellFormats.Append(new CellFormat()
            {
                BorderId = 0,
                FillId = 0,
                FontId = 0,
                NumberFormatId = 22,    // Date + Time
                FormatId = 0,
                ApplyNumberFormat = true
            });

            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            _styleFormatDateTime = cellFormats.ChildElements.Count - 1;

            // Create "cellStyles" node.
            var cellStyles = new CellStyles();
            cellStyles.Append(new CellStyle()
            {
                Name = "Normal",
                FormatId = 0,
                BuiltinId = 0
            });
            cellStyles.Count = (uint)cellStyles.ChildElements.Count;

            // aggiungo tutti nodi in ordine
            styleSheet.Append(fonts);
            styleSheet.Append(fills);
            styleSheet.Append(borders);
            styleSheet.Append(cellStyleFormats);
            styleSheet.Append(cellFormats);
            styleSheet.Append(cellStyles);

            return styleSheet;
        }

        /// <summary>
        /// ritorna il numero di riga corrente
        /// </summary>
        /// <returns></returns>
        public int GetRowNumber()
        {
            return _rowNumber;
        }

        /// <summary>
        /// aggiunge una scheda all'Excel
        /// da richiamare come prima operazione dopo il costruttore
        /// </summary>
        /// <param name="sheetName"></param>
        public void AddSheet(string sheetName)
        {
            var sheetPart = _package.WorkbookPart.AddNewPart<WorksheetPart>();
            _sheetData = new SheetData();
            sheetPart.Worksheet = new Worksheet(_sheetData);

            var sheets = _package.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
            string relationshipId = _package.WorkbookPart.GetIdOfPart(sheetPart);

            uint sheetId = 1;
            if (sheets.Elements<Sheet>().Any())
            {
                sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
            }

            var sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
            sheets.Append(sheet);
        }

        /// <summary>
        /// aggiunta di header al Sheet (opzionale)
        /// </summary>
        /// <param name="headers"></param>
        public void AddHeaders(List<string> headers)
        {
            NewRow();
            foreach (var title in headers)
            {
                AddCell(title);
            }
            AddRow();
        }

        /// <summary>
        /// Crea una nuova riga ma NON la aggiunge al Sheet
        /// </summary>
        /// <returns></returns>
        public int NewRow()
        {
            _row = new Row();
            return _rowNumber;
        }

        /// <summary>
        /// aggiunge una nuova riga al Sheet
        /// </summary>
        public void AddRow()
        {
            _sheetData.AppendChild(_row);
            _row = null;
            _rowNumber++;
            _cellNumber = 1;
        }

        private static Cell NewCell(CellValues type)
        {
            var cell = new Cell
            {
                DataType = type
            };
            return cell;
        }

        private string AddrowInternal(Cell cell)
        {
            var cn = _cellNumber - 1; // -1 perchè parte da 1 e non 0

            _row.AppendChild(cell);
            _cellNumber++;

            if (cn > 26)
            {
                var I1 = cn / 26;
                var I0 = cn % 26;

                return CELL_BASE_NAMES[I0].ToString() + CELL_BASE_NAMES[I1].ToString();
            }
            return CELL_BASE_NAMES[cn].ToString();
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo Stringa
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(string value)
        {
            var cell = NewCell(CellValues.String);
            cell.CellValue = new CellValue(value);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo Formula
        /// </summary>
        /// <param name="value"></param>
        public string AddCellFormula(string formula)
        {
            var cell = NewCell(CellValues.String);
            cell.CellFormula = new CellFormula(formula);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo boleano
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(bool value)
        {
            var cell = NewCell(CellValues.Boolean);
            cell.CellValue = new CellValue(value);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo Intero
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(int value)
        {
            var cell = NewCell(CellValues.Number);
            cell.CellValue = new CellValue(value);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo Decimal
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(decimal value)
        {
            var cell = NewCell(CellValues.Number);
            cell.CellValue = new CellValue(value);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo Decimal nullable
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(decimal? value)
        {
            var cell = NewCell(CellValues.Number);
            if (value.HasValue)
            {
                cell.CellValue = new CellValue(value.Value);
            }
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo DateTime
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(DateTime value, bool showTime = false)
        {
            var cell = NewCell(CellValues.Date);
            if (Dateformat2007)
            {
                double oaValue = value.ToOADate();
                cell.CellValue = new CellValue(oaValue.ToString(_ciEN));
            }
            else
            {
                cell.CellValue = new CellValue(value.ToString("s"));
            }
            cell.StyleIndex = Convert.ToUInt32(showTime ? _styleFormatDateTime : _styleFormatDate);
            return AddrowInternal(cell);
        }

        /// <summary>
        /// aggiunge, ad una riga, una cella di tipo DateTime nullable
        /// </summary>
        /// <param name="value"></param>
        public string AddCell(DateTime? value, bool showTime = false)
        {
            var cell = NewCell(CellValues.Date);
            if (value.HasValue)
            {
                if (Dateformat2007)
                {
                    double oaValue = value.Value.ToOADate();
                    cell.CellValue = new CellValue(oaValue.ToString(_ciEN));
                }
                else
                {
                    cell.CellValue = new CellValue(value.Value.ToString("s"));
                }
            }
            cell.StyleIndex = Convert.ToUInt32(showTime ? _styleFormatDateTime : _styleFormatDate);
            return AddrowInternal(cell);
        }

        public void Close()
        {
            Dispose();
        }

        public void Dispose()
        {
            if (_package != null)
                _package.Dispose();
        }

    }
}
La libreria risolve un problema semplice, ovvero esportare dei dati in forma tabellare, piatta, senza nessuna particolare formattazione.

Come si usa

Posso generare un Excel in memoria o con MemoryStream e salvarlo su filesystem con CopyTo
C#: Esempio in memoria
using (MemoryStream mem = new MemoryStream())
{
    using (var xls = new Services.SimpleExcelService())
    {
        // creo un file in memoria
        xls.Create(mem);
        // oppure lo creo direttamente su file
        // xls.Create(@"c:\temp\prova1.xlsx");

        xls.AddSheet("Scheda 1");

        xls.AddHeaders(new List<string> { "Id", "Title", "Value", "Date", "Value x 10" });

        foreach (var item in items)
        {
            var rowNumber = xls.NewRow();

            xls.AddCell(item.ID);
            xls.AddCell(item.Title);
            xls.AddCell(item.Value);
            xls.AddCell(item.Date);
            xls.AddCellFormula($"C{rowNumber}*10");

            xls.AddRow();
        }
    }

    mem.Position = 0;
    using (FileStream file = new FileStream(@"c:\temp\provaX3.xlsx", FileMode.OpenOrCreate, FileAccess.Write))
        mem.CopyTo(file);
}
Excel demoExcel demo

Un altro possibile uso è all'interno di un controller MVC
C#
[ApiController]
[Route("api/[controller]")]
public class ExportController : ControllerBase
{

    private readonly ILogger<TodoController> _logger;
    private readonly ExportService _service;

    public ExportController(ILogger<TodoController> logger, ExportService service)
    {
        _logger = logger;
        _service = service;
    }

    [HttpGet]
    [Route("excel")]
    public async Task<IActionResult> Excel()
    {
        try
        {
            var mem = new MemoryStream();

            string fileName = await _service.GetExcel(mem);

            mem.Position = 0;

            return new FileStreamResult(mem, SimpleExcelService.CONTENT_TYPE)
            {
                FileDownloadName = fileName,
                LastModified = DateTime.Now
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Excel");

            return BadRequest();
        }
    }

}
dove il servizio ExportService è questo
C#: controller MVC
public class ExportService
{
    private readonly ILogger<ExportService> _logger;
    private readonly SimpleExcelService _excelService;

    public ExportService(ILogger<ExportService> logger, SimpleExcelService excelService)
    {
        _logger = logger;
        _excelService = excelService;

        _logger.LogTrace("Export service");
    }

    private async Task<List<ExcelMockModel>> GetMockData()
    {
        var items = new List<ExcelMockModel> {
            new ExcelMockModel { ID=1, Title="Prova 1", Value=1231331.422M, Date= DateTime.Now },
            new ExcelMockModel { ID=2, Title="Prova 2", Value=731.22M, Date= new DateTime(2021,12,31) },
            new ExcelMockModel { ID=3, Title="Prova 3", Value=731.22M, Date= new DateTime(2021,5,21) }
        };

        return items;
    }

    public async Task<string> GetExcel(Stream strm)
    {
        string fileName = $"Sgart_demo_export_excel_{DateTime.Now:yyyy-MM-dd-HH-mm-ss}.{SimpleExcelService.FILE_EXTENSION}";
        _logger.LogTrace($"Excel nema: {fileName}");

        var items = await GetMockData();

        _excelService.Create(strm);

        _excelService.AddSheet("Sheet 1");

        _excelService.AddHeaders(new List<string> { "Id", "Title", "Value", "Date", "Value x 10" });

        foreach (var item in items)
        {
            // creo la riga 
            var rowNumber = _excelService.NewRow();

            // aggiungo i volori delle celle
            _excelService.AddCell(item.ID);
            _excelService.AddCell(item.Title);
            var lettValue = _excelService.AddCell(item.Value);
            _excelService.AddCell(item.Date);
            _excelService.AddCellFormula($"{lettValue}{rowNumber}*10");

            // aggiungo la riga all'excel
            _excelService.AddRow();
        }

        _excelService.Close();

        return fileName;
    }
}

Dati per la demo

Questi sono i dati usati nel primo esempio
C#: Dati di esempio
public class DatiEsempio
{
    public int ID { get; set; }
    public string Title { get; set; }
    public decimal Value { get; set; }
    public DateTime Date { get; set; }
}

var items = new List<DatiEsempio> {
    new DatiEsempio { ID=1, Title="Prova 1", Value=1231331.422M, Date= DateTime.Now },
    new DatiEsempio { ID=1, Title="Prova 2", Value=731.22M, Date= new DateTime(2021,12,31) }
};
Una progetto funzionante che usa questa libreria, lo si può trovare su GitHub - Sgart.Net5.
Potrebbe interessarti anche: