welly has asked for the wisdom of the Perl Monks concerning the following question:
Dear All,
I Have a problem, i'm already connect to my database (Oracle DB) using perl, and success display the result of my query (Select * from tab), But, the display using HTML.
I want the result of my query using Excel, The mean is : if i running my query (Select * from tab) in perl, then the result of the query (Select * from tab) in Excel....
Some body help me....for the script in perl ?
Or any body have another solution ?.
Thanks
Best Regards
-welly-
Re: Display Report Using Excel
by tstock (Curate) on Jul 07, 2004 at 02:51 UTC
|
If you are trying to make output from a CGI open in excel, you could output a CSV (comma separated values) file, or use Spreadsheet::WriteExcel to output an excel file.
Remember to use use Content-type: application/vnd.ms-excel (or text/x-csv) instead of text/html in your CGI. You might also have to name your cgi .csv or .xls for braindead IE to work properly, since a lot of times it doesn' respect the Content-Disposition filename correctly. Sample code -
#! /usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect( @connect_param );
my $data = $dbh->selectall_arrayref( 'SELECT * FROM country_info' );
print "Content-Type: text/x-csv\n\n";
for (@$data)
{
print join ",", map { "\"$_\"" } @$_;
print "\n";
}
Tiago | [reply] [d/l] |
|
Use Content-type - application-octet-stream.
#! /usr/bin/perl
use strict;
use warnings;
use DBI;
use CGI;
my $q = new CGI;
my $dbh = DBI->connect( @connect_param );
my $data = $dbh->selectall_arrayref( 'SELECT * FROM country_info' );
print $q->header('Content-type=application/octet-stream;name="data.csv
+"');
for (@$data)
{
print join ",", map { "\"$_\"" } @$_;
print "\n";
}
Neeraj
| [reply] [d/l] |
Re: Display Report Using Excel
by chime (Friar) on Jul 07, 2004 at 09:17 UTC
|
If you have your data in a csv file the next step is just to put in this sub routine to create an excel file.
If your data is not in a csv but in another type of file format - you can change the 'comma' split part to something else if your data is seperated by just whitespace or another seperater.
The code is just to give you a pointer in the right direction.
You should read the Spreadsheet::WriteExcel module information as well.
The code is below
| [reply] [d/l] |
|
Um, -- you for re-inventing the CSV parser. The most obvious problem is that CSV fields can be enclosed in quotes, and when so, can contain commas, which should not be considered delimiters. Text::CSV does the right thing.
| [reply] [d/l] |
Re: Display Report Using Excel
by dragonchild (Archbishop) on Jul 07, 2004 at 13:28 UTC
|
Use Excel::Template.
------
We are the carpenters and bricklayers of the Information Age.
Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose
I shouldn't have to say this, but any code, unless otherwise stated, is untested
| [reply] |
|