C＃/ Excel中：使用周围最大尺寸系列在图表上 [英] C#/Excel: Working Around Maximum Series Size On Chart
问题描述
我需要帮助编程而无法放在一个Excel系列作图更多的积分。
据的 http://office.microsoft.com/en-us/excel/HP100738491033.aspx 上的Excel 2007中显示的点的最大数量图表256000鉴于每个系列在32000点帽出，8系列都必须绘制完整的256000点。我的客户要求每个图点的最大量的绘图因为与我们合作的大型数据集。
我有一个C＃适度经验/ Excel的互操作，所以我想它会很容易通过每组32000点编程方式创建一个工作表，然后循环，并将其添加到图作为一个系列，停车时数据完全绘制或8系列绘制。如果有色得当，8系列是从一个单一的一系列可视没有区别。
不幸的是我在这里。我遇到的主要问题是：
这弹出，奇怪的是，当我执行该行出现：
和伴随：
我不明白我怎么会产生这样的弹出/警告/异常：img21 / 5153 / exceptionb.pngALT =从HRESULT异常之前，我甚至指定的数据以图形。是Excel的巧言令色在这里？
作为暂时的解决办法，我已经把chart.ChartType =图表类型语句转换成一个try-catch块，所以我可以继续下去。
如下面所示，我的分块代码工作按计划，而是试图将数据添加到图形时，我仍然遇到同样的问题。 Excel中说，我想清楚的时候我不是以图形太多了点。
（全尺寸图片）
我明白我可能没有正确每个系列都还没有相关的X值，但我试图让这对我工作之前。走得更远
任何帮助，将不胜感激。
下面是完整的代码：
公共无效DrawScatterGraph（字符串xColumnLetter，串yColumnLetterStart，串yColumnLetterStop，串xAxisLabel，串yAxisLabel，串chartTitle，Microsoft.Office.Interop.Excel.XlChartType图图表，布尔includeTrendline，布尔includeLegend）
{
INT totalRows = dataSheet.UsedRange.Rows.Count; //数据表的私有类变量
//已正确设置到工作表
//我们想从
，来图，若（totalRows 2）抛出新异常（未生成图形+ chartTitle.Replace（'\\\
'，''）
+，因为没有足够的数据存在）;
ChartObjects图表=（ChartObjects）dataSheet.ChartObjects（Type.Missing）;
ChartObject chartObj = charts.Add（100，300，500，300）;
图图= chartObj.Chart;
尝试{chart.ChartType =图表类型; }
赶上{} //我不知道这是为什么抛出异常，但我
//经历这个问题铲平暂时
如果（totalRows < SizeOfSeries）//我们可以在一个序列图中的数据 - 耶！
{
范围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（系列S IN的SeriesCollection）
{
s.XValues = xValues;
}
}
，否则//我们需要拆分到多个系列的数据 - 这还没有工作，
{
INT STARTROW = 1;
，而（STARTROW< totalRows）
{
INT stopRow =（STARTROW + SizeOfSeries）-1;
如果（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，类型。失踪）;
}
赶上（例外EXC）
{
抛出新的异常（yColumnLetterStart + startRow.ToString（）+！+ yColumnLetterStop + stopRow.ToString（）+！ + exc.Message）;
}
STARTROW = stopRow + 1;
}
}
chart.HasLegend = includeLegend;
chart.HasTitle = TRUE;
chart.ChartTitle.Text = chartTitle;
轴轴;
轴=（轴）chart.Axes（XlAxisType.xlCategory，XlAxisGroup.xlPrimary）;
axis.HasTitle = TRUE;
axis.AxisTitle.Text = xAxisLabel;
axis.HasMajorGridlines = FALSE;
axis.HasMinorGridlines = FALSE;
轴=（轴）chart.Axes（XlAxisType.xlValue，XlAxisGroup.xlPrimary）;
axis.HasTitle = TRUE;
axis.AxisTitle.Text = yAxisLabel;
axis.HasMajorGridlines = TRUE;
axis.HasMinorGridlines = FALSE;
如果（includeTrendline）
{
趋势线T =（趋势线）（（系列）chart.SeriesCollection（1））的趋势线（Type.Missing）。
t.Add（XlTrendlineType.xlLinear，Type.Missing，Type.Missing，0，0，Type.Missing，假的，假的，AutoTrendlineByChameleon）;
}
chart.Location（XlChartLocation.xlLocationAsNewSheet，图）;
}
如果活动单元格中一个数据块，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:
This pop-up, strangely enough, appears when I execute the line:
and is accompanied by:
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.
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屋！
