Named cell styles are still explicitly declared

Styling cells in Microsoft Excel has its difficulties (as I’ve written before). The biggest one is keeping track of all the indices. In Open XML SDK, you have the ability to have a named cell style.

At first glance, you might think that’s awesome. You just use a named cell style, and all the related styles are applied. It’s like there’s a red car that uses hybrid fuels. “Yes, I would like to have a red car that uses hybrid fuels.” Not quite.

You see, the named cell style is dependent on the implementing spreadsheet software. For example, Microsoft Excel has the “Normal”, “Bad”, “Good” and “Neutral” named cell styles. But Google Spreadsheets and OpenOffice.org Calc do not have to have those named cell styles, or even style it the same as Microsoft Excel. This is where Open XML SDK isn’t quite “open”… After much research and work, I discovered the SDK is basically Open-XML-ising Microsoft Excel (and Word and PowerPoint). I’m neutral on the stands of open source and “forcing standards”. I just use whatever there is, and make something within the limitations.

Because of the dependency, the underlying individual styles need to be declared explicitly. Actually more so because of the dependency.

So for my spreadsheet software library SpreadsheetLight, I used Excel as the guideline.

In researching Excel named cell styles, I had to look at the underlying XML files (because Open XML spreadsheets are made of XML files). While the Open XML SDK comes with a document explorer (the Productivity Tool), I needed to make notes and also that I felt the need to see the XML file itself, rather than using the explorer tool.

This gave me a problem because while XML files are supposed to be human-readable, it doesn’t make it easy to read. The “natural” XML file has no indents. Oh my Godiva chocolate, it’s so hard to read… Then I remembered I had an XML tool, XML Studio. I fired that up and a few clicks later, the XML file had nice indents and I could find out where the individual style tags were. XML Studio was amazing to use.

Disclaimer: I was given a free developer license of XML Studio by Liquid Technologies. But the software is really useful if you work with XML files a lot.

After doing my notes for a while, I discovered even that’s not enough. There were too many individual styles! I needed the indices for those styles, because only the index was referenced in the final style (CellFormat classes). I didn’t really feel up to annotating the indices… until I remembered my partially completed Open XML spreadsheet decompiler tool. When I created that tool, one of my aims was to put in comments on the index of the individual styles.

Note to Liquid Technologies: You might want to consider putting in XML comments on the index of an XML child tag with respect to its parent. But I don’t know if that’s useful to programming spheres other than Open XML…

Anyway, my hard work paid off, and SpreadsheetLight allows you to apply named cell styles. Here’s how the spreadsheet looks like:
Applying named cell styles

Note that some of the named cell styles use accent colours. The accent colours are part of the spreadsheet’s theme. So in offering named cell styles as a feature, I also had to allow you to create your own theme. And here’s the code using SpreadsheetLight:

System.Drawing.Color[] clrs = new System.Drawing.Color[12];
clrs[0] = System.Drawing.Color.White;
clrs[1] = System.Drawing.Color.Black;
clrs[2] = System.Drawing.Color.WhiteSmoke;
clrs[3] = System.Drawing.Color.DarkSlateGray;
clrs[4] = System.Drawing.Color.DarkRed;
clrs[5] = System.Drawing.Color.OrangeRed;
clrs[6] = System.Drawing.Color.DarkGoldenrod;
clrs[7] = System.Drawing.Color.DarkOliveGreen;
clrs[8] = System.Drawing.Color.Navy;
clrs[9] = System.Drawing.Color.Indigo;
clrs[10] = System.Drawing.Color.SkyBlue;
clrs[11] = System.Drawing.Color.MediumPurple;

SLDocument sl = new SLDocument("ColourWheel", "Castellar", "Harrington", clrs);

sl.SetRowHeight(6, 24);
sl.SetColumnWidth(1, 1);
sl.SetColumnWidth(2, 13);
sl.SetColumnWidth(3, 13);
sl.SetColumnWidth(4, 13);
sl.SetColumnWidth(5, 13);
sl.SetColumnWidth(6, 13);
sl.SetColumnWidth(7, 13);

sl.SetCellValue(2, 2, "Normal");
sl.ApplyNamedCellStyle(2, 2, SLNamedCellStyleValues.Normal);
sl.SetCellValue(2, 3, "Bad");
sl.ApplyNamedCellStyle(2, 3, SLNamedCellStyleValues.Bad);
sl.SetCellValue(2, 4, "Good");
sl.ApplyNamedCellStyle(2, 4, SLNamedCellStyleValues.Good);
sl.SetCellValue(2, 5, "Neutral");
sl.ApplyNamedCellStyle(2, 5, SLNamedCellStyleValues.Neutral);

sl.SetCellValue(3, 2, "Calculation");
sl.ApplyNamedCellStyle(3, 2, SLNamedCellStyleValues.Calculation);
sl.SetCellValue(3, 3, "Check Cell");
sl.ApplyNamedCellStyle(3, 3, SLNamedCellStyleValues.CheckCell);
sl.SetCellValue(3, 4, "Explanatory Text");
sl.ApplyNamedCellStyle(3, 4, SLNamedCellStyleValues.ExplanatoryText);
sl.SetCellValue(3, 5, "Input");
sl.ApplyNamedCellStyle(3, 5, SLNamedCellStyleValues.Input);

sl.SetCellValue(4, 2, "Linked Cell");
sl.ApplyNamedCellStyle(4, 2, SLNamedCellStyleValues.LinkedCell);
sl.SetCellValue(4, 3, "Note");
sl.ApplyNamedCellStyle(4, 3, SLNamedCellStyleValues.Note);
sl.SetCellValue(4, 4, "Output");
sl.ApplyNamedCellStyle(4, 4, SLNamedCellStyleValues.Output);
sl.SetCellValue(4, 5, "Warning Text");
sl.ApplyNamedCellStyle(4, 5, SLNamedCellStyleValues.WarningText);

sl.SetCellValue(6, 2, "Heading 1");
sl.ApplyNamedCellStyle(6, 2, SLNamedCellStyleValues.Heading1);
sl.SetCellValue(6, 3, "Heading 2");
sl.ApplyNamedCellStyle(6, 3, SLNamedCellStyleValues.Heading2);
sl.SetCellValue(6, 4, "Heading 3");
sl.ApplyNamedCellStyle(6, 4, SLNamedCellStyleValues.Heading3);
sl.SetCellValue(6, 5, "Heading 4");
sl.ApplyNamedCellStyle(6, 5, SLNamedCellStyleValues.Heading4);
sl.SetCellValue(6, 6, "Title");
sl.ApplyNamedCellStyle(6, 6, SLNamedCellStyleValues.Title);
sl.SetCellValue(6, 7, "Total");
sl.ApplyNamedCellStyle(6, 7, SLNamedCellStyleValues.Total);

sl.SetCellValue(8, 2, "Accent1");
sl.ApplyNamedCellStyle(8, 2, SLNamedCellStyleValues.Accent1);
sl.SetCellValue(8, 3, "Accent2");
sl.ApplyNamedCellStyle(8, 3, SLNamedCellStyleValues.Accent2);
sl.SetCellValue(8, 4, "Accent3");
sl.ApplyNamedCellStyle(8, 4, SLNamedCellStyleValues.Accent3);
sl.SetCellValue(8, 5, "Accent4");
sl.ApplyNamedCellStyle(8, 5, SLNamedCellStyleValues.Accent4);
sl.SetCellValue(8, 6, "Accent5");
sl.ApplyNamedCellStyle(8, 6, SLNamedCellStyleValues.Accent5);
sl.SetCellValue(8, 7, "Accent6");
sl.ApplyNamedCellStyle(8, 7, SLNamedCellStyleValues.Accent6);

sl.SetCellValue(9, 2, "Accent1Perc60");
sl.ApplyNamedCellStyle(9, 2, SLNamedCellStyleValues.Accent1Percentage60);
sl.SetCellValue(9, 3, "Accent2Perc60");
sl.ApplyNamedCellStyle(9, 3, SLNamedCellStyleValues.Accent2Percentage60);
sl.SetCellValue(9, 4, "Accent3Perc60");
sl.ApplyNamedCellStyle(9, 4, SLNamedCellStyleValues.Accent3Percentage60);
sl.SetCellValue(9, 5, "Accent4Perc60");
sl.ApplyNamedCellStyle(9, 5, SLNamedCellStyleValues.Accent4Percentage60);
sl.SetCellValue(9, 6, "Accent5Perc60");
sl.ApplyNamedCellStyle(9, 6, SLNamedCellStyleValues.Accent5Percentage60);
sl.SetCellValue(9, 7, "Accent6Perc60");
sl.ApplyNamedCellStyle(9, 7, SLNamedCellStyleValues.Accent6Percentage60);

sl.SetCellValue(10, 2, "Accent1Perc40");
sl.ApplyNamedCellStyle(10, 2, SLNamedCellStyleValues.Accent1Percentage40);
sl.SetCellValue(10, 3, "Accent2Perc40");
sl.ApplyNamedCellStyle(10, 3, SLNamedCellStyleValues.Accent2Percentage40);
sl.SetCellValue(10, 4, "Accent3Perc40");
sl.ApplyNamedCellStyle(10, 4, SLNamedCellStyleValues.Accent3Percentage40);
sl.SetCellValue(10, 5, "Accent4Perc40");
sl.ApplyNamedCellStyle(10, 5, SLNamedCellStyleValues.Accent4Percentage40);
sl.SetCellValue(10, 6, "Accent5Perc40");
sl.ApplyNamedCellStyle(10, 6, SLNamedCellStyleValues.Accent5Percentage40);
sl.SetCellValue(10, 7, "Accent6Perc40");
sl.ApplyNamedCellStyle(10, 7, SLNamedCellStyleValues.Accent6Percentage40);

sl.SetCellValue(11, 2, "Accent1Perc20");
sl.ApplyNamedCellStyle(11, 2, SLNamedCellStyleValues.Accent1Percentage20);
sl.SetCellValue(11, 3, "Accent2Perc20");
sl.ApplyNamedCellStyle(11, 3, SLNamedCellStyleValues.Accent2Percentage20);
sl.SetCellValue(11, 4, "Accent3Perc20");
sl.ApplyNamedCellStyle(11, 4, SLNamedCellStyleValues.Accent3Percentage20);
sl.SetCellValue(11, 5, "Accent4Perc20");
sl.ApplyNamedCellStyle(11, 5, SLNamedCellStyleValues.Accent4Percentage20);
sl.SetCellValue(11, 6, "Accent5Perc20");
sl.ApplyNamedCellStyle(11, 6, SLNamedCellStyleValues.Accent5Percentage20);
sl.SetCellValue(11, 7, "Accent6Perc20");
sl.ApplyNamedCellStyle(11, 7, SLNamedCellStyleValues.Accent6Percentage20);

sl.SetCellValue(13, 2, 12345678);
sl.ApplyNamedCellStyle(13, 2, SLNamedCellStyleValues.Comma);
sl.SetCellValue(13, 4, 12345678);
sl.ApplyNamedCellStyle(13, 4, SLNamedCellStyleValues.Comma0);
sl.SetCellValue(14, 2, 12345678);
sl.ApplyNamedCellStyle(14, 2, SLNamedCellStyleValues.Currency);
sl.SetCellValue(14, 4, 12345678);
sl.ApplyNamedCellStyle(14, 4, SLNamedCellStyleValues.Currency0);
sl.SetCellValue(15, 2, 123);
sl.ApplyNamedCellStyle(156, 2, SLNamedCellStyleValues.Percentage);

sl.SaveAs("NamedCellStyles.xlsx");

I set the column widths of the 2nd to 7th column, and the row height of the 6th row so it’s easier to see.

The main body text (the minor font) is in Harrington, and the title font (major font) is Castellar. You will note that even though the major font is supposedly used for heading and title texts, only the named cell style Title uses the major font. The headings 1 through 4 use the minor font.

Built-in styles for Excel Open XML

So a blog reader, Sebastien, once asked about built-in style numbers used in Open XML for Excel. I’m sorry to break the news to you. It’s not straightforward to use those built-in styles.

What are these built-in styles? Check these out:

Excel built-in styles

For instance, Sebastien was asking how to use the “Good” and “Bad” built-in styles. So the good news is, you can use those named styles. The bad news is, the various style effects (font colour, background colour, borders) aren’t automatically added in for you. From the Microsoft documentation of CellStyle:

This element represents the name and related formatting records for a named cell style in this workbook.

Annex H contains a listing of cellStyles whose corresponding formatting records are implied rather than explicitly saved in the file. In this case, a builtinId attribute is written on the cellStyle record, but no corresponding formatting records are written.

For all built-in cell styles, the builtinId determines the style, not the name. For all cell styles, Normal is applied by default.

Read the second paragraph again. It means even if you use the built-in style “Good”, you still need to create a style with the green background and green text font colour.

Since this is the case, I suggest you just stick to creating your own custom styles. It’s what’s going to happen anyway.

Now there is a magic number, 164. Your custom styles will start being numbered from 164 onwards. This implies there are 164 built-in styles (0-indexed). My search efforts only gave me a partial list.

Please refer to the ECMA-376 documentation for Office Open XML formats. Download the 1st edition, part 4. Inside, you’ll find a PDF. Turn to page 2135 (as of this writing. It’s page 2128 on the PDF itself though). You’ll find this:

Excel Open XML built-in style list

That list is probably that of “standard” styles. From reading the next few pages of the documentation, I believe most of those 164 built-in styles are due to internationalisation issues.

So my conclusion is, for the “standard” built-in styles you’re interested in, you still have to create the supporting style effects (font colour, background colour and the like). For the “non-standard” built-in styles, they are probably different based on the localisation of your Excel file. So you’re better off just creating your own custom styles, which I teach you how here and here. Or you can get my programming guide for detailed explanations and source code.

Advanced styling in Excel Open XML

I’ve shown you how to create a stylesheet in Excel Open XML. I’ve even shown you how to insert an image in Excel Open XML too. Today, I’ll show you how to do advanced styling. Warning: confusing indices ahead.

First, let me show you what the resulting Excel sheet should look like:
Styled Excel Open XML

We will insert an image as before. We’ll also put in some kind of header text. This is to illustrate the use of a second font. We’ll also use borders, just because we haven’t done it. And we’ll also put in some cell colour (Andy, I’m looking at you…)

I’ll just dump the code in case you’re the impatient type:

static void Main(string[] args)
{
	string sFile = "ExcelOpenXmlWithImageAndStyles.xlsx";
	if (File.Exists(sFile))
	{
		File.Delete(sFile);
	}
	BuildWorkbook(sFile);
}

private static void BuildWorkbook(string filename)
{
	try
	{
		using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
		{
			WorkbookPart wbp = xl.AddWorkbookPart();
			WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
			Workbook wb = new Workbook();
			FileVersion fv = new FileVersion();
			fv.ApplicationName = "Microsoft Office Excel";
			Worksheet ws = new Worksheet();
			SheetData sd = new SheetData();

			WorkbookStylesPart wbsp = wbp.AddNewPart<WorkbookStylesPart>();
			wbsp.Stylesheet = CreateStylesheet();
			wbsp.Stylesheet.Save();

			string sImagePath = "polymathlogo.png";
			DrawingsPart dp = wsp.AddNewPart<DrawingsPart>();
			ImagePart imgp = dp.AddImagePart(ImagePartType.Png, wsp.GetIdOfPart(dp));
			using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
			{
				imgp.FeedData(fs);
			}

			NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
			nvdp.Id = 1025;
			nvdp.Name = "Picture 1";
			nvdp.Description = "polymathlogo";
			DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
			picLocks.NoChangeAspect = true;
			picLocks.NoChangeArrowheads = true;
			NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
			nvpdp.PictureLocks = picLocks;
			NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
			nvpp.NonVisualDrawingProperties = nvdp;
			nvpp.NonVisualPictureDrawingProperties = nvpdp;

			DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
			stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();

			BlipFill blipFill = new BlipFill();
			DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
			blip.Embed = dp.GetIdOfPart(imgp);
			blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
			blipFill.Blip = blip;
			blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
			blipFill.Append(stretch);

			DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
			DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
			offset.X = 0;
			offset.Y = 0;
			t2d.Offset = offset;
			Bitmap bm = new Bitmap(sImagePath);
			//http://en.wikipedia.org/wiki/English_Metric_Unit#DrawingML
			//http://stackoverflow.com/questions/1341930/pixel-to-centimeter
			//http://stackoverflow.com/questions/139655/how-to-convert-pixels-to-points-px-to-pt-in-net-c
			DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
			extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
			extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
			bm.Dispose();
			t2d.Extents = extents;
			ShapeProperties sp = new ShapeProperties();
			sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
			sp.Transform2D = t2d;
			DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
			prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
			prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
			sp.Append(prstGeom);
			sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());

			DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
			picture.NonVisualPictureProperties = nvpp;
			picture.BlipFill = blipFill;
			picture.ShapeProperties = sp;

			Position pos = new Position();
			pos.X = 0;
			pos.Y = 0;
			Extent ext = new Extent();
			ext.Cx = extents.Cx;
			ext.Cy = extents.Cy;
			AbsoluteAnchor anchor = new AbsoluteAnchor();
			anchor.Position = pos;
			anchor.Extent = ext;
			anchor.Append(picture);
			anchor.Append(new ClientData());
			WorksheetDrawing wsd = new WorksheetDrawing();
			wsd.Append(anchor);
			Drawing drawing = new Drawing();
			drawing.Id = dp.GetIdOfPart(imgp);

			wsd.Save(dp);

			UInt32 index;
			Random rand = new Random();

			sd.Append(CreateHeader(10));
			sd.Append(CreateColumnHeader(11));

			for (index = 12; index < 30; ++index)
			{
				sd.Append(CreateContent(index, ref rand));
			}

			ws.Append(sd);
			ws.Append(drawing);
			wsp.Worksheet = ws;
			wsp.Worksheet.Save();
			Sheets sheets = new Sheets();
			Sheet sheet = new Sheet();
			sheet.Name = "Sheet1";
			sheet.SheetId = 1;
			sheet.Id = wbp.GetIdOfPart(wsp);
			sheets.Append(sheet);
			wb.Append(fv);
			wb.Append(sheets);

			xl.WorkbookPart.Workbook = wb;
			xl.WorkbookPart.Workbook.Save();
			xl.Close();
		}
	}
	catch (Exception e)
	{
		Console.WriteLine(e.ToString());
		Console.ReadLine();
	}
}

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 = StringValue.FromString("Calibri");
	FontSize ftsz = new FontSize();
	ftsz.Val = DoubleValue.FromDouble(11);
	ft.FontName = ftn;
	ft.FontSize = ftsz;
	fts.Append(ft);

	ft = new DocumentFormat.OpenXml.Spreadsheet.Font();
	ftn = new FontName();
	ftn.Val = StringValue.FromString("Palatino Linotype");
	ftsz = new FontSize();
	ftsz.Val = DoubleValue.FromDouble(18);
	ft.FontName = ftn;
	ft.FontSize = ftsz;
	fts.Append(ft);

	fts.Count = UInt32Value.FromUInt32((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);

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

	fills.Count = UInt32Value.FromUInt32((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);

	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);

	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 = UInt32Value.FromUInt32((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 nfDateTime = new NumberFormat();
	nfDateTime.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
	nfDateTime.FormatCode = StringValue.FromString("dd/mm/yyyy hh:mm:ss");
	nfs.Append(nfDateTime);

	NumberFormat nf4decimal = new NumberFormat();
	nf4decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
	nf4decimal.FormatCode = StringValue.FromString("#,##0.0000");
	nfs.Append(nf4decimal);

	// #,##0.00 is also Excel style index 4
	NumberFormat nf2decimal = new NumberFormat();
	nf2decimal.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
	nf2decimal.FormatCode = StringValue.FromString("#,##0.00");
	nfs.Append(nf2decimal);

	// @ is also Excel style index 49
	NumberFormat nfForcedText = new NumberFormat();
	nfForcedText.NumberFormatId = UInt32Value.FromUInt32(iExcelIndex++);
	nfForcedText.FormatCode = StringValue.FromString("@");
	nfs.Append(nfForcedText);

	// index 1
	cf = new CellFormat();
	cf.NumberFormatId = nfDateTime.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 2
	cf = new CellFormat();
	cf.NumberFormatId = nf4decimal.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 3
	cf = new CellFormat();
	cf.NumberFormatId = nf2decimal.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 4
	cf = new CellFormat();
	cf.NumberFormatId = nfForcedText.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 5
	// Header text
	cf = new CellFormat();
	cf.NumberFormatId = nfForcedText.NumberFormatId;
	cf.FontId = 1;
	cf.FillId = 0;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 6
	// column text
	cf = new CellFormat();
	cf.NumberFormatId = nfForcedText.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 0;
	cf.BorderId = 1;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 7
	// coloured 2 decimal text
	cf = new CellFormat();
	cf.NumberFormatId = nf2decimal.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 2;
	cf.BorderId = 0;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	// index 8
	// coloured column text
	cf = new CellFormat();
	cf.NumberFormatId = nfForcedText.NumberFormatId;
	cf.FontId = 0;
	cf.FillId = 2;
	cf.BorderId = 1;
	cf.FormatId = 0;
	cf.ApplyNumberFormat = BooleanValue.FromBoolean(true);
	cfs.Append(cf);

	nfs.Count = UInt32Value.FromUInt32((uint)nfs.ChildElements.Count);
	cfs.Count = UInt32Value.FromUInt32((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 = StringValue.FromString("Normal");
	cs.FormatId = 0;
	cs.BuiltinId = 0;
	css.Append(cs);
	css.Count = UInt32Value.FromUInt32((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 = StringValue.FromString("TableStyleMedium9");
	tss.DefaultPivotStyle = StringValue.FromString("PivotStyleLight16");
	ss.Append(tss);

	return ss;
}

private static Row CreateHeader(UInt32 index)
{
	Row r = new Row();
	r.RowIndex = index;

	Cell c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 5;
	c.CellReference = "A" + index.ToString();
	c.CellValue = new CellValue("Congratulations! You can now create Excel Open XML styles.");
	r.Append(c);

	return r;
}

private static Row CreateColumnHeader(UInt32 index)
{
	Row r = new Row();
	r.RowIndex = index;

	Cell c;
	c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 6;
	c.CellReference = "A" + index.ToString();
	c.CellValue = new CellValue("Product ID");
	r.Append(c);

	c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 6;
	c.CellReference = "B" + index.ToString();
	c.CellValue = new CellValue("Date/Time");
	r.Append(c);

	c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 6;
	c.CellReference = "C" + index.ToString();
	c.CellValue = new CellValue("Duration");
	r.Append(c);

	c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 6;
	c.CellReference = "D" + index.ToString();
	c.CellValue = new CellValue("Cost");
	r.Append(c);

	c = new Cell();
	c.DataType = CellValues.String;
	c.StyleIndex = 8;
	c.CellReference = "E" + index.ToString();
	c.CellValue = new CellValue("Revenue");
	r.Append(c);

	return r;
}

private static Row CreateContent(UInt32 index, ref Random rd)
{
	Row r = new Row();
	r.RowIndex = index;

	Cell c;
	c = new Cell();
	c.CellReference = "A" + index.ToString();
	c.CellValue = new CellValue(rd.Next(1000000000).ToString("d9"));
	r.Append(c);

	DateTime dtEpoch = new DateTime(1900, 1, 1, 0, 0, 0, 0);
	DateTime dt = dtEpoch.AddDays(rd.NextDouble() * 100000.0);
	TimeSpan ts = dt - dtEpoch;
	double fExcelDateTime;
	// Excel has "bug" of treating 29 Feb 1900 as valid
	// 29 Feb 1900 is 59 days after 1 Jan 1900, so just skip to 1 Mar 1900
	if (ts.Days >= 59)
	{
		fExcelDateTime = ts.TotalDays + 2.0;
	}
	else
	{
		fExcelDateTime = ts.TotalDays + 1.0;
	}
	c = new Cell();
	c.StyleIndex = 1;
	c.CellReference = "B" + index.ToString();
	c.CellValue = new CellValue(fExcelDateTime.ToString());
	r.Append(c);

	c = new Cell();
	c.StyleIndex = 2;
	c.CellReference = "C" + index.ToString();
	c.CellValue = new CellValue(((double)rd.Next(10, 10000000) + rd.NextDouble()).ToString("f4"));
	r.Append(c);

	c = new Cell();
	c.StyleIndex = 3;
	c.CellReference = "D" + index.ToString();
	c.CellValue = new CellValue(((double)rd.Next(10, 10000) + rd.NextDouble()).ToString("f2"));
	r.Append(c);

	c = new Cell();
	c.StyleIndex = 7;
	c.CellReference = "E" + index.ToString();
	c.CellValue = new CellValue(((double)rd.Next(10, 1000) + rd.NextDouble()).ToString("f2"));
	r.Append(c);

	return r;
}

I am not going to even attempt to explain to you the various parts in detail. That might drive me insane. I’ll just broadly explain why some of the code parts exist.

In the CreateStylesheet() function, you have the CellFormat classes. These represent unique styling information. And for every unique style, you need to create a new CellFormat class. Let’s look at the new unique styles we have.

  • Header text, styled with Palatino Linotype font at 18pt
  • Column header text, Calibri font and size 11pt, bordered on all sides
  • Column header text, Calibri font and size 11pt, bordered on all sides, coloured orange
  • Forced text format, so long consecutive series of digits still render as text
  • DateTime format
  • Numeric value formatted with 4 decimal point
  • Numeric value formatted with 2 decimal point
  • Numeric value formatted with 2 decimal point and coloured orange

For background (and foreground in this case) colour, I added a new fill/patternFill with ARGB colour 00ff9728. This is in addition to the default no-patterns and gray-pattern. That orange colour is thus index 2 (0-based).

For borders, other than the default no-border version, I added the borders-on-all-sides version. This is border index 1 (0-based).

There are 4 different text formatting:

  • Datetime format
  • 4 decimal
  • 2 decimal
  • Forced text

These are represented with NumberFormat classes, and I’ve clumped them together.

Then comes the CellFormat classes. You … I … You know what, just read the code and see if you understand. I’ve already commented the index numbers above each unique style. These index numbers refer to the style index, not the border index or pattern index. These index numbers are the indices used when you want a particular cell to be formatted in a certain way.

For example, the cell containing the header text should have the style index 5, where the FontId is 1 (because we used the second [Spreadsheet] Font, not to confused with the System.Drawing.Font).

If you have a lot of unique styles to set, you have your work cut out for you.

Download the source code. Download the resulting Excel file.

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