As part of our development activities, both for customers and internal projects, we have often faced the need to create reports containing data of different types. The format we usually use – which is also the most requested one- for these reports is definitely Office Open XML: it is read and processed by the spreadsheet software of the main office suites and ensures the quality and lightness of the document.
In the .NET world, there are several third-party plugins allow us to create Office Open XML files. After tested some of them, we decided to opt for Open XML SDK, which seemed to us the best choice in terms of functionality, performance and configurability and, last but not least, because it natively allows the use of the Office Open XML format.
Some notes on Office Open XML
The Office Open XML format is a standard for text documents, presentations, and spreadsheets, and it can be implemented by multiple applications on different platforms. The main purpose of the Open XML format is to decouple the information from the software that creates it, without loss of data. An Open XML file is structured in a text file created with an XML markup, stored in a ZIP archive and consists of several parts, depending on the type of document.
In the case of spreadsheets, the Open XML document can contain:
- Parts of the document (mandatory)
- One or more worksheets
- Charts
- Tables
- Custom XML
Open XML SDK
Open XML SDK is an open-source library, released under the Apache 2.0 license by Microsoft for developers, which provides namespaces and members to support the most common office software, including – of course – Microsoft Office.
Official documentation is available at this link: https://docs.microsoft.com/en-us/office/open-xml/open-xml-sdk
Document creation
In the following example, we reported a real scenario concerning the creation of a product report of a store.
We created the ColumnAttribute class, which inherits from System.Attribute and defines a set of attributes related to a single column. This annotation will be added to single properties of class/classes on which the report is based.
/// <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; }
}
Fields without ColumnAttribute annotation will be ignored and not exported.
In order to centralize the creation of Office Open XML spreadsheets, we have created the OpenXMLProvider class. It provides us a generic GenerateWorksheet method that allows us to take as input a list of elements, to exclude some fields and to define the name of the main sheet.
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);
}
}
The core of our solution is certainly the OpenXMLWorksheetBuilder class, which allows us to create an Office Open XML spreadsheet by writing native XML code inside the sheet.
Before writing the list of our products in the file, we should define the styles of the file, using the GenerateWorkbookStylesPartContent method., At first glance, it may seem a bit mind-bending, but it allows us to create a list of styles and formats to be used in our spreadsheet.
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 this method we defined, in order:
- Spreadsheets and main namespaces
- Numerical formats and dates
- Fonts
- Cell colors
- Edges
- Cell formats
The cell formats are those that will be used in formatting our cells. Depending on the order used to insert them, the formats take a progressive Uint Id, so the first will have id 0U, the second 1U, etc.
From our cell formats we can create a new enumeration type, so that we can use formatting styles more easily:
public enum CustomStyles
{
Base = 0,
Normal = 1,
Header = 2,
Date = 3,
Time = 4,
DateTime = 5,
Currency = 6,
Percentage = 7
}
Once the styles of our sheet have been inserted, we can start writing data inside it. First, let’s start from the column headings, using the CreateHeaderCell method:
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;
}
We also write data cells by using following generic method called 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 processes the cell content and compares it with the attributes defined for the corresponding column, in order to choose the best formatting.
In our case, for instance, the values of type decimal defined with the IsCurrency attribute are processed differently from those with the IsPercentage attribute, to obtain two different and congruous formatting for the same type.
The same goes for dates, which by default are complete (with date and time), but if necessary they can be formatted date only or time only, depending on the IsDate or IsTime attributes.
Then, we can add metadata to our file, using the SetDocumentMetadata method:
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>
here the Creator attributes is set by configuration file parameter.
All classes and methods introduced up to now are used by our generic method 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 this method, after creating the workbook object and the styles, we proceed to write headers and data.
To write cells quickly and efficiently, we proceed in this way:
- Opening external tag using OpenXmlWriter.WriteStartElement
- Possible adding of attributes using the OpenXmlAttributeclass (we suggest to use them to have best readability in XML)
- Writing cell value by using OpenXmlWriter.WriteElement
- Closing external tag with OpenXmlWriter.WriteEndElement
Concerning the web side, API controller ProductsController has only one method, GetExport, that, in this case, takes as input the number of products to create and gives back a stream of data containing the Open XML file.
The product list will be dynamically generated by using Bogus library, that allows you to create fake objects and is useful for our purpose.
[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");
}
Project execution
For example purpose, we make a simple GUI, in which you can insert the number of elements, and, after clicking on Download Report button, download the file.
Running the project, we can obtain this screen:
Once downloaded the file, we can open it and see the content:
Validating file with Open XML Productivity Tool
In generating more or less complex files, it can often happen that they are corrupt or even illegible. When we open the file, an unpleasant alert comes up, warning us that we cannot read our file and that it will try to recover it.
To overcome this problem and know what is wrong in creating the file, there is Open XML Productivity Tool, which scans our file and checks its syntactical and semantic correctness.
The tool is easy to use: just open it, select a file, and after opening it, click Validate. In the column on the left, we are shown the Worksheet parts, and on the right, there are indicated warnings and errors. Usually, the warnings generate an alert for “damaged file”, but still readable, which instead it does not happen for files with errors.
Scrolling down the section on the right, we can eventually verify the errors that will make us correct code that generates the report.
The code used in this article is available here.
