Spreadsheet::ParseExcel::SaveParser(3pm)

Usage

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 | ...         | ...         | ...         |  ...

Overview

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)

Releases

12 releases since Debian 3.0 (Woody); the text changed 9 times.