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');
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.
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();
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.
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 an 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 you 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 you 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 of
"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.
$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 ''.
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.
The "Spreadsheet::ParseExcel::Format" class has the following
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.
Returns the "Font" object for the Format.
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
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
Returns the indent level of the "Left" horizontal alignment.
Returns true if textwrap is on.
Returns true if "Shrink to fit" is set for the format.
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
Return true if the "justify last" property is set for the format.
Returns the direction that the text is read from.
Returns an array ref of border styles as follows:
[ $left, $right, $top, $bottom ]
Returns an array ref of border color indexes as follows:
[ $left, $right, $top, $bottom ]
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
Returns an array ref of fill pattern and color indexes as follows:
[ $pattern, $front_color, $back_color ]
Returns true if the cell is locked.
Returns true if the cell is Hidden.
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 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
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:
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";
}
Decryption¶
If a workbook is "protected" then Excel will encrypt the file whether
a password is supplied or not. As of version 0.59 Spreadsheet::ParseExcel
supports decrypting Excel workbooks using a default or user supplied password.
However, only the following encryption scheme is supported:
Office 97/2000 Compatible encryption
The following encryption methods are not supported:
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
See the following for more information on Excel encryption:
<
http://office.microsoft.com/en-us/office-2003-resource-kit/important-aspects-of-password-and-encryption-protection-HA001140311.aspx>.
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.
Alexey Mazurin added the decryption facility.
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.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¶
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.