C# Excel 图表改变颜色

Question

我正在尝试将图表系列的线条颜色更改为“无”。我尝试设置颜色和标记属性，但程序出错。

我的代码：

 Excel.ChartObjects _ChartObjects = (Excel.ChartObjects)(oSheet1.ChartObjects(Missing.Value));
            Excel.ChartObject _ChartObject = _ChartObjects.Add(170, 0, 400, 300);
            Excel.Chart _Chart = _ChartObject.Chart;
            Excel.Range oRng1;
            oRng1 = oSheet1.get_Range("A1","E55");            
            _Chart.SetSourceData(oRng1, Excel.XlRowCol.xlColumns);
            _Chart.ChartType = Microsoft.Office.Interop.Excel.XlChartType.xlLineMarkers;
            oWB.ShowPivotChartActiveFields = true;
            //_Chart.ChartTitle.Font.Size = 12;
            _Chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementDataTableWithLegendKeys);
            _Chart.DataTable.Font.Size = 6;
            oWB.ShowPivotChartActiveFields = false;
            _Chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleRotated);
            _Chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementLegendNone);
            //_Chart.Legend.Delete();
            _Chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementPrimaryValueAxisTitleNone);
            _Chart.Location(Excel.XlChartLocation.xlLocationAsNewSheet, "Tax Weekly Term-Chart");
           Excel.SeriesCollection seriesCollection = (Excel.SeriesCollection)_Chart.SeriesCollection(Type.Missing);

            Excel.Series series = (Excel.Series)seriesCollection.Item(4);

需要代码将系列的颜色设置为“无”。任何建议都会有用。

Answer 1

此代码可以根据每个切片的图例标签设置电子表格中所有饼图的切片颜色：

Sub SetPieChartColours()

    ' Iterates through all pie charts in the dashboard and apply colours to the appropriate legends

    ' Colour indices:
    ' Passed (Green)             10
    ' Not Completed (Yellow)     19
    ' No run (Blue)              37
    ' Failed (Maroon)            18
    ' Failed Issue (Pink)        24
    ' Failed Defect (Red)        3

    Dim savePtLabel As String
    Dim ThisPt As String

    Dim NumPoints As Integer
    Dim x As Integer

    Dim pie As ChartObject
    For Each pie In ActiveSheet.ChartObjects

         ' Check that the current chart object is a pie chart
         If pie.Chart.ChartType = xlPie Then

            NumPoints = pie.Chart.SeriesCollection(1).Points.Count
            For x = 1 To NumPoints

                ' Save the label currently attached to the current slice
                If pie.Chart.SeriesCollection(1).Points(x).HasDataLabel = True Then
                    savePtLabel = pie.Chart.SeriesCollection(1).Points(x).DataLabel.Text
                Else
                    savePtLabel = ""
                End If

                ' Assign a new data label of just the point name
                pie.Chart.SeriesCollection(1).Points(x).ApplyDataLabels Type:= _
                   xlDataLabelsShowLabel, AutoText:=True
                ThisPt = pie.Chart.SeriesCollection(1).Points(x).DataLabel.Text

                ' Based on the label of this slice, set the color
                Select Case ThisPt
                    Case "Failed-Defect"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 3
                    Case "Failed-Issue"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 24
                    Case "Failed"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 18
                    Case "No Run"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 37
                    Case "Not Completed"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 18
                    Case "Passed"
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 10
                    Case Else
                        ' Aroo! The label of the current slice doesn't match any expected labels
                        pie.Chart.SeriesCollection(1).Points(x).Interior.ColorIndex = 1
                End Select

                ' Return the label to it's original state
                pie.Chart.SeriesCollection(1).Points(x).ApplyDataLabels Type:=xlDataLabelsShowNone, AutoText:=True

            Next x
         End If
     Next

End Sub

此代码可以设置条形图颜色：

Sub SetBarChartColours()

    Dim savePtLabel As String
    Dim ThisPt As String

    Dim NumPoints As Integer
    Dim x As Integer

    Dim bar As ChartObject

    For Each bar In ActiveSheet.ChartObjects
       If bar.Chart.Name = "Dashboard Chart 5" Then
            NumPoints = bar.Chart.SeriesCollection.Count
            For x = 1 To NumPoints
                MsgBox bar.Chart.Legend.LegendEntries(x).LegendKey.Interior.ColorIndex


            Next x
        End If
    Next

End Sub

也许可以帮助您！