Do you need to create Excel spreadsheets with C# or VB.NET?

Ahhh Microsoft Excel spreadsheets, an important and usually unavoidable tool in the day-to-day operations of an office worker. I too was inextricably bound to Excel during my professional programming career.

“Wait, weren’t you working as a programmer? Why were you using Excel like a ‘normal’ office worker?”

Ahhh, that’s because I work with said ‘normal’ office workers. One of my users then, a product manager, needed access to the database records. Due to circumstantial requirements, there’s no user interface that’s flexible enough to fix the problem. So the solution was to dump the records into an Excel file and send it to him. I would also send him a short analysis, and he would reply with the changes in the Excel file. I was to check the data integrity and then when it’s done, I would upload it into the database. As a result, I became well acquainted with basic Excel data manipulation.

Moving along, I worked with more users. Customer service officers, sales representatives, marketing staff, administrative staff, switch operators (I was in the telecommunications industry), technical staff, managers and directors. One common requirement from all of them was reports. There were 3 types: in text, PDF and Excel. By far the most popular type of report was the Excel reports. That’s because it’s easily editable and be manipulated by the users. The second reason was that the data required tended to be tabular, and Excel excelled (no pun intended) in that.

So to cut the story short (but the full story is in my programming guide. Yes, I’m going to tell you about it in a little while), I started looking into Open XML formats for Excel spreadsheets. My experiments yielded accepted results for my users. And I wrote about my experiments here. And some programmers found the articles useful. Then one fine day, it hit me; I should create a programming guide on it.

Spreadsheet Open XML From Scratch

Spreadsheet Open XML From Scratch

Before I tell you more, answer this question. “Do you need to create Excel spreadsheets with C# or VB.NET?” If so, you’re in luck because I created a guide just for you. It’s called Spreadsheet Open XML From Scratch. This guide will teach you how to create Excel spreadsheets using C# or VB.NET and the Open XML SDK (version 2.0). You can read the full details here.

The price is set at US$ 47. I decided on that price after thinking how much you will learn from it, and how much time and effort I’ve put into it. Half of the guide contains a compilation of the existing articles I’ve written, with the written material edited (some with new material added) and the source code cleaned up. The source code then was written with the Open XML SDK 2.0 CTP version. With the cleaned up source code, it’s written with the Open XML SDK 2.0 release version. There’s at least one class that will stop working if you use my old code. I’ve also written VB.NET versions of the C# code, so you can use that if you’re more comfortable with VB.NET.

The other half of the guide contains all new material and source code. As a bonus, there’s even a section teaching you how to use an Excel template to shortcut your spreadsheet generation process.

As an additional bonus, if you are one of the first 10 buyers, I will throw in a free 30 day consultation. You can ask me anything on the concepts involved, the source code, the Open XML SDK or Excel in general. This is in addition to the 1 year satisfaction and effectiveness guarantee, and 1 year unlimited free updates to the guide. This information is given to you because you’re reading my blog (it’s not on the product page). I’d offer it to more people, but there’s a physical limitation on my time.

You don’t have to buy my guide if you don’t want to for any reason. No problem, you can still read my blog for free. But if you’re interested, go check out my programming guide Spreadsheet Open XML From Scratch. Or you can buy the guide directly here. Thanks for reading.

First programming product almost done

As I mentioned earlier, I’m preparing a guide to creating Excel files using just code and the Open XML SDK. I’m calling it “Excel Open XML From Scratch” (nice name, huh?). All the source code (C# and VB.NET) had been written and tested. They work! Yay!

So now, I’m writing the accompanying PDF to explain the code and concepts. As mentioned before, I’m targeting a “before Christmas” launch. Now that we’re nearer the date, I can confidently tell you that it’s going to be sometime within the next few days. It will be released on 17 December. This gives you a week before Christmas to get this as a present for yourself or a programmer friend.

The price will be set at USD 17 (see actual price on product page), which I believe is a fair price considering that it will save you hours of work. Think about how much you’re paid per hour. Probably more than the price of this product. Hmm… I might even have to increase the price…

This is my first programming-related product, so I’m really excited by this. If you don’t really care about C#, VB.NET, Open XML or Excel, then I apologise. Just ignore any related posts for the immediate couple of weeks.

Basic guide to Excel Open XML

Ok, I don’t know why I didn’t think of it before. So it appears that there’s some need to create Excel files using Open XML SDK 2.0. I’m halfway done with my guide to doing that. It will be sold as an ebook (packaged with source code). It’s a consolidation of some of the articles I’ve already written on the subject, together with some all new articles. Included topics are:

  • How to create a stylesheet and use it
  • How to insert an image into a worksheet
  • How to insert multiple images into a worksheet
  • More advanced styling options
  • Setting column widths
  • Setting column and row settings (such as grouping columns and hiding rows)
  • Text alignment in a cell

When I teach, I believe in giving you the simplest explanation and code. This allows you to create more complex functions if you want. If I complicate it for you, you might not know what the individual parts are. It’s like giving you all 26 alphabets and let you play with it. Much better if you learn the individual alphabets and it’s up to you to form words with them.

I haven’t finalised the cost of the product, nor the release date. But I’m planning a “before Christmas” launch, so you should see it in my store soon. This is a heads up in case you’re agonising over a Christmas present for a programmer (or yourself). The price will be fair, considering that I will be saving you tons of hours of research and pain. It will also come with both C# and VB.NET supporting code.

Having worked in a mid-sized company, I understand that sometimes your IT department doesn’t allow third party products (or doesn’t have the budget to buy the license). I don’t consider the Open XML SDK 2.0 to be a third party product. Besides, it’s by Microsoft (and free!). Most companies should be able to accept that. The source code will only need the Open XML SDK (and the .NET Framework of course) to work.

My product license will be fairly flexible, so you can use my code to create commercial products. And the license is tied to the product, not to number of computers or developers or servers (or whatever complicated rule being used by other software companies). You only need to buy 1 copy. (But tell your friends to buy as well because I need to eat)

So yeah, that’s all I have to say. If there’s a particular topic you want me to cover, there’s still some time. Comment below and I might consider adding it to the guide. If this turns out to be popular (as in I get to eat a couple of meals because of it), I’ve got plans to include more Excel Open XML goodness, and even expand to Word and PowerPoint. We’ll see…

How to insert multiple images in Excel Open XML

A blog reader, Fabrice Sacré, sent me an email with code about how to insert multiple images into an Excel file using Open XML. (I had fun playing Yanni’s “In The Mirror” on the virtual piano on his site.) He based it on my article on inserting just one single image into an Excel file. Based on his email address and signature, he works in the Red Cross. Wow! I’m honoured to be saving lives somehow, somewhere, even if in a long, circuitous and indirect manner. Possibly, I’m thinking too highly of myself and there’s no impact at all to the world at large, but I’m trying not to consider that.

Now when I started with the original code, I was in that “create xlsx, rename to zip, scrutinise xml till I fall asleep” mode. Frankly speaking, I had no idea what I was doing most of the time. And in this respect, the order of the individual code/data components was important, but I don’t know which one should come first (in fact, I still don’t). In particular, the code for inserting an image was before the code for inserting data. That worked fine for 1 image, but didn’t turn out so well when I tried with Fabrice’s code.

Ok, here’s the thing. Fabrice used an Excel template file (.xltx). He loaded that up into a new Excel file, then inserted images. Basically, you have an Excel file, then you obtained the worksheet you want, and then insert images to that worksheet. After I understood that (I had to ask him to send his full source code, because he only sent the image insertion function code), I made some changes to my original code to use his function code, and it worked! I’m not going to post the full source code he sent, because it might be sensitive (Red Cross! Saving lives, people!).

Here are the 3 images I used:
Cloud streaks
I like clouds and blue skies.

Crystallised pavillion
I used the “Crystalize” [sic] function in Paint.NET.

Dozing cat
Everyone loves cats, right?

So here’s the main body of the code:

static void Main(string[] args)
{
    string sFile = "ExcelOpenXmlMultipleImageInsert.xlsx";
    if (File.Exists(sFile))
    {
        File.Delete(sFile);
    }
    try
    {
        BuildWorkbook(sFile);
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
    Console.WriteLine("Program end");
}

protected static void BuildWorkbook(string filename)
{
    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();

        Row r = new Row();
        r.RowIndex = 15;
        Cell c;
        c = new Cell();
        c.DataType = CellValues.String;
        c.StyleIndex = 1;
        c.CellReference = "G15";
        c.CellValue = new CellValue("We have multiple images!");
        r.Append(c);
        sd.Append(r);

        ws.Append(sd);                
        wsp.Worksheet = ws;

        // It happens the resolution for the 3 images are 72 dpi
        // Images are 240 by 180 pixels
        // Adjust as needed

        InsertImage(ws, 0, 0, "cloudstreaks.jpg");
        // 2286000 = 180 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 0, 2286000, "crystallisedpavilion.png");
        // 3048000 = 240 (pixels) * 914400 (magic number) / 72 (bitmap resolution)
        InsertImage(ws, 3048000, 0, "dozingcat.jpg");

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

I used the CreateStyleSheet() from this article. I want you to note that the data creation (the single Cell containing the sentence) is now before the image insertion. My approach was originally to model what I see on an Excel worksheet. I would most probably see the image(s) at the top, then the (table of) data below. Hence I modeled the coding to be as such. Apparently, that didn’t work out so well.

[UPDATE: For the CreateStyleSheet() function, it turns out that the NumberFormats class is no longer available in Open XML SDK 2.0. It’s now NumberingFormats. I’ve changed it for the source code for this article (but not for previous code articles)]

Fabrice’s approach was to use a template, then insert images to a particular worksheet. The data sheet was already created (via the template). My approach was to insert images and data in memory, so to speak, before committing the save to the worksheet/workbook parts. Basically, I was trying to create a worksheet out of thin air, add images, add data, and then plug it onto the workbook. Oh well, whatever works then…

Here’s the image insertion function Fabrice sent:

protected static void InsertImage(Worksheet ws, long x, long y, long? width, long? height, string sImagePath)
{
    try
    {
        WorksheetPart wsp = ws.WorksheetPart;
        DrawingsPart dp;
        ImagePart imgp;
        WorksheetDrawing wsd;

        ImagePartType ipt;
        switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
        {
            case "png":
                ipt = ImagePartType.Png;
                break;
            case "jpg":
            case "jpeg":
                ipt = ImagePartType.Jpeg;
                break;
            case "gif":
                ipt = ImagePartType.Gif;
                break;
            default:
                return;
        }

        if (wsp.DrawingsPart == null)
        {
            //----- no drawing part exists, add a new one

            dp = wsp.AddNewPart<DrawingsPart>();
            imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
            wsd = new WorksheetDrawing();
        }
        else
        {
            //----- use existing drawing part

            dp = wsp.DrawingsPart;
            imgp = dp.AddImagePart(ipt);
            dp.CreateRelationshipToPart(imgp);
            wsd = dp.WorksheetDrawing;
        }

        using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
        {
            imgp.FeedData(fs);
        }

        int imageNumber = dp.ImageParts.Count<ImagePart>();
        if (imageNumber == 1)
        {
            Drawing drawing = new Drawing();
            drawing.Id = dp.GetIdOfPart(imgp);
            ws.Append(drawing);
        }

        NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
        nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
        nvdp.Name = "Picture " + imageNumber.ToString();
        nvdp.Description = "";
        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);

        DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();

        if (width == null)
            extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
        else
            extents.Cx = width;

        if (height == null)
            extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
        else
            extents.Cy = height;

        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 = x;
        pos.Y = y;
        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());
        wsd.Append(anchor);
        wsd.Save(dp);
    }
    catch (Exception ex)
    {
        throw ex; // or do something more interesting if you want
    }
}

protected static void InsertImage(Worksheet ws, long x, long y, string sImagePath)
{
    InsertImage(ws, x, y, null, null, sImagePath);
}

As of now, it supports only the image formats gif, jpg (jpeg) and png. However, there’s no stopping you from adding support for bmp, tiff, emf and other image formats supported by the ImagePartType enumeration.

Download the source code and the resulting Excel file.

Have fun.

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

OneCellAnchor might be easier to use than TwoCellAnchor

A reader Kevin emailed me about the use of OneCellAnchor class. This is for coding using Excel Open XML format. The relevant article is about image insertion in Excel. Here’s his comment:

I’ve been using the OneCellAnchor and find it perfect: position by row/column and size by image extent in EMU and best of all, the image will not resize when column widths are set.

If you’re having trouble with figuring out the TwoCellAnchor class properties, you might want to check out OneCellAnchor class instead. Might be easier to work with.

Thanks, Kevin, for that information.

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

Calculating column widths in Excel Open XML

Commenter Roie said that the formula for calculating column widths in Excel Open XML should still be manageable. This is despite the scary formula mentioned before:

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

So I put on my explorer’s hat, and decided against my better judgment to delve into that inexplicable equation. Turns out, it wasn’t too bad. Here’s a screenshot of the resulting Excel file:

Excel Open XML calculate column widths

The code is almost the same as when we’re setting custom column widths, but here it is in its full glory:

static void Main(string[] args)
{
	string sFile = "ExcelOpenXmlCalculateColumnWidth.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 sILT = "Iced Lemon Tea Is An Awesome Drink!";
			double fSimpleWidth = 0.0f;
			double fWidthOfZero = 0.0f;
			double fDigitWidth = 0.0f;
			double fMaxDigitWidth = 0.0f;
			double fTruncWidth = 0.0f;

			System.Drawing.Font drawfont = new System.Drawing.Font("Calibri", 11);
			// I just need a Graphics object. Any reasonable bitmap size will do.
			Graphics g = Graphics.FromImage(new Bitmap(200,200));
			fWidthOfZero = (double)g.MeasureString("0", drawfont).Width;
			fSimpleWidth = (double)g.MeasureString(sILT, drawfont).Width;
			fSimpleWidth = fSimpleWidth / fWidthOfZero;

			for (int i = 0; i < 10; ++i)
			{
				fDigitWidth = (double)g.MeasureString(i.ToString(), drawfont).Width;
				if (fDigitWidth > fMaxDigitWidth)
				{
					fMaxDigitWidth = fDigitWidth;
				}
			}
			g.Dispose();
			
			// Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
			fTruncWidth = Math.Truncate((sILT.ToCharArray().Count() * fMaxDigitWidth + 5.0) / fMaxDigitWidth * 256.0) / 256.0;

			Columns columns = new Columns();
			columns.Append(CreateColumnData(1, 1, fSimpleWidth));
			columns.Append(CreateColumnData(2, 2, fTruncWidth));
			columns.Append(CreateColumnData(3, 3, 35.42578125));
			ws.Append(columns);

			Row r;
			Cell c;

			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A1";
			c.CellValue = new CellValue(sILT);
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "B1";
			c.CellValue = new CellValue(sILT);
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "C1";
			c.CellValue = new CellValue(sILT);
			r.Append(c);
			sd.Append(r);

			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A2";
			c.CellValue = new CellValue(string.Format("Simple width: {0}", fSimpleWidth));
			r.Append(c);
			sd.Append(r);

			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A3";
			c.CellValue = new CellValue(string.Format("Truncation width: {0}", fTruncWidth));
			r.Append(c);
			sd.Append(r);

			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A4";
			c.CellValue = new CellValue(string.Format("Width of '0': {0}", fWidthOfZero));
			r.Append(c);
			sd.Append(r);

			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A5";
			c.CellValue = new CellValue(string.Format("Max Width of Digits: {0}", fMaxDigitWidth));
			r.Append(c);
			sd.Append(r);

			ws.Append(sd);
			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 Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
{
	Column column;
	column = new Column();
	column.Min = StartColumnIndex;
	column.Max = EndColumnIndex;
	column.Width = ColumnWidth;
	column.CustomWidth = true;
	return column;
}

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

Note this new part:

string sILT = "Iced Lemon Tea Is An Awesome Drink!";
double fSimpleWidth = 0.0f;
double fWidthOfZero = 0.0f;
double fDigitWidth = 0.0f;
double fMaxDigitWidth = 0.0f;
double fTruncWidth = 0.0f;

System.Drawing.Font drawfont = new System.Drawing.Font("Calibri", 11);
// I just need a Graphics object. Any reasonable bitmap size will do.
Graphics g = Graphics.FromImage(new Bitmap(200,200));
fWidthOfZero = (double)g.MeasureString("0", drawfont).Width;
fSimpleWidth = (double)g.MeasureString(sILT, drawfont).Width;
fSimpleWidth = fSimpleWidth / fWidthOfZero;

for (int i = 0; i < 10; ++i)
{
	fDigitWidth = (double)g.MeasureString(i.ToString(), drawfont).Width;
	if (fDigitWidth > fMaxDigitWidth)
	{
		fMaxDigitWidth = fDigitWidth;
	}
}
g.Dispose();

// Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256
fTruncWidth = Math.Truncate((sILT.ToCharArray().Count() * fMaxDigitWidth + 5.0) / fMaxDigitWidth * 256.0) / 256.0;

I am using the MeasureString() function of the Graphics object to get the pixel width of a string when rendered in a particular font.

Roie gave a simple formula to calculate the column width, which is the pixel width of the string divided by the pixel of the zero character “0”. So let’s test that.

Then there’s that beast of a formula. We’ll need the number of characters in the string and the maximum pixel width of the digits. Turns out that the maximum pixel width of all digits is that of “0”. So Roie was right on that. Actually it’s logical, because “0” spans the horizontal and vertical space. If you want to test for alphabets, then “M” and “W” are my guesses.

Anyway, I created the Excel file, then did the save as zip and opening the XML file trick. And found that 35.42578125 to be the column width to comfortably cover the string “Iced Lemon Tea Is An Awesome Drink!” rendered in Calibri font.

What do you know, 35.42578125 is also what was calculated using that unwieldy formula. So yeah, that wasn’t so bad.

Roie simplified that formula, and my only gripe is that rounding errors might add up when the string is long. Ah well, the only way you know if your theory works is if you test it. I’m paranoid about these things because my math background practically force me to look at calculation errors of any formula. You do not want a sin(x) = x error on your hands.

So there you have it. The custom column widths are calculable. Have fun with the code and 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.

Custom column widths in Excel Open XML

Commenter Steven wanted to know how to set the column widths in Excel, in Open XML format. I replied, then thought you might also want to know how to do that. It’s fairly straightforward, and here’s how the sample Excel file looks like:

Excel Open XML custom column widths

I like iced lemon tea.

And here’s the code:

static void Main(string[] args)
{
	string sFile = "ExcelOpenXmlWithCustomWidths.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();

			Columns columns = new Columns();
			columns.Append(CreateColumnData(1, 1, 11));
			columns.Append(CreateColumnData(2, 4, 23.5703125));
			columns.Append(CreateColumnData(6, 6, 6.5703125));
			ws.Append(columns);

			Row r;
			Cell c;

			// header
			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A1";
			c.CellValue = new CellValue("Product ID");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "B1";
			c.CellValue = new CellValue("Product Description");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "C1";
			c.CellValue = new CellValue("Bill Description");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "D1";
			c.CellValue = new CellValue("Discount Description");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "E1";
			c.CellValue = new CellValue("Currency");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "F1";
			c.CellValue = new CellValue("Cost");
			r.Append(c);
			sd.Append(r);

			// content
			r = new Row();
			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "A2";
			c.CellValue = new CellValue("PROD12345");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "B2";
			c.CellValue = new CellValue("Iced Lemon Tea");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "C2";
			c.CellValue = new CellValue("Special Iced Lemon Tea");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "D2";
			c.CellValue = new CellValue("Iced Lemon Tea (50% off)");
			r.Append(c);

			c = new Cell();
			c.DataType = CellValues.String;
			c.CellReference = "E2";
			c.CellValue = new CellValue("USD");
			r.Append(c);

			c = new Cell();
			c.StyleIndex = 3;
			c.DataType = CellValues.Number;
			c.CellReference = "F2";
			c.CellValue = new CellValue("5.95");
			r.Append(c);
			sd.Append(r);

			ws.Append(sd);
			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 Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
{
	Column column;
	column = new Column();
	column.Min = StartColumnIndex;
	column.Max = EndColumnIndex;
	column.Width = ColumnWidth;
	column.CustomWidth = true;
	return column;
}

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

You already know how to create stylesheets, so I’ll skip explaining that. I deliberately simplified the content of the Excel cells so we can concentrate on just the column widths. There’s this helper function:

private static Column CreateColumnData(UInt32 StartColumnIndex, UInt32 EndColumnIndex, double ColumnWidth)
{
	Column column;
	column = new Column();
	column.Min = StartColumnIndex;
	column.Max = EndColumnIndex;
	column.Width = ColumnWidth;
	column.CustomWidth = true;
	return column;
}

That should be fairly straightforward to understand. There’s a Min and Max property because Excel is lazy, so if you have a set of contiguous columns with the same width, you set using that with one “column” (class), so to speak.

Now, the Width property is… hard to calculate. So where did I get my values? By generating the .xlsx file, opening it up, adjusting the column widths to taste, saving it, changing the extension to .zip, opening up the zip file, searching for my data XML worksheet, opening that up, and look for something like this:

<cols>
<col min=”1″ max=”1″ width=”11″ customWidth=”1″ />
<col min=”2″ max=”4″ width=”23.5703125″ customWidth=”1″ />
<col min=”6″ max=”6″ width=”6.5703125″ customWidth=”1″ />
</cols>

There, you have your widths. Go back to your code and plug that in. Tada, mission accomplished.

“Wait, so why can’t those widths be calculated?”

Well, they can. I just think the effort’s not worth it. First, you need to know the font of the text in that cell/column. Then there’s some weird truncation calculation based on the width of characters in that font… You know what, here’s an extract from the Open XML SDK documentation:

width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}] / {Maximum Digit Width} * 256) / 256

You are free to go perform the calculation. Knock yourself out. Let me know how it goes.

Here’s the source code, and here’s 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.

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.

How to insert an image in Excel Open XML

This is a continuation of a series of articles on using Excel Open XML. Previously we looked at how to create stylesheets. Today, we’ll look at inserting images. (You will need the Open XML SDK from Microsoft)

Similar to when I was writing code for the stylesheet creation, I have little idea on why any piece of code has to exist for an image to be inserted successfully. All I can advise you is to insert one image into an Excel file, save it as an Excel Open XML format, rename it to a .zip extension, unzip it, and study the contents like crazy. That’s what I did.

Here, I am going to show you the shortcut, and relieve you of all that pain. You will learn to create a simple Excel file, with just one Excel sheet in it. And on that one sheet, is an image. That’s it. You’ll be surprised to know how much code you have to write…

static void Main(string[] args)
{
	string sFile = "ExcelOpenXmlWithImage.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();

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

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

It’s a console program, so create your C# project accordingly. There were 3 reference articles I used:

The use of the ImagePart I can understand. It’s to load the image file you had in mind. The rest is pure gibberish until you hit the part on resolution. That’s where I used the Stack Overflow articles as reference. Then it was gibberish until the positioning of the image.

This is where I did something different. If you did the trick on inserting image into Excel, saving it as Open XML format, renaming to .zip, and unzipping that file, you might find this in the xl/drawings/drawing1.xml file:

-<xdr:twoCellAnchor editAs="oneCell">
- <xdr:from>
  <xdr:col>0</xdr:col> 
  <xdr:colOff>0</xdr:colOff> 
  <xdr:row>0</xdr:row> 
  <xdr:rowOff>0</xdr:rowOff> 
  </xdr:from>
- <xdr:to>
  <xdr:col>6</xdr:col> 
  <xdr:colOff>248195</xdr:colOff> 
  <xdr:row>7</xdr:row> 
  <xdr:rowOff>152608</xdr:rowOff> 
  </xdr:to>

This is for positioning the image. I’m using the AbsoluteAnchor instead of the TwoCellAnchor. After much hairtearing, I’ve decided the TwoCellAnchor class is too hard to use. Let me tell you why.

[UPDATE: A reader Kevin noted the following:

I’ve been using the OneCellAnchor and find it perfect: position by row/column and size by image extent in EMU and best of all, the image will not resize when column widths are set.

So you can look at the OneCellAnchor class and see if it suits you better.
]

The from tag should be easy to understand. The col tag refers to which Excel column (zero-based) is the left side of the image in. The colOff tag is the offset. What offset you ask? The offset from the left-border of the column referenced in the col tag, in EMU. Yes, that English Metric Unit. The row and rowOff tags refer to the Excel row and offset from said Excel row (based on the top border of the image).

So far, it’s 0 for all 4 properties, because we’re inserting the image at the top left corner. Now comes the fun part.

In the to tag, the bottom right of the image is in the Excel cell, column 6, row 7. That’s not all. The bottom right of the image is 248195 EMUs from the left of column 6, and 152608 EMUs from the top of row 7. Note that the image is not 248195 EMUs wide nor 152608 high.

Since those offsets are calculated based on its current cell, if the widths and heights of previous cells (column and row indices less than current ones) change, these offsets change. This means, if I widen the first cell, the offset will change from 248195 EMUs to say 1000 EMUs.

The fun part is, if I widen the first cell enough, the bottom right of the image could end up in column 5. So the column and column offset values are in flux.

To really nail this part down, I will need to know the exact widths of every column up to where the bottom right of the image is. I will probably decide on some default column width in EMU, set every column to be that width. Then I’ll convert the width of the image from pixels to EMU and calculate the offset based on that default column width. And woe befall me if I happen to place the image other than at the top left corner, since I would have to do the same thing for the top left corner of the image.

That wouldn’t be feasible if your document happen to require a fixed width for some columns, due to the content of those columns. So your columns won’t all have the same width. So you’ll have to keep track of all the column widths.

Needless to say, I researched and found the AbsoluteAnchor class. Much fewer properties to deal with…

Just in case you’re wondering, no, I don’t know why the ID of NonVisualDrawingProperties is 1025. That’s the value I found when I did the renaming-unzipping-gawk-at-xlsx-content trick. I believe it can be some other value, but I don’t know the valid range. If you do know, chime in with a comment.

Oh and here’s the image used.
Polymath Programmer logo text
Lovely, isn’t it? *smile*

And you can download the resulting Excel file ExcelOpenXmlWithImage.xlsx.

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

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.