++ed by:
WKI MJEMMESON SZABGAB HMBRAND TOMITA

12 PAUSE users
23 non-PAUSE users.

John McNamara
and 1 contributors

NAME

Spreadsheet::ParseExcel - Read information from an Excel file.

SYNOPSIS

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

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

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

        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();

        for my $row ( $row_min .. $row_max ) {
            for my $col ( $col_min .. $col_max ) {

                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;

                print "Row, Col    = ($row, $col)\n";
                print "Value       = ", $cell->value(),       "\n";
                print "Unformatted = ", $cell->unformatted(), "\n";
                print "\n";
            }
        }
    }

DESCRIPTION

The Spreadsheet::ParseExcel module can be used to read information from Excel 95-2003 binary files.

The module cannot read files in the Excel 2007 Open XML XLSX format. See the Spreadsheet::XLSX module instead.

Parser

new()

The new() method is used to create a new Spreadsheet::ParseExcel parser object.

    my $parser = Spreadsheet::ParseExcel->new();

As an advanced feature it is also possible to pass a call-back handler to the parser to control the parsing of the spreadsheet.

    $parser = Spreadsheet::ParseExcel->new(
                        [
                          CellHandler => \&cell_handler,
                          NotSetCell  => 1,
                        ]);

The call-back can be used to ignore certain cells or to reduce memory usage. See the section "Reducing the memory usage of Spreadsheet::ParseExcel" for more information.

Parse($filename, [$formatter])

The Parser Parse() method return a "Workbook" object.

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

If an error occurs Parse() returns undef.

The $filename parameter is generally the file to be parsed. However, it can also be a filehandle or a scalar reference.

The optional $formatter array ref can be an reference to a "Formatter Class" to format the value of cells.

Note: Versions of Spreadsheet::ParseExcel prior to 0.50 also documented a Workbook Parse() method as a syntactic shortcut for the above new() and Parse() combination. This is now deprecated since it breaks error handling.

ColorIdxToRGB()

The ColorIdxToRGB() method returns a RGB string corresponding to a specified color index. The RGB string has 6 characters, representing the RGB hex value, for example 'FF0000'. The color index is generally obtained from a FONT object.

    $RGB = $parser->ColorIdxToRGB($color_index);

Workbook

A Spreadsheet::ParseExcel::Workbook is created via the Spreadsheet::ParseExcel Parse() method:

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

The Workbook class has methods and properties that are outlined in the following sections.

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

Workbook Properties

A workbook object exposes a number of properties as shown below:

    $workbook->{Worksheet }->[$index]
    $workbook->{File}
    $workbook->{Author}
    $workbook->{Flg1904}
    $workbook->{Version}
    $workbook->{SheetCount}
    $workbook->{PrintArea }->[$index]
    $workbook->{PrintTitle}->[$index]

These properties are generally only of interest to advanced users. Casual users can skip this section.

$workbook->{Worksheet}->[$index]

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

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

It is now deprecated, use worksheets()) instead.

$workbook->{File}

Returns the name of the Excel file.

$workbook->{Author}

Returns the author of the Excel file.

$workbook->{Flg1904}

Returns true if the Excel file is using the 1904 date epoch instead of the 1900 epoch. The Windows version of Excel generally uses the 1900 epoch while the Mac version of Excel generally uses the 1904 epoch.

$workbook->{Version}

Returns the version of the Excel file.

$workbook->{SheetCount}

Returns the numbers of "Worksheet" objects in the Workbook.

$workbook->{PrintArea}->[$index]

Returns an array ref of print areas. Each print area is as follows:

    [ $start_row, $start_col, $end_row, $end_col]

$workbook->{PrintTitle}->[$index]

Returns an array ref of print title hash refs. Each print title is as follows:

    {
        Row    => [$start_row, $end_row],
        Column => [$start_col, $end_col]
    }

Worksheet

The Spreadsheet::ParseExcel::Worksheet class encapsulates the properties of an Excel worksheet. It has the following methods:

    # Commonly used methods.
    $worksheet->get_cell()
    $worksheet->row_range()
    $worksheet->col_range()
    $worksheet->get_name()

    # Infrequently used methods.
    $worksheet->get_h_pagebreaks()
    $worksheet->get_v_pagebreaks()
    $worksheet->get_merged_areas()
    $worksheet->get_row_heights()
    $worksheet->get_col_widths()
    $worksheet->get_default_row_height()
    $worksheet->get_default_col_width()
    $worksheet->get_header()
    $worksheet->get_footer()
    $worksheet->get_margin_left()
    $worksheet->get_margin_right()
    $worksheet->get_margin_top()
    $worksheet->get_margin_bottom()
    $worksheet->get_margin_header()
    $worksheet->get_margin_footer()
    $worksheet->get_paper()
    $worksheet->get_start_page()
    $worksheet->get_print_order()
    $worksheet->get_print_scale()
    $worksheet->get_fit_to_pages()
    $worksheet->is_portrait()
    $worksheet->is_centered_horizontally()
    $worksheet->is_centered_vertically()
    $worksheet->is_print_gridlines()
    $worksheet->is_print_row_col_headers()
    $worksheet->is_print_black_and_white()
    $worksheet->is_print_draft()
    $worksheet->is_print_comments()

The Spreadsheet::ParseExcel::Worksheet class exposes a lot of methods but in general very few are required unless you are writing an advanced filter.

The most commonly used methods are shown below. The others are documented in Spreadsheet::ParseExcel::Worksheet.

get_cell($row, $col)

Return the "Cell" object at row $row and column $col if it is defined. Otherwise returns undef.

    my $cell = $worksheet->get_cell($row, $col);

row_range()

Returns a two-element list ($min, $max) containing the minimum and maximum defined rows in the worksheet. If there is no row defined $max is smaller than $min.

    my ( $row_min, $row_max ) = $worksheet->row_range();

col_range()

Returns a two-element list ($min, $max) containing the minimum and maximum of defined columns in the worksheet. If there is no column defined $max is smaller than $min.

    my ( $col_min, $col_max ) = $worksheet->col_range();

get_name()

The get_name() method returns the name of the worksheet, such as 'Sheet1'.

    my $name = $worksheet->get_name();

Other Worksheet methods

For other, less commonly used, Worksheet methods see Spreadsheet::ParseExcel::Worksheet.

Cell

The Spreadsheet::ParseExcel::Cell class has the following methods and properties.

Cell methods

value()

Formatted value of the cell.

unformatted()

Unformatted value of the cell.

Cell properties

    $cell->{Val}
    $cell->{Type}
    $cell->{Code}
    $cell->{Format}
    $cell->{Merged}
    $cell->{Rich}

$cell->{Val}

Returns the unformatted value of the cell. This is Deprecated, use $cell->unformatted() instead.

$cell->{Type}

Returns the type of cell such as Text, Numeric or Date.

If the type was detected as Numeric, and the Cell Format matches m{^[dmy][-\\/dmy]*$}, it will be treated as a Date type.

$cell->{Code}

Returns the character encoding of the cell. It is either undef, ucs2 or _native_.

If undef then the character encoding seems to be ascii.

If _native_ it means that cell seems to be 'sjis' or something similar.

$cell->{Format}

Returns the "Format" object for the cell.

$cell->{Merged}

Returns true if the cell is merged.

$cell->{Rich}

Returns an array ref of font information about each string block in a "rich", i.e. multi-format, string. Each entry has the form:

    [ $start_position>, $font_object ]

For more information refer to the example program sample/dmpExR.pl.

Format

The Spreadsheet::ParseExcel::Format class has the following properties:

Format properties

    $format->{Font}
    $format->{AlignH}
    $format->{AlignV}
    $format->{Indent}
    $format->{Wrap}
    $format->{Shrink}
    $format->{Rotate}
    $format->{JustLast}
    $format->{ReadDir}
    $format->{BdrStyle}
    $format->{BdrColor}
    $format->{BdrDiag}
    $format->{Fill}
    $format->{Lock}
    $format->{Hidden}
    $format->{Style}

These properties are generally only of interest to advanced users. Casual users can skip this section.

$format->{Font}

Returns the "Font" object for the Format.

$format->{AlignH}

Returns the horizontal alignment of the format where the value has the following meaning:

    0 => No alignment
    1 => Left
    2 => Center
    3 => Right
    4 => Fill
    5 => Justify
    6 => Center across
    7 => Distributed/Equal spaced

$format->{AlignV}

Returns the vertical alignment of the format where the value has the following meaning:

    0 => Top
    1 => Center
    2 => Bottom
    3 => Justify
    4 => Distributed/Equal spaced

$format->{Indent}

Returns the indent level of the Left horizontal alignment.

$format->{Wrap}

Returns true if textwrap is on.

$format->{Shrink}

Returns true if "Shrink to fit" is set for the format.

$format->{Rotate}

Returns the text rotation. In Excel97+, it returns the angle in degrees of the text rotation.

In Excel95 or earlier it returns a value as follows:

    0 => No rotation
    1 => Top down
    2 => 90 degrees anti-clockwise,
    3 => 90 clockwise

$format->{JustLast}

Return true if the "justify last" property is set for the format.

$format->{ReadDir}

Returns the direction that the text is read from.

$format->{BdrStyle}

Returns an array ref of border styles as follows:

    [ $left, $right, $top, $bottom ]

$format->{BdrColor}

Returns an array ref of border color indexes as follows:

    [ $left, $right, $top, $bottom ]

$format->{BdrDiag}

Returns an array ref of diagonal border kind, style and color index as follows:

    [$kind, $style, $color ]

Where kind is:

    0 => None
    1 => Right-Down
    2 => Right-Up
    3 => Both

$format->{Fill}

Returns an array ref of fill pattern and color indexes as follows:

    [ $pattern, $front_color, $back_color ]

$format->{Lock}

Returns true if the cell is locked.

$format->{Hidden}

Returns true if the cell is Hidden.

$format->{Style}

Returns true if the format is a Style format.

Font

Spreadsheet::ParseExcel::Font

Format class has these properties:

Font Properties

    $font->{Name}
    $font->{Bold}
    $font->{Italic}
    $font->{Height}
    $font->{Underline}
    $font->{UnderlineStyle}
    $font->{Color}
    $font->{Strikeout}
    $font->{Super}

$font->{Name}

Returns the name of the font, for example 'Arial'.

$font->{Bold}

Returns true if the font is bold.

$font->{Italic}

Returns true if the font is italic.

$font->{Height}

Returns the size (height) of the font.

$font->{Underline}

Returns true if the font in underlined.

$font->{UnderlineStyle}

Returns the style of an underlined font where the value has the following meaning:

     0 => None
     1 => Single
     2 => Double
    33 => Single accounting
    34 => Double accounting

$font->{Color}

Returns the color index for the font. The index can be converted to a RGB string using the ColorIdxToRGB() Parser method.

$font->{Strikeout}

Returns true if the font has the strikeout property set.

$font->{Super}

Returns one of the following values if the superscript or subscript property of the font is set:

    0 => None
    1 => Superscript
    2 => Subscript

Formatter class

Spreadsheet::ParseExcel::Fmt*

Formatter class will convert cell data.

Spreadsheet::ParseExcel includes 2 formatter classes. FmtDefault and FmtJapanese. It is also possible to create a user defined formatting class.

The formatter class Spreadsheet::ParseExcel::Fmt* should provide the following functions:

ChkType($self, $is_numeric, $format_index)

Method to check the type of data in the cell. Should return Date, Numeric or Text. It is passed the following parameters:

$self

A scalar reference to the Formatter object.

$is_numeric

If true, the value seems to be number.

$format_index

The index number for the cell Format object.

TextFmt($self, $string_data, $string_encoding)

Converts the string data in the cell into the correct encoding. It is passed the following parameters:

$self

A scalar reference to the Formatter object.

$string_data

The original string/text data.

$string_encoding

The character encoding of original string/text.

ValFmt($self, $cell, $workbook)

Convert the original unformatted cell value into the appropriate formatted value. For instance turn a number into a formatted date. It is passed the following parameters:

$self

A scalar reference to the Formatter object.

$cell

A scalar reference to the Cell object.

$workbook

A scalar reference to the Workbook object.

FmtString($self, $cell, $workbook)

Get the format string for the Cell. It is passed the following parameters:

$self

A scalar reference to the Formatter object.

$cell

A scalar reference to the Cell object.

$workbook

A scalar reference to the Workbook object.

Reducing the memory usage of Spreadsheet::ParseExcel

In some cases a Spreadsheet::ParseExcel application may consume a lot of memory when processing a large Excel file and, as a result, may fail to complete. The following explains why this can occur and how to resolve it.

Spreadsheet::ParseExcel processes an Excel file in two stages. In the first stage it extracts the Excel binary stream from the OLE container file using OLE::Storage_Lite. In the second stage it parses the binary stream to read workbook, worksheet and cell data which it then stores in memory. The majority of the memory usage is required for storing cell data.

The reason for this is that as the Excel file is parsed and each cell is encountered a cell handling function creates a relatively large nested cell object that contains the cell value and all of the data that relates to the cell formatting. For large files (a 10MB Excel file on a 256MB system) this overhead can cause the system to grind to a halt.

However, in a lot of cases when an Excel file is being processed the only information that is required are the cell values. In these cases it is possible to avoid most of the memory overhead by specifying your own cell handling function and by telling Spreadsheet::ParseExcel not to store the parsed cell data. This is achieved by passing a cell handler function to new() when creating the parse object. Here is an example.

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

    my $parser = Spreadsheet::ParseExcel->new(
        CellHandler => \&cell_handler,
        NotSetCell  => 1
    );

    my $workbook = $parser->Parse('file.xls');

    sub cell_handler {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];

        # Do something useful with the formatted cell value
        print $cell->value(), "\n";

    }

The user specified cell handler is passed as a code reference to new() along with the parameter NotSetCell which tells Spreadsheet::ParseExcel not to store the parsed cell. Note, you don't have to iterate over the rows and columns, this happens automatically as part of the parsing.

The cell handler is passed 5 arguments. The first, $workbook, is a reference to the Spreadsheet::ParseExcel::Workbook object that represent the parsed workbook. This can be used to access any of the Spreadsheet::ParseExcel::Workbook methods, see "Workbook". The second $sheet_index is the zero-based index of the worksheet being parsed. The third and fourth, $row and $col, are the zero-based row and column number of the cell. The fifth, $cell, is a reference to the Spreadsheet::ParseExcel::Cell object. This is used to extract the data from the cell. See "Cell" for more information.

This technique can be useful if you are writing an Excel to database filter since you can put your DB calls in the cell handler.

If you don't want all of the data in the spreadsheet you can add some control logic to the cell handler. For example we can extend the previous example so that it only prints the first 10 rows of the first two worksheets in the parsed workbook by adding some if() statements to the cell handler:

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

    my $parser = Spreadsheet::ParseExcel->new(
        CellHandler => \&cell_handler,
        NotSetCell  => 1
    );

    my $workbook = $parser->Parse('file.xls');

    sub cell_handler {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];

        # Skip some worksheets and rows (inefficiently).
        return if $sheet_index >= 3;
        return if $row >= 10;

        # Do something with the formatted cell value
        print $cell->value(), "\n";

    }

However, this still processes the entire workbook. If you wish to save some additional processing time you can abort the parsing after you have read the data that you want, using the workbook ParseAbort method:

    #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

    my $parser = Spreadsheet::ParseExcel->new(
        CellHandler => \&cell_handler,
        NotSetCell  => 1
    );

    my $workbook = $parser->Parse('file.xls');

    sub cell_handler {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];

        # Skip some worksheets and rows (more efficiently).
        if ( $sheet_index >= 1 and $row >= 10 ) {
            $workbook->ParseAbort(1);
            return;
        }

        # Do something with the formatted cell value
        print $cell->value(), "\n";

    }

KNOWN PROBLEMS

  • Issues reported by users: http://rt.cpan.org/Public/Dist/Display.html?Name=Spreadsheet-ParseExcel

  • This module cannot read the values of formulas from files created with Spreadsheet::WriteExcel unless the user specified the values when creating the file (which is generally not the case). The reason for this is that Spreadsheet::WriteExcel writes the formula but not the formula result since it isn't in a position to calculate arbitrary Excel formulas without access to Excel's formula engine.

  • If Excel has date fields where the specified format is equal to the system-default for the short-date locale, Excel does not store the format, but defaults to an internal format which is system dependent. In these cases ParseExcel uses the date format 'yyyy-mm-dd'.

REPORTING A BUG

Bugs can be reported via rt.cpan.org. See the following for instructions on bug reporting for Spreadsheet::ParseExcel

http://rt.cpan.org/Public/Dist/Display.html?Name=Spreadsheet-ParseExcel

SEE ALSO

  • xls2csv by Ken Prows (http://search.cpan.org/~ken/xls2csv-1.06/script/xls2csv).

  • xls2csv and xlscat by H.Merijn Brand (these utilities are part of Spreadsheet::Read, see below).

  • excel2txt by Ken Youens-Clark, (http://search.cpan.org/~kclark/excel2txt/excel2txt). This is an excellent example of an Excel filter using Spreadsheet::ParseExcel. It can produce CSV, Tab delimited, Html, XML and Yaml.

  • XLSperl by Jon Allen (http://search.cpan.org/~jonallen/XLSperl/bin/XLSperl). This application allows you to use Perl "one-liners" with Microsoft Excel files.

  • Spreadsheet::XLSX (http://search.cpan.org/~dmow/Spreadsheet-XLSX/lib/Spreadsheet/XLSX.pm) by Dmitry Ovsyanko. A module with a similar interface to Spreadsheet::ParseExcel for parsing Excel 2007 XLSX OpenXML files.

  • Spreadsheet::Read (http://search.cpan.org/~hmbrand/Spreadsheet-Read/Read.pm) by H.Merijn Brand. A single interface for reading several different spreadsheet formats.

  • Spreadsheet::WriteExcel (http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm). A perl module for creating new Excel files.

  • Spreadsheet::ParseExcel::SaveParser (http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel/SaveParser.pm). This is a combination of Spreadsheet::ParseExcel and Spreadsheet::WriteExcel and it allows you to "rewrite" an Excel file. See the following example (http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/lib/Spreadsheet/WriteExcel.pm#MODIFYING_AND_REWRITING_EXCEL_FILES). It is part of the Spreadsheet::ParseExcel distro.

  • Text::CSV_XS (http://search.cpan.org/~hmbrand/Text-CSV_XS/CSV_XS.pm) by H.Merijn Brand. A fast and rigorous module for reading and writing CSV data. Don't consider rolling your own CSV handling, use this module instead.

MAILING LIST

There is a Google group for discussing and asking questions about Spreadsheet::ParseExcel. This is a good place to search to see if your question has been asked before: http://groups-beta.google.com/group/spreadsheet-parseexcel/

DONATIONS

If you'd care to donate to the Spreadsheet::ParseExcel project, you can do so via PayPal: http://tinyurl.com/7ayes

TODO

  • The current maintenance work is directed towards making the documentation more useful, improving and simplifying the API, and improving the maintainability of the code base. After that new features will be added.

  • Fix open bugs and documentation for SaveParser.

  • Add Formula support, Hyperlink support, Named Range support.

  • Improve Spreadsheet::ParseExcel::SaveParser compatibility with Spreadsheet::WriteExcel.

  • Improve Unicode and other encoding support. This will probably require dropping support for perls prior to 5.8+.

ACKNOWLEDGEMENTS

From Kawai Takanori:

First of all, I would like to acknowledge the following valuable programs and modules: XHTML, OLE::Storage and Spreadsheet::WriteExcel.

In no particular order: Yamaji Haruna, Simamoto Takesi, Noguchi Harumi, Ikezawa Kazuhiro, Suwazono Shugo, Hirofumi Morisada, Michael Edwards, Kim Namusk, Slaven Rezic, Grant Stevens, H.Merijn Brand and many many people + Kawai Mikako.

DISCLAIMER OF WARRANTY

Because this software is licensed free of charge, there is no warranty for the software, to the extent permitted by applicable law. Except when otherwise stated in writing the copyright holders and/or other parties provide the software "as is" without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the software is with you. Should the software prove defective, you assume the cost of all necessary servicing, repair, or correction.

In no event unless required by applicable law or agreed to in writing will any copyright holder, or any other party who may modify and/or redistribute the software as permitted by the above licence, be liable to you for damages, including any general, special, incidental, or consequential damages arising out of the use or inability to use the software (including but not limited to loss of data or data being rendered inaccurate or losses sustained by you or third parties or a failure of the software to operate with any other software), even if such holder or other party has been advised of the possibility of such damages.

LICENSE

Either the Perl Artistic Licence http://dev.perl.org/licenses/artistic.html or the GPL http://www.opensource.org/licenses/gpl-license.php

AUTHOR

Current maintainer 0.40+: John McNamara jmcnamara@cpan.org

Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org

Original author: Kawai Takanori (Hippo2000) kwitknr@cpan.org

COPYRIGHT

Copyright (c) 2009 John McNamara

Copyright (c) 2006-2008 Gabor Szabo

Copyright (c) 2000-2006 Kawai Takanori

All rights reserved. This is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License.