Perl: Parse Excel with Search and Replace

Question

I'm building a PERL script to search and replace the EXCEL cell A1. There is an error in my code, it will find "Apples" in the input file but will NOT write replace "Apples" with "Peaches" in the output file.

I have an XLS EXCEL INPUT file (file_in.xls) with the contents displayed below:

A---------------B
-----------------------
1 Apples  | Carrots
-----------------------
2 Oranges | Spinach
-----------------------
3 Grapes  | Celery 
-----------------------

I need to perform search/replace for Apples to Peaches in the cell (A1) for the EXCEL output file (file_out.xls):

A----------------B
-----------------------
1 Peaches | Carrots
-----------------------
2 Oranges | Spinach
-----------------------
3 Grapes  | Celery 
-----------------------

Here is the Perl code:

 use v5.10.0;
 use warnings;

 use Spreadsheet::ParseExcel;
 use Spreadsheet::ParseExcel::SaveParser;
 use Spreadsheet::WriteExcel;

 my $parser = Spreadsheet::ParseExcel::SaveParser->new();
 my $file1 = $parser->Parse("C:/Perl/scripts/file_in.xls");
 my $workbook_R = $parser->parse('file_in.xls');

 my $workbook_W = Spreadsheet::WriteExcel->new('C:\Perl\scripts\file_out.xls');
 my $worksheet_W = $workbook_W->add_worksheet();

 my $sheet = ${ $file1->{Worksheet_R} }[0];

 for my $worksheet_R ( $workbook_R->worksheets() ) {

 my ( $row_min, $row_max ) = $worksheet_R->row_range();
 my ( $col_min, $col_max ) = $worksheet_R->col_range();

for my $row ( $row_min .. $row_max ) {
for my $col ( $col_min .. $col_max ) {

        my $cell = $worksheet_R->get_cell( $row, $col );
        next unless $cell;

        if($cell->value() =~ /Apples/) {
        $worksheet_R->write($cell,"Oranges");}
      }
    }
   }

Answer 1

There are a couple of problems with your code:

1. You're not writing to the output file, $worksheet_W

2. You're not writing out the contents of the cells that don't have Apples in them.

   for my $row ( $row_min .. $row_max ) {
     for my $col ( $col_min .. $col_max ) {
   
       my $cell = $worksheet_R->get_cell( $row, $col );
       // if the cell contains Apples, write 'Peaches' instead
       if($cell->value() =~ /Apples/) {
         $worksheet_W->write($row, $col,"Peaches");
       }
       else {
         // print the existing cell contents
         $worksheet_W->write($row, $col, $cell);
       }
     }
   }