I need to create large generic xlsx reports (over 500k rows with ~50columns). I wrote method which generates this reports, but i can't figure out how set up cells' format (data format/style (borders etc)) and how put formulas to cells using OpenXmlWriter.
private void FillExcelWorksheet<T>(String fileName, IEnumerable<T> source)
{
var properties = typeof(T).GetProperties();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
//this list of attributes will be used when writing a start element
List<OpenXmlAttribute> stringAttributes = new List<OpenXmlAttribute>()
{
new OpenXmlAttribute("t", null, "str")
};
OpenXmlWriter writer;
var workBookPart = document.AddWorkbookPart();
var workSheetPart = workBookPart.AddNewPart<WorksheetPart>();
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
writer.WriteStartElement(new Row());
foreach (PropertyInfo property in properties)
{
var pAttributes = property.GetCustomAttributes<ReportColumnAttribute>();
if (pAttributes.Any())
{
var attribute = pAttributes.First();
writer.WriteStartElement(new Cell(), stringAttributes);
//write the cell value
writer.WriteElement(new CellValue(attribute.ColumnName));
// write the end cell element
writer.WriteEndElement();
}
}
// write the end row element
writer.WriteEndElement();
foreach (var item in source)
{
//write the row start element with the row index attribute
writer.WriteStartElement(new Row());
foreach (PropertyInfo property in properties)
{
var pAttributes = property.GetCustomAttributes<ReportColumnAttribute>();
if (pAttributes.Any())
{
//write the cell start element with the type and reference attributes
writer.WriteStartElement(new Cell(), stringAttributes);
//write the cell value
writer.WriteElement(new CellValue(property.GetValue(item) != null ?
property.GetValue(item).ToString() : String.Empty));
var attribute = pAttributes.First();
var format = attribute.Format;
//todo apply data format
// write the end cell element
writer.WriteEndElement();
}
}
// write the end row element
writer.WriteEndElement();
}
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = fileName,
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();
}
}
result with styles
private DownloadResult FillExcelWorksheet<T>(String name, IEnumerable<T> source, Action<OpenXmlWriter, WorkbookStylesPart, SharedStringTablePart, Int32> addNonGenericReportInfo = null)
{
var properties = typeof(T).GetProperties();
var folder = ConfigurationManager.AppSettings["ReportsPath"];
if (!Directory.Exists(folder)) {
Directory.CreateDirectory(folder);
}
//remove old files
Directory.GetFiles(folder)
.Select(f => new FileInfo(f))
.Where(f => f.CreationTime < DateTime.Now.AddDays(-1))
.ToList()
.ForEach(f => f.Delete());
var fileName = String.Format("{0}_{1}.xlsx", name, Guid.NewGuid());
using (var document = SpreadsheetDocument.Create(folder + fileName, SpreadsheetDocumentType.Workbook))
{
//this list of attributes will be used when writing a start element
List<OpenXmlAttribute> stringAttributes = new List<OpenXmlAttribute>()
{
new OpenXmlAttribute("t", null, "str")
};
OpenXmlWriter writer;
var workBookPart = document.AddWorkbookPart();
var workSheetPart = workBookPart.AddNewPart<WorksheetPart>();
var sharedStringPart = workBookPart.AddNewPart<SharedStringTablePart>();
sharedStringPart.SharedStringTable = new SharedStringTable();
var stylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = new Stylesheet();
stylesPart.Stylesheet.NumberingFormats = new NumberingFormats();
// create fills
stylesPart.Stylesheet.Fills = new Fills();
stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.None } }); // required, reserved by Excel
//EXAMPLE DON'T REMOVE
//stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = new PatternFill { PatternType = PatternValues.Gray125 } }); // required, reserved by Excel
//// create a solid red fill
//var solidRed = new PatternFill() { PatternType = PatternValues.Solid };
//solidRed.ForegroundColor = new ForegroundColor { Rgb = HexBinaryValue.FromString("FFFF0000") }; // red fill
//solidRed.BackgroundColor = new BackgroundColor { Indexed = 64 };
//stylesPart.Stylesheet.Fills.AppendChild(new Fill { PatternFill = solidRed });
stylesPart.Stylesheet.Fills.Count = 3;
// blank cell format list
stylesPart.Stylesheet.CellStyleFormats = new CellStyleFormats();
stylesPart.Stylesheet.CellStyleFormats.AppendChild(new CellFormat());
stylesPart.Stylesheet.CellStyleFormats.Count = 1;
//Borders
stylesPart.Stylesheet.Borders = new Borders();
stylesPart.Stylesheet.Borders.AppendChild(new Border());
var border = new Border();
border.Append(new BottomBorder() { Style = BorderStyleValues.Thin });
stylesPart.Stylesheet.Borders.AppendChild(border);
stylesPart.Stylesheet.Borders.Count = 2;
//Fonts
stylesPart.Stylesheet.Fonts = new Fonts();
stylesPart.Stylesheet.Fonts.AppendChild(new Font());
var font = new Font();
font.Bold = new Bold();
stylesPart.Stylesheet.Fonts.AppendChild(font);
stylesPart.Stylesheet.Fonts.Count = 2;
// cell format list
stylesPart.Stylesheet.CellFormats = new CellFormats();
// empty one for index 0, seems to be required
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat());
// cell format references style format 0, font 0, border 0, fill 2 and applies the fill
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { FormatId = 0, FontId = 1, BorderId = 1, FillId = 0, ApplyFill = true }).AppendChild(new Alignment { Horizontal = HorizontalAlignmentValues.Center });
stylesPart.Stylesheet.CellFormats.Count = 2;
writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
writer.WriteStartElement(new Row());
foreach (PropertyInfo property in properties)
{
var pAttributes = property.GetCustomAttributes<ReportColumnAttribute>();
if (pAttributes.Any())
{
var attribute = pAttributes.First();
var cell = new Cell();
cell.DataType = CellValues.SharedString;
cell.StyleIndex = 1;
writer.WriteStartElement(cell);
//write the cell value
var index = InsertSharedStringItem(attribute.ColumnName, sharedStringPart);
writer.WriteElement(new CellValue(index.ToString()));
// write the end cell element
writer.WriteEndElement();
if (!String.IsNullOrEmpty(attribute.Format) && !_styleFormats.ContainsKey(attribute.Format))
{
//164 is first free format id
var formatId = 164 + (uint)_styleFormats.Count;
var format = new NumberingFormat { NumberFormatId = formatId, FormatCode = attribute.Format };
stylesPart.Stylesheet.NumberingFormats.AppendChild(format);
stylesPart.Stylesheet.CellFormats.AppendChild(new CellFormat { NumberFormatId = formatId, FontId = 0, BorderId = 0, FillId = 0, ApplyFill = true });
//stylesPart.Stylesheet.CellFormats.AppendChild(format);
stylesPart.Stylesheet.CellStyleFormats.Count++;
_styleFormats.Add(attribute.Format, new StyleIndexes(stylesPart.Stylesheet.CellFormats.Count, formatId));
stylesPart.Stylesheet.CellFormats.Count++;
}
}
}
// write the end row element
writer.WriteEndElement();
foreach (var item in source)
{
//write the row start element with the row index attribute
writer.WriteStartElement(new Row());
foreach (PropertyInfo property in properties)
{
var pAttributes = property.GetCustomAttributes<ReportColumnAttribute>();
if (pAttributes.Any())
{
var attribute = pAttributes.First();
var cell = new Cell();
if (!String.IsNullOrEmpty(attribute.Format))
{
cell.StyleIndex = (uint)_styleFormats[attribute.Format].StyleIndex;
}
var typeCode = Type.GetTypeCode(property.PropertyType);
//write the cell start element with the type and reference attributes
if (typeCode == TypeCode.String)
{
writer.WriteStartElement(cell, stringAttributes);
}
else
{
cell.DataType = CellValues.Number;
writer.WriteStartElement(cell);
}
var value = String.Empty;
if (typeCode == TypeCode.DateTime)
{
value = property.GetValue(item) != null ?
Convert.ToDateTime(property.GetValue(item)).ToOADate().ToString() : String.Empty;
}
else {
value = property.GetValue(item) != null ?
property.GetValue(item).ToString() : String.Empty;
}
//write the cell value
writer.WriteElement(new CellValue(value));
// write the end cell element
writer.WriteEndElement();
}
}
// write the end row element
writer.WriteEndElement();
}
if (addNonGenericReportInfo != null)
{
addNonGenericReportInfo(writer, stylesPart, sharedStringPart, source.Count());
}
sharedStringPart.SharedStringTable.Save();
stylesPart.Stylesheet.Save();
// write the end SheetData element
writer.WriteEndElement();
// write the end Worksheet element
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = name,
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
// End Sheets
writer.WriteEndElement();
// End Workbook
writer.WriteEndElement();
writer.Close();
document.Close();
}
return new DownloadResult
{
Name = name + ".xlsx",
PathToDownload = folder + fileName,
};
}
private static int InsertSharedStringItem(string text, SharedStringTablePart shareStringPart)
{
// If the part does not contain a SharedStringTable, create one.
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
int i = 0;
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in shareStringPart.SharedStringTable.Elements<SharedStringItem>())
{
if (item.InnerText == text)
{
return i;
}
i++;
}
// The text does not exist in the part. Create the SharedStringItem and return its index.
shareStringPart.SharedStringTable.AppendChild(new SharedStringItem(new DocumentFormat.OpenXml.Spreadsheet.Text(text)));
shareStringPart.SharedStringTable.Save();
return i;
}
private Dictionary<String, StyleIndexes> _styleFormats;
private class StyleIndexes
{
public StyleIndexes(uint styleIndex, uint numberIndex)
{
StyleIndex = styleIndex;
NumberIndex = numberIndex;
}
public uint StyleIndex { get; set; }
public uint NumberIndex { get; set; }
}