struggling with spreadsheet::WriteExcel and writing columns

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

here's the whole script so you can see what it needs to accomplish.

I need it to write that data from the ifelse portion of the script to the new spreadsheet without skipping rows. I was given a few ideas, none of which were ideal. because I didn't enter the whole script, so you guys couldn't see how it starts and where the data comes from. So here it is.

#!/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 $!;
  my $workbook1 = Spreadsheet::WriteExcel->new('newproduct.xls');
  my $worksheet1 = $workbook1->add_worksheet(); 
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})  {
  
 # 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 ($row+1, 0, "$valA");
    $worksheet->write ($row+1, 1, "$valB");
   }
}
[download]

I know that the variables at the end are storing blank spaces as it goes through all the data. somehow I have to make new variables to store the data that doesn't make a match. that means its a new product code. The product code and the price need to go into the new spreadsheet in the order they are found with no skipped lines. Ive been writing stuff non-stop, I'm so close. I could load the codes and prices that aren't matched into an array and then output them to the spreadsheet in order as suggested by someone, but when I do that, I get syntax errors. I've been at it all day.

Im dyin ova he'a

ken

Comment on struggling with spreadsheet::WriteExcel and writing columns Download Code

Replies are listed 'Best First'.
Re: beating a horse thats not quite dead by toolic (Bishop) on Jun 05, 2012 at 23:55 UTC
How do I compose an effective node title?	[reply]
Re: struggling with spreadsheet::WriteExcel and writing columns by kcott (Archbishop) on Jun 06, 2012 at 06:19 UTC
Your core problem is here: `if (exists $colD{$valA}) { ... elsif (! exists $colD{$valA}) { ... }` [download] Clearly, if `exists $colD{$valA}` is false, then its negation must be true: `if (exists $colD{$valA}) { ... else { ... }` [download] However, as you never set `$colD{$valA}`, the condition will always be false and the `else` block will only ever be entered. There are lots of other issues with this code. Here's a non-exhaustive list in no particular order. Let Perl tell you when you've done something wrong Add `use warnings;` - right after `use strict;` is a good place. If you're having trouble understanding the messages, add `use diagnostics;` for more verbose messages. Relevant documentation: warnings and diagnostics. `... use strict; use warnings; use diagnostics; ...` [download] Lay out your code in a consistent fashion Your indentations appear almost totally arbitrary and your use of whitespace seems quite erratic. This makes reading your code difficult but it doesn't need to be. Please read: perlstyle. Use more meaningful variable names There's a number of places where some sort of naming convention would have helped you. For instance, you use three variables for worksheet objects: `$sheet`, `$worksheet1` and `$worksheet`. Except where you declare and assign these, they don't convey a lot of meaning. Perhaps `$sheet_in` and `$sheet_out` would have been more informative. And yes, that is two new names for three existing variables: another bug in your code that I just found! Use lexical variables for filehandles Instead of `open FILE ...`, use something like `open my $whatever_fh ...`. See open for a discussion of this. Avoid pointless repetition of code You have the identical statement `my $valA = $sheet->{Cells}[$row][0]->{Val};` in both blocks of your `if-else` code. This would be better as: `my $valA = $sheet->{Cells}[$row][0]->{Val}; if (exists $colD{$valA}) { ...` [download] That should be enough for you to get your code working. If you need to ask further questions, please first read: How do I post a question effectively? -- Ken	[reply] [d/l] [select]
Re: beating a horse thats not quite dead by thomas895 (Deacon) on Jun 06, 2012 at 00:02 UTC
To add on to this: ~~Would you please stop with the strange node titles? They're not funny anymore, and only confusing. Also,~~ your node contains no question, and therefore does not belong in this section. ~Thomas~ `confess( "I offer no guarantees on my code." );`	[reply] [d/l]
Re^2: beating a horse thats not quite dead by trickyq (Acolyte) on Jun 06, 2012 at 00:09 UTC
i hear and i obey	[reply]