I'll expand upon the same advice that I gave to you privately in an email: slow down. Use Data::Dumper to look at the data that you're capturing. Look at documentation to get the contents of a cell. If you're going to use code that you got from somewhere else, don't use it unless you can describe what each line does.
-----Original Message----- From: Ken Furff [mailto:frazzmata...@gmail.com] Sent: Wednesday, June 06, 2012 3:21 PM To: beginners@perl.org Subject: there has to be a way ok so I figured out that Spreadsheet::writeExcel only writes new spreadsheets and that its difficult to overwrite cells or append them in an existing spreadsheet. Is there a module out there that I can use to do what I'm trying to do? I have written a script which uses Spreadsheet::xlsx to pull data from two columns and compare the product id codes in the cells. If the product ID from Column A exists anywhere in Column D that means there is a price change. The new price is in column B. I need to copy the price to Column R. if the product ID from A doesnt exist in D, it means its a new product and it is copied to a spreadsheet that I make with WriteExcel. That works perfectly. heres my code: #!/usr/bin/perl use strict; use Spreadsheet::XLSX; use SpreadSheet::WriteExcel; use Spreadsheet::ParseExcel; use File::Copy; #copy("build.xlsx","build1.xls") or die "Copy failed: $!"; 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 my $workbook1 = Spreadsheet::WriteExcel->new('newproduct.xls'); my $worksheet1 = $workbook1->add_worksheet(); my $write_row = 1; for my $row (1..$row_max){ my $valA = $sheet->{Cells}[$row][0]->{Val}; my $valB = $sheet->{Cells}[$row][1]->{Val}; # does this value exist in Col D if (exists $colD{$valA}) { my $xlrow = $row+1; #if the above statement is true, I need to write $valB to column R. } else { #output new products to text file # ... $worksheet1->write ($write_row, 0, "$valA"); $worksheet1->write ($write_row, 1, "$valB"); $write_row++; # ... } } I don't care how this is accomplished. There are 24 columns of information that need to stay intact in the spreadsheet. I just need to rewrite column R with the new prices. someone please point me to a way to do this. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/ -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
