如何使用C＃在Excel中更改系列颜色？

I have written a program in C# whereby it automatically generates a graph for me from a CSV file and puts it onto a new XLS file. However, I need to change the color of the Line (as it is a Line Chart) to red rather than the default blue.

我在C＃中编写了一个程序，它从CSV文件中自动为我生成一个图形并将其放到一个新的XLS文件中。但是，我需要将Line的颜色（因为它是折线图）更改为红色而不是默认的蓝色。

I am finding this extremely difficult to do and the stuff I've found online has not worked. Please can someone tell me how to do this?

我发现这很难做到，我在网上发现的东西都没用。请有人能告诉我怎么做吗？

4 个解决方案

#1

Most of these type of problems come from not being able to find the exact object and property that needs to be changed.

大多数这类问题都来自于无法找到需要更改的确切对象和属性。

A sure way to get to this information is to open your Excel file and go to the line chart. Start recording a macro, then change the item that you want to change. Stop recording the macro, and look at the code it generated. This will give you the exact object and property that must be used.

获取此信息的一种可靠方法是打开Excel文件并转到折线图。开始录制宏，然后更改要更改的项目。停止录制宏，并查看它生成的代码。这将为您提供必须使用的确切对象和属性。

You can then make sure that your C# code is using the correct object and property syntax.

然后，您可以确保您的C＃代码使用正确的对象和属性语法。

#2

Here is an example. I noticed when I tried to pass an integer that the bytes seem to be read in reverse order. So assigning 0xFF0000 makes the color blue and 0x0000FF turns the line red. Fortunately Microsoft provided an enumeration.

这是一个例子。我注意到当我尝试传递一个整数时，字节似乎以相反的顺序读取。因此，分配0xFF0000会使颜色变为蓝色，而0x0000FF会将该行变为红色。幸运的是，Microsoft提供了一个枚举。

Random random = new Random();
Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();
xla.Visible = true;
Workbook wb = xla.Workbooks.Add(XlSheetType.xlWorksheet);

Worksheet ws = (Worksheet)xla.ActiveSheet;

// Now create the chart.
ChartObjects chartObjs = (ChartObjects)ws.ChartObjects();
ChartObject chartObj = chartObjs.Add(150, 20, 300, 300);
Chart xlChart = chartObj.Chart;
for (int row = 0; row < 16; row++)
{
    ws.Cells[row + 2, 2] = row + 1;
    ws.Cells[row + 2, 3] = random.Next(100);
}

Range xValues = ws.Range["B2", "B17"];
Range values = ws.Range["C2", "C17"];

xlChart.ChartType = XlChartType.xlLine;
SeriesCollection seriesCollection = chartObj.Chart.SeriesCollection();

Series series1 = seriesCollection.NewSeries();
series1.XValues = xValues;
series1.Values = values;

series1.Format.Line.ForeColor.RGB = (int)XlRgbColor.rgbRed;
series1.Format.Line.Weight = 5;

#3

To change the color of a line series, you can use the border property:

要更改线系列的颜色，可以使用border属性：

series.Border.Color = (int)Excel.XlRgbColor.rgbGreen;

The colors can also be changed via the chart legend.

也可以通过图表图例更改颜色。

To change the color of a line:

要更改线条的颜色：

((Excel.LegendEntry)chart.Legend.LegendEntries(1)).LegendKey.Border.ColorIndex = 10;

To change the color of a bar:

要更改条形的颜色：

((Excel.LegendEntry)chart.Legend.LegendEntries(1)).LegendKey.Interior.Color = (int)Excel.XlRgbColor.rgbRed;

#4

Recording a macro is definitely not a "sure" way to find the answer. For me in Excel 2007 with my chart recording gives me nothing except a long list of "ActiveSheet.ChartObjects("Chart 1").Activate". None of the answers above (or elsewhere when I searched) worked for me; however, I was able to change line thickness and text and change the markers.

录制宏绝对不是找到答案的“确定”方式。对于我来说，在Excel 2007中使用我的图表记录除了一长串“ActiveSheet.ChartObjects（”图表1“）之外什么都没有给我。激活”。上面的答案（或我搜索的其他地方）都没有为我工作;但是，我能够改变线条粗细和文字并更改标记。

The problem I was having with changing color is that the color is defaulting to Automatic for me when I make a new chart or add a new series. In order to turn off this behavior apparently you need to set the Line.Visible property to msoTriStateMixed. If I change Visible back to msoTrue, then the lines go back to their original color, and the "Automatic" is checked under Format Data Series, Line Style properties again. This code works for me in Excel 2007:

我改变颜色的问题是，当我制作新图表或添加新系列时，颜色默认为自动。为了关闭此行为，显然您需要将Line.Visible属性设置为msoTriStateMixed。如果我将Visible更改回msoTrue，则线条将恢复为原始颜色，并再次在“格式数据系列”，“线条样式”属性下选中“自动”。此代码适用于Excel 2007：

Excel.Series series = (Excel.Series)chartPage.SeriesCollection(1);
series.Format.Line.Weight = 1.0F;
series.Format.Line.Visible = MsoTriState.msoTriStateMixed;  //Tri-State 
series.Format.Line.ForeColor.RGB =(int)Microsoft.Office.Interop.Excel.XlRgbColor.rgbRed;

#1