Chewing The Cud

When using the Spreadsheet_Excel_Writer library from the PEAR repository, I came across an issue I didn’t see handled in the docs (as of this writing, I am using Spreadsheet_Excel_Writer 0.9.1 beta)

My application creates spreadsheets that contain order information. Part of each row is a list of up to 20 ISBNs and the quantities desired of each. The issue came in how to handle ISBNs that had a leading zero. When I first looked through the PEAR docs for the library, a Worksheet method, writeString, looked to be the solution. However, the end result was that while the leading zero was maintained, the cell’s format was still numeric. This resulted in the application receiving the generated xls to then drop the zero, resulting in an invalid ISBN.

Looking over the internals of the Worksheet::writeString method didn’t reveal an undocumented feature that would ensure a cell was read as text, regardless of its contents. I next looked at the Format::setNumFormat method as I knew it contained ways to format the number as currency, timestamp, fractions, etc. You could then pass this Format object as the optional fourth parameter to the Worksheet::write method.

Contained in the Format::setNumFormat docs was a link to the OpenOffice.org documentation of the Excel File Format (found here, pdf). Interested in how exactly the file was structured, I read on. What I learned that was directly applicable is that each cell contains a pointer to a format definition, or XF record, and it was this XF record where formatting was stored. From the doc, section 4.6:

All cell formatting attributes are stored in XF records…The cell records themselves contain an index into the XF record list. This way of string cell formatting saves memory and decreases the file size.

So if two cells use the same formatting, like the ISBN columns would, each cell would contain a pointer to the XF record that would tell Excel the cell was text. Seciton 4.6.1 lists the 6 groups of formatting attributes, the first of which is number format, which is then an index to a FORMAT record. Okay, we’re on to something here. Further in the pdf, in section 5.49, we see the definition of the FORMAT record. Lo and behold, the table of formats from the setNumFormat page is listed in the pdf, but we see that the PEAR listing is incomplete. Scanning the complete table in the pdf, we see index 49, type Text, format string ‘@’. Bingo.

Our code for formatting numeric data as text in a string goes a little something like this (modified from the PEAR example code):

$workbook = new Spreadsheet_Excel_Writer();
$worksheet =& $workbook->addWorksheet();
 
// We'll show dates with a three letter month and four digit year format
$text_format =& $workbook->addFormat();
$text_format->setNumFormat('@');
 
$worksheet->write(0, 0, "Without formatting");
$worksheet->write(0, 1, '0123'); // cell contains 123
 
$worksheet->write(1, 0, "With formatting");
$worksheet->write(1, 1, '0123', $text_format); // cell contains 0123

To verify, generate the xls and open it. Right click the cells to modify the format of the cell, and see that the first cell is formatted as a general number, and the second cell is formatted as text.

The meta-moral is to read the docs and follow references to get at the source material. Had I not opened the pdf, it may have been a few more time units finding the information on Google. Plus, I learned a lot more about an important file format. I can sleep easy knowing I’m that much more knowledgeable.

Thought I’d go ahead and announce, mainly to myself, that I will be working through SICP. The rub…doing it in Javascript. Seems as though most other languages are covered (I know Erlang is taken) and since I am doing an increasingly large amount of Javascript, coupled with the eventual prevalence of server-side Javascript, I figured it best to start getting intimate. What I like about this task is that since SICP has been so widely covered on the web, I have many resources to aid in better understanding the material (and it is some thick material). Anyway, I’ve begun chapter one and will post the chapters, as well as excerpts I find interesting, in no pre-defined timeframe.

Oh yeah, and I’m engaged.

It’s hard to understate the usefulness and robust feature set that most of the GNU tools have in their arsenal. Today, I’ll make mention of one such tool, wget, and a novell use of the command.

As I go through my work, I find that sites we agree to take over have little structure. They generally were slapped together a long time ago, with little thought to organization, made with Dreamweaver or, Stallman forbid, FrontPage. I’m not judging; as long as something looks okay in the browser, a company can proclaim, “We’re on the intarwebs!” However, tracking down all of their pages to be converted into a CMS, for instance, can be time consuming. Not wanting to waste a client’s money by searching through the source for links and images, then manually reconstructing the layout of the files, I fell on my trusty GNU tool wget. (I also did not have FTP access, but I knew there were dead pages that I didn’t want to resurrect. Using wget in this case helped me retrieve only the pages that were still linked to from the main page).

Here’s a variation of the incantation of wget I used:

wget -r -A '*.htm*, *.jpg, *.png, *.gif' -l 3 http://www.example-site.com

What’s it all mean?
-r: wget should retrieve recursively
-A: takes a comma-separated list of patterns to match files to accept (use -R to reject). In this case, we want all htm, html, and most picture format files.
-l: denotes how far down the rabbit hole to venture. I started with 1, so only links from the first page were parsed and followed. I then tried 2, following links that were a level below the parent and compared the resulting structure. Trying 3, I found no difference between 3’s results and 2’s results, meaning all links had been followed and accounted for.

The result:
A directory called www.example-site.com that contains the files in their layout on the server. Now I knew which pages needed converting and which images to add to the new site.

A side note: A handy way to see the layout of your newly downloaded directory is to use the tree command.

tree www.example-site.com/

will display something like this:

www.example-site.com/
|– about.html
|– calendar.html
|– committees.html
|– contact.html
|– otherdir
| `– index.html
|– images
| |– header.gif
| |– logo.gif
| `– spacer.gif
|– index.html
|– join.html
|– news.html
|– partnerships.html
`– scoopholiday.html

I’ve had the pleasure of working on the technology that powers iliveinspired.com and know that the founders, Rob and Chris, are working their butts off to make this a great service. Armed with not much more than inspiration and determination, they are taking their service to the world by foot. Their first foray into marching marketing was to sign the Dalai Lama on as a content provider. Not only did the Dalai Lama receive them (read about their trip, starting here), but he embraced them and their message and agreed to work with the service.

Now these two are on a mission to sign Oprah and are generating some press about it. Pick up the story from their blog here, and then read about some of the press they’ve gotten here, here, here, and here. This is a great service, there are lots of different themes to choose from, and it’s a cheap service that can provide a lot of value. Why else would they offer the first 45 days free of charge?

What’s keeping you from living inspired today? Visit I Live Inspired and start receiving daily inspiration on your phone.

Always on the lookout for increases in efficiency, I love when I find a slick snippet of command line goodness that makes a hard sounding task simple and quick. I was tasked with creating an email list from a database and putting it into a csv format, and had only the command line to interface with the DB. My first attempt revolved around using the SELECT … INTO OUTFILE syntax. Unfortunately, I was unable to write out to a file with the DB user I had access to. What’s a fella to do?

Unix pipes to the rescue.

First, the whole command:

echo “select * from example;” | mysql -u user -p dbname | tr ‘^V^I’ ‘,’ > filename.csv

Let’s break this down, in case Ben is reading and can’t follow along. The echo statement contains your query. It is sent to the mysql command, which connects you to the database and executes the query, returning the data in tab-delimited format to the console. The tr command reads from STDIN, and replaces tabs (Ctrl-V Ctrl-I) with whatever delimiter you want (in this case the comma). The final touch is sending it to a file of your choosing.

*Note* - You actually have to type the Ctrl-V Ctrl-I when entering this command. Copy/paste won’t cut it in the example above.

*Note* - You typically do not want to actually enter your mysql password on the command line, as commands run are typically logged. Omit the password to force mysql to ask for it (it won’t interfere with the query). And if you don’t have your mysql access password protected, WTF? You’re asking for trouble.

So there you have it. Simple, easy to follow, effective. As always, this example can be extended into a variety of different ways. It’s up to you to figure it out (you can, of course, pay me to figure it out).

One of the projects I’ve been helping get off the ground is I Live Inspired, an inspirational text-messaging service. The site is good, and the concept is great. One can never have too much inspiration.

The founders, Rob and Chris, are on a mission. They are seeking an audience with the Dalai Lama in Indiana and are walking 7 days in hopes of getting a 7 minute audience. They are also keeping a blog of their adventures. These guys are not much older than me and are trying to get a positive service off the ground. Whether they have audience with the Dalai Lama or not, the experience of the walk, the people they’ve met thus far, and the people they’ve yet to meet, will change their lives. And we get to share in that through their writing.

So take a minute, read up on what the future of America is up to, and if you want some extra inspiration delivered to your phone daily, consider signing up for one of the many great communities at I Live Inspired.

* Disclaimer: While I have helped create the site, I do not receive any compensation for spreading the word. I think its a great service and deserves notice.

I have been playing with some drawing code in javascript, storing coordinates and using them later on in the application. My list of coordinates is of the form [ [id1, x1, y1, width1, height1], [id2, x2, y2, width2, height2],…]. A requirement of the application is that a user can delete a set of coordinates from the list. Using prototype.js, I created a simple function to remove the nested array based on the id.

// remove an array from the list based on the id
function remove(id, list) {
    return $A(list).map(
        function(arr) {
            if ( $A(arr).first() == id ) { return ; }
            else { return arr; }
        }).compact();
}

In your favorite editor, this function can be a one-liner, but spacing helps here for clarity and formatting on the page. Onward!

So what’s happening? The first thing we do is wrap list with the $A() call to ensure we have access to the extensions prototype gives us for arrays (I’m calling the parameter a list because I’m on an Erlang kick and it has infiltrated my core!). Once extended, we call the map function to iterate through the list and apply the supplied function to each element in the list (in this case it is a list of arrays, so each element passed to the supplied function will be an array as well).

Within the supplied function, we are dealing with a single array of the form [id, x, y, width, height], so $A(arr).first() returns the id of the array. This value is compared to the value of the id parameter and if it matches, returns nothing, or ‘undefined’ in Javascript. If the ids don’t match, it returns the array unaltered. As the map function iterates through the list, a new list is created containing the results of the supplied function. So the return value of the map function call is an array. We then call the compact function on the resulting array, which removes any undefined values from the array, essentially leaving only those arrays that did not have the id passed in.

This function is fairly specialized; the requirements for the function are fairly specific. A more general function could be written but that is an exercise left to the reader.

So you want to download some files from an ftp server, but they are contained in more than one subdirectory. With a straight ftp client, you would have to recurse through all of the directories and mget each directory’s contents manually. Never fear, though, there is a little utility that can help - wget.

> wget -r ftp://user:pass@ftpsite.com/directory .

If the ftp site allows anonymous logins, you can omit the user:pass portion. This will get everything…it is left as an exercise to the reader to customize the command.

As we all know (actually, many probably don’t know) Internet Explorer has claimed many hours of developer time trying to get a feature working with the quirks of IE. One quirk that I’ve dealt with recently was the :hover pseudo-class and its implementation across various browsers. The most notable quirk is that IE only supports the :hover on anchor tags (<a>). What’s a fella to do when he wants a drop down menu that displays the sub-menu when the mouse is hovering over an li element? Write some Javascript to aid IE in rendering the drop-down effect properly.

The first draft of our menu is here. If you are unfortunate enough to be using IE, you probably won’t see the sub-menu items. So how do we negotiate this? With a little extra class, and some Javascript.

The second draft of our menu can be found here. The differences to note:

• The li:hover rule is now accompanied by a li.over as well
• The function fixHover()
• The function init()

So we added a rule that says any ul with a parent li with a class of over will also get the styling that a ul with a parent with li:hover gets; in this case - display the underlying ul. Next, we added a function (fixHover) that took an element, and retrieved all of it’s immediate children nodes. We then iterate through the list of children, basically adding two events, “mouseover” and “mouseout”, to for each element to observe. For “mouseover” events, append the classname “over” to the element; on “mouseout” events, remove the “over” classname. The essence here is that :hover is the CSS equivalent of observing the “mouseover” and “mouseout” events. The draw back to our solution is that if Javascript is turned off, the sub-menus remain hidden from the user.

NOTE: I am not a designer, so the purpose of this article is to merely illustrate the ability to apply hover-type functionality to any element on the page in IE and not showcase my ability to make things look nice.

Another feature to mention is the init function and the Event.observe() call, which calls the init function after the window has finished loading the page. This is a must because we cannot apply the “mouseover” and “mouseout” event observations until the nodes have been created in the DOM. Best to leave this until the window has loaded. Both of the functions rely on the prototype.js library to retrieve the child nodes, iterate through the nodes, and attach events to the nodes. It is possible to do this without prototype or with another library, but I leave it up to the reader to translate this code to their library of choice.

We pushed collabofit.com live the other day, restricting access to personal invites for the time being. Interested in the experience? Want to be an early adopter? Shoot me a mail and I’ll set you up. Check out Katanaa and the collabofit blog for more information.