Re^2: Can't use an undefined value as a hash reference when passing hash key into Excel OLE call.

It appears to only happen when the $key is set at the 2nd value in the excel call (the one for the column). You set the key to the first value (the row), you need to set the key to the column value. I ran your code and it worked fine, but when I modified it so it was like my code (which crashed) I got the same error "Can't use an undefined value as a HASH reference at C:/test.pl line 22."

use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add();
for (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

my $worksheet = $wb->Sheets(1);
my %hash = (1 => 'One', 
            5 => 'Five',
            10=> 'Ten');
            
my $row = 0;
foreach my $key (sort keys %hash){
    $row = $row + 1;
    print "Key: $key\nValue: $hash{$key}\n";
    $worksheet->Cells($row, $key)->{Value} = $hash{$key};
}
[download]

Comment on Re^2: Can't use an undefined value as a hash reference when passing hash key into Excel OLE call. Download Code

Replies are listed 'Best First'.
Re^3: Can't use an undefined value as a hash reference when passing hash key into Excel OLE call. by davies (Monsignor) on Nov 11, 2011 at 08:15 UTC
Congratulations on fixing the problem per your update to the root node. You have taught me something I didn't know about hash keys. In my experience, Perl respects integers, but clearly hash keys are coerced into something different. I'm a little nervous, though, about coercing by adding zero. If the key has been changed into a floating point number, you might conceivably get 0.9999 (however many) instead of 1. Excel has to have an integer as a row or column number, and if that coercion is done by `int` or something comparable, you might get an "out by one" error. Although we have tried row 1 and it seems to work, it remains possible that in some system you will attempt to access row or column zero and get a run-time error. I therefore prefer the approach I have used in the following code. As an aside, `$row++` is more Perlish and less typing than your way, but your way works fine. `use strict; use warnings; use diagnostics; use Win32::OLE; my $xl = Win32::OLE->new('Excel.Application'); $xl->{Visible} = 1; my $wb = $xl->Workbooks->Add(); for (2..$wb->Sheets->{Count}) { $wb->Sheets(2)->Delete; } my $worksheet = $wb->Sheets(1); my %hash = (1 => 'One', 5 => 'Five', 10=> 'Ten'); my $row = 0; foreach my $key (sort keys %hash){ $row++; $worksheet->Cells($row, int($key + .5))->{Value} = $hash{$key}; }` [download] Regards, John Davies	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: Can't use an undefined value as a hash reference when passing hash key into Excel OLE call.
by davies (Monsignor) on Nov 11, 2011 at 08:15 UTC

Congratulations on fixing the problem per your update to the root node. You have taught me something I didn't know about hash keys. In my experience, Perl respects integers, but clearly hash keys are coerced into something different. I'm a little nervous, though, about coercing by adding zero. If the key has been changed into a floating point number, you might conceivably get 0.9999 (however many) instead of 1. Excel has to have an integer as a row or column number, and if that coercion is done by int or something comparable, you might get an "out by one" error. Although we have tried row 1 and it seems to work, it remains possible that in some system you will attempt to access row or column zero and get a run-time error. I therefore prefer the approach I have used in the following code. As an aside, $row++ is more Perlish and less typing than your way, but your way works fine.

use strict;
use warnings;
use diagnostics;
use Win32::OLE;

my $xl = Win32::OLE->new('Excel.Application');
$xl->{Visible} = 1;
my $wb = $xl->Workbooks->Add();
for (2..$wb->Sheets->{Count}) {
    $wb->Sheets(2)->Delete;
}

my $worksheet = $wb->Sheets(1);
my %hash = (1 => 'One', 
            5 => 'Five',
            10=> 'Ten');
            
my $row = 0;
foreach my $key (sort keys %hash){
    $row++;
    $worksheet->Cells($row, int($key + .5))->{Value} = $hash{$key};
}
[download]

Regards,

John Davies

[reply]
[d/l]
[select]