Stylizing your Excel worksheets with Open XML 2.0


It’s a long overdue post so I will get right to it. This is not a post about Themes and I probably will never get into it.  The class that applies the actual styling is a collection called CellFormats, which holds a number of CellFormat objects, which basically indexes the Ids of defined Fonts, Fills, Borders, Alignments, etc..  There is also another collection called CellStyleFormats that makes the indents in the ribbon control (under styles group box) to show what your styles implement.  Filling out this collection is optional.  Lets get to coding.
I am going to show how to apply the most commonly used styles, Font, Fills, Borders, and Alignments.  I’ll leave the rest for you to practice on your own.  Try adding a money number format to cell A3.  FYI, I am using Open XML 2.0 (April 2008 CTP).

First off, let’s insert your style sheet part to the workbook (check out how to create a workbook):

WorkbookStylesPart stylesPart = spreadSheet.WorkbookPart.AddNewPart<WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();

Now to implement the GenerateStyleSheet method:

private Stylesheet GenerateStyleSheet()
{
    return new Stylesheet(
        new Fonts(
            new Font(                                                               // Index 0 – The default font.
                new FontSize(){ Val = 11 },
                new Color(){ Rgb = new HexBinaryValue() { Value = “000000” } },
                new FontName(){ Val = “Calibri” }),
            new Font(                                                               // Index 1 – The bold font.
                new Bold(),
                new FontSize(){ Val = 11 },
               
new Color(){ Rgb = new HexBinaryValue() { Value = “000000” } },
                new FontName(){ Val = “Calibri” }),
            new Font(                                                               // Index 2 – The Italic font.
                new Italic(),
                new FontSize(){ Val = 11 },
                new Color(){ Rgb = new HexBinaryValue() { Value = “000000” } },

                new FontName(){ Val = “Calibri” }),
            new Font(                                                               // Index 2 – The Times Roman font. with 16 size
                new FontSize(){ Val = 16 },
               
new Color(){ Rgb = new HexBinaryValue() { Value = “000000” } },
                new FontName(){ Val = “Times New Roman” })
        ),
        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 yellow fill.
                new PatternFill(
                    new ForegroundColor(){ Rgb = new HexBinaryValue() { Value = “FFFFFF00”} }
                ){ 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(){ FontId = 0, 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(){ FontId = 1, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 1 – Bold 
            new CellFormat(){ FontId = 2, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 2 – Italic
            new CellFormat(){ FontId = 3, FillId = 0, BorderId = 0, ApplyFont = true },       // Index 3 – Times Roman
            new CellFormat(){ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true },       // Index 4 – Yellow Fill
            new CellFormat(                                                                   // Index 5 – Alignment
                new Alignment(){ Horizontal = HorizontalAlignmentValues.Center, Vertical = VerticalAlignmentValues.Center }
            ){ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
            new CellFormat(){ FontId = 0, FillId = 0, BorderId = 1, ApplyBorder = true }      // Index 6 – Border
        )
    ); // return
}

Finally we apply the styles to the individual cells using StyleIndex:

new Cell(new CellValue(“Bold”)) { DataType = CellValues.String, StyleIndex = 1 },   // Cell A1

You should get the following:

Comments (9)

  1. Anonymous says:

    I’ve merged this code with the creating an excel doc stuff you did previously (and made some minor amends).

    Firstly, there seem to be some errors in the above code.  Namely the colours of the fonts should have 8 digits ("FFFFFFFF") and one of the FillIds in the CellFormat list is invalid.

    Secondly, even fixing those, I’m not getting any styling.  In fact, the text I’m putting in is there but transparent (so StyleIndex = 0 it would seem.

    I’m also not sure if the StyleIndex is correctly selecting a CellFormat (which is what we have created).

    And as you can see, I don’t know much open xml!

    Thanks and please keep generating examples!

  2. Anonymous says:

    Good code, you do have a few errors, the last guy was right, the digits need to be 8 characters (the HexBinaryValue function isn’t necessary or even available for me), and the line that says:

    new CellFormat(){ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true },  

    the FillId should be 1, at 2 it causes an error.

  3. Anonymous says:

    Okay, through some more debugging, I noticed that the 0 indexed fill is always none, and 1 is always Gray125, so the Fills section should start with:

    new Fills(new Fill(new PatternFill() {PatternType = PatternValues.None }), new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }),

    Then the custom fills can be added.  I found this out here:

    http://www.eggheadcafe.com/community/aspnet/4/10115456/openxml-stylesheet-filli.aspx

  4. Anonymous says:

    Actually, the color only needs 6 digits.  When I changed my color from FFFFFF to 000000, the font transparency issue disappeared.

  5. Chris Quon says:

    I fixed the Fill, not sure why I left out the declaration of the Gray125 because I index correctly the fill Id in the cellformats.  So I edited the post and added in the Gray125.  The code can run, cut/paste, now and it will work.

    Colors can be 6 Hex digits, FFFFFF is "white" which explains the transparency.  I'll change it to black.

    Also there is a new release of the OpenXML 2.0 SDK (March 2010).

  6. Anonymous says:

    Thank u… It worked for me…

  7. Anonymous says:

    Thanks buddy. Worked perfectly for me 🙂

  8. Anonymous says:

    Thanks alot

  9. Anonymous says:

    Thanks a lot buddy. This helped a lot.