We had another nice win for Sprockets this week; web-based Microsoft Excel .xls output.

Personally, I hate Excel. I regard Microsoft’s desktop applications as poorly made, marketing-led disasters which achieve sales at the cost of productivity and happiness across the globe. Considering the immense assets available to Microsoft, it is truly astonishing that they produce applications that make no sense, are ugly, inconsistent, do not work properly, frustrate their users, and waste resources. In Microsoft’s litany of crimes against humanity, Excel is #1, somehow managing to be even more awful than Word. I truly hate Excel and always will.

However, client wants Excel format, so I love Excel.

It turns out that Excel reads Office Open XML files with a .xls extension as though they were native files. This means that Sprockets, which is an XML generator sans pareil, can output to .xls, instead of having to use a minor league format like CSV. The code looks like this:

$workbook = new Sprocket( 'Workbook' );
...
        
$document = $workbook->DocumentProperties();
$document->xmlns = "urn:schemas-microsoft-com:office:office";
$document->Author( "Innova Formularymaker" );
...

$excel_workbook = $workbook->ExcelWorkbook();
$excel_workbook->xmlns = "urn:schemas-microsoft-com:office:excel";
        
$styles = $workbook->Styles();
$header_style = $styles->Style();
$header_style->setAttribute( 'ss:ID', 'header' );
...

Remember that this is pure Sprockets code; there’s no special Excel library being used here. Up until the code above, Sprockets had never even thought about workbooks and document properties. The objects and attributes are all being created dynamically, deus ex machina.

Incidentally, the use of setAttribute() instead of regular sprocket setter calls are made necessary by the colons in xml namespaces.

For Sprockets,

$tag->setAttribute( 'name', 'value' );

is equivalent to:

$tag->name = 'value';

The row data can be added as a table in a worksheet:

$worksheet = $workbook->Worksheet();
$worksheet->setAttribute( 'ss:Name', "Formulary" );
$table = $worksheet->Table();
...

$entry_row = $table->Row();

And so on. Excel is surprisingly fussy about the structure of its XML, but the entire Excel functionality – formulas, styling, macros – can be represented.

Some of the idiomatic aspects of the format are pure Microsoft. For example, the table declaration needs to know how many rows it contains (and crashes the parser if the number is wrong). That the problem could be fixed in half an hour, but isn’t, is indicative of Microsoft’s half-hearted embrace of open formats. Indeed, it would be much, much easier to support Open Document Format, but since Microsoft seems to be determined not to go there, the exercise would be academic.

Excel-format output is a nice leveraging of the Sprockets code. The next step is to wrap up our acquired knowledge of the Office Open XML format in a Sprockets library, so we don’t have to learn it all over again next time. Code reuse is usually difficult in output-specific situations, but Sprockets makes it much easier.