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.

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…

3D Bézier curve editor

Timo Suoranta created a 3D Bézier curve editor. As of this writing, the program runs on Windows and requires OpenGL version 3 or later (shaders are involved). Here’s a screenshot:

Bezier curve editor
[click for larger image]

It looks awesome. What, no? Then you have to understand picking. In 2D, any point you click on the screen is exact. The point you click on is the point selected.

In 3D, it’s different. There are an infinite number of planes behind that virtual screen you picked on. Think of looking up at the clouds in the sky. You know the water droplets are scattered sparsely and densely in the sky. You know they are in a 3D space. But you, looking up at the sky, only see one plane, the 2D plane that has the water droplets rendered onto.

In this case, it’s simpler. We are only concerned with the points on the Bézier curve itself. Timo used the closest point to the clicked point on the screen as the chosen point. Basically you “shoot” a ray from the clicked point into the vanishing point in the far distance (far far far distance, as in infinity far). When your ray hits the Bézier curve, that’s the chosen point. You can find out more about the method by searching on the Internet for “3d picking” or something similar.

So how do you edit a point once it’s chosen? Timo solved it by using 3 cones to represent the X, Y and Z axes. Dragging on the cones move the point along the respective direction of the axis. Notice the 3 cones at the right side.

Bezier curve editor with axes
[click for larger image]

I believe most 3D rendering software use something similar to edit points.

Now notice the small details Timo added:

  • The floor is a checker board to illustrate the notion of space
  • The vertical lines drawn from the points on the curve to the checker board to show the spatial relation
  • If you hover over the big blue spheres, the checker board, or the curve itself, they glow pulse-like

Wait, you haven’t downloaded the program? Here’s the link.

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.

Minor irks between C# and VB.NET

It’s about the way you think about programming. This isn’t another debate on which language is better. Just noting the differences because of how I think. The first is…

Declaring variables

After I think through the logic I want, the first thing that comes to mind might be “I need an integer”. This works well:

int i;

This, not so much:

Dim i As Int32

In C#, the name of the variable is secondary, at least at the point when it’s created. I need an integer. I don’t really care what its name is (yet). Nor does the compiler.

In VB.NET, I have to come up with a name. And if my RPG days are any indication, I take a long time coming up with names. By the time I think up an appropriate name, I forgot what type it’s supposed to be.

It’s like the active-passive voice in English. “He ate the apple.” and “The apple was eaten.” Which do you want to focus on?

I might be wrong. VB views variables as containers for values, hence there’s no point in fixing the type at declaration (like Javascript)? And VB.NET inherits the language structure.

Arrays

In C#, arrays are declared like so:

int[] ia = new int[5];

In VB.NET, they are declared like so:

Dim ia(5) As Int32

There’s a catch though. The array in C# has 5 elements. The one in VB.NET has 6.

Both languages treat arrays with zero-based indices. In VB.NET, the number used in declaring the size of the array means the last index of the array.

So if I wanted 5 elements, I should declare it as:

Dim ia(4) As Int32

Ok, I guess my frustration has run its course…