MBH MBH - 3 months ago 6
Perl Question

Why is this array only printing the last number?

My first time working with Perl. I'm using it to take data from multiple cells from one Excel file and put them in another, existing Excel file.

I've managed to extract the data I need from the first file and put it into an @array. I started a new file to experiment with writing the data into the specific cells I need.

The problem is that when the script runs it has the same number in all cells, 18365. While the @rows arrays is working correctly and putting the number in the correct cell, the @revenue array only prints the last number.

Is there something I am overlooking or not understanding? Is there a better way to do this? I thank you in advance.

use warnings;
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;

my $parser = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook = $parser->Parse('xls_test.xls');

if (!defined $workbook ) {
die $parser->error(), ".\n";
}

my $worksheet = $workbook->worksheet(0);

my @rows = (2, 10, 17);
my @revenue = (10200, 9025, 18365);

my $col = 2;

foreach my $rev (@revenue) {
foreach my $r (@rows) {
$worksheet->AddCell( $r, $col, "$rev" );
}
}

$workbook->SaveAs('xls_test.xls');

Answer

If you take your loop:

foreach my $rev (@revenue) {
  foreach my $r (@rows) {
    $worksheet->AddCell( $r, $col, "$rev" );
  }
}

The last $rev to write is 18365, and you will overwrite the previous values in all 3 rows.

What you can do is creating a row-to-revenue hash from both your lists and traverse it:

my @rows = (2, 10, 17);
my @revenue = (10200, 9025, 18365);
my $col = 2;

my %data;
for my $i (0 .. $#rows) {
    $data{$rows[$i]} = $revenue[$i]; # row => revenue
}

foreach $row (keys %data) {
    $worksheet->AddCell($row, $col, $data{$row});
}
Comments