C＃/ Excel：在图表上操作最大系列大小 [英] C#/Excel: Working Around Maximum Series Size On Chart

问题描述

根据 http://office.microsoft.com/en-us/excel/HP100738491033.aspx Excel 2007图表上可显示的最大点数为256000。给定每个系列在32000点上限，8系列需要绘制完整的256000分。我的客户需要绘制每张图表的最大点数，因为我们合作的数据量很大。

我有C＃/ Excel互操作的经验，所以我以为会轻松编程创建工作表，然后循环遍历每组32000个点，并将它们作为一个系列添加到图形中，当数据完全绘制时停止，或绘制8个系列。如果彩色正确，8系列在视觉上与单个系列无法区分。

不幸的是，我在。我遇到的主要问题是：

（全尺寸）
可用于2-D图表的数据系列中的最大数据点数是32,000 ... http://img14.imageshack.us/img14/9630/errormessagen.png

这个弹出窗口奇怪的是出现当我执行该行：

chart.ChartType = chartType （其中chartType是xlXYScatterLines）http://img2.imageshack.us/img2/2413/linean.png

并附有：

HRESULT异常：0x800AC472 http://img21.imageshack.us /img21/5153/exceptionb.png

我不明白我甚至可以在我甚至指定数据之前生成这样的弹出/警告/异常被绘制Excel是否试图在这里变得聪明？

作为临时解决方法，我将chart.ChartType = chartType语句放入try-catch块，以便我可以继续。

如下图所示，我的chunking代码正在按预期工作，但是当尝试向图中添加数据时，我仍然遇到相同的问题。 Excel表示，当我不清楚时，我试图绘制太多分。

（全尺寸图片）
带有监视窗口的代码块http：/ /img12.imageshack.us/img12/5360/snippet.png

我知道我可能没有X值与每个系列正确关联，但是

任何帮助将不胜感激。

这是完整的代码：

  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是一个私有类变量，
 //已经正确设置为工作表
 //我们要从
 
中绘制if（totalRows< 2）throw new异常（不生成图形+ chartTitle.Replace（'\\\
'，''）
 +因为没有足够的数据存在）; 
 
 ChartObjects图表=（ChartObjects）dataSheet.ChartObjects（Type.Missing）; 
 ChartObject chartObj = charts.Add（100,300,500,300）; 
图表= chartObj.Chart; 
 
 try {chart.ChartType = chartType; } 
 catch {} //我不知道为什么这是抛出一个异常，但我是
 //暂时通过这个问题推土机
 
 if（totalRows < SizeOfSeries）//我们可以在单个系列中绘制数据 -  yay！ 
 {
范围xValues = dataSheet.get_Range（xColumnLetter +2，xColumnLetter + totalRows.ToString（））; 
范围yValues = dataSheet.get_Range（yColumnLetterStart +1，yColumnLetterStop + totalRows.ToString（））; 
 chart.SetSourceData（yValues，XlRowCol.xlColumns）; 
 SeriesCollection seriesCollection =（SeriesCollection）chart.SeriesCollection（Type.Missing）; 
 foreach（SeriesC in seriesCollection）
 {
 s.XValues = xValues; 
} 
} 
 else //我们需要跨多个系列分割数据 - 这不起作用
 {
 int startRow = 1; 
 while（startRow< totalRows）
 {
 int stopRow =（startRow + SizeOfSeries）-1; 
 if（stopRow> totalRows）stopRow = totalRows; 
范围curRange = dataSheet.get_Range（yColumnLetterStart + startRow.ToString（），yColumnLetterStop + stopRow.ToString（））; 
尝试
 {
（（SeriesCollection）chart.SeriesCollection（Type.Missing））。添加（curRange，XlRowCol.xlColumns，
 Type.Missing，Type.Missing，Type。失踪）; 
} 
 catch（异常exc）
 {
抛出新异常（yColumnLetterStart + startRow.ToString（）+！+ yColumnLetterStop + stopRow.ToString（）+！ + exc.Message）; 
} 
 startRow = stopRow + 1; 
} 
} 
 
 chart.HasLegend = includeLegend; 
 chart.HasTitle = true; 
 chart.ChartTitle.Text = chartTitle; 
 
轴轴; 
 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）
 {
趋势线t =（趋势线）（（系列）chart.SeriesCollection（1））趋势线（Type.Missing）; 
 t.Add（XlTrendlineType.xlLinear，Type.Missing，Type.Missing，0，0，Type.Missing，false，false，AutoTrendlineByChameleon）; 
} 
 
 chart.Location（XlChartLocation.xlLocationAsNewSheet，Graph）; 
} 
  

解决方案

如果活动单元格处于一个数据块，Excel可能会假设您要绘制范围。

选择不在数据旁边的空白单元格，然后插入图表。它将是空白的，而不是预填充。

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) http://img2.imageshack.us/img2/2413/linean.png

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");
    }

解决方案

If the active cell is in a block of data, Excel may assume you want to plot the range.

Select a blank cell which is not next to the data, then insert the chart. It will be blank, rather than prepopulated.

这篇关于C＃/ Excel：在图表上操作最大系列大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！