private static string Format( this DateTime value, DateTimeFormat dateTimeFormat, ReportToWorkbookProjectionContext context) { var cultureKind = dateTimeFormat?.CultureKind ?? context.CultureKind ?? CultureKind.Invariant; var dateTimeFormatKind = dateTimeFormat?.FormatKind ?? DateTimeFormatKind.FullDateTimePatternShortTime; var localizeTimeZone = dateTimeFormat?.LocalizeTimeZone ?? false; var localTimeZone = dateTimeFormat?.LocalTimeZone ?? context.LocalTimeZone ?? StandardTimeZone.Unknown; if (localizeTimeZone && (localTimeZone == StandardTimeZone.Unknown)) { throw new InvalidOperationException("Cannot localize time zone of timestamp unless the local time zone is specified."); } if (localizeTimeZone) { value = TimeZoneInfo.ConvertTimeFromUtc(value, localTimeZone.ToTimeZoneInfo()); } var result = value.ToString(dateTimeFormatKind, cultureKind); return(result); }
private static void AddRowBase( this CellCursor cursor, RowBase rowBase, ReportToWorkbookProjectionContext context, PassKind passKind) { if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyRowFormat(rowBase.Format); } for (var x = 0; x < rowBase.Cells.Count; x++) { if (x > 0) { cursor.MoveRight(rowBase.Cells[x - 1].ColumnsSpanned ?? 1); } if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyRowFormat(rowBase.Format); } cursor.AddCell(rowBase.Cells[x], context, passKind); } }
private static void AddDataRows( this CellCursor cursor, DataRows dataRows, ReportToWorkbookProjectionContext context, PassKind passKind) { if (dataRows == null) { return; } if (dataRows.Rows.Any()) { for (var x = 0; x < dataRows.Rows.Count; x++) { cursor.ResetColumn(); cursor.MoveDown(); if (x == 0) { cursor.AddMarker(TopLeftDataCellMarker); } if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyDataRowsFormat(dataRows.Format); } cursor.AddRow(dataRows.Rows[x], context, passKind, dataRows.Format); } cursor.AddMarker(BottomRightDataCellMarker); } }
private static void AddFooterRows( this CellCursor cursor, FooterRows footerRows, ReportToWorkbookProjectionContext context, PassKind passKind) { if (footerRows == null) { return; } if (footerRows.Rows.Any()) { foreach (var flatRow in footerRows.Rows) { cursor.ResetColumn(); cursor.MoveDown(); if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyFooterRowsFormat(footerRows.Format); } cursor.AddFlatRow(flatRow, context, passKind); } } }
private static void AddFlatRow( this CellCursor cursor, FlatRow flatRow, ReportToWorkbookProjectionContext context, PassKind passKind) { cursor.AddRowBase(flatRow, context, passKind); }
private static void AddCell( this CellCursor cursor, ICell cell, ReportToWorkbookProjectionContext context, PassKind passKind) { if (passKind == PassKind.Data) { if ((cell.ColumnsSpanned ?? 1) > 1) { // ReSharper disable once PossibleInvalidOperationException var mergeRange = cursor.Worksheet.GetRange(cursor.RowNumber, cursor.RowNumber, cursor.ColumnNumber, cursor.ColumnNumber + (int)cell.ColumnsSpanned - 1); mergeRange.SetMergeCells(true); } } if ((passKind == PassKind.Formatting) && (cell is IHaveCellFormat haveCellFormat)) { cursor.CellRange.ApplyCellFormat(haveCellFormat.Format); } var notSupportedException = new NotSupportedException(Invariant($"This type of cell is not supported: {cell.GetType().ToStringReadable()}.")); if (cell is INotSlottedCell) { if (cell is INullCell) { } else if (cell is ConstCell <string> stringConstCell) { if (passKind == PassKind.Data) { cursor.Cell.Value = stringConstCell.Value; } } else if (cell is ConstCell <int> intConstCell) { if (passKind == PassKind.Data) { cursor.Cell.Value = intConstCell.Value; } } else { throw notSupportedException; } } else if (cell is ISlottedCell) { throw notSupportedException; } else { throw notSupportedException; } }
private static void AddRow( this CellCursor cursor, DataStructure.Row row, ReportToWorkbookProjectionContext context, PassKind passKind, DataRowsFormat dataRowsFormat) { cursor.AddRowBase(row, context, passKind); if (cursor.HasMarker(BottomRightNonSummaryDataCellMarker)) { cursor.RemoveMarker(BottomRightNonSummaryDataCellMarker); } cursor.AddMarker(BottomRightNonSummaryDataCellMarker); if ((row.ChildRows != null) && row.ChildRows.Any()) { foreach (var childRow in row.ChildRows) { cursor.ResetColumn(); cursor.MoveDown(); if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyDataRowsFormat(dataRowsFormat); } cursor.AddRow(childRow, context, passKind, dataRowsFormat); } } if (row.CollapsedSummaryRows != null && row.CollapsedSummaryRows.Any()) { throw new NotSupportedException("collapsed summary rows are not supported"); } if (row.ExpandedSummaryRows != null) { foreach (var expandedSummaryRow in row.ExpandedSummaryRows) { cursor.ResetColumn(); cursor.MoveDown(); if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyDataRowsFormat(dataRowsFormat); } cursor.AddFlatRow(expandedSummaryRow, context, passKind); } } }
private static void AddTreeTable( this CellCursor cursor, TreeTable treeTable, ReportToWorkbookProjectionContext context, PassKind passKind) { if (passKind == PassKind.Formatting) { // Format whole table cursor.CanvassedRange.ApplyTableFormat(treeTable.Format); // Format columns var tableColumns = treeTable.TableColumns; var topLeftDataCellReference = cursor.HasMarker(TopLeftDataCellMarker) ? cursor.GetMarkedCellReference(TopLeftDataCellMarker) : null; var bottomRightNonSummaryDataCellReference = cursor.HasMarker(BottomRightNonSummaryDataCellMarker) ? cursor.GetMarkedCellReference(BottomRightNonSummaryDataCellMarker) : null; var bottomRightHeaderCellReference = cursor.HasMarker(BottomRightHeaderCellMarker) ? cursor.GetMarkedCellReference(BottomRightHeaderCellMarker) : null; foreach (var column in tableColumns.Columns) { var wholeColumnRange = cursor.Worksheet.GetRange(cursor.RowNumber, cursor.MaxRowNumber, cursor.ColumnNumber, cursor.ColumnNumber); // ReSharper disable once PossibleNullReferenceException var dataCellsRange = topLeftDataCellReference == null ? null : cursor.Worksheet.GetRange(topLeftDataCellReference.RowNumber, bottomRightNonSummaryDataCellReference.RowNumber, cursor.ColumnNumber, cursor.ColumnNumber); var lastHeaderCellToLastNonSummaryDataCellRange = bottomRightHeaderCellReference == null ? null : dataCellsRange == null ? null : cursor.Worksheet.GetRange(bottomRightHeaderCellReference.RowNumber, bottomRightNonSummaryDataCellReference.RowNumber, cursor.ColumnNumber, cursor.ColumnNumber); // Individual column format will override the format applied to all table columns, // that's why we first apply the table columns format and then the individual column's format. wholeColumnRange.ApplyColumnFormat(dataCellsRange, lastHeaderCellToLastNonSummaryDataCellRange, tableColumns.ColumnsFormat); wholeColumnRange.ApplyColumnFormat(dataCellsRange, lastHeaderCellToLastNonSummaryDataCellRange, column.Format); cursor.MoveRight(); } cursor.ResetColumn(); } // Add rows cursor.AddTableRows(treeTable.TableRows, context, passKind); }
private static void AddTableRows( this CellCursor cursor, TableRows tableRows, ReportToWorkbookProjectionContext context, PassKind passKind) { if (tableRows == null) { return; } cursor.AddHeaderRows(tableRows.HeaderRows, context, passKind); cursor.AddDataRows(tableRows.DataRows, context, passKind); cursor.AddFooterRows(tableRows.FooterRows, context, passKind); }
public static Workbook ToExcelWorkbook( this Report report, ReportToWorkbookProjectionContext context = null) { if (report == null) { throw new ArgumentNullException(nameof(report)); } context = context ?? new ReportToWorkbookProjectionContext(); // Setup the workbook var result = General.CreateStandardWorkbook().RemoveDefaultWorksheet(); // Add document properties var documentProperties = context.BuildDocumentPropertiesDelegate == null ? new DocumentProperties() : context.BuildDocumentPropertiesDelegate(report.Title); // If report has a timestamp, should we set BuiltInDocumentPropertyKind.CreationDate and LastSaveTime to that timestamp? // Should those properties reflect when the workbook was created or when the report was created? result.SetDocumentProperties(documentProperties); // Add sections foreach (var section in report.Sections) { var worksheet = result.Worksheets.Add(section.Name); var cursors = new Cursors { ChromeCursor = new CellCursor(worksheet), }; // On first pass, add data. cursors.AddSection(section, context, PassKind.Data, report); // On second pass, apply formatting. cursors.AddSection(section, context, PassKind.Formatting, report); } return(result); }
private static void AddHeaderRows( this CellCursor cursor, HeaderRows headerRows, ReportToWorkbookProjectionContext context, PassKind passKind) { if (headerRows == null) { return; } if (headerRows.Rows.Any()) { for (var x = 0; x < headerRows.Rows.Count; x++) { cursor.ResetColumn(); cursor.MoveDown(); if ((x == 0) && (passKind == PassKind.Data)) { cursor.AddMarker(TopLeftHeaderCellMarker); } if (passKind == PassKind.Formatting) { cursor.CanvassedRowRange.ApplyHeaderRowsFormat(headerRows.Format); } cursor.AddFlatRow(headerRows.Rows[x], context, passKind); } if (passKind == PassKind.Data) { cursor.AddMarker(BottomRightHeaderCellMarker); } } }
private static void AddSection( this Cursors cursors, Section section, ReportToWorkbookProjectionContext context, PassKind passKind, Report report) { // Add upper chrome (e.g. section title) var chromeCursor = cursors.ChromeCursor; chromeCursor.Reset(); var hasTitle = (!string.IsNullOrWhiteSpace(report.Title)) || (!string.IsNullOrWhiteSpace(section.Title)); if (passKind == PassKind.Data) { if (hasTitle) { var title = string.IsNullOrWhiteSpace(report.Title) ? string.Empty : report.Title; if (!string.IsNullOrWhiteSpace(section.Title)) { title = title + ": " + section.Title; } chromeCursor.Cell.Value = title; } } if (passKind == PassKind.Formatting) { if (hasTitle) { // Section model doesn't have any formatting for title. chromeCursor.Cell.GetRange().ApplyCellFormat(new CellFormat(fontFormat: new FontFormat(fontSizeInPoints: 18))); } } // Add tree table cursors.TreeTableCursor = cursors.TreeTableCursor ?? new CellCursor(chromeCursor.Worksheet, chromeCursor.RowNumber, chromeCursor.StartColumnNumber); var treeTableCursor = cursors.TreeTableCursor; treeTableCursor.Reset(); treeTableCursor.AddTreeTable(section.TreeTable, context, passKind); // Add bottom chrome (e.g. copyright and terms of use) chromeCursor.MoveDown(treeTableCursor.MaxRowNumber - chromeCursor.RowNumber + 1); if (passKind == PassKind.Data) { // By default, display the timestamp if available. if ((report.TimestampUtc != null) && (report.Format?.DisplayTimestamp ?? true)) { chromeCursor.MoveDown(); chromeCursor.Cell.Value = ((DateTime)report.TimestampUtc).Format(report.Format?.TimestampFormat, context); } var additionalInfo = report.AdditionalInfo; if (additionalInfo != null) { if (additionalInfo.Copyright != null) { chromeCursor.MoveDown(); chromeCursor.Cell.Value = additionalInfo.Copyright; } if (additionalInfo.TermsOfUse != null) { chromeCursor.MoveDown(); chromeCursor.Cell.Value = additionalInfo.TermsOfUse; } } } // Overall formatting if (passKind == PassKind.Formatting) { chromeCursor.CanvassedRange.ApplySectionFormat(section.Format); chromeCursor.CanvassedRange.ApplyReportFormat(report.Format); } }