Do beer ladies need a career path?

Beer ladies, in the Singapore context, are women who serve beer at the local coffee shops. They generally chat with the (generally male) customers so they are more likely to buy more beer. Maybe it’s obvious to you, but it still needs to be said.

And we’re talking about this because I went to a philosophy cafe session recently. And that was the topic. We generalised the term “beer ladies” for our discussion (including child stars and air stewardesses). And you can add in your comments on the original post “Do beer ladies need a career path?“.

Be careful. My friend is a Toastmaster and a philosopher. Your points had better have some backing to them, or be prepared to be scathed by words.

I brought up the point about entrepreneurs and was quickly shot down. Entrepreneurs don’t fit into the “career path” kind of thing. Their point was that entrepreneurs start up a business and if it fails, that’s the end of the story. If it succeeds, they become managers, and that’s also the end of the story (or career path). I don’t think it’s that simple, but entrepreneurs are a complex bunch anyway.

At the start of the session, we were supposed to come up with 6 plausible topics, and the final topic would be decided by votes. On a whim, I suggested “How many digits to PI does it have to be, before you think it’s real?“. My philosopher friend absolutely loved it. I was joking! Luckily no one took it up…

My philosopher friend had 2 things he forbade: no photos of the attendees (especially of him) and nothing regarding Singapore. His argument is that, we can always generalise the topic to the world at large. So even if you’re not from Singapore, you are welcome to join us, since the topics won’t be localised.

The venue is at Nook, 15 Chu Lin Road, held from 8pm to 10pm, on the 3rd Wednesday of every month. The next one is on 17 February 2010.

Since he forbade me to take photos, this was all I could capture:

Almoncino

It’s called Almoncino, and I think it’s a blend of almonds and cappuccino.

“So what’s the answer to the question? Do beer ladies need a career path or not?”

Well, go take a look yourself. And share your thoughts while you’re there. Did we miss out anything? Were any assumptions wrong?

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.

Squares, hexagons and distance

Most traditional board games use squares to segregate space.

Square terrain

Space is divided evenly. Lines are easy to draw. Everything is structured. Bliss.

Except that when you need to move to a diagonal square, you need to move 2 squares instead of √2 squares. Wait, a non-integer movement? That cannot be tracked!

Dungeons & Dragons 4th Edition (a tabletop RPG with physical positional tracking) state that a diagonal square movement costs the same as a perpendicular square movement. Meaning you just move 1 square to reach that diagonal square. This also has its problems.

For example, there’s a concept of push in D&D, where as long as the target being pushed is moved further away, it counts as a push. Bringing us to this situation:

Perpendicular push movement

I talked about this briefly on my other blog, but didn’t go into the math details. So the blue dot is you, the red dot is the enemy, and the brown dot is where the enemy is pushed to.

The distance between you and the enemy is (do the Pythagoras thing) 2√2 (approx 2.8284). The distance between you and the final position of the enemy is √10 (approx 3.1623). Sure √10 is greater than 2√2, so mathematically speaking, the enemy is moving away from you. But common sense is telling me otherwise, because the direction of the push emanates from you.

Anyway, to combat the shortcomings of the square terrain, there’s the hexagonal terrain.

Hexagonal terrain

Under this division of space, all adjacent spots are equidistant to your position. Well, it still has its problems. You still need 2 hexagons of movement to reach the first hexagon directly above you.

Hexagonal movement

So what’s the actual cost of movement? Let’s look at this extracted diagram:

Hexagonal movement calculation

Let h be half of the actual distance. Doing the Pythagoras thing again, we have
1^2 = h^2 + (1/2)^2
=> 1 = h^2 + 1/4
=> h^2 = 3/4
=> h = √3/2 (h is positive)

Therefore, the actual distance is 2h which is √3 (approx 1.7321). Not quite 2 hexagons, is it?

This is part of the reason why, when games are created on computers, that these limitations disappear. Because computers can do the distance calculations and tracking. You can move in any direction, for any amount of units of movement, as long as you do not hit anything.

And that is called collision detection.