FAQ: How do I update an existing Excel file

[jmcnamara]

Q. How do I update an existing Excel file

A. An Excel file is a binary file within a binary file. It contains
several interlinked checksums and changing even one byte can cause it
to become corrupted.

As such you cannot simply append or update an Excel file. The only way
to achieve this is to read the entire file into memory, make the
required changes or additions and write the file out again.

You can read and rewrite an Excel file using the
Spreadsheet::ParseExcel::SaveParser module which is a wrapper around
Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. It is part of the
Spreadsheet::ParseExcel package:

http://search.cpan.org/search?dist=Spreadsheet-ParseExcel

However, you can only rewrite the features that Spreadsheet::WriteExcel
supports so macros, graphs and some other features in the original
Excel file will be lost. Also, currently, formulas aren't copied.

Here is an example:

#!/usr/bin/perl -w

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

# Open the template with SaveParser
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('template.xls');

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

# Get the format from the cell
my $format = $template->{Worksheet}[$sheet]
->{Cells}[$row][$col]
->{FormatNo};

# Write data to some cells
$template->AddCell(0, $row, $col, 1, $format);
$template->AddCell(0, $row+1, $col, "Hello", $format);

# Add a new worksheet
$template->AddWorksheet('New Data');

# The SaveParser SaveAs() method returns a reference to a
# Spreadsheet::WriteExcel object. If you wish you can then
# use this to access any of the methods that aren't
# available from the SaveParser object. If you don't need
# to do this just use SaveAs().
#
my $workbook;

{
# SaveAs generates a lot of harmless warnings about unset
# Worksheet properties. You can ignore them if you wish.
local $^W = 0;

# Rewrite the file or save as a new file
$workbook = $template->SaveAs('new.xls');
}

# Use Spreadsheet::WriteExcel methods
my $worksheet = $workbook->sheets(0);

$worksheet->write($row+2, $col, "World2");

$workbook->close();

__END__

John.
--