无法使用openxml在excel中设置所需的背景颜色(Unable to set desired background color in excel using openxml)

我是新手，打开xml并尝试将标题行的背景颜色设置为灰色，但始终将其设置为黑色。请参考我正在使用的以下代码。

return new Stylesheet( new Fonts( new Font( // Index 0 - The default font. new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), new Font( // Index 1 - The bold white font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "ffffff" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }), new Font( // Index 2 - The bold red font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "ff0000" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }), new Font( // Index 2 - The bold red font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }) ), new Fills( new Fill( // Index 0 - The default fill. new PatternFill() { PatternType = PatternValues.None }), new Fill( // Index 1 - The default fill of gray 125 (required) new PatternFill() { PatternType = PatternValues.Gray125 }), new Fill( // Index 2 - The blue fill. new PatternFill( new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "006699" } } ) { PatternType = PatternValues.Solid }), new Fill( // Index 3 - The grey fill. new PatternFill( new BackgroundColor () { Rgb = new HexBinaryValue(){ Value = "808080" } } ) { PatternType = PatternValues.Solid } ) ), new Borders( new Border( // Index 0 - The default border. new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder()), new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell new LeftBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new RightBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new TopBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new BottomBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new DiagonalBorder()) ), new CellFormats( new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 1, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead new CellFormat( new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 1, FillId = 2, BorderId = 0, ApplyFont = true }, // Index 1 - Bold White Blue Fill new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 2, FillId = 2, BorderId = 0, ApplyFont = true } , // Index 2 - Bold Red Blue Fill new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 3, FillId = 3, BorderId = 0, ApplyFont = true } ) ); // return

生成的输出：

期望的输出：

请帮助我。提前致谢

I am new to open xml and trying to set the background color of header row to gray but it always set it to black. Please refer following code which I am using.

return new Stylesheet( new Fonts( new Font( // Index 0 - The default font. new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new FontName() { Val = "Calibri" }), new Font( // Index 1 - The bold white font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "ffffff" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }), new Font( // Index 2 - The bold red font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "ff0000" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }), new Font( // Index 2 - The bold red font. new DocumentFormat.OpenXml.Spreadsheet.Bold(), new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11 }, new DocumentFormat.OpenXml.Spreadsheet.Color() { Rgb = new HexBinaryValue() { Value = "000000" } }, new DocumentFormat.OpenXml.Spreadsheet.FontName() { Val = "Calibri" }) ), new Fills( new Fill( // Index 0 - The default fill. new PatternFill() { PatternType = PatternValues.None }), new Fill( // Index 1 - The default fill of gray 125 (required) new PatternFill() { PatternType = PatternValues.Gray125 }), new Fill( // Index 2 - The blue fill. new PatternFill( new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "006699" } } ) { PatternType = PatternValues.Solid }), new Fill( // Index 3 - The grey fill. new PatternFill( new BackgroundColor () { Rgb = new HexBinaryValue(){ Value = "808080" } } ) { PatternType = PatternValues.Solid } ) ), new Borders( new Border( // Index 0 - The default border. new LeftBorder(), new RightBorder(), new TopBorder(), new BottomBorder(), new DiagonalBorder()), new Border( // Index 1 - Applies a Left, Right, Top, Bottom border to a cell new LeftBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new RightBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new TopBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new BottomBorder( new Color() { Auto = true } ) { Style = BorderStyleValues.Thin }, new DiagonalBorder()) ), new CellFormats( new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }) { FontId = 1, FillId = 0, BorderId = 0 }, // Index 0 - The default cell style. If a cell does not have a style index applied it will use this style combination instead new CellFormat( new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 1, FillId = 2, BorderId = 0, ApplyFont = true }, // Index 1 - Bold White Blue Fill new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 2, FillId = 2, BorderId = 0, ApplyFont = true } , // Index 2 - Bold Red Blue Fill new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center } ) { FontId = 3, FillId = 3, BorderId = 0, ApplyFont = true } ) ); // return

Generated output:

Desired output:

Pls help me. Thanks in advance

最满意答案

PatternFill的文档（ ECMA-376标准的第18.8.32节）说（强调我的）：

对于固体细胞填充（无图案），使用fgColor 。对于指定图案的单元格填充，则单元格填充颜色由bgColor元素指定。

您已指定PatternType = PatternValues.Solid但您提供的是BackgroundColor而不是ForegroundColor 。从BackgroundColor更改为ForegroundColor将解决您的问题，即此行：

new BackgroundColor() { Rgb = new HexBinaryValue(){ Value = "808080" } }

应成为：

new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "808080" } }

The documentation for PatternFill (section 18.8.32 of the ECMA-376 standard) says (emphasis mine):

For solid cell fills (no pattern), fgColor is used. For cell fills with patterns specified, then the cell fill color is specified by the bgColor element.

You have specified PatternType = PatternValues.Solid but you are providing a BackgroundColor rather than a ForegroundColor. Changing from the BackgroundColor to the ForegroundColor will solve your issue i.e. this line:

new BackgroundColor() { Rgb = new HexBinaryValue(){ Value = "808080" } }

should become:

new ForegroundColor() { Rgb = new HexBinaryValue() { Value = "808080" } }

更多推荐