Creating Excel File with Perl

gb123z has asked for the wisdom of the Perl Monks concerning the following question:

I have a perl program running on Windows Server 2003/IIS6.0 that should just open an excel report file in the client's browser and let them save it. The code worked on a server running 2000/IIS5.0, but not on this new server. The excel file is created and then saved on the server, not the client. I am using the following sample code to test opening excel on the client:

use strict; 
use CGI ':standard';
use CGI::Carp qw/fatalsToBrowser/; 
use DBD::ODBC;
use strict;
use List::Util qw[min max];
#use warnings;

use Spreadsheet::WriteExcel;

my $filename = "mydata.xls"; # generated on-the-fly
print "Content-disposition: attachment;filename=$filename\n";
print "Content-type: application/vnd.ms-excel\n\n";

my $workbook  = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->add_worksheet();
$worksheet->write(0, 0, "Hello");
$worksheet->write(0, 1, "world");
[download]

The code runs, but it looks like the output is printed to the web page, not excel. It has been awhile since I setup a site to use perl/cgi script. Have I missed a security setting somewhere in IIS, do you have any suggestions? Thanks. Greg

Comment on Creating Excel File with Perl Download Code

Replies are listed 'Best First'.
Re: Spreadsheet:: WriteExcel by Corion (Patriarch) on Mar 12, 2009 at 16:07 UTC
Have you made sure that your problem is related to Spreadsheet::WriteExcel? Your (badly chosen) node title suggests that you presume the problem is with the module, but have you taken it out and sent a canned Excel file instead? Personally, I think one of your problems is that you're sending bad HTTP headers, because you're using plain newlines (`\n`) where the HTTP spec wants carriage return+newline (`\r\n`). It's either that or the case that you're not binmodeing your output filehandle before writing binary data to it. I'd clear out both of these possibilities before looking deeper into Spreadsheet::WriteExcel.	[reply] [d/l] [select]
Creating Excel File with Perl by gb123z (Initiate) on Mar 12, 2009 at 19:14 UTC
I apologize for the bad node title. I hope the updated node title is better. I didn't think there was an issue with the Spreadsheet::WriteExcel module and miscommunicated this. Instead of using the sample code I posted, I copied my original Perl code that works on the older server to the new server. Now it creates the excel report file when I call the .pl page from the browser, but saves the excel file on the server and doesn't open it on the client like it does on the older server. Would this be related to the permissions set on the new server?	[reply]
Re: Spreadsheet:: WriteExcel by VinsWorldcom (Prior) on Mar 12, 2009 at 16:55 UTC
If nothing has changed in the script or the Perl version on the server - could it realistically be the code or the Spreadsheet::WriteExcel module? I've converted websites from IIS5 (Win2k) to IIS6 (Win2k3) and magically my scripts (.BAT, .PL, etc...) stop working. It had to do with permissions on the directories the scripts were in, the fact that the .BAT scripts specifically execute via CMD.EXE (which lives in %systemroot%\system32 - a NO-NO for IIS6) and the permissions for the user account running the IIS WWWW service. Once I straightened out M$ security issues, my scripts ran fine again	[reply]
Re^2: Spreadsheet:: WriteExcel by gb123z (Initiate) on Mar 12, 2009 at 18:11 UTC
I probably titled this node incorrectly and/or explained my issue incorrectly. I agree, I don't think the issue is with the code itself or Spreadsheet::WriteExcel. I have set the permission on the directory where I have the scripts for now to give 'everyone' full control to try to resolve the issue. My original code runs, but saves the excel file to the server and doesn't make it available to the client. Does it sound like I still have a permission issue somewhere?	[reply]
Re^3: Spreadsheet:: WriteExcel by VinsWorldcom (Prior) on Mar 13, 2009 at 14:13 UTC
It's been a while since I used IIS - or any web server for that matter - but does IIS understand the MIME type you're using and how to handle it? Also, the client side browser is going to have to launch an ?ActiveX control? to view the Excel sheet in the browser? I will need to understand how to handle Excel files, correct?	[reply]
Re: Creating Excel File with Perl by Bloodnok (Vicar) on Mar 12, 2009 at 19:22 UTC
It may be that there's something of use to you in IIS & perl - are they compatible ?. A user level that continues to overstate my experience :-))	[reply]