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.

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.