C# Excel Chart Change color
I am trying to change the line color of the chart series to None. I tried setting the color and the marker property but the program errors out.
My code:
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);
_Ch开发者_StackOverflowart.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);
Need code to set the color of the series to None.Any suggestions would be useful.
This code can set the colours of the slices of all the pie charts in my spreadsheet based on each slice's legend's label:
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
And this code can set barchart colours:
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
Maybe could help you!
精彩评论