/// <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;
}
