On Jun 6, 2012, at 8:52 AM, Ken Furff wrote:
> 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?
There is no variable $worksheet in your program. There is $sheet and
$worksheet1. Which one do you mean?
The module Spreadsheet::XLSX only reads spreadsheet files (in the Excel 2007
format); it does not allow you to add information to an existing spreadsheet.
There are no "write" methods defined in that module.
If you really want to add data to an existing spreadsheet, you will have to
copy everything from the existing spreadsheet to a new spreadsheet, then add
whatever additional fields you want.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
