使用c#创建多个Excel图表对象
本文关键字:对象 Excel 创建 使用 | 更新日期: 2023-09-27 18:04:37
我已经花了几个小时尝试用我的c#应用程序在Excel中创建一些图表。我试图创建多个图表对象。有更好的方法吗?我确信行"chartObject[col] = (Excel.Chart)oWB.Charts.Add(失踪。价值,失踪。价值,失踪。Value, Missing.Value)是我错的地方。
现在,当我调用这一行时,它有时会创建上一个图表的副本,但有时它会工作。我完全不明白这其中的逻辑。
感谢private void CreateCharts(Excel.Worksheet oWS, int numRows, int numCols)
{
Excel.Workbook oWB = (Excel.Workbook)oWS.Parent;
Excel.Series oSeries;
//Excel._Chart chartObject;
Excel.Chart[] chartObject = new Excel.Chart[numCols];
Excel.SeriesCollection[] oSeriesCollection = new Excel.SeriesCollection[numCols];
int length = numRows + 2;
string colname;
//then you can assign as much as series you want,
for (int col = 0; col < numCols; col++)
{
//create a new chart
chartObject[col] = (Excel.Chart)oWB.Charts.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
chartObject[col].ChartType = Excel.XlChartType.xlLine;
oSeriesCollection[col] = (Excel.SeriesCollection)chartObject[col].SeriesCollection();
//add the actual occupancy
colname = GetExcelColumnName(col * 3 + 1);
oSeries = oSeriesCollection[col].NewSeries();
oSeries.Values = oWS.Range[colname + "2", colname + length];
//add the expected occupancy
colname = GetExcelColumnName(col * 3 + 2);
oSeries = oSeriesCollection[col].NewSeries();
oSeries.Values = oWS.Range[colname + "2", colname + length];
}
}
问题在于Excel中的Chart对象实际上是一个图表表,而图表本身是一个ChartObject对象,并且您使用Shape对象来处理它。这里有一个链接,另一个稍微讨论了一下,还有一些来自这个MS链接的VBA代码,显示了它的一部分,注意有几种不同的方法来完成它:
Sub AddChart_Excel()
Dim objShape As Shape
' Create a chart and return a Shape object reference.
' The Shape object reference contains the chart.
Set objShape = ActiveSheet.Shapes.AddChart(XlChartType.xlColumnStacked100)
' Ensure the Shape object contains a chart. If so,
' set the source data for the chart to the range A1:C3.
If objShape.HasChart Then
objShape.Chart.SetSourceData Source:=Range("'Sheet1'!$A$1:$C$3")
End If
End Sub