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

Ok Im using the WriteExcel module now. Works fine. I'm just trying to figure out how to populate a whole column. im basically guessing at the syntax here, from what I saw in some examples in cpan. the write stuff is in the last section, the elsif at the bottom

#!/usr/bin/perl
use strict;
use Spreadsheet::XLSX;
use SpreadSheet::WriteExcel;

my $excel = Spreadsheet::XLSX -> new ('build.xlsx');
my $sheet = $excel->Worksheet('Sheet1');
my ($row_min,$row_max) = $sheet->row_range();

# scan col D and store values
my %colD=();
for my $row ($row_min..$row_max){
  my $valD = $sheet->{Cells}[$row][3]->{Val}; 
  $colD{$valD} = $row+1; # excel row number
}

# scan col A starting at row 2
open FILE,'>','feckyou.txt' or die $!;
for my $row (1..$row_max){
  my $valA = $sheet->{Cells}[$row][0]->{Val}; 
  # does this value exist in Col D
  if (exists $colD{$valA}) {
    my $valB = $sheet->{Cells}[$row][1]->{Val};
    my $xlrow = $row+1;
    print FILE "price change [A$xlrow]=[D$colD{$valA}] Value=$valB\n";
    
  }
#output new products to text file
  elsif (!exists $colD{$valA})  {
  my $workbook = Spreadsheet::WriteExcel->new('newproduct.xls');
  my $worksheet = $workbook->add_worksheet(); 
 # open FILE2, '>','newproducts.txt' or die $!;
    my $valB = $sheet->{Cells}[$row][1]->{Val};
#    print FILE2 "New Product: = $valA Price = $valB\n";
#    close FILE2;
    $worksheet->write (0, $row+1, "$valA");
    $worksheet->write (1, $row+1, "$valB");
   }
  }

[download]

Im trying to write the results from $valA into column 0, row 0 thru however many new products there are. And the same with $valB which is the prices of the new products. It creates the excel file, but its empty. Once again some fundamental part of this is going by me.

please help

thanks

Replies are listed 'Best First'.
Re: spreadsheet::WriteExcel and whos on first?
by runrig (Abbot) on Jun 05, 2012 at 18:43 UTC
    Just doing this:
      my $workbook = Spreadsheet::WriteExcel->new('newproduct.xls');
  my $worksheet = $workbook->add_worksheet();
 # open FILE2, '>','newproducts.txt' or die $!;
    my $valB = $sheet->{Cells}[$row][1]->{Val};
#    print FILE2 "New Product: = $valA Price = $valB\n";
#    close FILE2;
    $worksheet->write (0, $row+1, "valA");
    $worksheet->write (1, $row+1, "valB");

    [download]
    gives me a non-empty spreadsheet, but I don't think even that is close to what you want. You are overwriting the spreadsheet on every iteration of your loop, and your row variable is in the column argument of the write() method. First maybe you want to create the spreadsheet outside of the loop?
[reply]
[d/l]
Re: spreadsheet::WriteExcel and whos on first?
by roboticus (Chancellor) on Jun 05, 2012 at 20:24 UTC

    trickyq:

    You might try a trivial example, and build upon it. Sometimes I find that helpful. For example, here's a quickie that will generate a simple spreadsheet:

    #!/usr/bin/perl
use 5.14.0;
use warnings;
use Spreadsheet::WriteExcel;

my $WB = Spreadsheet::WriteExcel->new("FOO.xls");
my $WS = $WB->add_worksheet("FRED");

while (<DATA>) {
    my ($row, $col, $val) = split /,\s*/, $_;
    last if ! defined $col;
    $WS->write($row, $col, $val);
}

__DATA__
0, 0, Cell A1
3, 3, Cell D4
1, 1, apple
1, 2, banana

    [download]

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
Re: spreadsheet::WriteExcel and whos on first?
by ww (Archbishop) on Jun 05, 2012 at 21:13 UTC
    Did you read perldoc Spreadsheet::WriteExcel, the extensive and informative docs?
[reply]
[d/l]

Back to Seekers of Perl Wisdom