/// <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)
                       ));
            }
        }
        /// <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>
        /// Processes the dynamic chart.
        /// </summary>
        /// <param name="dataPart">The data part.</param>
        /// <param name="tableDataSheetName">Name of the table data sheet.</param>
        /// <param name="tableData">The table data.</param>
        /// <param name="presentationWSPart">The presentation ws part.</param>
        /// <returns></returns>
        private static bool ProcessDynamicChart(IDataPart dataPart, string tableDataSheetName, TableData tableData, WorksheetPart presentationWSPart)
        {
            // TODO: Check this - At the moment, only one chart is supported
            //   Ie. Export the dataPart to a worksheet, which is married with a presentation worksheet,
            //       which contains a single chart (the chart that is cloned).
            if (dataPart is IPreparable) // Don't think it needs to be preparable anymore.
            {
                var chartPart = presentationWSPart.DrawingsPart.ChartParts.FirstOrDefault();
                if (chartPart != null)
                {
                    string id = ChartModel.GetIdOfChartPart(chartPart);

                    // Get the chart we wish to update using a ChartModel, and the last series in the chart, so that we can clone the series.
                    ChartModel chartModel = ChartModel.GetChartModel(presentationWSPart.Worksheet, id);

                    if (chartModel.ChartElements == null)
                    {
                        return(false);
                    }

                    int seriesCount = 0;
                    foreach (OpenXmlCompositeElement chartElement in chartModel.ChartElements)
                    {
                        seriesCount += chartModel.GetSeriesElements(chartElement).Count();
                    }

                    if (seriesCount == 0)
                    {
                        return(false);
                    }
                    else
                    {
                        return(ProcessDynamicChart(tableDataSheetName, tableData, chartModel));
                    }
                }
            }

            return(false);
        }