/// <summary> /// Adds new excel sheet. /// </summary> /// <param name="reportProvider">Report provider.</param> /// <param name="reportRows">Report rows.</param> /// <returns>Builder instance.</returns> public ExcelReportBuilder AddReportSheet(IReportProvider reportProvider, IEnumerable <IPropertyContainer> reportRows) { reportProvider.AssertArgumentNotNull(nameof(reportProvider)); reportRows.AssertArgumentNotNull(nameof(reportRows)); var sheetMetadata = reportProvider.GetMetadata <ExcelSheetMetadata>() ?? _defaultSheetMetadata; // Add a WorksheetPart to the WorkbookPart. WorkbookPart workbookPart = _documentContext.Document.WorkbookPart; uint sheetCount = workbookPart.GetSheetCount(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>($"sheet{sheetCount+1}"); var sheetContext = new SheetContext(_documentContext, worksheetPart, sheetMetadata, reportProvider); AddSheet(sheetContext); AddSheetData(sheetContext, reportRows); // External customization var customizeFunc = sheetContext.SheetMetadata?.GetValue(ExcelSheetMetadata.ConfigureSheet); customizeFunc?.Invoke(sheetContext); return(this); }
private void AddSheetData( SheetContext sheetContext, IEnumerable <IPropertyContainer> dataRows) { SheetData sheetData = sheetContext.SheetData; var columns = sheetContext.Columns; if (sheetContext.IsNotTransposed) { // HEADER ROW var headerCells = columns.Select(column => ConstructCell(column.PropertyRenderer.TargetName, CellValues.SharedString)); sheetData.AppendChild(new Row(headerCells)); // DATA ROWS foreach (var item in dataRows) { var valueCells = columns.Select(columnContext => ConstructCell(columnContext, item)); sheetData.AppendChild(new Row(valueCells)); } } else { // NAME COLUMN var headerCells = columns.Select(columnContext => ConstructCell(columnContext.PropertyRenderer.TargetName, CellValues.SharedString)); Row[] rows = headerCells.Select(headerCell => new Row(headerCell)).ToArray(); // VALUE COLUMN for (int rowIndex = 0; rowIndex < rows.Length; rowIndex++) { var column = columns[rowIndex]; foreach (var item in dataRows) { ConstructCell(column, item); } } foreach (var item in dataRows) { for (var index = 0; index < columns.Count; index++) { var column = columns[index]; Row row = rows[index]; Cell cell = ConstructCell(column, item); row.AppendChild(cell); } } foreach (Row row in rows) { sheetData.AppendChild(row); } } }
private void AddSheet(SheetContext sheetContext) { WorkbookPart workbookPart = sheetContext.DocumentContext.WorkbookPart; WorksheetPart worksheetPart = sheetContext.WorksheetPart; uint sheetCount = workbookPart.GetSheetCount(); Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac xr xr2 xr3" } }; worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006"); worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"); worksheet.AddNamespaceDeclaration("xr", "http://schemas.microsoft.com/office/spreadsheetml/2014/revision"); worksheet.AddNamespaceDeclaration("xr2", "http://schemas.microsoft.com/office/spreadsheetml/2015/revision2"); worksheet.AddNamespaceDeclaration("xr3", "http://schemas.microsoft.com/office/spreadsheetml/2016/revision3"); worksheetPart.Worksheet = worksheet; SheetViews sheetViews = worksheet.GetOrCreateSheetViews(); SheetView sheetView = new SheetView { WorkbookViewId = (UInt32Value)0U }; if (sheetCount == 0) { sheetView.TabSelected = true; } sheetViews.AppendChild(sheetView); SheetFormatProperties sheetFormatProperties = new SheetFormatProperties { DefaultRowHeight = 15D, DyDescent = 0.25D, }; ColumnContext CreateColumnContext(IPropertyRenderer renderer) => new ColumnContext( sheetContext, renderer.GetMetadata <ExcelColumnMetadata>() ?? _defaultColumnMetadata, renderer); sheetContext.Columns = sheetContext .ReportProvider .Renderers .Select(CreateColumnContext) .ToList(); Columns columns = sheetContext.IsNotTransposed ? CreateColumns(sheetContext.Columns) : CreateColumnsTransposed(); SheetData sheetData = new SheetData(); //workSheet.Append(sheetDimension); worksheet.Append(sheetViews); worksheet.Append(sheetFormatProperties); if (columns != null) { worksheet.Append(columns); } worksheet.Append(sheetData); //workSheet.Append(pageMargins); // Append a new worksheet and associate it with the workbook. Sheets sheets = workbookPart.Workbook.Sheets; Sheet sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = sheetCount + 1, Name = sheetContext.ReportProvider.ReportName, }; sheets.Append(sheet); bool freezeTopRow = ExcelMetadata.GetFirstDefinedValue( ExcelMetadata.FreezeTopRow, sheetContext.SheetMetadata, sheetContext.DocumentMetadata); if (freezeTopRow) { worksheet.FreezeTopRow(rowNum: 1); } sheetContext.SheetData = sheetData; sheetContext.Sheet = sheet; }
public ColumnContext(SheetContext sheetContext, IExcelMetadata columnMetadata, IPropertyRenderer propertyRenderer) { SheetContext = sheetContext.AssertArgumentNotNull(nameof(sheetContext)); ColumnMetadata = columnMetadata.AssertArgumentNotNull(nameof(columnMetadata)); PropertyRenderer = propertyRenderer.AssertArgumentNotNull(nameof(propertyRenderer)); }