I have written a script for updating and modifying a price list in Excel. First
let me show you the script:
#!/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();
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;
print FILE "price change [A$xlrow]=[D$colD{$valA}] Value=$valB\n";
} else {
#output new products to text file
# ...
$worksheet1->write ($write_row, 0, "$valA");
$worksheet1->write ($write_row, 1, "$valB");
$write_row++;
# ...
}
}
SO this works. Except in the if section above where it writes to a file, I
actually need it to write to column R (17) in the same spreadsheet the data is
coming from. I just need that $valB to be written to column R if the condition
of the if is met. Every time I try to just declare it and then write to it
using
$worksheet->write ($row+1, 1, "$valB");
it gives me an error to the effect that its not a declared object.
Even though I clearly declare it.
I think its something to do with reading it in SPreadsheet::xlsx and then
trying to write to the same sheet using Spreadsheet::WriteExcel
but i ho hnestly have no idea why
can someone help me?
--
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
http://learn.perl.org/