Coloring cells in an OpenXml SpreadsheetDocument

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à !