Adventures in XML land: combining Excel, XML, HTML and JavaScript

the process
using Excel to generate XML, jQuery and Ajax for animations

In a past long ago I ventured into this barren land, where bugs crept up my sleeves and pants. In most cases after a short scary trip I’d be happy to be back home in PHP or plain JavaScript land.

What am I talking about? Client side “apps”, by which I mean browser based, server-less HTML pages, where everything is done on the client. Examples of such applications are CD Rom viewers, Touch Screen Console applications, and Information Display (like the train station screens).

Recently I got a request to make an Information Display. I started happily to look at how this would work using modern browser based techniques. After a couple of days of experimentation I must say that it was a wonderful journey, and I am convinced this technology will have a great future.

Ten years ago CD Roms with an HTML viewer were simple pages with little interactivity, or they came with a web server to do interesting stuff. Now, with the advent of powerful JavaScript libraries and Office 2007 we can now integrate these into a cool looking almost server-centric interactive display.

I have used the following technologies, and will explain hereafter how:

Excel 2007 and XML

Excel serves as my database. It consists of worksheets of tabular data. The data is exported to an XML file using the XMLTOOLS addin, which you can find on the microsoft site. It is very loosely designed: when I need an extra column, I insert it and start typing. I will have to re-generate an XML mapping then, and export the contents to an XML file. The advantage: the person working with this ‘database’ only needs to know Excel, and how to click on a few buttons.

jQuery and Cycle

I added jQuery most and for all for the Cycle plugin. It allowed me to create stunning visual transitions for ‘slides’. The slides are actually simple divs in an HTML page.


I used CSS3 for creating nice looking gradients. Also I used CSS3 for zebra tables and drop shadows on images. Overall it means there is no need whatsoever for images to enhance the visuals. I think that’s how future web development will and should occur.

SVG and/or Canvas

In fact you hardly ever need both, given that these technologies mostly overlap in capability. An advantage of Canvas is that it is actually a program (javascript) instead of a declarative markup. Of course the same argument makes SVG more attractive for its simplicity.

Ultimately I decided to use SVG for both my static vector based images (floor plan) and my animation (a clock). The SVG animation actually looked better than the canvas one and I wasn’t very interested in modifying the default look.

For the floor plan I hunted for a good SVG or Canvas editor. What I found was Google Docs (!). Recently they added a diagram editor, that can export to SVG. The resulting code unfortunately looks like hexadecimal soup, but the good thing is you can easily modify the diagram on Google Docs and export again.

Right now my app only works in FireFox 3.6, and that’s just fine, baby!!

Note: I cannot share the application since it’s made for our organization, but if you need help in setting up one yourself just add to the comments…

Rubik’s cube Average formula in Excel

stopwatchAny speedcuber knows how to calculate his or her average: total all, remove fastest and slowest, and divide. Here’s how you do that in Excel

=( SUM(A1:A12) – MIN(A1:A12) – MAX(A1:A12) ) / (COUNT(A1:A12) – 2)

But that is tedious of course, and your constantly changing the range to find the average of e.g. 5, or a running average. So instead I wrote a little VBA function you can put in a module.

  • Press ALT+F11 to go to the editor
  • Choose Insert > Module
  • Choose Insert > Procedure
  • Type CubeAVG
  • Click Function
  • Click OK

Now edit so it’s like the code below

' cubeavg : calculate speedcubing average
Public Function cubeavg(r)
    Dim total As Double, fastest As Double, slowest As Double, n As Integer
    total = WorksheetFunction.Sum(r)
    fastest = WorksheetFunction.Min(r)
    slowest = WorksheetFunction.Max(r)
    n = WorksheetFunction.Count(r) - 2
    cubeavg = (total - slowest - fastest) / n
End Function

Don’t forget to save the file…

Convert Excel date value to SQL date

When creating SQL statements you’ll often need a date in the ISO 8601 standard format (e.g. 2010-03-26 12:34).

Of course you can change the format in Excel to show it as such, but that doesn’t give you the string you need, e.g. in an insert or update statement.

Here’s an Excel function to make an SQL date value, presuming the date value is in cell A1:

=TEXT(A1,”yyyy-mm-dd hh:MM:ss”)

This circumvents the use of complicated IF and date/time functions. Append a “Z” if you need to indicate the timezone as UTC (i.e. GMT) time.

Here’s a short VBA function to create this type of date

Function SQLDate(d)
SQLDate = WorksheetFunction.Text(d, "yyyy-mm-dd hh:MM:ss")
End Function

Put this code in a new module in your workbook to instantly start using the function in Excel like this: “=SQLDate(A1)”

SQL date in Excel
SQL date in Excel

More information:

How to remove duplicates from a list

There are tons of ways to remove duplicates from a list of items, most of which are way too complicated and technical for a noob (if you don’t know what this is, then that’s you) to perform.

Let’s look at some of them, and let me know in the comments if you think these were useful. Here they come, in order of increasing difficulty (geekness)


removing duplicates with textpad
textpad sort & remove

TextPad is a free (well, nagware) text editor with so many built in tools I cannot talk about it without crying…

  • open the file in TextPad
  • select Tools > Sort
  • check the box at ‘remove duplicate lines’
  • click OK


Another program abused by millions (billions?) to do stuff that could be done with a 10 year old cellphone. What the bozos at Microsoft tell you is the dumbest way to do it, because you’re overwriting your original list. Here’s the smart way

  • Excel Pivot
    Excel Pivot

    Select the data

  • Click Data > PivotTable… (Office 2003) or Insert > PivotTable (Office 2007)
  • Click ‘Finish’ to create a new sheet with an empty pivot table
  • Drag the column for which you need to remove duplicates into the left part of the pivottable
  • adding flavor: you can now sort, filter, group, analyze, you name it.


If your data is in a database, and you have access to SQL, perform the following query:

  • SELECT DISTINCT(column) FROM table
  • in place of ‘column’ you type what you need to be unique
  • in place of ‘table’ you type the table name
  • tip: you can combine more columns by typing (column1, column2, …)


If you have the file on a linux or unix system, from the terminal (command line) type

  • sort -u file > output
  • where ‘file’ is the name of your file and ‘output’ is the name of the output file.

I’d like to know if you can come up with new and maybe even faster ways!

How to ruin your website in 10 steps

I have worked for almost 10 years in web development now, and here’s what I have learned from the clients: you can ruin your website too!

Here’s how:
1. Get the cheapest designer you can get. If your nephew says he can do it, let him do it. It’s not that hard.
2. That being said, you can also do it yourself. Why pay for some fancy designer when you can buy or get a template. And HTML+CSS is easy, right?
3. Even cheaper, copy someone else’s site and just change the text.
4. Don’t start a forum or community. There are a lot of people out there ready to say nasty things about you.
5. You should get a CMS if possible. Then you can control everything yourself real easy, and choose any font and color. Did you know with a CMS you can just copy straight from MS Word? You can make sure people notice your links by using a big red font.
6. Don’t listen to any advice from your designer. They’re just after your money, not to help you.
7. If it looks good in Internet Explorer, then you’re done. There are so many browsers there’s no keeping up with it, and most people use IE anyway.
8. Almost everyone has broadband, so keep your images in high quality. Browsers resize them anyway when they display them.
9. Tables are cool. With tables you can get layouts done that are really hard without them.
10. Get as much information from your visitors as possible. When they sign up, ask for name, gender, location, email, interests. You can even sell that information later and make a good profit.

And all of the above will help you to get the lowest possible position on Google. But there are companies that can help you to become #1, just wait for the ‘offers’ to come in.

Adrian Monk and the case of the crippled DVD driver

Finally solved all computer problems, where nobody else could. After having Infoman have a look at my PC they said it was a virus, and it was repaired, but the problem (sudden reboots) kept occurring. In addition, my DVD player didn’t work anymore. I opened the case and found a dangling cable end, almost touching the motherboard. I think when the fan started blowing, it would sometimes short-circuit the MOBO (it’s a guess still). Anyway I secured it and the problem is now gone. 2 weeks, no reboot.

The broken CD player was a problem with an application named ‘HotSpot Shield’. Actually a pretty good app that enables you to access all kinds of sites that are blocked in your country (e.g. Veoh, Hulu, some YouTube vids). But, the thing came with very annoying ads so I uninstalled it. A couple of days ago I bought a wireless router and setup a network. All went well, but the connection dropped suddenly. I checked the TCP/IP stack and saw ‘Hotspot Shield’ as a layer in there. Damn! I said: “He’s the guy”.

Adrian Monk
Adrian Monk : "here's what happened"

Here’s what happened (Adrian Monk style): “At first we thought it was maybe a virus causing this, but cleaning with plenty of scanners didn’t resolve it. The next suspect was the hard disk, but even chckdsk /r didn’t solve the problems. Then we suspected the drivers themselves and reinstalled and updated all drivers. That didn’t work. But then we found HotSpot leaving a trace in network settings. Hotspot Shield needed to intercept all traffic in order to make sure it looked like it was coming from the US. So it put itself not only in Program Files, but also as an add-on in browsers, and as a layer in TCP/IP. That interfered with the built-in modem driver, which then failed. The modem driver looked for another resource and used one that conflicted with the DVD drive. After removing HotSpot from all network settings and uninstalling the DVD driver, it came back automatically and started responding again. Patient saved, and HotSpot put away.”