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();

[download]
PS: In documentation about WriteExcel, it says "AutoFit" is impossible: Documentation
Sorry for my bad english :-(

Steve_p - changed title to be more "searchable"

  • Comment on Excel::SpreadSheet : Can't call method "set_column" on an undefined value
  • Download Code

Replies are listed 'Best First'.
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();

    [download]

    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]

Back to Seekers of Perl Wisdom