C＃/ Excel：在图表上工作最大系列尺寸 [英] C#/Excel: Working Around Maximum Series Size On Chart
问题描述
我需要帮助以编程方式绘制比单个Excel系列更适合的点数。
根据）
我知道我可能没有正确地关联每个系列的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 charts =（ChartObjects）dataSheet.ChartObjects（Type.Missing）;
ChartObject chartObj = charts.Add（100，300，500，300）;
图表chart = chartObj.Chart;
try {chart.ChartType = chartType; }
catch {} //我不知道为什么这是抛出一个异常，但我是
//通过这个问题临时推出
if（totalRows < SizeOfSeries）//我们可以在单个系列中绘制数据 - yay！
{
Range 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（series系列中的系列）
{
s.XValues = xValues;
}
}
else //我们需要在多个系列之间拆分数据 - 这不工作
{
int startRow = 1;
while（startRow
int stopRow =（startRow + SizeOfSeries）-1;
if（stopRow> totalRows）stopRow = totalRows;
范围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。失踪）;
}
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）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））。
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:
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屋！
