从图表表创建excel.worksheet对象 [英] Create excel.worksheet object from chart sheet

问题描述

我正在通过VB.net在Excel中进行一些图表编制，但是在尝试为刚创建的图表创建新的工作表对象时遇到问题.创建完图表后，请执行以下操作以在新工作表上找到图表:

I am doing some charting in Excel via VB.net and I am having an issue trying to create a new worksheet object for a chart I just created. When I am done creating the chart, I do the following to locate the chart on a new sheet:

chartPage.Location(XlChartLocation.xlLocationAsNewSheet, "Chart")

完成此操作后，我便可以通过以下方式激活新的图表工作表:

After doing this, I am then able to activate the new chart worksheet via:

Dim XLSheets As Sheets = xlWorkBook.Sheets
XLSheets("Chart").activate()

然后，我想为我要做的新图表创建一个新的工作表对象:

I then want to create a new Worksheet object for the new chart sheet I do:

Dim XLChartSheet As Excel.Worksheet
XLChartSheet = xlWorkBook.Sheets("Chart")

但是，这会引发错误:

无法将类型为Microsoft.Office.Interop.Excel.ChartClass的COM对象转换为接口类型Microsoft.Office.Interop.Excel.Worksheet

我也尝试了下面的代码，但得到了相同的错误

However, this throws an error:

Unable to cast COM object of type Microsoft.Office.Interop.Excel.ChartClass to interface type Microsoft.Office.Interop.Excel.Worksheet

I have also tried the code below and I get the same error

XLChartSheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
XLChartSheet = CType(XLSheets("Chart"), Excel.Worksheet)

我可以为非图表工作表创建一个新的工作表对象，并且工作正常.在我看来，由于某种原因，它认为此图表表不是工作表.

有什么建议吗?

我尝试过的事情:

这是我的更多代码:

I can create a new worksheet object for a non-chart sheet and it works fine. It appears to me that for some reason it doesn''t think this chart sheet is a sheet.

Any suggestions?

What I have tried:

Here is more of my code:

Dim chartPage As Excel.Chart
Dim xlCharts As Excel.ChartObjects
Dim myChart As Excel.ChartObject
Dim R1 As Range, R2 As Excel.Range
Dim chartRange As Excel.Range

xlCharts = xlWorkSheet.ChartObjects
myChart = xlCharts.Add(250, 10, 500, 350)
chartPage = myChart.Chart
            

''Create a union range for the chart range
R1 = xlWorkSheet.Range(xlWorkSheet.Cells(1, 1), xlWorkSheet.Cells(LastCellRow, 2))
R2 = xlWorkSheet.Range(xlWorkSheet.Cells(1, 4), xlWorkSheet.Cells(LastCellRow, 4))

chartRange = xlApp.Union(R1, R2)
chartPage.SetSourceData(Source:=chartRange)

''Set the chart range to columns
chartPage.PlotBy = Excel.XlRowCol.xlColumns


''Change the chart type for each series
Dim ChartSeries As Excel.SeriesCollection = chartPage.SeriesCollection
ChartSeries.Item(1).ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
ChartSeries.Item(1).ChartType = XlChartType.xlColumnClustered

''Change the data label precision
If SelectedDownTimeChartUnits.ToUpper = "MINUTES" Then
   ChartSeries.Item(1).DataLabels.numberformat = "0"
Else ''Hours
   ChartSeries.Item(1).DataLabels.numberformat = "0.00"
End If

ChartSeries.Item(1).DataLabels.font.size = 7


''Create the 2nd series for the cumulative percentage
ChartSeries.Item(2).ApplyDataLabels(XlDataLabelsType.xlDataLabelsShowValue)
ChartSeries.Item(2).ChartType = XlChartType.xlLineMarkers
ChartSeries.Item(2).AxisGroup = XlAxisGroup.xlSecondary
''Format data labels
ChartSeries.Item(2).DataLabels.numberformat = "0.00%"
ChartSeries.Item(2).DataLabels.font.size = 7
ChartSeries.Item(2).DataLabels.position = Microsoft.Office.Interop.Excel.XlDataLabelPosition.xlLabelPositionAbove


''Move the legend
chartPage.Legend.Position = XlLegendPosition.xlLegendPositionBottom

''Add the main chart title
chartPage.HasTitle = True
chartPage.ChartTitle.Text = "Down Time Pareto for " & SelectedMachine & " From " & DatStart & " to " & DatEnd


''Add axis titles
XLAxisCategory = chartPage.Axes(, Excel.XlAxisGroup.xlPrimary)
XLAxisCategory.Item(XlAxisType.xlCategory).HasTitle = True
XLAxisCategory.Item(XlAxisType.xlCategory).AxisTitle.Text = "Down Time Reason"

XLAxisValue = chartPage.Axes(, XlAxisGroup.xlPrimary)
XLAxisValue.Item(XlAxisType.xlValue).HasTitle = True
XLAxisValue.Item(XlAxisType.xlValue).AxisTitle.Text = SelectedDownTimeChartUnits & " of Down Time"

''Add Secondary Axes title
XLSecondaryAxisValue = chartPage.Axes(, Excel.XlAxisGroup.xlSecondary)
XLSecondaryAxisValue.Item(XlAxisType.xlValue, XlAxisGroup.xlSecondary).HasTitle = True
XLSecondaryAxisValue.Item(XlAxisType.xlValue, XlAxisGroup.xlSecondary).AxisTitle.Text = "Cumulative %"
            
''AutoFit all the columns
xlWorkSheet.Columns.AutoFit()

''Move chart to separate sheet
chartPage.Location(XlChartLocation.xlLocationAsNewSheet, "Chart")

推荐答案

如果Sheets("Chart")对象表示工作簿中的图表对象，则必须使用

If Sheets("Chart") object represents a chart object in workbook, you have to use Chart[^] object rather than Worksheet. Note that chart object differs from standard sheet. It does not contain any cell! It''s just a chart.

XLChartObject = DirectCast(xlWorkBook.Charts("Chart"), Excel.Chart)
'or
XLChartObject = DirectCast(XLSheets("Chart"), Excel.Chart)

MSDN写道:

Chart对象也是Sheets集合的成员. Sheets集合包含工作簿中的所有工作表(图表工作表和工作表).使用Sheets(index)(其中index是工作表索引号或名称)来返回单个工作表.

The Chart object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets). Use Sheets(index), where index is the sheet index number or name, to return a single sheet.

有关更多详细信息，请参见:
图表界面(Microsoft.Office.Interop.Excel) [ ^ ]
使用Visual Basic for Applications代码在Microsoft Office Excel 2003中创建图表 [ ^ ]
DirectCast运算符(Visual Basic)| Microsoft文档 [ ^ ]

For further details, please see:
Charts interface (Microsoft.Office.Interop.Excel)[^]
Creating Charts in Microsoft Office Excel 2003 Using Visual Basic for Applications Code[^]
DirectCast Operator (Visual Basic) | Microsoft Docs[^]

这篇关于从图表表创建excel.worksheet对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！