FreeKB - Append or replace data in an Excel spreadsheet in Perl (Spreadsheet::ParseExcel)
Append or replace data in an Excel spreadsheet in Perl (Spreadsheet::ParseExcel)

Home > Search


Install the Spreadsheet::ParseExcel module. Import the Spreadsheet::ParseExcel and Spreadsheet::ParseExcel::SaveParser modules into your Perl script.

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

 

These two lines are used to define the spreadsheet that contains the data (orignal.xls in this example).

my $parser   = new Spreadsheet::ParseExcel::SaveParser;
my $original = $parser->Parse('original.xls');

 

These variables are used so that you are starting at cell A1 in the first sheet in the spreadsheet.

my $sheet = 0;
my $row   = 0;
my $col   = 0;

 

This gets the format of the data in the spreadsheet (bold italic underline font et cetera) so that you do not lose the format of the spreadsheet.

my $format   = $template->{Worksheet}[$sheet]
                        ->{Cells}[$row][$col]
                        ->{FormatNo};

 

This replaces certain cells with the text "Hello World".

# Updates row 0 column 0 (cell A1) with the text Hello World
$original->AddCell(0, 0, 0, 'Hello World', $format);

# Updates row 0 column 1 (cell B1) with the text Hello World
$original->AddCell(0, 0, 1, 'Hello World', $format);

# Updates row 1 column 0 (cell A2) with the text Hello World
$original->AddCell(0, 1, 0, 'Hello World', $format);

 

This creates a new spreadsheet, most appropriately named new.xls.

my $workbook;

{
  local $^W = 0;
  $workbook = $template->SaveAs('new.xls');
}

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter 913dc in the box below so that we can be sure you are a human.




Comments