apparently win32::OLE can be used to write to an existing spreadsheet. I have
read the cpan docs and tried a hundred things, but I cant seem to get the
syntax right.
I'm going to post this script one more time.
#!/usr/bin/perl
use strict;
use Spreadsheet::XLSX;
use File::Copy;
use Win32::OLE;
#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 $workbook = Spreadsheet::WriteExcel->new('newproduct.xls');
my $worksheet = $workbook->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;
Right here can someone show me how to write $valB into Column R on the row
where the match was made.
} else {
#output new products to text file
# ...
$worksheet->write ($write_row, 0, "$valA");
$write_row++;
# ...
}
}
the script is kind of simple. All it does is take a value from Column A of a
spreadsheet and look for it in Column D. If it finds a match that means there
was a price change. The price is in Column B and is stored in the script in
$valB. if the price is changed, as indicated by the match, then the price needs
to be copied to Column R (17), I am told that Win32::OLE can do this. but I
have never used it, I read through the docs but sometimes the examples in cpan
leave me feeling like I know less than when I went in.
Can win32::OLE write to an existing spreadsheet? if it can, can someone give me
an example that will help me understand the syntax that does this.
Thank you
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/
