I am using OpenXml for Office SDK to create a spreadsheet. I am having trouble getting the cell background colors working properly. Any cell I try to apply a background color to shows as black.
Here is an excerpt of my xml that gets generated:
<x:styleSheet xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x14ac="http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac" xmlns:x16r2="http://schemas.microsoft.com/office/spreadsheetml/2015/02/main" xmlns:xr="http://schemas.microsoft.com/office/spreadsheetml/2014/revision" mc:Ignorable="x14ac x16r2 xr" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<x:fonts count="10">
<x:font>
<x:sz val="14" />
<x:name val="Calibri" />
</x:font>
<x:font>
<x:sz val="12" />
<x:name val="Calibri" />
</x:font>
<x:font>
<x:sz val="12" />
<x:name val="Arial" />
</x:font>
<x:font>
<x:sz val="10" />
<x:name val="Calibri" />
</x:font>
<x:font>
<x:sz val="9" />
<x:name val="Arial" />
</x:font>
<x:font>
<x:sz val="10" />
<x:name val="Century Schoolbook" />
</x:font>
<x:font>
<x:sz val="11" />
<x:name val="Times New Roman" />
</x:font>
<x:font>
<x:sz val="11" />
<x:name val="Arial" />
</x:font>
<x:font>
<x:sz val="10" />
<x:name val="Arial" />
</x:font>
<x:font>
<x:sz val="11" />
<x:name val="Calibri" />
</x:font>
</x:fonts>
<x:fills count="9">
<x:fill>
<x:patternFill patternType="none" />
</x:fill>
<x:fill>
<x:patternFill patternType="gray125" />
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFFF9900" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFFFCC99" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FF000000" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFFFFF99" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFFFCC00" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFC0C0C0" />
</x:patternFill>
</x:fill>
<x:fill>
<x:patternFill patternType="solid">
<x:bgColor rgb="FFFFFF00" />
</x:patternFill>
</x:fill>
</x:fills>
<x:borders count="1">
<x:border>
<x:left />
<x:right />
<x:top />
<x:bottom />
<x:diagonal />
</x:border>
</x:borders>
<x:cellStyleXfs count="1">
<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" />
</x:cellStyleXfs>
<x:cellXfs count="23">
<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<x:xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="1" fillId="1" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="2" fillId="1" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="3" fillId="3" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="4" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="4" fillId="5" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="4" fillId="6" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="8" fillId="7" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="8" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="9" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" />
<x:xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="0" fillId="2" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="4" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="5" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="2" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="6" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="7" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="8" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="9" fillId="4" borderId="0" xfId="0" applyFont="1" applyFill="1" />
<x:xf numFmtId="0" fontId="9" fillId="8" borderId="0" xfId="0" applyFont="1" applyFill="1" />
</x:cellXfs>
<x:cellStyles count="1">
<x:cellStyle name="Normal" xfId="0" builtinId="0" />
</x:cellStyles>
<x:dxfs count="0" />
<x:tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16" />
<x:extLst>
<x:ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}">
<x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1" />
</x:ext>
<x:ext xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" uri="{9260A510-F301-46a8-8635-F512D64BE5F5}">
<x15:timelineStyles defaultTimelineStyle="TimeSlicerStyleLight1" />
</x:ext>
</x:extLst>
</x:styleSheet>
The code used to generate it looks like this:
fillList is just a List if Colors
Fills fillsNode = new Fills() { Count = GetUint(_fillList.Count)};
foreach (System.Drawing.Color color in _fillList) {
fillsNode.AppendChild(GetFill(color.R, color.G, color.B));
}
stylesheet1.Append(fillsNode);
These functions are used to create the Fill object and translate r, g and b values to the formatted hex string. This is one of the places I suspect I may be going wrong.
private Fill GetFill(int r, int g, int b) {
PatternFill pf1 = new PatternFill { PatternType = PatternValues.Solid };
pf1.Append(new BackgroundColor() { Rgb = GetHexStringFromRgb(r, g, b)});
Fill f1 = new Fill();
f1.Append(pf1);
return f1;
}
private string GetHexStringFromRgb(int r, int g, int b) {
return $"FF{r.ToString("X2")}{g.ToString("X2")}{b.ToString("X2")}";
}
Any help would be appreciated.
