Archive for February, 2009

Dreams They Complicate My Life

Had one of those constant dreams nights which I take as indicative of good sleep, though I was awoken by my iPod once. I've been listening to the over-my-head In Our Time podcast because, whether it's terribly interesting or terribly boring, it makes me terribly sleepy. It was off-putting to wake up hearing a stranger in a dark room speaking of the Fall of Carthage. The highlights:

  1. Falls into what I would call the "Tetris Dream" category, when you've become a little too interested in something: had a dream where I was in a field hospital somewhere in the jungle watching a TV report, hosted by @leolaporte about Canadian Twitterers, whom he referred to as "C-itters". I'd really hoped we were beyond that kind of prejudice in 2009.
  2. I was a amateur anti-mob sniper, recruited for the job by a rogue government agent who's ex-girlfriend I was dating (she was also recruited for the cause; for the record, I'm pretty sure it was this woman from Numb3rs). I was up in our hotel room[1] with my assignment in my sights when I noticed the Mob's snipers hanging out of the hotel window right next to me. Jumping back out of the window, I went to inventory the guns and ammunition I'd been left when the treachery became clear: the case had almost no bullets (but plenty AA batteries, the guns being electric). To heighten the stress of the moment, the cleaning crew started coming around the halls.
  3. To cap the evening, I got another go at my newest recurring dream. Since my Mom died, I've been having this strange cartographic dream where I try to map out the city of Newport[2] from a boat. It never works, the boat sinks and whatever I'm looking for goes unfound. I could probably save this dream-self a lot of time if he'd give me a whack at his map.

[1] We'd gotten to the point in our relationship where we were not only comfortable sharing a hotel room, we could interrupt a vacation to gun down mobsters without any negative effect on our relationship
[2] More accurately, the whole of Aquidneck Island, as I think last time I wound up in Portsmouth under the Mount Hope Bridge.

Tags:

PHP Excel Exporter

A few times a year a client needs to export something from a database table to Excel. There's a simple hack to do it in most any language. There are actually a few, but having come up as a web developer, my preferred trick is to just build an HTML table and serve it as Excel by setting the mime type header. Having done this dozens of times, I finally formalized this into a simple PHP class tonight to save myself some time and figured I might as well share it.

The bad news: because I am lazy, it relies on an old data connection class I wrote years ago when I was even less bright than I am now. The thing's so ugly I posted it somewhere else because I am too ashamed to host it here. You can rip that out and use whatever you prefer by just changing the logic in _get_table() below. If you do choose to use my old data-class.php, be aware it expects 4 constants, DB_SERVER, DB_USER, DB_USER_PASS, DB_NAME to create a connection to the database.

Here's the exporter code itself Update: I moved the code to snipplr because this WordPress plugin doesn't handle newline characters very well.

The simplest use is to instantiate an object, tell the exporter what you want to appear in the header row in the spreadsheet (by setting column_heads to an array of values) and then calling export(), passing it the SQL query that gets the data. If the number of fields in your query doesn't match the number of heads in column_heads, the resulting HTML will be a mess. You will understand if the code assumes you never make such mistakes. Here's a code example:

  1.  
  2. $e = new ExcelExporter();
  3. $e->column_heads = array("First Name", "Last Name");
  4. echo $e->export("SELECT first_name, last_name FROM table");
  5.  

Quick notes:

  • Control the Excel filename in my example by setting $e->filename("something-else.xls")
  • Add a timestamp to every file (useful for making sure the filename is always unique) by setting $e->timestamp_file = true
  • When you're trying to implement this and it's not working and having to say yes to the popup and let the file open in Excel is driving you crazy, set $e->debug = true and it will skip the Excel headers, sending the output to the browser

The big gotcha that works well for me but might not for you: there's a hook in the code that passes every data column through _format_field(). In my current class, this looks for any field with "_date" in the column name, assumes that field is a Unix timestamp and transforms the value into a m/d/y date. If you live in the other 99% of the world where people format their dates un-Americanly, well, you can do that like this: $e->date_format("d/m/y") or whatever other crazy date/ time format you like.

If you think that behavior stinks, rip it out. Alternatively, you can modify it or subclass this code (like "client-xyz-exporter extends ExcelExporter" for every client who lives in Excel) and change _format_field() to do whatever you want in a one-off sort of way. This is not high art, it's just a faster way of making someone happy (if you can imagine the kind of person whose life is improved by additional spreadsheets).

Windows GPG Front-End

I'm doing some work with GPG encryption and I always like to have a visual/ gui front-end to use to make sure I haven't screwed something up in my command line adventures. I came across Cryptophane today and it seems like a nice way to keep track of my particular Alice and Bob. The only problem I ran across was that my GPG install was in a non-standard place and Cryptophane doesn't look in the registry (I'm pretty sure GPG writes to it). The error wasn't immediately clear and there's no online help (though a .chm is provided), so I thought I'd post this for anyone else who runs into a similar problem. My shortcut target now looks like this:
"G:\Program Files\Cryptophane\Cryptophane.exe" --gpg-path "G:\Program Files\GNU\GnuPG\gpg.exe"

Your paths may vary, etc.