/// <summary>
/// Finds the nth available column in a <see cref="TableData" /><br />
/// This is simply the nth non-excluded column in the TableData
/// </summary>
/// <param name="tableData">The Table to be searched.</param>
/// <param name="which">The which.</param>
/// <returns>
/// A <see cref="TableColumn" />
/// </returns>
/// <exception cref="InvalidOperationException"></exception>
private static int FindNonExcludedColumnIndex(TableData tableData, int which)
{
// Find the index of the first column which is not marked as Exluded
int foundColumnCount = 0;
int foundColumnIdx = -1;
for (int idx = 0; idx < tableData.Columns.Count; idx++)
{
TableColumn col = tableData.Columns[idx];
ChartExcludeOption colExcludedOption = col.ChartOptions.GetOptionOrDefault <ChartExcludeOption>();
// No exclude option set, or exclude option set to false
if (colExcludedOption == null || colExcludedOption.Exclude == false)
{
foundColumnCount++;
if (foundColumnCount == which)
{
foundColumnIdx = idx;
break;
}
}
}
if (foundColumnIdx == -1)
{
throw new InvalidOperationException(string.Format("The first {0} (read 1 as first, 2 as second etc) non-excluded column does not exist in the TableData.", which));
}
return(foundColumnIdx);
}
/// <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>
/// Determines the Category (or X1) Axis column within the table by doing the following:
/// 1. If there are no columns, then returns null
/// 2. Iterates over each column, if 'IsCategory1Axis == true', then returns that column.<br />
/// 3. If no column with 'IsCategory1Axis == true', then assumes the first column, hence returns column 0.
/// </summary>
/// <param name="tableData">The table to be tested</param>
/// <param name="treatRowAsSeries">if set to <c>true</c> [treat row as series].</param>
/// <param name="dataSheetName">Name of the data sheet.</param>
/// <returns></returns>
/// <exception cref="InvalidOperationException">There are no non-excluded columns which can be used as the 'Category 1' axis in chart</exception>
private static CompositeRangeReference DetermineCategory1AxisRange(TableData tableData, bool treatRowAsSeries, string dataSheetName)
{
if (treatRowAsSeries)
{
int rowIndex = -1;
// When rows are treated as series, the Category 1 Axis is the row that contains the row headings,
// unless a row exists which is explicitly marked as being the Category 1 Axis
foreach (TableDataRowInfo row in tableData.RowData)
{
// Get series related information about the row
var rowInfo = new ChartSeriesInfo(row.RowData);
if (rowInfo.IsCategory1Axis)
{
rowIndex = (int)row.TableRowIndex;
}
}
if (rowIndex == -1)
{
// No row marked to be used as the Category 1 Axis, so we assume the table heading row
rowIndex = (int)tableData.MapContainer.ExcelRowStart;
}
// Now we've determined which row will be used as the 'Category 1 Axis',
// we need to find the first non-excluded column in which the values reside.
int category1AxisColumnIndex = FindNonExcludedColumnIndex(tableData, 2);
TableColumn firstColumn = tableData.Columns[category1AxisColumnIndex];
var rangeReference = new CompositeRangeReference
(
dataSheetName,
(uint)rowIndex,
(uint)firstColumn.DataRegion.ExcelColumnStart,
(uint)rowIndex,
(uint)firstColumn.DataRegion.ExcelColumnStart
);
// Count up to last column excluding those marked excluded
while (category1AxisColumnIndex < (tableData.Columns.Count - 1))
{
category1AxisColumnIndex++;
TableColumn tableColumn = tableData.Columns[category1AxisColumnIndex];
ChartExcludeOption excludeOption = tableColumn.ChartOptions.GetOptionOrDefault <ChartExcludeOption>();
if (excludeOption == null || excludeOption.Exclude == false)
{
rangeReference.Update(dataSheetName,
(uint)rowIndex,
(uint)tableColumn.DataRegion.ExcelColumnStart,
(uint)rowIndex,
(uint)tableColumn.DataRegion.ExcelColumnStart);
}
}
// Again (see above), we are currently ignoring excluded column in the table from this range reference.
return(rangeReference);
}
else
{
// Axis Data Range is first non-excluded column data, unless a column is explitly marked as being the Category 1 Axis
TableColumn dataColumn = null;
if (tableData.Columns.Count == 0)
{
return(null);
}
TableColumn firstNonExcludedColumn = null;
foreach (TableColumn column in tableData.Columns)
{
var colInfo = new ChartSeriesInfo(column);
if (colInfo.IsCategory1Axis)
{
dataColumn = column;
break;
}
if (firstNonExcludedColumn == null && !colInfo.SuppressSeries)
{
firstNonExcludedColumn = column;
}
}
if (dataColumn == null)
{
dataColumn = firstNonExcludedColumn;
}
// Throw exception if no axis column defined.
if (dataColumn == null)
{
throw new InvalidOperationException("There are no non-excluded columns which can be used as the 'Category 1' axis in chart");
}
return(new CompositeRangeReference
(
new RangeReference(dataSheetName,
(uint)dataColumn.DataRegion.ExcelRowStart + 1,
(uint)dataColumn.DataRegion.ExcelColumnStart,
(uint)dataColumn.DataRegion.ExcelRowEnd,
(uint)dataColumn.DataRegion.ExcelColumnEnd)
));
}
}