/// <summary> /// Creates an exported sheet of a specific configuration /// </summary> /// <typeparam name="T"></typeparam> /// <param name="exportConfiguration"></param> /// <param name="columns"></param> /// <returns></returns> private SheetData CreateExportSheet <T>(SpreadsheetConfiguration <T> exportConfiguration, out Columns columns) where T : class { //Build out our sheet information var data = new SheetData(); UInt32 currentRow = 1; if (exportConfiguration.RenderTitle) { var row = new Row { RowIndex = 1 }; var headerCell = new Cell { CellReference = $"A{currentRow}", CellValue = new CellValue(exportConfiguration.DocumentTitle), DataType = CellValues.String, StyleIndex = (int)FontStyleIndex.Header }; row.Append(headerCell); data.Append(row); //Increment row currentRow++; } if (exportConfiguration.RenderSubTitle) { var row = new Row { RowIndex = currentRow }; var headerCell = new Cell { CellReference = $"A{currentRow}", CellValue = new CellValue(exportConfiguration.DocumentSubTitle), DataType = CellValues.String, StyleIndex = (int)FontStyleIndex.SubHeader }; row.Append(headerCell); data.Append(row); //Increment row currentRow++; } //Run data headers var headerProperties = TypeDescriptor.GetProperties(typeof(T)); var headerRow = new Row { RowIndex = currentRow }; var customFormats = new Dictionary <string, UInt32Value>(); foreach (PropertyDescriptor prop in headerProperties) { var headerCell = new Cell { CellValue = new CellValue(prop.DisplayName), DataType = CellValues.String, StyleIndex = (int)FontStyleIndex.DataHeader }; headerRow.Append(headerCell); //Handle formats if (prop.Attributes.Count <= 0) { continue; } foreach (var attribute in prop.Attributes) { if (!(attribute is SpreadsheetColumnFormatAttribute detail)) { continue; } switch (detail.Format.ToLowerInvariant()) { case "d": customFormats.Add(prop.DisplayName, (int)FontStyleIndex.NormalDate); break; case "c": customFormats.Add(prop.DisplayName, (int)FontStyleIndex.NormalCurrency); break; } break; } } data.Append(headerRow); currentRow++; //Run the data foreach (var item in exportConfiguration.ExportData) { var dataRow = new Row { RowIndex = currentRow }; foreach (PropertyDescriptor prop in headerProperties) { var itemValue = prop.GetValue(item); var dataCell = new Cell { CellValue = new CellValue(itemValue?.ToString()), DataType = CellValues.String }; if (customFormats.ContainsKey(prop.DisplayName)) { dataCell.StyleIndex = customFormats[prop.DisplayName]; if (dataCell.StyleIndex == 4) { dataCell.DataType = CellValues.Number; dataCell.CellValue = new CellValue(decimal.Parse(itemValue?.ToString())); } else if (dataCell.StyleIndex == 5) //Date { dataCell.CellValue = new CellValue(DateTime.Parse(itemValue.ToString()).ToShortDateString()); } } dataRow.Append(dataCell); } data.Append(dataRow); currentRow++; } //Auto-size columns = AutoSize(data); return(data); }
/// <summary> /// Creates a single worksheet document using the provided configuration information /// </summary> /// <typeparam name="T">The object type for exporting</typeparam> /// <param name="exportConfiguration">The loaded configuration</param> /// <returns>A completed MS Excel file</returns> public byte[] CreateSingleSheetSpreadsheet <T>(SpreadsheetConfiguration <T> exportConfiguration) where T : class { //Validate input if (string.IsNullOrWhiteSpace(exportConfiguration.WorksheetName)) { throw new ArgumentException("Worksheet name must be supplied", nameof(exportConfiguration.WorksheetName)); } if (exportConfiguration.ExportData == null) { throw new ArgumentException("Export data must be specified", nameof(exportConfiguration.ExportData)); } if (exportConfiguration.RenderTitle && string.IsNullOrEmpty(exportConfiguration.DocumentTitle)) { throw new ArgumentException("Document Title is required when 'Render Title' is true", nameof(exportConfiguration.DocumentTitle)); } if (exportConfiguration.RenderSubTitle && string.IsNullOrEmpty(exportConfiguration.DocumentSubTitle)) { throw new ArgumentException("Document Sub Title is required when 'Render Sub Title' is true", nameof(exportConfiguration.DocumentSubTitle)); } //Setup a memory stream to hold the generated file using (var documentStream = new MemoryStream()) { //Create the document & overall workbook var spreadsheetDocument = SpreadsheetDocument.Create(documentStream, SpreadsheetDocumentType.Workbook); var workbookPart = spreadsheetDocument.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); //Setup our styles var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>(); stylesPart.Stylesheet = CreateStylesheet(); stylesPart.Stylesheet.Save(); var data = CreateExportSheet(exportConfiguration, out var columns); //Add a worksheet to our document var worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); worksheetPart.Worksheet.Append(columns); worksheetPart.Worksheet.Append(data); //Add the sheet to the workbook var sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()); var sheet = new Sheet { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = exportConfiguration.WorksheetName }; sheets.Append(sheet); workbookPart.Workbook.Save(); spreadsheetDocument.Close(); //Return the bytearray documentStream.Seek(0, SeekOrigin.Begin); return(documentStream.ToArray()); } }