Time is circular

It’s the end of the year 2009. There’s also something you should know. I’m not affected much by holidays, as in I don’t do anything special on or for them. To me, they’re like another Sunday. The days pretty much blur into each other.

Time is circular. Seasons change, things change, people change, stuff happens, but time goes on. In fact, we humans can’t comprehend how time continues to go on and on (except for the movie Titanic, but that’s another story…) that we have to break it up. Like years, and months, and days.

Time is circular. In fact, the very notion of time itself is questionable. Sure the sun rises at dawn, and sets at dusk. You think the sun cares that you designated a time called “dawn” and “dusk” for it to rise and set? The sun just happened. It just spun all 9 (sorry Pluto) 8 planets around it, while orbiting another larger source of gravity. What does time mean?

Time is circular. In fact, if you think about it, space and time are the same. If you walked from one spot to another, how far did you cover? What if I asked you how long you took? Is it the same? What if you didn’t move, and just sat there for an hour? It just meant it took you one hour to move zero metres.

Time is, perhaps, an illusion.

I started out with the goal of thanking you for reading my blog, for tolerating some of my inane writings, for finding something useful out of others. Then my mind wandered, and I wrote something philosophical, and very likely another one of those inane writings I mentioned. Let me get back on track.

It’s been about 2 and half years since I started writing. Perhaps you found a programming article useful. Perhaps you found a funny article. Perhaps you corrected me on a blatant mistake. Perhaps you’re too shy to voice your opinions (I know, I’m psychic, remember?).

I thank you.

Yes, even you, the mutant genius squirrel who’s living in some forest plotting world domination. Wait… what am I talking about…

My goals for 2010 are:

  • Create a small business selling game/RPG products at Honeybeech
  • Get better at math
  • Get better at programming
  • Get better at writing
  • Make people laugh (in a good way)
  • Tell better stories
  • World domination

Since time is circular, there’s no real point in setting goals just for the new year. You should be doing that all the time, and readjusting as needed anyway. But hey, I need to change my calendar, so it’s a good time as any.

What are your goals?

And this has been brought to you by the entirely self-serving advertisement of the Math Wizard, possibly the smartest RPG character you’ll ever play.

Financial reports must be untouched by human hands

People handling billing or financial data are usually very uptight. Especially when it comes to numbers. If you thought mathematicians, statisticians or economists were protective of their numerical figures, go talk to someone who works in the financial department.

PI is an elegant number. It goes to about 3.14159265, and describes the ratio of the circumference of a circle to its diameter. It doesn’t mean much to a finance person though.

But the bits have mercy if their report shows that there’s a $3.14 missing.

Sometimes, in the course of my work, I get requests to dump data from the database. Well, more like generating ad-hoc reports based on whatever was needed. Excel seems to be the preferred output format, since it displays all the data in nice little columns and rows. And let’s face it, the user is probably more skilled in Excel macros and functions than you and I are. Let them do their little calculations and predictions and pie charts, I say.

But noooo… Finance people will have none of that.

“I cannot manipulate data.” was the usual answer.

Wait, when I churned out those data, wasn’t I manipulating data, of sorts? I was doing SUMs, GROUP BYs and ORDER BYs with the database queries. If the user wanted a sum over that particular column, and I didn’t provide it, just use the inbuilt Excel sum function.

“Oh no, I cannot manipulate data.”

Apparently, whatever financial reports must be completely generated by the computer. All financial reports must be untouched by human hands. Let’s hope you got that algorithm right. Wait, aren’t you human? Would that mean the reports went through human hands?

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.

Why women land their feet completely on a step when climbing stairs

Why are we talking about this? It began with the end of a work day. My bus stop was on the other side of the road, and I started climbing the stairs of the overhead bridge. My mind wandered, and fantasised about what kind of story I was going to spin useful fact I could write to entertain and educate you.

Tanned legs

I observed that women, while climbing stairs, put their entire foot, tip to heel, on the step. Men, on the other hand, tend to climb with the balls of their feet/shoes. I had witnessed this many a time in the past, and today, I’m determined to write it down.

I will give you the gut-feeling, no-research-done and completely-pulled-from-the-air answer. So why do women land their feet completely on a step when climbing stairs?

Because women wear high heels.

The flat bottom at the front of women’s high heel shoes is the only non-trivial-surface part meant for contact with another surface. The high heel is supposed to help keep it flat with a surface.

Men’s shoes are (mostly) flat. This give men an extra option. A typical step is just slightly wider than a typical human’s foot, lengthwise. Men, for whatever reason, have a higher tendency to run up the stairs. In order to avoid stubbing their toes, they only step with the front half of their shoes.

Thus the reasoning for the observation that women place their foot completely on a step while climbing, whereas men climb with only half of their foot.

Disclaimer: The reasoning is pulled from the ethers of imagination mixed with baseless presumptions, but the observation is real. Your task is to verify if the observation is true. And if so, come up with your own reasoning and deductions.

[image by Emanuele Tortora]

Stuff I am doing lately

I had a tumultuous past month (or so).

My network adaptor died on me, and since my computer then was about 5 years old, I thought “Why not just get a new computer?” So I did.

Due to some personal reasons, I feel a bit worn out. So I’m putting that ebook project on hold for a while. Partly because I’m also channelling my energy to another blog. Please visit Honeybeech, where I tell stories, mainly about my Dungeons & Dragons gameplay adventures.

Rest assured that I’m still here. So I’ll be writing math and programming topics here at Polymath Programmer, and RPG/fantasy/fiction stuff over at Honeybeech.

Because even programmers need to eat (as in “eat properly”, despite whatever you’ve heard about pizzas and fizzy drinks), I’m writing an ebook as a D&D game supplement. It’s called Math Wizard (I know, it’s so “me”, right?), and the character’s powers are mostly based on math and science concepts.

Considering all my options, I believe I have a better chance at making “Math Wizard” work better than “Discipline and Deflection” (the original ebook project). I also believe the former can inspire more people and ignite their imagination and curiosity than the latter. I will still create both, and I’m just constrained by time and effort.

A friend also introduced me to a book store here in Singapore called BooksActually. They have a sister branch called “Polymath & Crust”. Awesome! They have the word “polymath” in it. I have to visit that book store.

Polymath & Crust

The store is located at No. 86 Club Street Singapore 069454, if you’re interested.
[Disclosure: I’m not paid by them. I even bought a book. See below.]

We went in, and it was a quaint little place, carrying books that you don’t see in major book stores. My friend bought himself a dictionary of symbols, and I bought a dictionary of mathematics.

Dictionary of Mathematics

Even their paper bag is interesting. I wonder what that interesting shape mean?

Polymath & Crust paper bag

Answer for matrix applications and civil engineering question

My friend, Christopher Ng (who has an electrical engineering degree, is currently an IT project manager, and is also a finance author. Talk about polymathy…) has generously helped me answer that matrix applications and civil engineering question. Let me bring it up again:

How do you picture matrix least-squares method of a traverse adjustment in surveying and in strain-stress material? Tell me about diagonal matrix practical interpretation.

I read through his answer and frankly speaking, and I’m ashamed to say, I don’t quite understand it.

He gave this link first:
http://answers.yahoo.com/question/index?qid=20091207151537AAYSibI

Then

Surveys and stress-strain characteristics are two different application domains in civil engineering. I can shed some light on matrices, MLS and stress-strain diagrams.

If you wiki the least squares approach approach [sic], you can find that the generalized problem can be expressed as some sort of a matrix. Solving matrix via quadratic minimization obtains the solution to the least squares problem. Bx = y

If you wiki stress-strain characteristics, it’s simple the physics we used to learn called Hooke’s law. A graph that plots force on the x axis and extension on the y-axis. The least squares approach is just like we do in O level physics, we draw the most probable line that crosses the origin point.

So the picture is that each plot (x,y) can be placed on the matrix and the aim is to draw a line through the points to determine Youngs modulus.

He then went on to explain

The traverse survey occurs when civil engineers take complicated compasses and walk the perimeter of a construction site. They enter the coordinates in bearings and distances traveled into a CAD software system. Ultimately, these engineers are expected to walk to the starting point and trace some kind of loop which is then translated into a map.

The MLS is used to correct errors and adjust the x,y coordinates in a map as there are 3D effects in terrain.

I have no idea how MLS works in this case but you can seek Crandall’s method in your web search to look for graphical examples.

MLS refers to matrix least squares. And O level physics is a physics examination taken by Singaporean students typically at age 16 for entrance towards higher education.

And yeah, what my friend said.

Help – Matrix applications and civil engineering

I have a problem. One of my readers asked me a question and I don’t have an answer. The question (paraphrased and mangled slightly) is:

How do you picture matrix least-squares method of a traverse adjustment in surveying and in strain-stress material? Tell me about diagonal matrix practical interpretation.

I think my brain exploded after reading it. What in tarnation is a traverse adjustment? Holy smokes, strain-stress material? I have no idea what I was in for…

After weeks of sitting on it, and doing some preliminary research, I’ve decided I can’t answer it. I’m sorry I let you down, O reader who sent me the question. I presume this is related to civil engineering. I know little about it, and I’ll probably spout rubbish if I start answering.

This is where you come in. Help answer the question. You can write it in a comment, or contact me via email. A civil engineering reader gets help, and you get a warm fuzzy feeling of doing something good.

UPDATE: We have an answer.

If not using the database, please disconnect

I’m maintaining some Windows programs created by the PowerBuilder software. The original developer didn’t plan for the programs to be used by many people. So the instant one of the programs was run, a database connection to the Sybase database was opened. And left there.

As more programs were created in this manner (and added to the suite of programs my team is in charge of), the number of total users also increased. Since the connections were held in place, table locks between users became a real problem, because a user could be done with an operation, but still hold onto the table. This also meant the database became clogged up with connections, usually non-active.

The better solution is to open the connection when you’re going to do any database operation, and then close it once you’re done. But the original programs were developed like eons ago. If I understand it correctly, client programs back then assumed they had total control over the database. Contrast that with the web applications of today, and let me just say that, I have my work cut out for me…

I decided to write something on this after reading Raymond’s article on cookie licking. So if you’re not using any database functions, please disconnect.

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.

FAHTR aka Free And Happen To Remember

The following completely fake conversation was inspired by an actual conversation with an internal company user.

User: The web application doesn’t let me do XYZ. I can’t download ABC data for customer PQR.
Me: Well, your customer database don’t have JKL. The code changes will take some time.

User: So how long will it take?
Me: It’s not urgent, right?

User: Well…
Me: Then I’ll do it on a FAHTR basis.

User: What father?
Me: FAHTR. It means I’ll do it when I’m free and I happen to remember to do it.