man Spreadsheet::ParseExcel::SaveParser (3): Rewrite an existing Excel file.

SYNOPSIS

Say we start with an Excel file that looks like this:

-----------------------------------------------------
| | A | B | C |
-----------------------------------------------------
| 1 | Hello | ... | ... | ...
| 2 | World | ... | ... | ...
| 3 | *Bold text* | ... | ... | ...
| 4 | ... | ... | ... | ...
| 5 | ... | ... | ... | ...

Then we process it with the following program:

    #!/usr/bin/perl
    use strict;
    use warnings;
    use Spreadsheet::ParseExcel;
    use Spreadsheet::ParseExcel::SaveParser;
    # Open an existing file with SaveParser
    my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
    my $template = $parser->Parse('template.xls');
    # Get the first worksheet.
    my $worksheet = $template->worksheet(0);
    my $row  = 0;
    my $col  = 0;
    # Overwrite the string in cell A1
    $worksheet->AddCell( $row, $col, 'New string' );
    # Add a new string in cell B1
    $worksheet->AddCell( $row, $col + 1, 'Newer' );
    # Add a new string in cell C1 with the format from cell A3.
    my $cell = $worksheet->get_cell( $row + 2, $col );
    my $format_number = $cell->{FormatNo};
    $worksheet->AddCell( $row, $col + 2, 'Newest', $format_number );
    # Write over the existing file or write a new file.
    $template->SaveAs('newfile.xls');

We should now have an Excel file that looks like this:

    -----------------------------------------------------
   |   |      A      |      B      |      C      |
    -----------------------------------------------------
   | 1 | New string  | Newer       | *Newest*    |  ...
   | 2 | World       | ...         | ...         |  ...
   | 3 | *Bold text* | ...         | ...         |  ...
   | 4 | ...         | ...         | ...         |  ...
   | 5 | ...         | ...         | ...         |  ...

DESCRIPTION

The "Spreadsheet::ParseExcel::SaveParser" module rewrite an existing Excel file by reading it with "Spreadsheet::ParseExcel" and rewriting it with "Spreadsheet::WriteExcel".

METHODS

Parser

new()

    $parse = new Spreadsheet::ParseExcel::SaveParser();

Constructor.

Parse()

    $workbook = $parse->Parse($sFileName);
    $workbook = $parse->Parse($sFileName , $formatter);

Returns a ``Workbook'' object. If an error occurs, returns undef.

The optional $formatter is a Formatter Class to format the value of cells.

Workbook

The "Parse()" method returns a "Spreadsheet::ParseExcel::SaveParser::Workbook" object.

This is a subclass of the Spreadsheet::ParseExcel::Workbook and has the following methods:

worksheets()

Returns an array of ``Worksheet'' objects. This was most commonly used to iterate over the worksheets in a workbook:

    for my $worksheet ( $workbook->worksheets() ) {
        ...
    }

worksheet()

The "worksheet()" method returns a single "Worksheet" object using either its name or index:

    $worksheet = $workbook->worksheet('Sheet1');
    $worksheet = $workbook->worksheet(0);

Returns "undef" if the sheet name or index doesn't exist.

AddWorksheet()

    $workbook = $workbook->AddWorksheet($name, %properties);

Create a new Worksheet object of type "Spreadsheet::ParseExcel::Worksheet".

The %properties hash contains the properties of new Worksheet.

AddFont

    $workbook = $workbook->AddFont(%properties);

Create new Font object of type "Spreadsheet::ParseExcel::Font".

The %properties hash contains the properties of new Font.

AddFormat

    $workbook = $workbook->AddFormat(%properties);

The %properties hash contains the properties of new Font.

Worksheet

Spreadsheet::ParseExcel::SaveParser::Worksheet

Worksheet is a subclass of Spreadsheet::ParseExcel::Worksheet. And has these methods :

The "Worksbook::worksheet()" method returns a "Spreadsheet::ParseExcel::SaveParser::Worksheet" object.

This is a subclass of the Spreadsheet::ParseExcel::Worksheet and has the following methods:

AddCell

    $workbook = $worksheet->AddCell($row, $col, $value, $format [$encoding]);

Create new Cell object of type "Spreadsheet::ParseExcel::Cell".

The $format parameter is the format number rather than a full format object.

To specify just same as another cell, you can set it like below:

    $row            = 0;
    $col            = 0;
    $worksheet      = $template->worksheet(0);
    $cell           = $worksheet->get_cell( $row, $col );
    $format_number  = $cell->{FormatNo};
    $worksheet->AddCell($row +1, $coll, 'New data', $format_number);

TODO

Please note that this module is currently (versions 0.50-0.60) undergoing a major restructuring and rewriting.

Known Problems

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, formulas aren't rewritten, only the result of a formula is written.

Only last print area will remain. (Others will be removed)

AUTHOR

Current maintainer 0.60+: Douglas Wilson [email protected]

Maintainer 0.40-0.59: John McNamara [email protected]

Maintainer 0.27-0.33: Gabor Szabo [email protected]

Original author: Kawai Takanori [email protected]

COPYRIGHT

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.