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