So there I was, working on a program that generates an Excel report. Whenever I have to write a program that interfaces with Excel (or, more generally, when I need COM automation), I work with Win32::OLE, and I have quite a deal of experience doing so. However, I have always found VBA's object model too counterintuitive and often find myself trying things based on hunches rather than common sense; the combination of Excel's macro recorder and ActiveState's VBScript Converter helps to ease the pain, but... Anyway, I didn't want to rely on a local Excel installation to get the job done. On the other hand, I wanted to take a multiplatform approach this time, so I decided to try Spreadsheet::WriteExcel. This module works fine and has an impressive documentation, chock-full of examples (screenshots and all!), but I got stuck when I tried to find a way of adding several different formatting features to the cells' content (say, words in different colors). I contacted the module's author, who replied quickly and informed me that this feature was not supported for technical reasons, but suggested that I could use a related module, Spreadsheet::WriteExcelXML, which provides the convenient write_html_string function for this purpose. The idea of generating XML output instead of the typical binary format seemed sexy enough, so I adapted the code to the new module.
But then I found another problem. I wanted to freeze the headers row and the freeze_panes() method didn't seem to work as it worked with Spreadsheet::WriteExcel. I read again the documentation and found out to my dismay that it's not yet supported in the XML version (although it will eventually be implemented). I could try to implement it myself, but I don't know well enough this XML vocabulary and it would take me far more time than I can afford right now. So I created a poor man's freeze panes functionality --I always wanted to create a poor man's version of something (just for the sake of using that buzzword); actually, I have done it many times, but never on purpose. So, here is the procedure I followed: I froze panes manually on a sample report open in Excel, and saved it. Then I compared the new file and the original one to get the lines added, and modified my code to insert that patch in a second pass into every automatically generated report:
my $patch = <<'END';
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>2</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
<x:FreezePanes x:Range="A2"> </x:FreezePanes>
END
open my $in, '<:encoding(UTF-8)', $log_path;
my $text = do { local $/; <$in> };
close $in;
chop($patch);
$text =~ s/<\/Table>/<\/Table>\n$patch/;
open my $out, '>:encoding(UTF-8)', $log_path;
print $out $text;
close $out;
Yes, it's a dirty trick, but it works!
Leave a comment