Nell’ambito delle nostre attività di sviluppo, sia per clienti che per progetti interni, ci siamo spesso trovati di fronte alla necessità di creare report contenenti dati di diversa natura. Il formato che di solito utilizziamo – e anche il più richiesto – per tali report è sicuramente Office Open XML, che viene letto ed elaborato dai software per fogli di calcolo delle principali suite di office e che assicura qualità e leggerezza del documento.
Nel mondo .NET esistono diversi plugin di terze parti che ci consentono di creare file Office Open XML. Dopo averne testati diversi, abbiamo deciso di optare per Open XML SDK, che ci è sembrata la migliore scelta in termini di funzionalità, di performance e di configurabilità, e, non ultimo, poiché consente l’utilizzo nativo del formato Office Open XML
Alcune note su Office Open XML
Il formato Office Open XML è uno standard per documenti di testo, presentazioni e fogli di calcolo, che può essere implementato da più applicazioni su piattaforme differenti. Lo scopo principale del formato Open XML è quello di disaccoppiare l’informazione dal software col quale viene creato senza perdere dati. Un file Open XML è strutturato naturalmente in un file di testo creato con markup XML conservato in un archivio ZIP ed è costituito da più parti, a seconda della tipologia di documento.
Nel caso dei fogli di calcolo, il documento Open XML può contenere al suo interno:
- Sezioni del documento (obbligatoria)
- Uno o più fogli di lavoro
- Grafici
- Tabelle
- XML personalizzato
Open XML SDK
Open XML SDK è una libreria open source, rilasciata sotto licenza Apache 2.0 da Microsoft per gli sviluppatori, che fornisce una serie di namespace e membri a supporto dei più comuni software di office, tra cui naturalmente Microsoft Office.
La documentazione ufficiale è disponibile al link: https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
Creazione del documento
Nell’esempio di seguito, abbiamo riportato uno scenario reale riguardante la creazione del report dei prodotti di uno store.
Abbiamo aggiunto una classe chiamata ColumnAttribute, derivata da una classe base Attribute, che serve a definire una serie di metadati relativi alla singola colonna. Tale annotazione verrà aggiunta alle singole proprietà della classe su cui sarà basato il report.
/// <summary >
/// ColumnAttribute:
/// Inherits from System.Attribute class
/// </summary >
public class ColumnAttribute : Attribute
{
// column name
public string Name { get; set; }
// is it a date?
[DefaultValue(false)]
public bool IsDate { get; set; }
// is it a time?
[DefaultValue(false)]
public bool IsTime { get; set; }
// is it a currency?
[DefaultValue(false)]
public bool IsCurrency { get; set; }
// is it a percentage?
[DefaultValue(false)]
public bool IsPercentage { get; set; }
// has it a width based on content?
[DefaultValue(false)]
public bool AutoSize { get; set; }
// has it to be sorted?
// Sort: -1 descending, 0 none, 1 ascending
[DefaultValue(0)]
public int Sort { get; set; }
// custom style index
public uint StyleIndex { get; set; }
}
public class Product
{
[Column(Name = "Id")]
public int Id { get; set; }
[Column(Name = "Ean")]
public string Ean { get; set; }
[Column(Name = "Name", AutoSize = true, Sort = 1)]
public string Name { get; set; }
[Column(Name = "Description")]
public string Description { get; set; }
[Column(Name = "Brand", AutoSize = true)]
public string Brand { get; set; }
[Column(Name = "Category", AutoSize = true)]
public string Category { get; set; }
[Column(Name = "Price", IsCurrency = true)]
public decimal Price { get; set; }
[Column(Name = "Quantity")]
public int Quantity { get; set; }
[Column(Name = "Rating", IsPercentage = true)]
public decimal Rating { get; set; }
[Column(Name = "ReleaseDate", IsDate = true)]
public DateTime ReleaseDate { get; set; }
}
I campi che non hanno l’annotation ColumnAttribute vengono ignorati dall’export.
Per centralizzare la creazione dei fogli di calcolo Office Open XML abbiamo invece realizzato la classe OpenXMLProvider, che ci fornisce un metodo generico GenerateWorksheet che ci consente di prendere in input una lista di elementi, eventualmente escludere alcuni campi e definire il nome dello sheet principale.
public class OpenXMLProvider
{
public byte[] File { get; private set; }
public void GenerateWorksheet<T> (IEnumerable<T> list, IEnumerable<String> fieldsToExclude = null, String name = "Foglio 1")
{
File = WorksheetUtilities.CreateGenericReport<T>(list, fieldsToExclude, name);
}
}
Il core della nostra soluzione è sicuramente la classe OpenXMLWorksheetBuilder, che ci consente di creare un foglio di calcolo Office Open XML scrivendo codice XML nativo all’interno dello sheet.
Prima di scrivere la lista dei nostri prodotti nel file, definiamo gli stili del file, tramite il metodo GenerateWorkbookStylesPartContent. A prima vista può sembrare un po’ cervellotico, ma ciò ci consentirà di creare una lista di stili, formattazioni e formati da utilizzare nel nostro foglio di calcolo.
private void GenerateWorkbookStylesPartContent(WorkbookStylesPart workbookStylesPart)
{
// Declaring stylesheet and main namespaces
Stylesheet stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
// Declaring customer number format:
NumberingFormats numberingFormats = new NumberingFormats() { Count = 5U };
NumberingFormat numberingFormatDateTime = new NumberingFormat() { NumberFormatId = 1U, FormatCode = "dd/mm/yyyy\\ hh:mm;@" };
NumberingFormat numberingFormatCurrency = new NumberingFormat() { NumberFormatId = 2U, FormatCode = "#,##0.00\\ \"€\"" };
NumberingFormat numberingFormatPercentage = new NumberingFormat() { NumberFormatId = 3U, FormatCode = "###,000\\ \"%\"" };
NumberingFormat numberingFormatDate = new NumberingFormat() { NumberFormatId = 4U, FormatCode = "dd/mm/yyyy;@" };
NumberingFormat numberingFormatTime = new NumberingFormat() { NumberFormatId = 5U, FormatCode = "hh:mm;@" };
numberingFormats.Append(numberingFormatDateTime);
numberingFormats.Append(numberingFormatCurrency);
numberingFormats.Append(numberingFormatPercentage);
numberingFormats.Append(numberingFormatDate);
numberingFormats.Append(numberingFormatTime);
// Declaring fonts:
Fonts fonts = new Fonts() { Count = 2U, KnownFonts = true };
Font fontNormal = new Font(
new FontName() { Val = "Calibri" },
new FontSize() { Val = _fontWidth }
);
Font fontWhiteBold = new Font(
new FontName() { Val = "Calibri" },
new FontSize() { Val = _fontWidth },
new Bold(),
new Color() { Rgb = HexBinaryConverter(System.Drawing.Color.White) }
);
fonts.Append(fontNormal);
fonts.Append(fontWhiteBold);
// Declaring cell filling:
Fills fills = new Fills() { Count = 2U };
Fill fillNone = new Fill(new PatternFill() { PatternType = PatternValues.None });
Fill fillGray125 = new Fill(new PatternFill() { PatternType = PatternValues.Gray125 });
Fill fillDodgerBlue = new Fill(new PatternFill()
{
PatternType = PatternValues.Solid,
ForegroundColor = new ForegroundColor() { Rgb = HexBinaryConverter(System.Drawing.Color.DodgerBlue) }
});
fills.Append(fillNone);
fills.Append(fillGray125);
fills.Append(fillDodgerBlue);
// Declaring borders:
Borders borders = new Borders() { Count = 2U };
Border borderNone = new Border();
Border borderFullThinGray = new Border(
new LeftBorder() { Style = BorderStyleValues.Thin, Color = new Color() { Rgb = HexBinaryConverter(System.Drawing.Color.Gray) } },
new RightBorder() { Style = BorderStyleValues.Thin, Color = new Color() { Rgb = HexBinaryConverter(System.Drawing.Color.Gray) } },
new TopBorder() { Style = BorderStyleValues.Thin, Color = new Color() { Rgb = HexBinaryConverter(System.Drawing.Color.Gray) } },
new BottomBorder() { Style = BorderStyleValues.Thin, Color = new Color() { Rgb = HexBinaryConverter(System.Drawing.Color.Gray) } }
);
borders.Append(borderNone);
borders.Append(borderFullThinGray);
// Declaring cell formats
CellFormats cellFormats = new CellFormats() { Count = 7U };
CellFormat cellFormatBase = new CellFormat() { FontId = 0U, FillId = 0U, BorderId = 0U };
CellFormat cellFormatNormal = new CellFormat() { FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyBorder = true };
CellFormat cellFormatHeader = new CellFormat() { FontId = 1U, FillId = 2U, BorderId = 1U, FormatId = 0U, ApplyFont = true, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true, Alignment = new Alignment() { Horizontal = HorizontalAlignmentValues.Center } };
CellFormat cellFormatDate = new CellFormat() { NumberFormatId = 4U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyNumberFormat = true, ApplyBorder = true };
CellFormat cellFormatTime = new CellFormat() { NumberFormatId = 5U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyNumberFormat = true, ApplyBorder = true };
CellFormat cellFormatDateTime = new CellFormat() { NumberFormatId = 1U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyNumberFormat = true, ApplyBorder = true };
CellFormat cellFormatCurrency = new CellFormat() { NumberFormatId = 2U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyNumberFormat = true, ApplyBorder = true, ApplyAlignment = true };
CellFormat cellFormatPercentage = new CellFormat() { NumberFormatId = 3U, FontId = 0U, FillId = 0U, BorderId = 1U, FormatId = 0U, ApplyNumberFormat = true, ApplyBorder = true, ApplyAlignment = true };
cellFormats.Append(cellFormatBase);
cellFormats.Append(cellFormatNormal);
cellFormats.Append(cellFormatHeader);
cellFormats.Append(cellFormatDate);
cellFormats.Append(cellFormatTime);
cellFormats.Append(cellFormatDateTime);
cellFormats.Append(cellFormatCurrency);
cellFormats.Append(cellFormatPercentage);
// Append all defined styles to worksheet
stylesheet.Append(numberingFormats);
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);
workbookStylesPart.Stylesheet = stylesheet;
}
In questo metodo abbiamo definito, in ordine:
- Foglio di calcolo e principali namespace
- Formati numerici e date
- Font
- Colori di cella
- Bordi
- Formati di cella
I formati di cella sono quelli che poi verranno utilizzati nella formattazione delle nostre celle. Nell’ordine in cui sono inseriti, i formati prendono un Id uint progressivo: il primo avrà id 0U, il secondo 1U, ecc .
Una enum ci aiuta a semplificare nel nostro codice l’accesso ai vari formati di cella disponibili
public enum CustomStyles
{
Base = 0,
Normal = 1,
Header = 2,
Date = 3,
Time = 4,
DateTime = 5,
Currency = 6,
Percentage = 7
}
Una volta inseriti gli stili del nostro foglio, possiamo finalmente iniziare a scrivere i dati partendo dalle intestazioni delle colonne che inseriamo grazie al metodo CreateHeaderCell:
private Cell CreateHeaderCell(PropertyInfo propertyInfo)
{
// Get custom ColumnAttribute from PropertyInfo object
var columnAttribute = propertyInfo.GetColumnAttribute();
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.StyleIndex = columnAttribute.StyleIndex > 0 ? columnAttribute.StyleIndex : (uint)CustomStyles.Header;
cell.CellValue = new CellValue(columnAttribute.Name.ToUpperInvariant());
return cell;
}
Per le celle contenenti i dati, invece, abbiamo creato un metodo generico chiamato CreateContentCell:
private Cell CreateContentCell <T > (T element, PropertyInfo propertyInfo)
{
// Get custom ColumnAttribute from PropertyInfo object
var columnAttribute = propertyInfo.GetColumnAttribute();
var value = propertyInfo.GetValue(element, null);
Cell cell = new Cell();
CellValues dataType = CellValues.String;
String text = String.Empty;
uint style = (uint)CustomStyles.Normal;
if (value == null)
{
}
else if (value is DateTime?)
{
dataType = CellValues.Number;
text = (value as DateTime?).Value.ToOADate().ToString(CultureInfo.InvariantCulture);
if (columnAttribute.IsDate)
{
style = (uint)CustomStyles.Date;
}
else if (columnAttribute.IsTime)
{
style = (uint)CustomStyles.Time;
}
else
{
style = (uint)CustomStyles.DateTime;
}
}
else if (value is decimal)
{
dataType = CellValues.Number;
if (columnAttribute.IsCurrency)
{
text = (Math.Round((value as decimal?).Value, 3)).ToString(CultureInfo.InvariantCulture);
style = (uint)CustomStyles.Currency;
}
else if (columnAttribute.IsPercentage)
{
text = (Math.Round((value as decimal?).Value * 100, 3)).ToString(CultureInfo.InvariantCulture);
style = (uint)CustomStyles.Percentage;
}
else
{
text = (Math.Round((value as decimal?).Value / 100, 3)).ToString(CultureInfo.InvariantCulture);
}
}
else if (value is int)
{
dataType = CellValues.Number;
text = ((value as int?).Value).ToString(CultureInfo.InvariantCulture);
}
else if (value is double)
{
dataType = CellValues.Number;
text = ((value as double?).Value).ToString(CultureInfo.InvariantCulture);
}
else if (value is bool?)
{
text = ((value as bool?).Value ? "SI" : "NO").ToString(CultureInfo.InvariantCulture);
}
else if (value is String)
{
text = (value as String).Replace("\0", String.Empty).ToString(CultureInfo.InvariantCulture);
}
cell.DataType = dataType;
cell.StyleIndex = style;
cell.CellValue = new CellValue(text.ToUpperInvariant());
return cell;
}
CreateContentCell elabora il contenuto della cella e lo mette a confronto con gli attributi definiti per la colonna corrispondente al fine di scegliere la formattazione migliore.
Nel nostro caso, ad esempio, i valori di tipo decimal che hanno l’attributo IsCurrency vengono elaborati diversamente da quelli che hanno l’attributo IsPercentage, in modo da ottenere due formattazioni differenti e congrue a partire dallo stesso tipo.
Lo stesso vale per le date, che di default sono date complete (con data e ora), ma all’occorrenza possono essere formattate come solo data o solo ora, a seconda degli attributi IsDate o IsTime.
Inoltre, possiamo aggiungere dei metadati al nostro file, tramite il metodo SetDocumentMetadata:
private void SetDocumentMetadata(OpenXmlPackage document)
{
document.PackageProperties.Title = "Sample report";
// for example: it retrieves 'Creator' attribute from appsettings.json
document.PackageProperties.Creator = _configuration.GetValue<string>("AppSettings:WorksheetAuthority");
document.PackageProperties.Created = DateTime.Now;
document.PackageProperties.Language = "en-US";
}
</string>
dove, ad esempio, l’attributo Creator è stato recuperato da file di configurazione.
Tutte le classi e i metodi presentati finora, vengono utilizzati dal metodo generico CreateReport:
public byte[] CreateReport <T >(IEnumerable <T > list, IEnumerableString> fieldsToExclude, String name)
{
using (MemoryStream memoryStream = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
// Add a WorkbookStylesPart
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart >();
// Create and define workbook styles
GenerateWorkbookStylesPartContent(workbookStylesPart);
// Add a WorksheetPart
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart > ();
OpenXmlWriter openXmlWriter = OpenXmlWriter.Create(worksheetPart);
openXmlWriter.WriteStartElement(new Worksheet());
openXmlWriter.WriteElement(new Columns());
openXmlWriter.WriteStartElement(new SheetData());
var propertyInfos = GetColumnAttributePropertyInfos <T > (fieldsToExclude).ToList();
List <OpenXmlAttribute > openXmlAttribute = new List <OpenXmlAttribute > ();
openXmlAttribute.Add(new OpenXmlAttribute("r", null, "1"));
openXmlWriter.WriteStartElement(new Row(), openXmlAttribute);
// Create header row
foreach (var propertyInfo in propertyInfos)
{
openXmlWriter.WriteElement(CreateHeaderCell(propertyInfo));
}
// end tag for header row
openXmlWriter.WriteEndElement();
foreach (var (element, rowIndex) in list.Select((v, i) = > (v, i)))
{
openXmlAttribute = new List <OpenXmlAttribute >();
// this is the row index
openXmlAttribute.Add(new OpenXmlAttribute("r", null, (rowIndex + 2).ToString()));
openXmlWriter.WriteStartElement(new Row(), openXmlAttribute);
foreach (var (propertyInfo, columnIndex) in propertyInfos.Select((v, i) = > (v, i)))
{
openXmlAttribute = new List <OpenXmlAttribute >();
openXmlAttribute.Add(new OpenXmlAttribute("t", null, (columnIndex + 1).ToString()));
openXmlWriter.WriteElement(CreateContentCell(element, propertyInfo));
}
// end tag for Row
openXmlWriter.WriteEndElement();
}
// end tag for SheetData
openXmlWriter.WriteEndElement();
// end tag for Worksheet
openXmlWriter.WriteEndElement();
openXmlWriter.Close();
openXmlWriter = OpenXmlWriter.Create(workbookPart);
openXmlWriter.WriteStartElement(new Workbook());
openXmlWriter.WriteStartElement(new Sheets());
openXmlWriter.WriteElement(new Sheet()
{
Name = name,
SheetId = 1U,
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart)
});
// end tag for Sheets
openXmlWriter.WriteEndElement();
// end tag for Workbook
openXmlWriter.WriteEndElement();
openXmlWriter.Close();
// Set column widths
SetColumnWidths(worksheetPart.Worksheet, propertyInfos, list);
// Set filtering and sorting
SetAutoFilterAndSorting(worksheetPart, workbookPart.Workbook, name, propertyInfos, list.Count() + 1);
// Set metadata for document
SetDocumentMetadata(spreadsheetDocument);
spreadsheetDocument.Close();
}
return memoryStream.ToArray();
}
}
In questo metodo, dopo aver creato l’oggetto workbook e gli stili, procediamo alla scrittura delle intestazioni e dei dati.
Per scrivere in maniera veloce ed efficiente le celle, procediamo così:
- Apertura container esterno tag tramite OpenXmlWriter.WriteStartElement
- Eventuale aggiunta attributi tramite la classe OpenXmlAttribute (consigliato per la leggibilità del file XML)
- Scrittura del valore nella cella tramite OpenXmlWriter.WriteElement
- Chiusura container esterno con OpenXmlWriter.WriteEndElement
Per quanto riguarda la parte Web, il controller API di esempio, ProductsController, ha un unico metodo GetExport che, a scopo dimostrativo, prende in input il numero di prodotti da creare e restituisce uno stream di dati contenente il file Open XML.
La lista dei prodotti viene generata dinamicamente tramite la libreria Bogus, che permette di creare oggetti fake, e che ci è risultata comodo da utilizzare in questo contesto.
[HttpGet("export/{count}")]
public ActionResult GetExport(int count = 0)
{
// it defines a product template with fake data
var productFaker = new Faker<Product> ()
.CustomInstantiator(f => new Product())
.RuleFor(p => p.Id, f => f.IndexFaker)
.RuleFor(p => p.Ean, f => f.Commerce.Ean13())
.RuleFor(p => p.Name, f => f.Commerce.ProductName())
.RuleFor(p => p.Description, f => f.Lorem.Sentence(f.Random.Int(5, 20)))
.RuleFor(p => p.Brand, f => f.Company.CompanyName())
.RuleFor(p => p.Category, f => f.Commerce.Categories(1).First())
.RuleFor(p => p.Price, f => f.Random.Decimal(1, 1000))
.RuleFor(p => p.Quantity, f => f.Random.Int(0, 1000))
.RuleFor(p => p.Rating, f => f.Random.Decimal(0, 1))
.RuleFor(p => p.ReleaseDate, f => f.Date.Past(2));
_openXMLProvider.GenerateWorksheet(productFaker.Generate(count), null, "Sheet 1");
return File(_openXMLProvider.File, "application/octet-stream");
}
Esecuzione del progetto
A scopo di esempio, abbiamo realizzato una piccola interfaccia grafica, in cui è possibile inserire il numero di elementi e, dopo aver cliccato su Download Report, scaricare il file.
Avviando il progetto, otteniamo questa schermata:
Scaricato il file, possiamo aprirlo e visualizzare il risultato:
Testare il file con Open XML Productivity Tool
Nel generare file più o meno complessi, può capitare che questi siano corrotti o addirittura illeggibili. In tal caso, alla loro apertura ci viene mostrata uno sgradevole alert che ci avvisa di non poter leggere il file e che verrà tentato un recupero.
Per ovviare a questo problema e capire cosa sbagliamo nel creare il file, c’è il tool Open XML Productivity Tool, che scansiona il file e ne verifica la correttezza sintattica e semantica.
Il tool è di facile utilizzo. Basta avviarlo, selezionare un file, e, dopo averlo aperto, cliccare su Validate. Nella colonna a sinistra ci vengono indicate le parti di cui è composto il Worksheet, e a destra sono indicati avvisi ed errori. Solitamente i warning generano alert di file danneggiato, ma comunque leggibile, cosa che invece non avviene per i file con errori.
Scorrendo la sezione a destra verifichiamo gli eventuali errori che ci porteranno a correggere il codice che ha generato il report.
Il codice utilizzato in questo articolo è disponibile qui
