Spreads...rseExcel(3)

📦
version 0.6600 · 10 versions
, in epel 9

💖 Spreadsheet::ParseExcel(3) — your Excel-reading bestie 💖

From the User Contributed Perl Documentation collection, meet Spreadsheet::ParseExcel(3). Bestie, if you've got an old-school Excel 95–2003 binary file and you need to pull info out of it with Perl, this module is literally your main character. 💅

🌸 NAME 🌸

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

✨ SYNOPSIS ✨

Okay so here's the group-chat demo. Run it, slay, repeat:

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

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

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

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.

🚨 Vibe check, and this one is important: the module cannot read files in the Excel 2007 Open XML XLSX format. See the Spreadsheet::XLSX module instead. No cap.

💅 Parser 💅

🆕 new()

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

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

It is possible to pass a password to decrypt an encrypted file:

$parser = Spreadsheet::ParseExcel->new( Password => 'secret' );

Only the default Excel encryption scheme is currently supported. See "Decryption".

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 returns a Workbook object.

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

If an error occurs parse() returns undef. In general, programs should contain a test for failed parsing as follows:

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

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

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

The optional $formatter parameter can be a reference to a Formatter Class to format the value of cells. This is useful for parsing workbooks with Unicode or Asian characters:

my $parser    = Spreadsheet::ParseExcel->new();
my $formatter = Spreadsheet::ParseExcel::FmtJapan->new();
my $workbook  = $parser->parse( 'Book1.xls', $formatter );

The Spreadsheet::ParseExcel::FmtJapan formatter also supports Unicode. If you encounter any encoding problems with the default formatter try that instead.

🚨 error()

The Parser error() method returns an error string if a parse() fails:

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

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}

If you wish to generate your own error string you can use the error_code() method instead (see below). The error() and error_code() values are as follows:

error()                         error_code()
=======                         ============
''                              0
'File not found'                1
'No Excel data found in file'   2
'File is encrypted'             3

The error_code() method is explained below.

Spreadsheet::ParseExcel will try to decrypt an encrypted Excel file using the default password or a user supplied password passed to new(), see above. If these fail the module will return the 'File is encrypted' error. Only the default Excel encryption scheme is currently supported, see "Decryption".

🔢 error_code()

The Parser error_code() method returns an error code if a parse() fails:

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

if ( !defined $workbook ) {
    die "Got error code ", $parser->error_code, ".\n";
}

This can be useful if you wish to employ your own error strings or error handling methods.

📚 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 main methods of the Workbook class are:

$workbook->worksheets()
$workbook->worksheet()
$workbook->worksheet_count()
$workbook->get_filename()

These more commonly used methods of the Workbook class are outlined below. The other, less commonly used, methods are documented in Spreadsheet::ParseExcel::Worksheet.

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

🔢 worksheet_count()

The worksheet_count() method returns the number of Worksheet objects in the Workbook.

my $worksheet_count = $workbook->worksheet_count();

🏷️ get_filename()

The get_filename() method returns the name of the Excel file or undef if the data was read from a filehandle rather than a file.

my $filename = $workbook->get_filename();

🗂️ Other Workbook Methods

For full documentation of the methods available via a Workbook object see Spreadsheet::ParseExcel::Workbook.

🦋 Worksheet 🦋

The Spreadsheet::ParseExcel::Worksheet class encapsulates the properties of an Excel worksheet.

A Worksheet object is obtained via the worksheets() or worksheet() methods.

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

# Or:

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

The most commonly used methods of the Worksheet class are:

$worksheet->get_cell()
$worksheet->row_range()
$worksheet->col_range()
$worksheet->get_name()

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 detailed 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 main methods — these are the two big ones.

$cell->value()
$cell->unformatted()

💎 value()

The value() method returns the formatted value of the cell.

my $value = $cell->value();

Formatted in this sense refers to the numeric format of the cell value. For example a number such as 40177 might be formatted as 40,117, 40117.000 or even as the date 2009/12/30.

If the cell doesn't contain a numeric format then the formatted and unformatted cell values are the same, see the unformatted() method below.

For a defined $cell the value() method will always return a value.

In the case of a cell with formatting but no numeric or string contents the method will return the empty string ''.

🪄 unformatted()

The unformatted() method returns the unformatted value of the cell.

my $unformatted = $cell->unformatted();

Returns the cell value without a numeric format. See the value() method above.

🗂️ Other Cell Methods

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

💅 Format 💅

Okay bestie, the Spreadsheet::ParseExcel::Format class has these 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. No cap, it's giving advanced-user territory. 🫶

✨ Format properties ✨

🦋 $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 is 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 mapping to an RGB color is defined by each workbook.

The index can be converted to a RGB string using the $workbook->ColorIdxToRGB() Parser method.

(Older versions of Spreadsheet::ParseExcel provided the ColorIdxToRGB class method, which is deprecated.)

🌸 $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 🧚

Formatters can be passed to the parse() method to deal with Unicode or Asian formatting.

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 🧠

Okay bestie, listen up, because this is the part where your laptop fans start SCREAMING. 😭 Sometimes a Spreadsheet::ParseExcel app eats up a genuinely unhinged amount of memory while chewing through a big Excel file, and then it just... dies. Doesn't finish. Rude. So here's why that happens and how you fix it. 💅

Spreadsheet::ParseExcel handles an Excel file in two stages. Stage one: it pulls the Excel binary stream out of the OLE container file using OLE::Storage_Lite. Stage two: it parses that binary stream to read workbook, worksheet and cell data and then stores all of it in memory. And the majority of the memory usage? That's all for storing cell data. 🫠

Why tho? Because as the Excel file gets parsed and each cell shows up, a cell handling function builds a relatively large nested cell object that holds the cell value plus all of the data about the cell formatting. For big files (they mean like a 10MB Excel file on a 256MB system, which, yes, iconic early-2000s chaos) that overhead can make the whole system grind to a halt. 💀

But here's the slay: in a lot of cases when you're processing an Excel file, the only thing you actually want is the cell values. And in those cases you can dodge most of the memory overhead by specifying your own cell handling function and telling Spreadsheet::ParseExcel not to store the parsed cell data. The way you do it is passing a cell handler function to new() when you create the parse object. Behold, 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";

}

Your user-specified cell handler gets passed as a code reference to new(), along with the parameter NotSetCell, which tells Spreadsheet::ParseExcel not to store the parsed cell. And ngl, the best part: you don't have to iterate over the rows and columns yourself, that happens automatically as part of the parsing. 😌✨

The cell handler gets 5 arguments. The first one, $workbook, is a reference to the Spreadsheet::ParseExcel::Workbook object that represents the parsed workbook — you can use it 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. Third and fourth, $row and $col, are the zero-based row and column number of the cell. And the fifth, $cell, is a reference to the Spreadsheet::ParseExcel::Cell object — that's the one you use to extract the data from the cell, see "Cell" for more info. 🎀

This technique is lowkey a lifesaver if you're writing an Excel to database filter, since you can just put your DB calls right in the cell handler. 🗂️

And if you don't want all the data in the spreadsheet, you can add some control logic to the cell handler. For example, we can extend the previous example so 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";

}

But heads up bestie, this still processes the entire workbook. 🙈 If you wanna save yourself some extra processing time too, you can abort the parsing after you've read the data you actually 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";

}

🔐 Decryption 🔐

So if a workbook is "protected", Excel will encrypt the file whether a password is supplied or not. No escaping it. 😤 As of version 0.59, Spreadsheet::ParseExcel supports decrypting Excel workbooks using a default or user supplied password. But — and this is the fine print, bestie — only the following encryption scheme is supported:

Office 97/2000 Compatible encryption

And these encryption methods are not supported, like at all, don't @ me:

Weak Encryption (XOR)
RC4, Microsoft Base Cryptographic Provider v1.0
RC4, Microsoft Base DSS and Diffie-Hellman Cryptographic Provider
RC4, Microsoft DH SChannel Cryptographic Provider
RC4, Microsoft Enhanced Cryptographic Provider v1.0
RC4, Microsoft Enhanced DSS and Diffie-Hellman Cryptographic Provider
RC4, Microsoft Enhanced RSA and AES Cryptographic Provider
RC4, Microsoft RSA SChannel Cryptographic Provider
RC4, Microsoft Strong Cryptographic Provider

Wanna know more about Excel encryption? Go here: <http://office.microsoft.com/en-us/office-2003-resource-kit/important-aspects-of-password-and-encryption-protection-HA001140311.aspx>. 🧚

⚠️ KNOWN PROBLEMS ⚠️

  • 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, let's be real, is generally not the case). The reason? 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. It's giving "I wrote the question but not the answer" energy. 😭
  • 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 🐛

Please report bugs on GitHub with a complete, working, sample application and a test xls file. Fr fr, both. The maintainers can't fix a vibe, they need a repro. 💖

<https://github.com/jmcnamara/spreadsheet-parseexcel/issues>

👀 SEE ALSO 👀

💸 DONATIONS 💸

If you'd care to donate to the Spreadsheet::ParseExcel project, you can do so via PayPal: <http://tinyurl.com/7ayes>. Support your local spreadsheet parser, bestie. 🫶

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

Alexey Mazurin added the decryption facility. 🔐✨

🫶 DISCLAIMER OF WARRANTY 🫶

Okay bestie, serious face for a sec 😌 because this is the legal part, and we do not skip it. 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. Not me, not the maintainers — you 🫵😭.

And legally, 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. That’s the whole legal vibe, no cap ⚖️💀.

🎀 LICENSE 🎀

It’s giving choose-your-fighter, bestie: either the Perl Artistic Licence http://dev.perl.org/licenses/artistic.html or the GPL http://www.opensource.org/licenses/gpl-license.php 💖.

👑 AUTHOR 👑

The maintainer timeline is iconic, okay:

  • Current maintainer 0.60+: Douglas Wilson dougw@cpan.org
  • Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org
  • Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
  • Original author: Kawai Takanori (Hippo2000) kwitknr@cpan.org
  • Copyright (c) 2014 Douglas Wilson
  • Copyright (c) 2009-2013 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 🫶✨.

And that’s the whole manpage, bestie 💖 this one is from perl v5.32.1, dated 2023-12-30, and it’s the Spreads...rseExcel(3) page — go forth and spreadsheet slay 👑🌈💅

📦 where this page comes from