在使用OpenXML时无法设置Excel中所需的背景颜色

Question

我刚接触 open 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

生成的输出:

期望的输出:

请帮我。先感谢您。

- Yashpal S

Answer 1

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" } }

- petelids