nathanvit has asked for the wisdom of the Perl Monks concerning the following question:
Hello to everybody,
I'm trying to order lines of an Excel's spreadsheet and set dimension of columns. i'll explain better. I've a db in MSSql server in which i've a table.
I want to put this table in Excel but ordered by a field called "filiali"...
I use DBI and DBD::ODBC to connect to MSSql server and Spreadsheet::WriteExcel::FromDB to put the table into Excel's file...
Then i use Spreadsheet::WriteExcel to order table and set coulmn width but my program tell me: "Can't call method "set_cloumn" on an undefined value at riversamento.pl line 28".
Does anybody can help me? What does it means?
How can i order by a field?
Thanks,
nathanvi
use DBI;
use strict;
use DBD::ODBC;
use Spreadsheet::WriteExcel::FromDB;
use Spreadsheet::WriteExcel;
my $user='intranet';
my $passwd='vittorio';
my $tabella='A_Filiali';
my @campi=qw/CodiceFiliale CittaFiliale Indirizzo Cap NumTel NumFax So
+cieta Apertura/;
my $file='prova.xls';
my $dbh = DBI->connect('dbi:ODBC:registri',$user, $passwd) or die "Non
+ riesco a connettermi!!\n";
my $ss = Spreadsheet::WriteExcel::FromDB->read($dbh, $tabella);
$ss->include_columns(@campi);
open FILE, ">$file" or die "Non riesco ad aprire il file!!\nErrore: $!
+\n";
binmode FILE;
print FILE $ss->as_xls;
close FILE;
my $workbook = Spreadsheet::WriteExcel->new($file);
my $worksheet=$workbook->sheets(0);
$worksheet->set_column(0,0, 30);
$workbook->close();
PS: In documentation about WriteExcel, it says "AutoFit" is impossible: Documentation
Sorry for my bad english :-(
Steve_p - changed title to be more "searchable"
Re: Excel::SpreadSheet : Can't call method "set_column" on an undefined value
by Anonymous Monk on Sep 23, 2004 at 10:23 UTC
|
If you look at the synopsis of Spreadsheet::WriteExcel, then you will see:
use Spreadsheet::WriteExcel; # Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new("perl.xls"); # Add
+ a worksheet
$worksheet = $workbook->add_worksheet();
you are using ->sheets(0) and not add_worksheet() which is incorrect. | [reply] [d/l] [select] |
|
Thanks i know but in worksheet method you can read sheets() and here it seems you can also use this method to get a sheet.
Probably i fall at this point?
| [reply] |
|
With sheets() you get a list (or one) existing worksheet, you can't use it to create one. (I guess)
Try adding a worksheet, and then using the sheets() method, then it will probably work.
| [reply] [d/l] [select] |
Re: Excel::SpreadSheet : Can't call method "set_column" on an undefined value
by jmcnamara (Monsignor) on Sep 23, 2004 at 18:03 UTC
|
The error comes from the fact that you haven't called add_worksheet() before trying to access the worksheet object.
Spreadsheet::WriteExcel creates a new file. It doesn't update existing files like you are trying to do in your example.
I thought that it would be possible to access the Spreadsheet::WriteExcel object that Spreadsheet::WriteExcel::FromDB uses but the module doesn't expose it.
It would be possible to subclass Spreadsheet::WriteExcel::FromDB and add the required functionality but it would probably just be easier to use DBI and Spreadsheet::WriteExcel directly.
--
John.
| [reply] |
|