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