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

trickyq has asked for the wisdom of the Perl Monks concerning the following question:

I have one final question concerning spreadsheet::xlsx

I have been writing a price list updater and with help and alot of reading I have a pretty badass little script. Thank you to all

my next question should be reasonably simple. Of Course its the things that seem the most simple that I seem to struggle with.

Heres the script so you can see what I am talking about

#!/usr/bin/perl
use strict;
use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX -> new ('build.xlsx');
my $sheet = $excel->Worksheet('Sheet1');
my ($row_min,$row_max) = $sheet->row_range();


my %colD=();
for my $row ($row_min..$row_max){
  my $valD = $sheet->{Cells}[$row][3]->{Val}; 
  $colD{$valD} = $row+1; # excel row number
}


open FILE,'>','feckyou.txt' or die $!;
for my $row (1..$row_max){
  my $valA = $sheet->{Cells}[$row][0]->{Val}; 
  # does this value exist in Col D
  if (exists $colD{$valA}) {
    my $valB = $sheet->{Cells}[$row][1]->{Val};
    my $xlrow = $row+1;
    print FILE "price change [A$xlrow]=[D$colD{$valA}] Value=$valB\n";
    
  }

  elsif (!exists $colD{$valA})  {
  open FILE2, '>','newproducts.txt' or die $!;
    my $valB = $sheet->{Cells}[$row][1]->{Val};
    print FILE2 "New Product: = $valA Price = $valB\n";
    close FILE2;
   }
  }
[download]

In the if statement above it takes any instance of a new product id in colA and looks for it in colD. if it finds it anywhere in ColD it puts it in a text file. I need it to take the price it finds in ColB, which is also currently printing to the text file. I need to print that value (the price in colB) to Column R in the corresponding row with column A.

So, if Column A6 has a product code of 12345 and the script finds a match all the way on Column D2341, it will write the price from Column B6 to Column R6.

I cannot find the syntax for writing back to an Excel spreadsheet using Spreadheet::xlsx

thank you

ken

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

Replies are listed 'Best First'.
Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by dasgar (Priest) on Jun 05, 2012 at 16:09 UTC
As others have pointed out, the documentation for Spreadsheet::XLSX clearly states that it's for reading only. I believe that you'll need to use Spreadsheet::XLSX to read in the spreadsheet and then use Excel::Writer::XLSX to write back to a file. Also, the "WRITING_EXCEL_FILES" section of Excel::Writer::XLSX's documentation lists some other alternatives "methods of getting data into Excel".	[reply]
Re^2: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by trickyq (Acolyte) on Jun 05, 2012 at 16:25 UTC
misunderstood and see the error in my ways I will look at and read the documentation for the modules you have pointed me to. Thank you	[reply]
Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by Lotus1 (Vicar) on Jun 05, 2012 at 13:50 UTC
From the cpan documentation for Spreadsheet::XLSX: Spreadsheet::XLSX - Perl extension for reading MS Excel 2007 files;	[reply]
Re^2: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by trickyq (Acolyte) on Jun 05, 2012 at 15:31 UTC
what is? I dont see it in the cpan docs,look there first always. That doc makes no sense at all to me. I do not see how I can write back to the spreadsheet anywhere.	[reply]
Re^3: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by Your Mother (Archbishop) on Jun 05, 2012 at 15:54 UTC
As the previous reply suggests, you cannot. The first real line in the Pod of Spreadsheet::XLSX is, "Perl extension for reading MS Excel 2007 files."	[reply]
Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by ~~David~~ (Hermit) on Jun 05, 2012 at 16:03 UTC
I have run into the same problem you face many times. I don't know if this is the best way, but I normally use Spreadsheet::XLSX for reading the file, but then I go back to open the file with Win32::OLE to update the fields that I need to change. It is a two-step process.	[reply]
Re: How Do I Write an Excel Spreadsheet using Spreadsheet::XLSX by poj (Abbot) on Jun 05, 2012 at 20:11 UTC
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 ); [download] poj	[reply] [d/l]