Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX

by poj (Priest)
on Jun 05, 2012 at 20:11 UTC ( #974542=note: print w/ replies, xml ) Need Help??


in reply to How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX

Are you sure you don't want to update R2341 ?
Your FILE2 will only ever show the last record written because it is being opened and closed inside the loop.
I think this might do what you want using Win32::OLE

use strict; use Win32::OLE; use Cwd; my $excel = Win32::OLE->new('Excel.Application') or die "Oops, cannot start Excel"; $excel->{'Visible'} =1; # open workbook my $dir = getcwd(); my $filename = $dir.'/build.xlsx'; my $book = $excel->Workbooks->Open( $filename ) or die( "Could not open $filename $!"); my $sheet = $book->Worksheets("Sheet1"); # range for Col D my $row_min = 1; my $row_max = $sheet->Range('D'.$sheet->Rows->Count)->End('-4162')->Ro +w; my %colD=(); for my $xlrow ($row_min..$row_max){ my $valD = $sheet->Range('D'.$xlrow)->{Value}; $colD{$valD} = $xlrow; # excel row number } open FILE, '>','feckyou.txt' or die $!; open FILE2,'>','newproducts.txt' or die $!; # last row in col A $row_max = $sheet->Range('A'.$sheet->Rows->Count)->End('-4162')->Row; for my $xlrow (2..$row_max){ my $valA = $sheet->Range('A'.$xlrow)->{Value}; my $valB = $sheet->Range('B'.$xlrow)->{Value}; # does this value exist in Col D if (exists $colD{$valA}) { print FILE "price change [A$xlrow]=[D$colD{$valA}] Value=$valB\n"; $sheet->Range("R".$xlrow)->{Value} = $valB; } else { print FILE2 "New Product: = $valA Price = $valB\n"; } } close FILE; close FILE2; print "Press return to end"; <STDIN>; # save and exit $book->SaveAs( $filename );
poj


Comment on Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://974542]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others avoiding work at the Monastery: (15)
As of 2015-07-02 13:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (40 votes), past polls