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.