How to create a stylesheet in Excel Open XML

Today, I’ll show you how to create a stylesheet in Excel Open XML with the minimum required. The styles I need are:

  • Forced text format for long consecutive string of digits
  • Date format
  • Decimal format

We’ll be using the Open XML SDK 2.0 from Microsoft. As of this writing, it’s still in Community Technical Preview state (August 2009), so I’ll just let you search online, in case the final product is released by the time you read this article.

The stylesheet is represented by the DocumentFormat.OpenXml.Spreadsheet.Stylesheet class. Through my hours (and hours and hours…) of playing around with the code, I still had to use almost the same default stylesheet when I unzip a blank Excel file in Open XML format. In case you don’t know, an Excel Open XML file (or any of the Microsoft Office products in Open XML format such as Word and PowerPoint) is basically a zip file of folders and XML files (and perhaps some media resources).

This is the stylesheet XML file produced by the code which you’ll see in a bit. Download ExcelOpenXmlStyles.xml (which is actually named styles.xml in the original zip file, but I renamed it to avoid clashing with my other files).

You could write an XML file directly with that content (say, using the StreamWriter class). You just have to be careful of the XML structure, such as opening and closing of tags, and taking care of child tags.

Or you could use the SDK.

I haven’t found anyone writing on how to create styles to format the content of the Excel file. Maybe it’s because just creating an Excel file is already an awesome accomplishment… Unfortunately, I can’t remember where are the one or two articles I read to create a basic Excel file…

Well, I figured out how to use the SDK to create the file. What I did was look at a tag in the XML file (downloadable from above), search for the corresponding class entry in the Open XML SDK help file, and use that class in the code. It’s a tedious process, and some tags have a different-looking name as the class (such as the cellStyleXfs tag and its CellStyleFormats class, or the unintuitive xf tag and its CellFormat class.)

Anyway, here’s the code:

private static Stylesheet CreateStylesheet()
{
	Stylesheet ss = new Stylesheet();

	Fonts fts = new Fonts();
	DocumentFormat.OpenXml.Spreadsheet.Font ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
	FontName ftn = new FontName();
	ftn.Val = "Calibri";
	FontSize ftsz = new FontSize();
	ftsz.Val = 11;
	ft.FontName = ftn;
	ft.FontSize = ftsz;
	fts.Append(ft);
	fts.Count = (uint)fts.ChildElements.Count;

	Fills fills = new Fills();
	Fill fill;
	PatternFill patternFill;
	fill = new Fill();
	patternFill = new PatternFill();
	patternFill.PatternType = PatternValues.None;
	fill.PatternFill = patternFill;
	fills.Append(fill);
	fill = new Fill();
	patternFill = new PatternFill();
	patternFill.PatternType = PatternValues.Gray125;
	fill.PatternFill = patternFill;
	fills.Append(fill);
	fills.Count = (uint)fills.ChildElements.Count;

	Borders borders = new Borders();
	Border border = new Border();
	border.LeftBorder = new LeftBorder();
	border.RightBorder = new RightBorder();
	border.TopBorder = new TopBorder();
	border.BottomBorder = new BottomBorder();
	border.DiagonalBorder = new DiagonalBorder();
	borders.Append(border);
	borders.Count = (uint)borders.ChildElements.Count;

	CellStyleFormats csfs = new CellStyleFormats();
	CellFormat cf = new CellFormat();
	cf.NumberFormatId = 0;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	csfs.Append(cf);
	csfs.Count = (uint)csfs.ChildElements.Count;

	uint iExcelIndex = 164;
	NumberFormats nfs = new NumberFormats();
	CellFormats cfs = new CellFormats();

	cf = new CellFormat();
	cf.NumberFormatId = 0;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cfs.Append(cf);

	NumberFormat nf;
	nf = new NumberFormat();
	nf.NumberFormatId = iExcelIndex++;
	nf.FormatCode = "dd/mm/yyyy hh:mm:ss";
	nfs.Append(nf);
	cf = new CellFormat();
	cf.NumberFormatId = nf.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = true;
	cfs.Append(cf);

	nf = new NumberFormat();
	nf.NumberFormatId = iExcelIndex++;
	nf.FormatCode = "#,##0.0000";
	nfs.Append(nf);
	cf = new CellFormat();
	cf.NumberFormatId = nf.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = true;
	cfs.Append(cf);

	// #,##0.00 is also Excel style index 4
	nf = new NumberFormat();
	nf.NumberFormatId = iExcelIndex++;
	nf.FormatCode = "#,##0.00";
	nfs.Append(nf);
	cf = new CellFormat();
	cf.NumberFormatId = nf.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = true;
	cfs.Append(cf);

	// @ is also Excel style index 49
	nf = new NumberFormat();
	nf.NumberFormatId = iExcelIndex++;
	nf.FormatCode = "@";
	nfs.Append(nf);
	cf = new CellFormat();
	cf.NumberFormatId = nf.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = true;
	cfs.Append(cf);

	nfs.Count = (uint)nfs.ChildElements.Count;
	cfs.Count = (uint)cfs.ChildElements.Count;

	ss.Append(nfs);
	ss.Append(fts);
	ss.Append(fills);
	ss.Append(borders);
	ss.Append(csfs);
	ss.Append(cfs);

	CellStyles css = new CellStyles();
	CellStyle cs = new CellStyle();
	cs.Name = "Normal";
	cs.FormatId = 0;
	cs.BuiltinId = 0;
	css.Append(cs);
	css.Count = (uint)css.ChildElements.Count;
	ss.Append(css);

	DifferentialFormats dfs = new DifferentialFormats();
	dfs.Count = 0;
	ss.Append(dfs);

	TableStyles tss = new TableStyles();
	tss.Count = 0;
	tss.DefaultTableStyle = "TableStyleMedium9";
	tss.DefaultPivotStyle = "PivotStyleLight16";
	ss.Append(tss);

	return ss;
}

The whole thing is actually very simple. There’s a SpreadsheetDocument class, which has as a child a WorkbookPart class, which has as a child a WorkbookStylesPart class, which has a Stylesheet property which you assign with the result of that function you see in the code above. *whew*

It’s logically structured. It’s just that I can’t find anything online or in the documentation about which classes I needed to use… hence the hours (and hours and hours…) of research and testing.

A few points to note:

  • The Font class used is different from System.Drawing.Font
  • System-defined style numbers are less than 164 (based on my experiments on custom styles). Hence the magic number. So custom style index numbers are 164 and above.
  • The style “#,##0.0000” is typically used by me for representing Internet traffic, as in 1,234.5670 MB.
  • The style “#,##0.00” is a standard format in Excel, with the style number 4
  • Forced text format is “@”, which is also a standard format in Excel, with the style number 49
  • The function is static because I’m using it in a console program.

There are a couple of classes used that I have no idea what they are used for. For example, the DifferentialFormats class and the TableStyles class. I just know that if I don’t create them as a child of the Stylesheet class, the Excel file will fail to open. This is the major time-drain of my research and experiments: determining the classes used to write the minimum code (or XML file).

Next time, I’ll show you how to insert an image into the Excel file. That one takes up a whole lot of code when compared to inserting an image file in HTML. I’ll conclude the whole Excel Open XML creation with the full code on generating a working Excel file. I’m setting all the pieces here piecemeal so I don’t have to explain everything in one shot.

There’s updated material and source code, together with more information on how to work with Open XML. Click here to find out more.

Comments

  1. Hi Vincent,

    Your explanantion of the SpreadsheetML styling process is impressive and it must have taken a considerable amount of investigative work to be able to document this. But I wonder whether you are trying to solve the wrong problem?

    In my development of ExtremeML (http://extrememl.codeplex.com) I have discovered that almost every situation that requires worksheet styling can be better accomplished by creating a workbook template that includes pre-styled elements and then injecting dynamic data into it. I recommend you take a look at my project user guide, which includes a bunch of tutorials showing how this technique can be used in very powerful ways. I am sure it will change your thinking about how best to harness the SDK.

  2. Hi Tim! It *did* take me a while to figure out all the stuff.

    I know about the workbook template solution. My work just happens to deal with a lot of reports. Which will mean a lot of templates. Sometimes, an extra column needs to be added because the database schema changed. It’s easier to make the change in code, than to find the template to change. In the long run, it’s easier to maintain, since my experience tell me the developers in my office have difficulty working with non-code stuff, like Excel…

    I have another article planned which takes the styling to a whole new level. You ain’t see nothing yet… hahaha…

    I will have a look at the ExtremeML. It might make my job easier. Thanks!

  3. Andy Cohen says:

    Vincent

    This is a great spreadsheet and I got it up and running.

    The problem im having is I need to show bordered cells. When I apply the sheet in your sample I get no borders?

    Also I cannot seem to get the cells to fill wiht any color?

    Any assistance would be greatly appreciated.

    Thanks

    Andy

  4. Hi Andy, I plan to have a more sophisticated version of the stylesheet written some time later. If you’re eager to know, you need to add one more border variable:

    =====

    Borders borders = new Borders();
    Border border = new Border();
    border.LeftBorder = new LeftBorder();
    border.RightBorder = new RightBorder();
    border.TopBorder = new TopBorder();
    border.BottomBorder = new BottomBorder();
    border.DiagonalBorder = new DiagonalBorder();
    borders.Append(border);

    border = new Border();
    border.LeftBorder = new LeftBorder();
    border.LeftBorder.Style = BorderStyleValues.Thin;
    border.RightBorder = new RightBorder();
    border.RightBorder.Style = BorderStyleValues.Thin;
    border.TopBorder = new TopBorder();
    border.TopBorder.Style = BorderStyleValues.Thin;
    border.BottomBorder = new BottomBorder();
    border.BottomBorder.Style = BorderStyleValues.Thin;
    border.DiagonalBorder = new DiagonalBorder();
    borders.Append(border);
    borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);

    =====

    Then set the BorderId of the CellFormat you want to use to 1 (it’s 0-based, so it takes on the border style of the second one). I set the top, right, bottom and left borders to “thin”. You can play around with the BorderStyleValues for more options.

    cf.BorderId = 1;

    As for the fill colour, try this when writing the “Fill” section:

    =====
    Fills fills = new Fills();
    Fill fill;
    PatternFill patternFill;

    fill = new Fill();
    patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.None;
    fill.PatternFill = patternFill;
    fills.Append(fill);

    fill = new Fill();
    patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.Gray125;
    fill.PatternFill = patternFill;
    fills.Append(fill);

    fill = new Fill();
    patternFill = new PatternFill();
    patternFill.PatternType = PatternValues.Solid;
    patternFill.ForegroundColor = new ForegroundColor();
    patternFill.ForegroundColor.Rgb = HexBinaryValue.FromString(“00c0c0c0”);
    patternFill.BackgroundColor = new BackgroundColor();
    patternFill.BackgroundColor.Rgb = patternFill.ForegroundColor.Rgb;
    fill.PatternFill = patternFill;
    fills.Append(fill);

    fills.Count = (uint)fills.ChildElements.Count;

    =====
    The third Fill is a solid colour, the foreground with a grey colour (hex ARGB 00c0c0c0), and the background colour is set to be the same as the foreground colour.

    Then you set the FillId of the appropriate CellFormat you want as 2 (it’s also 0-based).

    cf.FillId = 2;

    Just note that, the order of adding new borders and fills is important. Because it affects the index you’re going to use for the respective property of the CellFormat.

  5. Andy Cohen says:

    Vince

    Thanks so much that works great. I appreciate the help. If you ever need a return favor drop an email.

    Andy

  6. Hi

    I am taking my first steps in Open XML Excel generation and I must say, it has not been easy to avoid “unreadable content”. Luckily with your site I can get my content in there. But I am having trouble understanding the styling. How do you assign a specific style to a specific cell? What are the names/numbers of the styles? ‘Cause if I try myCell.StyleIndex = 4; it just does not work.

    Thanks a lot in advance!

  7. Hi Vicky, your

    myCell.StyleIndex = 4;

    means myCell gets the style given by the 5th CellFormat in the variable cfs (CellFormats class). So you should have at least 5 CellFormat class appended already. Then myCell will get the font style, border style and other styles in that 5th CellFormat.

    Basically, you create a CellFormat class with all the styles you want. Then you set the StyleIndex property to the appropriate index of the CellFormats class (with all the CellFormat classes required).

    Hope that helps.

  8. your code is too chim, must come to here, your blog, to understand how you create the custEBill logic. LOL

  9. It’s not *that* chim lah, Liu Lu…

  10. Sebastien says:

    Hello,

    I have kind of same question as Vicky, but I would like to use the builtin styles.
    According to MSDN (http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellstyle.aspx) there are pre-defined styles that could be used directly (unless I totally misunderstood…).
    Basically, I would like to use styles at index 26 and 27 (Good and Bad) to quickly show correct and erroneous values I generate.

    Infortunatly, settings the StyleIndex property of the Cell instances got me nowhere but to “unreadable content”.

    If you understood, how to use builtins style, I would appreciate a lot some information.

    Thanks in advance.

  11. Hi Sebastien, I’m afraid using the builtin styles aren’t as straightforward as you think (I was surprised too). From the MSDN:

    “The built-in cell styles are written here by name, but the corresponding formatting records are assumed rather than explicitly written.”

    What they mean is that setting some variable to index 26 or 27 is not enough to make that style the “Good” version or the “Bad” version. You still have to set the individual font, border, background colour and so on.

    For example, you still have to create the (by default) Calibri font, green background colour for the “Good” builtin style. And then, you add this part:

    CellStyles css = new CellStyles();
    CellStyle cs = new CellStyle();
    cs.Name = “Normal”;
    cs.FormatId = 0;
    cs.BuiltinId = 0;
    css.Append(cs);

    cs = new CellStyle();
    cs.Name = “Good”;
    cs.FormatId = 1;
    cs.BuiltinId = 26;
    css.Append(cs);

    css.Count = (uint)css.ChildElements.Count;
    ss.Append(css);

    And then going back to the individual CellFormat with your “Good” styles (in particular, the background colour), set:

    cf.FormatId = 1;

    because the FormatId for the CellStyle is 1. I hope that’s sufficient for you to work with.