/// <summary>
/// Processes the dynamic chart.
/// </summary>
/// <param name="tableDataSheetName">Name of the table data sheet.</param>
/// <param name="tableData">The table data.</param>
/// <param name="chartModel">The chart model.</param>
/// <returns></returns>
/// <exception cref="ArgumentNullException">chartModel</exception>
private static bool ProcessDynamicChart(string tableDataSheetName, TableData tableData, ChartModel chartModel)
{
if (chartModel == null)
{
throw new ArgumentNullException("chartModel");
}
TempDiagnostics.Output(string.Format("Processing chart in sheet '{0}'", tableDataSheetName));
var seriesFactory = new SeriesFactory(chartModel);
if (seriesFactory.SourceSeriesCount > 0)
{
if (tableData.TreatRowAsSeries == false)
{
// A COLUMN of data represents a series...
// Find the Range that representes the first Axis Data for all of the series...
CompositeRangeReference category1AxisRange = DetermineCategory1AxisRange(tableData, tableData.TreatRowAsSeries, tableDataSheetName);
foreach (TableColumn column in tableData.Columns)
{
// Skip if we are on the category column
if (column.DataRegion.ExcelColumnStart == category1AxisRange.MinColumnIndex)
{
continue;
}
// Get information about the series that will be based on the column
var seriesInfo = new ChartSeriesInfo(column);
// We need a column that has not been actively excluded
if (seriesInfo.BaseOnChartSeriesIndex >= 0 && !seriesInfo.SuppressSeries)
{
// Get the template series or get a copy if already used
OpenXmlCompositeElement clonedSeries = seriesFactory.GetOrCloneSourceSeries(seriesInfo.BaseOnChartSeriesIndex);
//TODO: Allow ability to assign colour palettes (as opposed to colours) to dynamically generated series
Color?seriesColour = seriesInfo.SeriesColour;
if (!seriesColour.HasValue)
{
int useCount = seriesFactory.GetUseCount(seriesInfo.BaseOnChartSeriesIndex);
seriesColour = new Color?((ColourPalette.GetColour(ColourPaletteType.GamTechnicalChartPalette, useCount - 1)));
}
var seriesTextRange = new CompositeRangeReference(new RangeReference(tableDataSheetName,
(uint)column.DataRegion.ExcelRowStart,
(uint)column.DataRegion.ExcelColumnStart));
// Series data is first column only
var seriesValuesRange = new CompositeRangeReference(new RangeReference(tableDataSheetName,
(uint)column.DataRegion.ExcelRowStart + 1,
(uint)column.DataRegion.ExcelColumnStart,
(uint)column.DataRegion.ExcelRowEnd,
(uint)column.DataRegion.ExcelColumnStart));
// Determine data ranges to be used within chart series.
ChartDataRangeInfo dataRangeInfo = new ChartDataRangeInfo
{
SeriesTextRange = seriesTextRange,
CategoryAxisDataRange = category1AxisRange,
SeriesValuesRange = seriesValuesRange,
};
// update all formula on the series
UpdateChartSeriesDataReferences(clonedSeries, dataRangeInfo, new SolidColorBrush(seriesColour.Value));
}
}
}
else
{
// A ROW of data represents a series...
// Find the Range that representes the first Axis Data for all of the series...
CompositeRangeReference category1AxisRange = DetermineCategory1AxisRange(tableData, tableData.TreatRowAsSeries, tableDataSheetName);
TableColumn seriesTextColumn = tableData.Columns[FindNonExcludedColumnIndex(tableData, 1)];
// Build a list of columns to include in the series
var seriesTableColumns = new List <TableColumn>();
int seriesValuesColumnIndex = FindNonExcludedColumnIndex(tableData, 2);
TableColumn firstSeriesValuesColumn = tableData.Columns[seriesValuesColumnIndex];
seriesTableColumns.Add(firstSeriesValuesColumn);
// Count up to last column, excluding those marked for exclusion
while (seriesValuesColumnIndex < (tableData.Columns.Count - 1))
{
seriesValuesColumnIndex++;
TableColumn tableColumn = tableData.Columns[seriesValuesColumnIndex];
ChartExcludeOption excludeOption = tableColumn.ChartOptions.GetOptionOrDefault <ChartExcludeOption>();
if (excludeOption == null || excludeOption.Exclude == false)
{
seriesTableColumns.Add(tableColumn);
}
}
foreach (TableDataRowInfo rowInfo in tableData.RowData)
{
// Extract chart series related properties from row data
var seriesInfo = new ChartSeriesInfo(rowInfo.RowData);
// Determine where data has been written into Excel
uint rowIndex = (uint)tableData.MapContainer.ExcelRowStart + rowInfo.TableRowIndex - 1;
// Skip if we are on the category column
if (rowIndex == category1AxisRange.MinRowIndex)
{
continue;
}
// We need a column that has not been actively excluded
if (seriesInfo.BaseOnChartSeriesIndex >= 0 && !seriesInfo.SuppressSeries)
{
// Get the template series or get a copy if already used
OpenXmlCompositeElement clonedSeries = seriesFactory.GetOrCloneSourceSeries(seriesInfo.BaseOnChartSeriesIndex);
//TODO: Allow ability to assign colour palettes (as opposed to colours) to dynamically generated series
Color?seriesColour = seriesInfo.SeriesColour;
if (!seriesColour.HasValue)
{
int useCount = seriesFactory.GetUseCount(seriesInfo.BaseOnChartSeriesIndex);
seriesColour = new Color?((ColourPalette.GetColour(ColourPaletteType.GamTechnicalChartPalette, useCount - 1)));
}
// SeriesText is the series heading used in legends
var seriesTextRange = new CompositeRangeReference(new RangeReference(tableDataSheetName,
rowIndex,
(uint)seriesTextColumn.DataRegion.ExcelColumnStart));
// From column after category to last column in table.
var seriesValuesRange = new CompositeRangeReference();
foreach (TableColumn tableColumn in seriesTableColumns)
{
seriesValuesRange.Update(tableDataSheetName,
rowIndex,
(uint)tableColumn.DataRegion.ExcelColumnStart,
rowIndex,
(uint)tableColumn.DataRegion.ExcelColumnStart);
}
// Determine data ranges to be used within chart series.
ChartDataRangeInfo dataRangeInfo = new ChartDataRangeInfo
{
SeriesTextRange = seriesTextRange,
CategoryAxisDataRange = category1AxisRange,
SeriesValuesRange = seriesValuesRange,
};
// update all formula on the series ( I know we're constantly swapping between brush and color here.... Address later)
UpdateChartSeriesDataReferences(clonedSeries, dataRangeInfo, new SolidColorBrush(seriesColour.Value));
}
}
}
// Remove all un-used template series and set the order of remaining.
uint seriesIndex = 0;
for (int idx = 0; idx < seriesFactory.SourceSeriesCount; idx++)
{
OpenXmlCompositeElement templateSeries = seriesFactory.GetSourceSeriesElement(idx);
// Value is the use-count, if zero then the template needs removing.
if (seriesFactory.GetUseCount(idx) == 0)
{
// Remove the template series.
templateSeries.Remove();
}
else
{
// Set template series index and order to initial value, then set clones
var templateIndex = templateSeries.Descendants <DrawingCharts.Index>().FirstOrDefault();
if (templateIndex != null)
{
templateIndex.Val = seriesIndex;
}
var templateOrder = templateSeries.Descendants <DrawingCharts.Order>().FirstOrDefault();
if (templateOrder != null)
{
templateOrder.Val = seriesIndex;
}
seriesIndex++;
OpenXmlElement lastElement = templateSeries;
foreach (var clonedElement in seriesFactory.GetClonedSeriesElements(idx))
{
var index = clonedElement.Descendants <DrawingCharts.Index>().FirstOrDefault();
if (index != null)
{
index.Val = seriesIndex;
}
var order = clonedElement.Descendants <DrawingCharts.Order>().FirstOrDefault();
if (order != null)
{
order.Val = seriesIndex;
}
lastElement.InsertAfterSelf <OpenXmlElement>(clonedElement);
lastElement = clonedElement;
seriesIndex++;
}
}
}
}
return(true);
}
/// <summary>
/// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>.
/// </summary>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="mapCoOrdinateContainer">The map co ordinate container.</param>
/// <param name="stylesManager">The styles manager.</param>
/// <param name="spreadsheetDocument">The spreadsheet document.</param>
public static void WriteMapToExcel(string sheetName,
WorksheetPart worksheetPart,
ExcelMapCoOrdinateContainer mapCoOrdinateContainer,
ExcelStylesManager stylesManager,
SpreadsheetDocument spreadsheetDocument)
{
TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName));
var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f);
// Manages the writing to Excel.
var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet);
//** First we need to clear the destination worksheet down (rows, columns and merged areas)
excelWriteManager.EmptyWorksheet();
// Build up Columns models on all nested containers.
RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel();
int colCount = columnsModel.Count();
// ==========================================================
// Traverse each column assigning start and end
// column indexes to the maps associated with that column.
// ==========================================================
RowOrColumnInfo columnInfo = columnsModel.First;
while (columnInfo != null)
{
double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddColumn(width, columnInfo.Hidden);
// Assign Excel start and end columns to maps for merge operations.
foreach (ExcelMapCoOrdinate map in columnInfo.Maps)
{
// Extend any cells that are merged across maps.
if (map is ExcelMapCoOrdinatePlaceholder)
{
var cell = map as ExcelMapCoOrdinatePlaceholder;
if (cell.MergeWith != null)
{
cell.ExcelColumnStart = cell.MergeWith.ExcelColumnStart;
cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol
}
}
if (map.ExcelColumnStart == 0)
{
map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol;
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
else
{
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
}
// Move on to next column in list
columnInfo = columnInfo.Next;
}
TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount));
// Build up Rows models on all nested containers.
RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel();
int rowCount = rowsModel.Count();
// ==========================================================
// Traverse each row assigning start and end
// row indexes to the maps associated with that row.
// ==========================================================
RowOrColumnInfo rowInfo = rowsModel.First;
while (rowInfo != null)
{
double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddRow(height, rowInfo.Hidden);
// Assign Excel start and end rows to maps for merge operations.
foreach (ExcelMapCoOrdinate map in rowInfo.Maps)
{
if (map.ExcelRowStart == 0)
{
map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow;
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
else
{
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
}
// Move on to next Row in list
rowInfo = rowInfo.Next;
}
TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount));
// Build a layered cells dictionary for all cells, keyed by Excel row and column index
var layeredCellsDictionary = new LayeredCellsDictionary();
mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary);
TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count));
// Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// We can now use the layeredCellsDictionary to build the
// excel workbook based on layered cell information
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
LayeredCellInfo layeredCellInfo = layeredCellsDictionary[currentCoOrdinate];
// Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column
ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo);
}
}
TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount));
//** Write the 2D array of cell information to the Excel Worksheet
//** building a list of areas that are to be merged.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow);
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// Pluck out information relating to the current cell
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
ExcelCellInfo cellInfo = layeredCellsDictionary[currentCoOrdinate].CellInfo;
// Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers
string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol);
// All merge cells should have the same style as the source merge cell.
if (cellInfo.MergeFrom != null)
{
// If MergeFrom is non-null, then this cell has been already been marked as a merge cell,
// whose style is to be the same as the source 'MergeFrom cell.
// Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged.
cellInfo.MergeFrom.MergeTo = cellInfo;
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo);
// Write in to Excel (style information only)
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
else
{
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo);
// NB! If we write a Null value then we lose cell formatting information for some reason
object value = cellInfo.Value == null ? string.Empty : cellInfo.Value;
// Write in to Excel
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
excelWriteManager.SetCellValue(cell, value);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
// Span the cell accross it's parent columns if specified
if (cellInfo.LastSpanRow == 0)
{
cellInfo.LastSpanRow = worksheetRow;
}
if (cellInfo.LastSpanColumn == 0)
{
cellInfo.LastSpanColumn = worksheetCol;
}
// Merge cells if required (spanning)
for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++)
{
for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++)
{
// Mark processed (so we don't over-write)
var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
if (coOrdinate != currentCoOrdinate)
{
var mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
ExcelCellInfo mergeCellInfo = layeredCellsDictionary[mergeCoOrdinate].CellInfo;
if (mergeCellInfo.MergeFrom == null)
{
mergeCellInfo.MergeFrom = cellInfo;
}
}
}
}
}
}
//#if DEBUG
// if (!skipMerge)
// {
//#endif
// Merge cells that have been marked to merge in the cellInfos dictionary.
MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary);
//#if DEBUG
// }
//#endif
TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount));
// Create a list of all of the the defined names in the container.
var definedNameList = new List <ExcelDefinedNameInfo>();
mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName);
// And write into Excel workbook
foreach (var definedNameInfo in definedNameList)
{
uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1;
uint rangeRowCount = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1;
if (rangeRowCount > 0 && rangeColumnCount > 0)
{
spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName,
definedNameInfo.DefinedName,
(uint)definedNameInfo.StartColumnIndex,
(uint)definedNameInfo.StartRowIndex,
(int)rangeColumnCount,
(int)rangeRowCount);
}
}
TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType));
}
