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: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
