C#/Excel: Working Around Maximum Series Size On Chart

Asked
Viewd8712

8

I need help programatically graphing more points than can fit in a single Excel series.

According to http://office.microsoft.com/en-us/excel/HP100738491033.aspx the maximum number of points displayable on an Excel 2007 chart is 256000. Given that each series caps out at 32000 points, 8 series are required to plot the full 256000 points. My customer requires plotting of maximum amount of points per chart due to the large data sets we work with.

I have moderate experience with C#/Excel interop so I thought it would be easy to programatically create a worksheet and then loop through each set of 32000 points and add them to the graph as a series, stopping when the data was fully plotted or 8 series were plotted. If colored properly, the 8 series would be visually indistinguishable from a single series.

Unfortunately here I am. The main problem I encounter is:

(full size) The maximum number of datapoints you can use in a data series for a 2-D chart is 32,000... http://img14.imageshack.us/img14/9630/errormessagen.png

This pop-up, strangely enough, appears when I execute the line:

chart.ChartType = chartType (where chartType is xlXYScatterLines)

and is accompanied by:

Exception from HRESULT: 0x800AC472 http://img21.imageshack.us/img21/5153/exceptionb.png

I do not understand how I could be generating such a popup/warning/exception before I have even specified the data to be graphed. Is Excel trying to be clever here?

As a temporary workaround, I've put the chart.ChartType = chartType statement into a try-catch block so I can keep going.

As the following shows, my "chunking" code is working as intended, but I still encounter the same problem when trying to add data to the graph. Excel says I am trying to graph too many points when clearly I am not.

(full size image) code block with watch window http://img12.imageshack.us/img12/5360/snippet.png

I understand I may not have the X Values correctly associated with each series yet, but I'm trying to get this to work before I go further.

Any help would be greatly appreciated.

Here's the full code:

public void DrawScatterGraph(string xColumnLetter, string yColumnLetterStart, string yColumnLetterStop, string xAxisLabel, string yAxisLabel, string chartTitle, Microsoft.Office.Interop.Excel.XlChartType chartType, bool includeTrendline, bool includeLegend)
    {
        int totalRows = dataSheet.UsedRange.Rows.Count; //dataSheet is a private class variable that 
                                                        //is already properly set to the worksheet
                                                        //we want to graph from

        if (totalRows < 2) throw new Exception("Not generating graph for " + chartTitle.Replace('\n', ' ') 
                                            + " because not enough data was present");

        ChartObjects charts = (ChartObjects)dataSheet.ChartObjects(Type.Missing);
        ChartObject chartObj = charts.Add(100, 300, 500, 300);
        Chart chart = chartObj.Chart;

        try { chart.ChartType = chartType; }
        catch { }   //i don't know why this is throwing an exception, but i'm
                    //going to bulldoze through this problem temporarily 

        if (totalRows < SizeOfSeries) //we can graph the data in a single series - yay!
        {
            Range xValues = dataSheet.get_Range(xColumnLetter + "2", xColumnLetter + totalRows.ToString());
            Range yValues = dataSheet.get_Range(yColumnLetterStart + "1", yColumnLetterStop + totalRows.ToString());
            chart.SetSourceData(yValues, XlRowCol.xlColumns);
            SeriesCollection seriesCollection = (SeriesCollection)chart.SeriesCollection(Type.Missing);
            foreach (Series s in seriesCollection)
            {
                s.XValues = xValues;
            }
        }
        else // we need to split the data across multiple series -- this doesn't work yet
        {
            int startRow = 1; 
            while (startRow < totalRows)
            {
                int stopRow = (startRow + SizeOfSeries)-1;  
                if (stopRow > totalRows) stopRow = totalRows;
                Range curRange = dataSheet.get_Range(yColumnLetterStart + startRow.ToString(), yColumnLetterStop + stopRow.ToString());
                try
                {
                    ((SeriesCollection)chart.SeriesCollection(Type.Missing)).Add(curRange, XlRowCol.xlColumns, 
                                                                            Type.Missing, Type.Missing, Type.Missing);
                }
                catch (Exception exc)
                {
                    throw new Exception(yColumnLetterStart + startRow.ToString() + "!" + yColumnLetterStop + stopRow.ToString() + "!" + exc.Message);
                }
                startRow = stopRow+1;
            }
        }

        chart.HasLegend = includeLegend;
        chart.HasTitle = true;
        chart.ChartTitle.Text = chartTitle;

        Axis axis;
        axis = (Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
        axis.HasTitle = true;
        axis.AxisTitle.Text = xAxisLabel;
        axis.HasMajorGridlines = false;
        axis.HasMinorGridlines = false;

        axis = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
        axis.HasTitle = true;
        axis.AxisTitle.Text = yAxisLabel;
        axis.HasMajorGridlines = true;
        axis.HasMinorGridlines = false;

        if (includeTrendline)
        {
            Trendlines t = (Trendlines)((Series)chart.SeriesCollection(1)).Trendlines(Type.Missing);
            t.Add(XlTrendlineType.xlLinear, Type.Missing, Type.Missing, 0, 0, Type.Missing, false, false, "AutoTrendlineByChameleon");
        }

        chart.Location(XlChartLocation.xlLocationAsNewSheet, "Graph");
    }

3 ответов

3

Если активная ячейка находится в блоке данных, Excel может предположить, что вы хотите построить диапазон.

Выберите пустую ячейку, которая не находится рядом с данными, затем вставьте диаграмму. Он будет пустым, а не заполненным заранее.

  • Спасибо, Джон! Я добавил следующее в верхней части моей функции:

             Range tempRange = dataSheet.get_Range("E1", "E2");
            tempRange.Select();
     

    Если столбец E пуст (мои данные только в столбцах A - C).

    После этого изменения все работало правильно.

    Еще раз спасибо!

    Vincent15 сентября 2009, 15:16
1

To help anyone who comes across this in the future, here's the complete function with Jon's fix:

    public void DrawScatterGraph(string xColumnLetter, string yColumnLetterStart, string yColumnLetterStop, string xAxisLabel, string yAxisLabel, string chartTitle, Microsoft.Office.Interop.Excel.XlChartType chartType, bool includeTrendline, bool includeLegend)
    {
        int totalRows = dataSheet.UsedRange.Rows.Count; //dataSheet is a private class variable that 
                                                        //is already properly set to the worksheet
                                                        //we want to graph from

        if (totalRows < 2) throw new Exception("Not generating graph for " + chartTitle.Replace('\n', ' ') 
                                               + " because not enough data was present");

        dataSheet.get_Range("Z1", "Z2").Select();   //we need to select some empty space
                                                    //so Excel doesn't try to jam the 
                                                    //potentially large data set into the 
                                                    //chart automatically

        ChartObjects charts = (ChartObjects)dataSheet.ChartObjects(Type.Missing);
        ChartObject chartObj = charts.Add(100, 300, 500, 300);
        Chart chart = chartObj.Chart;
        chart.ChartType = chartType;
        SeriesCollection seriesCollection = (SeriesCollection)chart.SeriesCollection(Type.Missing);

        if (totalRows < SizeOfSeries) //we can graph the data in a single series - yay!
        {
            Range xValues = dataSheet.get_Range(xColumnLetter + "2", xColumnLetter + totalRows.ToString());
            Range yValues = dataSheet.get_Range(yColumnLetterStart + "1", yColumnLetterStop + totalRows.ToString());
            chart.SetSourceData(yValues, XlRowCol.xlColumns);

            foreach (Series s in seriesCollection)
            {
                s.XValues = xValues;
            }
        }
        else // we need to split the data across multiple series 
        {
            int startRow = 2; 

            while (startRow < totalRows)
            {
                int stopRow = (startRow + SizeOfSeries)-1;  
                if (stopRow > totalRows) stopRow = totalRows;

                Series s = seriesCollection.NewSeries();
                s.Name = "ChunkStartingAt" + startRow.ToString();
                s.XValues = dataSheet.get_Range(xColumnLetter + startRow.ToString(), xColumnLetter + stopRow.ToString());
                s.Values = dataSheet.get_Range(yColumnLetterStart + startRow.ToString(), yColumnLetterStop + stopRow.ToString());

                startRow = stopRow+1;
            }
        }

        chart.HasLegend = includeLegend;
        chart.HasTitle = true;
        chart.ChartTitle.Text = chartTitle;

        Axis axis;
        axis = (Axis)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);
        axis.HasTitle = true;
        axis.AxisTitle.Text = xAxisLabel;
        axis.HasMajorGridlines = false;
        axis.HasMinorGridlines = false;

        axis = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);
        axis.HasTitle = true;
        axis.AxisTitle.Text = yAxisLabel;
        axis.HasMajorGridlines = true;
        axis.HasMinorGridlines = false;

        if (includeTrendline)
        {
            Trendlines t = (Trendlines)((Series)chart.SeriesCollection(1)).Trendlines(Type.Missing);
            t.Add(XlTrendlineType.xlLinear, Type.Missing, Type.Missing, 0, 0, Type.Missing, false, false, "AutoTrendlineByChameleon");
        }

        chart.Location(XlChartLocation.xlLocationAsNewSheet, "Graph");
    }
2

Действительно ли ваш график должен быть в Excel? С таким количеством точек данных производительность была бы ужасной.

Можно предложить использование стороннего компонента для создания графика. Конкретный метод того, как это сделать, зависит от того, нужно ли вам иметь возможность просматривать данные в Excel или выходной граф просто должен быть доступен где-то еще.

Если график не должен быть виден в Excel, просто передайте точки данных и просмотрите изображение в графическом приложении или в веб-браузере.

Если вам действительно нужно просмотреть график в Excel, вы можете позвонить во внешнее приложение для построения графиков и передать ему набор точек данных. Когда он вернет изображение, просто вставьте его в Excel с помощью vba.

Я могу дать вам дополнительную информацию об обоих подходах, если вам нужно.

Кроме того, можно подумать и о том, нужна ли вам возможность детализации на графике. С таким количеством точек данных я не могу представить, что вы бы это сделали.


Если вы ответите на следующие вопросы, это может помочь людям сформулировать более точные ответы.

  1. Какой пользовательский интерфейс будет отображать вывод этих элементов? (например, Excel, веб-приложение ASP.NET, Windows Forms, WPF, Silverlight и др.)

  2. Должны ли эти графики создаваться в реальном времени по запросу пользователя или они создаются и хранятся? Если они генерируются по запросу, какой максимальный период времени пользователи сочтут приемлемым для ожидания?

  3. Насколько важно, чтобы вы действительно использовали Excel? Вы используете его, потому что это требование для отображения, или это просто то, что удобно?

  4. Насколько важен "Вау-фактор" для отображения графиков? Просто иметь графики или они должны быть очень красивыми?

  5. Требуются ли пользователям какие-либо возможности для детализации графика или достаточно просто просмотра изображения?

  • Спасибо за помощь, Энтони. Я был близок к тому, чтобы рассмотреть другие альтернативы, прежде чем Джон дал свой совет. К вашему сведению, я начал работать с Excel, потому что 1) он легко доступен для моей "огороженной" базы пользователей и 2) Иногда графики нужно настраивать вручную после автоматического создания, и все пользователи обучены в Excel.

    Vincent15 сентября 2009, 15:20