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:
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.
I'm a mathematician, programmer, writer, 
