How to write to a excel file while downloading in cgi perl

[ghosh123]

Hi
I am working on extracting some information from a webpage and dumping them into a excel file. The webpage from which I am extracting the report has link on it as 'Create report'. Upon clicking that link it goes to a script in the server and does the following things :

1. Creates the Excel using Spreadsheet::WriteExcel
2. Extracts the page content using LWP::UserAgent, HTML::TableExtract qw(tree)
3. Writes to the excel and closes it .
4. Lets the user download it.

Now the question is, how can I write something to that generated excel file in time of downloading it. Say for example I want to write some heading and title at the begining of the excel file while downloading.
All the above 4 functionalities are working fine. For your ease of understanding I am just putting the last part of the code below :

## please assume that extracting the page content and 
## writing to the excel is already done before this 
## following code :

$workbook->close;
open(FILE, "<$filename") || die "Unable to download file \n";
my @fileholder;

@fileholder = <FILE>; close (FILE);


unlink $filename;
print "Content-Type:application/x-download\n";
print "Content Disposition:attachment;filename=report.xls\n\n";
print @fileholder;

Thanks

[Corion]

If the code writing to the Excel file is already written, why don't you apply your changes there, before calling $workbook->close?

Alternatively, (re)open the workbook, make your changes, and then save it to a tempfile, and send that tempfile to the user.

Also see Spreadsheet::ParseExcel::SaveParser.

[glasswalk3r]

I think you should change the order of operations:

1. Extracts the page content using LWP::UserAgent, HTML::TableExtract
2. Creates the Excel using Spreadsheet::WriteExcel
3. Writes to the excel and closes it
4. Lets the user download it

This way you would avoid creating the Excel file if the content recovered is not correct (think getting a 404 from LWP).

I would also consider two things:

• Use XML instead of creating a Excel file: unless your formating and using formulas in the Excel, XML is more portable and probably faster to write using XML::Writer. Newer versions of Excel can work pretty well with XML (unless the XML content is really large).
• If you really need Excel, then assign the content of the file to a scalar instead of an array. If you are at a Windows OS, maybe you should use binmode() before printing it.