Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
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 chanting in the Monastery: (5)
As of 2014-08-23 05:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (172 votes), past polls