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 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.
