/// <summary> /// Adds new excel sheet. /// </summary> /// <param name="reportRenderer">Report renderer.</param> /// <param name="reportRows">Report rows.</param> /// <param name="fillCellReferences">Fill cell references after sheet builded.</param> /// <returns>Builder instance.</returns> public ExcelReportBuilder AddReportSheet( IReportRenderer reportRenderer, IEnumerable <IPropertyContainer> reportRows, bool fillCellReferences = true) { reportRenderer.AssertArgumentNotNull(nameof(reportRenderer)); reportRows.AssertArgumentNotNull(nameof(reportRows)); var sheetMetadata = reportRenderer.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, reportRenderer); AddSheet(sheetContext); AddSheetData(sheetContext, reportRows); if (fillCellReferences) { sheetContext.SheetElement.FillCellReferences(forceFill: fillCellReferences); } // External customization var configureSheet = ExcelSheetMetadata.ConfigureSheet.GetFirstDefinedValue( sheetContext.SheetMetadata, _documentMetadata); configureSheet?.Invoke(sheetContext); return(this); }
/// <summary> /// Initializes a new instance of the <see cref="ColumnContext"/> class. /// </summary> /// <param name="sheetContext">Owner sheet context.</param> /// <param name="columnMetadata">Column metadata.</param> /// <param name="propertyRenderer"><see cref="IPropertyRenderer"/> to render column cells.</param> public ColumnContext( SheetContext sheetContext, IExcelMetadata columnMetadata, IPropertyRenderer propertyRenderer) { SheetContext = sheetContext.AssertArgumentNotNull(nameof(sheetContext)); ColumnMetadata = columnMetadata.AssertArgumentNotNull(nameof(columnMetadata)); PropertyRenderer = propertyRenderer.AssertArgumentNotNull(nameof(propertyRenderer)); }
public ExcelReportBuilder AddSheetDataTBD( IReportRenderer reportRenderer, IEnumerable <IPropertyContainer> reportRows, bool fillCellReferences = true) { var sheetMetadata = reportRenderer.GetMetadata <ExcelSheetMetadata>() ?? _defaultSheetMetadata; // FIND ALL PARTS!!! WorkbookPart workbookPart = _documentContext.Document.WorkbookPart; WorksheetPart worksheetPart = workbookPart.GetPartsOfType <WorksheetPart>().FirstOrDefault(); Sheets sheets = workbookPart.Workbook.Sheets; var sheetContext = new SheetContext(_documentContext, worksheetPart, sheetMetadata, reportRenderer); sheetContext.SheetData = new SheetData(); return(this); }
private void AddSheetData( SheetContext sheetContext, IEnumerable <IPropertyContainer> dataRows) { SheetData sheetData = sheetContext.SheetData; var columns = sheetContext.Columns; var configureRow = ExcelSheetMetadata.ConfigureRow.GetFirstDefinedValue( sheetContext.SheetMetadata, sheetContext.DocumentContext.DocumentMetadata); if (sheetContext.IsNotTransposed) { // HEADER ROW var headerCells = columns.Select(ConstructHeaderCell); sheetData.AppendChild(new Row(headerCells)); // DATA ROWS foreach (var dataRow in dataRows) { // Create cells CellContext[] cellContexts = new CellContext[columns.Count]; for (var i = 0; i < columns.Count; i++) { var columnContext = columns[i]; cellContexts[i] = ConstructCell(columnContext, dataRow, callCustomize: false); } // Create row Row excelRow = new Row(cellContexts.Select(context => context.Cell)); // Customize row configureRow?.Invoke(new RowContext(cellContexts, excelRow, dataRow)); // Customize cells for (var i = 0; i < columns.Count; i++) { CellContext cellContext = cellContexts[i]; var configureCell = ExcelCellMetadata.ConfigureCell.GetFirstDefinedValue( cellContext.CellMetadata, cellContext.ColumnContext.ColumnMetadata, sheetContext.SheetMetadata, sheetContext.DocumentContext.DocumentMetadata); configureCell?.Invoke(cellContext); } sheetData.AppendChild(excelRow); } } else { // NAME COLUMN var headerCells = columns.Select(ConstructHeaderCell); Row[] excelRows = headerCells.Select(headerCell => new Row(headerCell)).ToArray(); // VALUE COLUMN dataRows = dataRows.ToArray(); foreach (var dataRow in dataRows) { for (var index = 0; index < columns.Count; index++) { var column = columns[index]; Row row = excelRows[index]; Cell cell = ConstructCell(column, dataRow).Cell; row.AppendChild(cell); } } foreach (Row row in excelRows) { 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) { // FreezeMetadata for optimization renderer.FreezeMetadata(); return(new ColumnContext( sheetContext, renderer.GetMetadata <ExcelColumnMetadata>() ?? _defaultColumnMetadata, renderer)); } sheetContext.Columns = sheetContext .ReportRenderer .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); 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.ReportRenderer.ReportName, }; sheets.Append(sheet); bool freezeTopRow = ExcelMetadata.FreezeTopRow.GetFirstDefinedValue( sheetContext.SheetMetadata, sheetContext.DocumentMetadata); if (freezeTopRow) { worksheet.FreezeTopRow(rowNum: 1); } sheetContext.SheetData = sheetData; sheetContext.Sheet = sheet; }