Calculate Excel column width pixel interval

Brace yourself. You’re about to learn the secret behind how Excel mysteriously calculates the column width intervals.

In this article, I’m not going into the details of the column widths, but the column width intervals. There’s a difference. From the Open XML SDK specs:

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

To put it mildly, that’s a load of hogwash. In the documentation, it says that for Calibri 11 point at 96 DPI, the maximum digit width is 7 pixels. That is also another load of hogwash. It’s actually 8 pixels (well, 7 point something…).

When you move the line on the column width in Excel, just 1 pixel to the left, what is the column width? When you move it 1 pixel to the right, what’s the column width?

It turns out the each pixel interval isn’t a simple multiple of an internal column width interval.

Let’s take Calibri 11 pt 96 DPI again. With a maximum digit width of 8 pixels, each column width interval per pixel is supposedly 1/7 or 1/(max digit width -1).

But wait! It’s not actually 1/7. It’s the largest number of 1/256 multiples that is less than 1/7.

Now 1/7 is about 0.142857142857143. The actual interval is 0.140625, which is 36/256.

4/7 is about 0.571428571428571. The actual interval is 0.5703125, which is 146/256. And you will note that 146 is not equal to (4 * 36).

If you’re using Open XML SDK (or however you choose to access an Open XML Excel file), when you set the column width as 8.142857142857143, internally, Excel will save it as 8.140625.

Here’s some code:

int iPixelWidth = 8;
double fIntervalCheck;
double fInterval;
for (int step = 0; step < iPixelWidth; ++step)
{
    fIntervalCheck = (double)step / (double)(iPixelWidth - 1);
    fInterval = Math.Truncate(256.0 * fIntervalCheck) / 256.0;
    Console.WriteLine("{0:f15} {1:f15}", fIntervalCheck, fInterval);
}

So now you know how the intervals are calculated. But what about the actual column width? Hmm... perhaps another article...

P.S. I'm currently doing research for how to do autofitting for rows and columns for my spreadsheet library. I found this "secret" after fiddling with Excel files for a couple of hours... I know I'm talking about my library a lot, but it's taking up a lot of my brain space right now, so yeah...

Optimal width and height after image rotation

A while ago, a blog reader Fabien sent me some code (you can read it here. Thanks Fabien!). The PHP code is a modification of my image rotation code with some upgrades.

I was looking through his code (French variable names!) and was puzzled by the initial section. I believe he based his code on my code where the resulting image wasn’t clipped after rotation, meaning the whole image was still in the picture/bitmap (though rotated).

In that piece of code, I just used the diagonal length of the image (from top-left corner to bottom-right corner) as the final length and breadth of the resulting image. This gave the simplest resulting image dimension without doing complicated maths calculations (a square in this case).

However, what if you want to know the optimal width and height of the resulting image after rotation? Meaning the best-fit width and height that just manages to contain the resulting rotated image. For that, I need to tell you some basic trigonometry and geometry.

Image rotation, optimal width and height

Suppose you have a rectangle with L as the length and H as the height. It is rotated t angles. I’m not going to explain the maths behind it. It involves complementary angles, supplementary angles, rotation symmetry and trigonometry with sines and cosines. Convince yourself that the diagram is true.

So after rotating t angles, the optimal width is L * cos(t) + H * cos(90 – t)

The optimal height is L * sin(t) + H * sin(90 – t)

Short digression: You might notice that any lengths that lie parallel to the x-axis usually involve cosines, and lengths that lie parallel to the y-axis usually involve sines. It’s just the way trigonometry works.

Now, although the image rotation is carried out with respect to the image’s centre, rotating by the top-left corner will result in the same optimal width and height. Again, this is basic maths so you’ll just have to convince yourself it’s true (and that I don’t really want to explain it…).

But that’s if t is an acute angle. What about other angles?

Image rotation, optimal width and height

For those angles, we just need to calculate the acute angle based on the initial rotation angle. After that, just substitute that calculated acute angle into our formula above. I have absolute confidence in your ability to check which quadrant in the Cartesian coordinate system does your rotation angle lie in.

UPDATE: In case you are unable to view images, if your rotation angle is in the 2nd quadrant, the calculated angle is (180 – t). If in the 3rd quadrant, it’s (t – 180). And if in the 4th quadrant, it’s (360 – t).

In practice, you might still want to pad a couple of pixels around. But that should give you the smallest image dimension which can still contain your rotated image.

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.