C#/Excel:处理图表上的最大系列大小 [英] C#/Excel: Working Around Maximum Series Size On Chart
问题描述
我需要帮助以编程方式绘制超过单个 Excel 系列所能容纳的更多点.
根据
并伴随着:
来自 HRESULT 的异常:0x800AC472 http://img21.imageshack.us/img21/5153/exceptionb.png
在我指定要绘制的数据之前，我不明白如何生成这样的弹出窗口/警告/异常.Excel 是否想在这里变得聪明?
作为临时解决方法，我已将 chart.ChartType = chartType 语句放入 try-catch 块中，以便我可以继续进行.
如下所示，我的分块"代码按预期工作，但在尝试向图形添加数据时仍然遇到同样的问题.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 Exception("Not生成图" + chartTitle.Replace('
', ' ')+ "因为没有足够的数据存在");ChartObjects 图表 = (ChartObjects)dataSheet.ChartObjects(Type.Missing);ChartObject chartObj = charts.Add(100, 300, 500, 300);图表图表 = chartObj.Chart;尝试 { chart.ChartType = chartType;}catch { }//我不知道为什么会抛出异常，但我是//暂时解决这个问题if (totalRows < SizeOfSeries)//我们可以在单个系列中绘制数据 - 是的！{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(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)).Add(curRange, XlRowCol.xlColumns,Type.Missing, Type.Missing, Type.Missing);}捕获(例外除外){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)chart.Axes(XlAxisType.xlCategory, XlAxisGroup.xlPrimary);轴.HasTitle = true;轴.AxisTitle.Text = xAxisLabel;axis.HasMajorGridlines = false;axis.HasMinorGridlines = false;轴 = (Axis)chart.Axes(XlAxisType.xlValue, XlAxisGroup.xlPrimary);轴.HasTitle = true;轴.AxisTitle.Text = yAxisLabel;axis.HasMajorGridlines = true;axis.HasMinorGridlines = false;如果(包括趋势线){趋势线 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");}如果活动单元格位于数据块中，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:
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('
', ' ')
+ " 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屋！
