Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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

by poj (Monsignor)
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 );

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://974542]
and not a whimper to be heard...

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (6)
As of 2018-06-18 21:45 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (110 votes). Check out past polls.