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 ...
