Tow days, and many readings. That's the time I needed to figure out how to set a cell color in an openXml spreadsheet.
It finally ends by the use of the OpenXml SDK Productivity Tool.
The main point seems to be that there must be a minimal stylesheet in the spreadsheet. Among other, this minimal stylesheet must comprise s two Fills. This styleshett may be generated by the following code:
private void GenerateWorkbookStylesPartContent(WorkbookPart workbookPart, String partId) {
WorkbookStylesPart wsp = workbookPart.AddNewPart(partId);
Stylesheet stylesheet = new Stylesheet() {
MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet.AddNamespaceDeclaration("mc",
"http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet.AddNamespaceDeclaration("x14ac",
"http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
Fonts fonts = new Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
Font font = new Font();
FontSize fontSize = new FontSize() { Val = 11D };
Color color = new Color() { Theme = (UInt32Value)1U };
FontName fontName = new FontName() { Val = "Calibri" };
FontFamilyNumbering fontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
font.Append(fontSize);
font.Append(color);
font.Append(fontName);
font.Append(fontFamilyNumbering);
font.Append(fontScheme);
fonts.Append(font);
stylesheet.Fonts = fonts;
Borders borders = new Borders() { Count = (UInt32Value)1U };
Border border = new Border();
LeftBorder leftBorder = new LeftBorder();
RightBorder rightBorder = new RightBorder();
TopBorder topBorder = new TopBorder();
BottomBorder bottomBorder = new BottomBorder();
DiagonalBorder diagonalBorder = new DiagonalBorder();
border.Append(leftBorder);
border.Append(rightBorder);
border.Append(topBorder);
border.Append(bottomBorder);
border.Append(diagonalBorder);
borders.Append(border);
stylesheet.Borders = borders;
stylesheet.Fills = new Fills();
Fill f = new Fill { PatternFill =
new PatternFill { PatternType = PatternValues.None}};
stylesheet.Fills.Append(f);
stylesheet.Fills.Append(new Fill { PatternFill =
new PatternFill { PatternType = PatternValues.Gray125 } });
CellFormats cellFormats = new CellFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat = new CellFormat() {
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U,
FormatId = (UInt32Value)0U };
cellFormats.Append(cellFormat);
stylesheet.CellFormats = cellFormats;
CellStyles cellStyles = new CellStyles() { Count = (UInt32Value)1U };
CellStyle cellStyle = new CellStyle() {
Name = "Normal", FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
stylesheet.CellStyles = cellStyles;
CellStyleFormats cellStyleFormats = new CellStyleFormats() { Count = (UInt32Value)1U };
CellFormat cellFormat2 = new CellFormat() {
NumberFormatId = (UInt32Value)0U,
FontId = (UInt32Value)0U,
FillId = (UInt32Value)0U,
BorderId = (UInt32Value)0U };
cellStyleFormats.Append(cellFormat2);
stylesheet.CellStyleFormats = cellStyleFormats;
cellStyles.Append(cellStyle);
wsp.Stylesheet = stylesheet;
}
From here, all what remain to do is handle the color in the cells, during the process I use a dictionary to avoid querying the stylesheet. In my case the key type is System.Drawing.Color because I'm exporting a DataGridView but, of course this type can be of any type you need.
private Dictionary _colors =
new Dictionary();
Then, somewhere in the code:
UInt32 cellStyleUid = 0;
if ( col != System.Drawing.Color.Transparent) {
if (!_colors.ContainsKey(col)) {
//that is the style does not exists for this color
if (_ssDoc.WorkbookPart.WorkbookStylesPart == null) {
GenerateWorkbookStylesPartContent(_ssDoc.WorkbookPart, "rId5");
}
//Create the Fill
Fill fill = new Fill();
PatternFill pf = new PatternFill { PatternType = PatternValues.Solid };
ForegroundColor fgc = new ForegroundColor {
Rgb = HexBinaryValue.FromString(Convert.ToString(col.ToArgb(), 16)) };
BackgroundColor bgc = new BackgroundColor() { Indexed = (UInt32Value)64U };
pf.Append(fgc);
pf.Append(bgc);
fill.Append(pf);
//update the stylesheet
_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills.Append(fill);
Int32 iFill = _ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills.Count() - 1;
_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills.Count = (UInt32)(iFill + 1);
//Create the CellFormat to use the created Fill
CellFormat lcf =
(CellFormat)_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.LastChild;
CellFormat cf = new CellFormat {
NumberFormatId = lcf.NumberFormatId,
FontId = lcf.FontId,
FillId = (UInt32Value)(UInt32)iFill,
BorderId = lcf.BorderId,
FormatId = lcf.FormatId,
ApplyFill = true,
ApplyFont = lcf.ApplyFont
};
//update the stylesheet
_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Append(cf);
Int32 iCellFormat =
_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count() - 1;
_ssDoc.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Count =
(UInt32)(iCellFormat + 1);
//put the index of the new CellFormat in the buffer
_colors.Add(col, (UInt32)iCellFormat);
}
//retrieve the index of the cell format for the color
cellStyleUid = _colors[col];
}
It remains to use the updated stylesheet at the cell level. With cell being of type DocumentFormat.OpenXml.Spreadsheet.Cell
if (cellStyleUid != 0)
cell.StyleIndex = cellStyleUid;
Et voilà !