How to work with style Index in Open xml?

Question

Can Anyone please explain How Style Index in OpenXml works? I have a business requirement where I need to apply background color to certain cells in an excel sheet. And Some style is already applied to other cells. So I need to decide which style Index I need to apply.

Answer 1

OpenXML styling can be confusing when you take the first look at it. Excel document styling falls under the SpreadsheetML markup language, which is different from Word and PowerPoint..

For typical cells in Excel, the only style information required is the StyleIndex (as you pointed out).

Cell cell16 = new Cell(){ CellReference = "HU1", StyleIndex = (UInt32Value)1U, DataType = CellValues.SharedString };

This corresponds to the s attribute in the XML:

      <x:c r="HU1" s="1" t="s">
        <x:v>0</x:v>
      </x:c>

The StyleIndex is the zero-based index of the CellFormat record in the Styles part.

The styles part (aka the Stylesheet of the workbook) contains the following sections:

• Numbering Formats
• Fonts
• Fills
• Borders
• Cell Style Formats
• Cell Formats <== cell styleindex is referring to one of these
• Cell Styles
• Differential Formats
• Table Styles
• Colors
• Stylesheet Extention List

Now inside the CellFormat record, there are references that refer back out to each of the following sections in the stylesheet:

1. Numbering Format (first bullet above)
2. Font (second bullet above)
3. Fill (third bullet above)
4. Border (fourth bullet above)

An example cell format in code looks like:

// this line is important to your question
CellFormat cellFormat5 = new CellFormat(){ NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)11U, 
//the rest of the CellFormat definition is not so important to your question
FormatId = (UInt32Value)0U, ApplyFill = true, ApplyBorder = true, ApplyAlignment = true };

To answer your question: apply a certain background color to certain cells . Let say we want to update cell B3 of your spreadsheet and B3 already has StyleIndex of 10.

You will need to follow these steps:

Step 1. If this is a new background color to the spreadsheet, add the background (aka Fill) to the Fills section of the Stylesheet (third bullet above) that contains your new color. If the color already exists, you need to find and remember the index of the existing Fill for that color. Either way, for this example lets say the Fill index you requre is 25.

Step 2. Create a new CellFormat that is a copy of the CellFormat at index 10. You will add this new CellFormat to the end of the CellFormat section. Lets say the index of the new CellFormat will be 53.

Step 3. You update the CellFormat at index 53 and make its Fill index property be 25 (from Step 1).

Last Step: Update the Cell in question B3, to have a new StyleIndex of 53

Note: This answer is for non-table cell styling in Excel - if you want styling information for table cells, please reply and Ill try and update or add an answer for it.

This answer comes mainly out of my experience and also interpretation of pages 73-79 of the free e-book: Open XML - The markup explained - by Wouter van Vugt. It is a good reference to use for all OpenXml.