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

So I have a hash that is loaded w/ values. The keys are numerical values like 1, 2, 3, etc... The values are just names. The keys correspond to columns #'s and the value is what will go into the cell. But when I run the code I get the error "Can't Use an undefined value as a HASH reference" related to the code: $worksheet->Cells($current_row, $key)->{Value}=$hash{$key}; So in the loop it prints the key correctly and prints the value correctly, but then bomb ats the next line. If I remove the $key value from the excel call and replace it with the number that $key printed it runs fine (of course the keys are not the same so this doesn't work, but tell me $key is the problem). So what can I do to fix this? I just find this very strange. I can print $key and it prints the correct numerical value, but when I try to use $key to write to excel I get an error? So how do I get the excel call to take or recognize my $key value? Not sure if this is something I'm doing incorrectly w/ the HASH key/value or if there is some way I can modify the hash key so its just a normal value that I can put into the excel write call. Also, the $current_row is ok, its auto incremented in another part of the code and I print that out and its ok. I know its something to do w/ the $key value, just don't know what or where to start. 
$worksheet = $workbook->Worksheets("Worksheet");
foreach my $key (sort keys %hash){
    print "Key: $key\nValue: $hash{$key}\n";
    $worksheet->Cells($current_row, $key)->{Value} = $hash{$key};
}

[download]
SOLUTION: Adding a new variable and setting that variable equal to $key + 0.... 
$worksheet = $workbook->Worksheets("Worksheet");
foreach my $key (sort keys %hash){
    print "Key: $key\nValue: $hash{$key}\n";
    $column = $key + 0;
    $worksheet->Cells($current_row, $column)->{Value} = $hash{$key};
}

[download]
  • Comment on Can't Use an undefined value as a HASH reference when passing HASH key into Excel OLE call.
  • Select or Download Code

Replies are listed 'Best First'.
Re: Can't use an undefined value as a hash reference when passing hash key into Excel OLE call.
by davies (Monsignor) on Nov 10, 2011 at 23:07 UTC

    I can't reproduce your problem. The following code runs and does what I think you say you ought to want, with a few minor tweaks, such as making the key correspond to the row, which makes things slightly easier for me. If you can show code that runs from scratch and demonstrates your problem, I might be able to do better.

    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');
foreach my $key (sort keys %hash){
    print "Key: $key\nValue: $hash{$key}\n";
    $worksheet->Cells($key, 1)->{Value} = $hash{$key};
}

    [download]

    Regards,

    John Davies

[reply]
[d/l]
      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]
[reply]
[d/l]

        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]
Re: Can't Use an undefined value as a HASH reference when passing HASH key into Excel OLE call.
by davies (Monsignor) on Nov 13, 2011 at 09:30 UTC

    A quick test proves that hash keys are strings, so your trick of adding zero is safe and you don't need my int nonsense. But there is a different trap waiting for you, as you are sorting the keys. The keys are strings and therefore will be sorted in ASCIIbetical order, not numeric order. This trap is waiting for me in some of my code.

    use strict;
use warnings;
use diagnostics;
my %hash = (1   => 'One',
            2   => 'Two',
            11  => 'Eleven',
            100 => 'Hundred');
            
for my $key (sort keys %hash){
    print $key . " " . $hash{$key} . "\n";
}

    [download]

    Regards,

    John Davies

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom