OpenXML SDK 2.0: Formatting Excel Values

This is a follow-up article to my previous post, OpenXML SDK 2.0: Export a DataTable to Excel. In that post, I demonstrated how to install and use the OpenXML SDK, and also provided sample code for exporting a DataTable to Microsoft Excel 2007. I highly recommend starting with the previous post if you are not familiar with the SDK.

This article builds on the original code and demonstrates how to apply style and value formatting to the cells inside the workbook.

To enable value and text formatting for the resulting workbook, I have created new helper methods.

Setting Colors and Styles

This first method will create a new Font object and add it to the stylesheet collection within the workbook:

1. private UInt32Value createFont(
2.              Stylesheet styleSheet,
3.              string fontName,
4.              Nullable<double> fontSize,
5.              bool isBold,
6.             System.Drawing. Color foreColor)
7.         {
8.  
9.              Font font = new Font();
10.             
11.              if (!string.IsNullOrEmpty(fontName))
12.             {
13.                  FontName name = new FontName()
14.                 {
15.                     Val = fontName
16.                 };
17.                 font.Append(name);
18.             }
19.  
20.              if (fontSize.HasValue)
21.             {
22.                  FontSize size = new FontSize()
23.                 {
24.                     Val = fontSize.Value
25.                 };
26.                 font.Append(size);
27.             }
28.  
29.              if (isBold == true)
30.             {
31.                  Bold bold = new Bold();
32.                 font.Append(bold);
33.             }
34.  
35.              if (foreColor != null)
36.             {
37.                  Color color = new Color()
38.                 {
39.                     Rgb = new HexBinaryValue()
40.                     {
41.                         Value =
42.                             System.Drawing. ColorTranslator.ToHtml(
43.                                 System.Drawing. Color.FromArgb(
44.                                     foreColor.A,
45.                                     foreColor.R,
46.                                     foreColor.G,
47.                                     foreColor.B)).Replace( "#", "")
48.                     }
49.                 };
50.                 font.Append(color);
51.             }
52.             styleSheet.Fonts.Append(font);
53.              UInt32Value result = styleSheet.Fonts.Count;
54.             styleSheet.Fonts.Count++;
55.              return result;
56.         }

The return value of the method above is the index number of the newly added Font object.

Next is the createFill method. This method works exactly like the createFont method and also returns a new index value after the Fill has been added to the workbook's styles collection:

1. private UInt32Value createFill(
2.     Stylesheet styleSheet,
3.     System.Drawing.Color fillColor)
4. {
5.     Fill fill = new Fill(
6.         new PatternFill(
7.              new ForegroundColor()
8.             {
9.                 Rgb = new HexBinaryValue()
10.                 {
11.                     Value =
12.                     System.Drawing. ColorTranslator.ToHtml(
13.                         System.Drawing. Color.FromArgb(
14.                             fillColor.A,
15.                             fillColor.R,
16.                             fillColor.G,
17.                             fillColor.B)).Replace( "#", "")
18.                 }
19.             })
20.         {
21.             PatternType = PatternValues.Solid
22.         }
23.     );
24.     styleSheet.Fills.Append(fill);
25.  
26.     UInt32Value result = styleSheet.Fills.Count;
27.     styleSheet.Fills.Count++;
28.     return result;
29. }

 

Finally, once you have created a Font and/or Fill, you need to create a CellFormat object and add it to your styles collection:

1. private UInt32Value createCellFormat(
2.     Stylesheet styleSheet,
3.     UInt32Value fontIndex,
4.     UInt32Value fillIndex,
5.     UInt32Value numberFormatId)
6. {
7.     CellFormat cellFormat = new CellFormat();
8.     
9.     if (fontIndex != null)
10.         cellFormat.FontId = fontIndex;
11.     
12.     if (fillIndex != null)
13.         cellFormat.FillId = fillIndex;
14.  
15.     if (numberFormatId != null)
16.     {
17.         cellFormat.NumberFormatId = numberFormatId;
18.         cellFormat.ApplyNumberFormat = BooleanValue.FromBoolean(true);
19.     }
20.  
21.     styleSheet.CellFormats.Append(cellFormat);
22.  
23.     UInt32Value result = styleSheet.CellFormats.Count;
24.     styleSheet.CellFormats.Count++;
25.     return result;
26. }

 

Once you have created your CellFormat objects, you can apply them to any cell value by setting the StyleIndex property for the Cell object. This code demonstrates using all of the methods above to create a header format for the first row of the resulting workbook:

The final piece of the solution is to add the styleIndex parameter to the createTextCell method and set the StyleIndex property on the Cell:

Formatting Cell Values Correctly

The fourth parameter for the createCellFormat method allows you to format cell values based on your data types. Although you can create custom cell formats in the same manner that you build the font and background color, the OpenXML specification (detailed in the section 8.8.30 of of the Standard ECMA-376 Office Open XML File Formats second edition draft) has a set of implied cell formats, meaning you can reference them without explicitly adding them to the StyleSheet. Here is a list of the implied formats from the draft:

If you run the sample code provided in the first article, one thing you will notice about the resulting Excel document is that even cells with numeric values are treated as text. Anyone familiar with Excel has seen this pop-up tag on many occasions:

Error correction in Excel highlights the values that are stored as text.

The reason behind this was due to the way cell values were added in the createTextCell method. Because the DataType of the cell is set to InlineString, the content passed into the cell was always treated as text. To overcome this limitation, I created another new method, createValueCell. First, I created three new basic styles using the createCellFormat method (using the cell formats from the table above):

You will notice in the code above that each "style" was created using the same createCellFormat method; the font and fill values have just been omitted.

Now while the table rows are being iterated through, we can check the data type of each value and decide whether or not to apply one of the formatted styles to each cell:

In most cases, you will know when to apply the correct styles to each value based on your source data. In either case, the createValueCell method will store the cell content correctly, and also allow you to format those values as needed:

The final result looks much more professional than the original:

This code does a lot to expand on the original article, but it still only covers a small percentage of what is possible within the OpenXML SDK. Hopefully these samples will help you become familiar with how the SDK works and you can customize your own solutions to fit your requirements. I am also providing an updated sample Visual Studio 2008 project with this article so you can review the entire solution.