/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="def">A sheet definition used to create the spreadsheet</param>
public static void Create <T>(
string fileName,
SheetDefinition <T> def)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
// create workbook
var workbook = new Workbook();
// add work sheet
var sheets = new Sheets();
sheets.AppendChild(CreateSheet(1, def, workbookPart));
workbook.AppendChild(sheets);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private static Sheet CreateSheet <T>(int sheetIndex, SheetDefinition <T> def, WorkbookPart workbookPart)
{
// create worksheet part
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var worksheetId = workbookPart.GetIdOfPart(worksheetPart);
// variables
var numCols = def.Fields.Count;
var numRows = def.Objects.Count;
var az = new List <Char>(Enumerable.Range('A', 'Z' - 'A' + 1).Select(i => (Char)i).ToArray());
var headerCols = az.GetRange(0, numCols);
var hasTitleRow = def.Title != null;
var hasSubtitleRow = def.SubTitle != null;
var titleRowCount = hasTitleRow ? 1 + (hasSubtitleRow ? 1 : 0) : hasSubtitleRow ? 1 : 0;
// get the worksheet data
int firstTableRow;
var sheetData = CreateSheetData(def.Objects, def.Fields, headerCols, def.IncludeTotalsRow, def.Title,
def.SubTitle,
out firstTableRow);
// populate column metadata
var columns = new Columns();
for (var col = 0; col < numCols; col++)
{
var width = ColumnWidth(sheetData, col, titleRowCount);
columns.AppendChild(CreateColumnMetadata((UInt32)col + 1, (UInt32)numCols + 1, width));
}
// populate worksheet
var worksheet = new Worksheet();
worksheet.AppendChild(columns);
worksheet.AppendChild(sheetData);
// add an auto filter
worksheet.AppendChild(new AutoFilter
{
Reference =
String.Format("{0}{1}:{2}{3}", headerCols.First(), firstTableRow - 1, headerCols.Last(),
numRows + titleRowCount + 1)
});
// add worksheet to worksheet part
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
return(new Sheet {
Name = def.Name, SheetId = (UInt32)sheetIndex, Id = worksheetId
});
}
