Need more examples on how to use Spreadsheet::ParseExcel

Question

I have been using the Spreadsheet::ParseExcel to list the contents of spreadsheet. I've seen several examples on how to dump the entire spreadsheet. I really would like to see how to use this script more selectively.

The example below from IBM basically dumps the content of all cells that have data.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);
print "FILE  :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";

print "AUTHOR:", $oBook->{Author} , "\n"
 if defined $oBook->{Author};

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "--------- SHEET:", $oWkS->{Name}, "\n";
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);
  }
 }
}

Can someone give me an example of how I can specify some action to take for a certain column for every row?

For example, I have a spreadsheet with 7 columns and n rows. I want to reformat the data in each of the columns in a certain way. Perhaps I want take column 6 for every row and append some text to the end of the string stored in the cell. How would that be set up?

Solution 1

To modify an Excel file, the Spreadsheet::ParseExcel::SaveParser module is very useful. It allows you to read a file, make modifications, and then write the altered content to a new file. Here's a simple example:

use strict;
use warnings;

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

my $excel_file_name = $ARGV[0];
my $parser          = Spreadsheet::ParseExcel::SaveParser->new();
my $workbook_orig   = $parser->Parse($excel_file_name);

# We will edit column 7 of the first worksheet.
my $worksheet = $workbook_orig->worksheet(0);
my $EDIT_COL = 6;

my ($row_min, $row_max) = $worksheet->row_range();
for my $r ($row_min .. $row_max){
    my $cell = $worksheet->get_cell($r, $EDIT_COL);
    unless (defined $cell){
        next; # Modify as needed to handle blank cells.
    }
    my $val = $cell->value . '_append_text';
    $worksheet->AddCell( $r, $EDIT_COL, $val, $cell->{FormatNo} );
}

# You can save the modifications to the same file, but when
# you are learning, it's safer to write to a different file.
$excel_file_name =~ s/\.xls$/_new.xls/;
$workbook_orig->SaveAs($excel_file_name);

For many other examples, see the excellent documentation:

• Spreadsheet::ParseExcel::SaveParser
• Spreadsheet::WriteExcel
• Spreadsheet::WriteExcel::Examples
• Spreadsheet::ParseExcel

Solution 2

To reformat the data in each of the columns in a certain way, you can use the combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules like

Create a new excel by Spreadsheet::WriteExcel module and add worksheet in it.Then Parse the existing Excel and write the content into new excel with formating.

Here is a sample example:

use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);
my $workbook  = Spreadsheet::WriteExcel->new('/root/Desktop/test_simple.xls');
my $worksheet = $workbook->add_worksheet();
$worksheet->set_column('A:A', 50);
my $format = $workbook->add_format();
            $format->set_size(10);
            $format->set_bold();
            $format->set_color('black');
            $format->set_font('Verdana');
            $format->set_text_wrap();
for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
        {
                    $oWkS = $oBook->{Worksheet}[$iSheet];
            for(my $iR = $oWkS->{MinRow} ;defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;$iR++)
            {
                for(my $iC = $oWkS->{MinCol} ;defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;$iC++)
                {
                   $oWkC = $oWkS->{Cells}[$iR][$iC];
                   $worksheet->write($iR , $iC,  $oWkC->Value, $format) if($oWkC); #writing a new excel from existing excel
                }
            }
        }

After writing a new excel from the existing excel, you can use API's of Spreadsheet::WriteExcel to append data in it.It will solve problems like

1. First it will retain your existing Excel file.
2. you can do formating into new excel file without modifying the original Excel File.