12 Dec 2003 huge   » (Apprentice)

sh : je te plains si tu n'as que ça à faire de lurker ici pour poster un message juste quand je reviens ici ... :-)

So, it's the week-end here in France.

This is very cool because I just solved my bug on my HTML/CSV/Excel library on IIS/ASP at work. The aim of this library is to output 2-dimensions data in HTML file (table), CSV file or Excel file (with the Spreadsheet::WriteExcel library). You feed in the formats of the columns (name of the column, size, data type for Excel pretty formatting, etc) and the data and automagically you got the file with the right format. This is useful when you have a listing to display to the user on a Website and want her/him to be able to download it in CSV and/or Excel. You just have to build the format, write the subroutine which will output the data and plug it in the library, and voila.

The library is able to write the data to a file. It can also send the file to the user on a website with Content-Disposition: attachment; filename= ... It can handle CGI (with "print" statement to output the header and then the content) or Win32::ASP (with $Response -> AddHeader ).

Here was the bug:

Everything was working fine on CGI/Apache/Linux. CSV and HTML were OK for ASP/IIS/Windows. But it was not working for Excel files on ASP. No error message, just an empty file. I guess it was a binary/text problem because CSV/HTML worked and a quick test show me that local file writing on the server worked.

Here is the solution:

I began to search on the Perl Script Introduction and see this useful code snippet on BinaryWrite. Not bad but this is useful when you had your data in a scalar. Unfortunaly, Spreadsheet::WriteExcel just write to files or "-" to output on STDOUT. The "new" method help had some useful information on CGI interfacing or mod_perl but nothing on ASP. But the answer was in "filehandle.pl", in the example 4. In this example, you can map a scalar to a file handle.

So, if I can get the output of Spreadsheet::WriteExcel in a string, turn binary mode on and output it to the browser, I'm in. Should work, in theory.

First step:

Install IO::Scalar:

ppm
install IO-Stringy
quit
Code :
use IO::Scalar;
use Win32::OLE::Variant;
...
$Response -> {ContentType} = 'application/vnd.ms-excel';
$Response -> AddHeader ('Content-Disposition', "attachment; filename=\"$filename\"");
(Hint : don't use "$Response -> AddHeader ('Content-Type')" because it will add the header, not overload the "text/html" Content-Type)

...

tie *XLS, 'IO::Scalar', \$xls_str;
$workbook  = Spreadsheet::WriteExcel -> new (\*XLS);
my $variant = Win32::OLE::Variant->new( VT_UI1, $xls_str);
$main::Response->BinaryWrite($variant);
So now, weeeeeeeeeeeek-end ...

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!