| Excel::ValueReader::XLSX(3pm) | User Contributed Perl Documentation | Excel::ValueReader::XLSX(3pm) |
Excel::ValueReader::XLSX - extracting values from Excel workbooks in XLSX format, fast
my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename_or_handle);
# .. or with syntactic sugar :
my $reader = Excel::ValueReader::XLSX->new($filename_or_handle);
# .. or with LibXML backend :
my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename_or_handle,
using => 'LibXML');
foreach my $sheet_name ($reader->sheet_names) {
my $grid = $reader->values($sheet_name);
my $n_rows = @$grid;
print "sheet $sheet_name has $n_rows rows; ",
"first cell contains : ", $grid->[0][0];
}
foreach my $table_name ($reader->table_names) {
my ($columns, $records) = $reader->table($table_name);
my $n_records = @$records;
my $n_columns = @$columns;
print "table $table_name has $n_records records and $n_columns columns; ",
"column 'foo' in first row contains : ", $records->[0]{foo};
}
my $first_grid = $reader->values(1); # the arg can be a sheet index instead of a sheet name
# iterator version of ->values()
my $iterator = $reader->ivalues($sheet_name);
while (my $row = $iterator->()) { process_row($row) }
# iterator version of ->table()
my ($columns, $iterator) = $reader->itable($table_name);
while (my $record = $iterator->()) { process_record($record) }
This module reads the contents of an Excel file in XLSX format. Unlike other modules like Spreadsheet::ParseXLSX or Data::XLSX::Parser, this module has no support for reading formulas, formats or other Excel internal information; all you get are plain values -- but you get them much faster !
Besides, this API has some features not found in concurrent parsers :
Two different backends may be used for extracting values :
The default is the "Regex" backend.
Although worksheets are usually accessed by name, they may also be accessed by numerical indices, starting at value 1. Some other Perl parsing modules use a different convention, where the first sheet has index 0. Here index 1 was chosen to be consistent with the common API for "collections" in Microsoft Office object model.
Methods "ivalues" and "itable" return iterators. Each call to the iterator produces a new data row from the Excel content, until reaching the end of data where the iterator returns "undef". Following the Iterator::Simple protocol, iterators support three different but semantically equivalent syntaxes :
while (my $row = $iterator->()) { process($row) }
while (my $row = $iterator->next) { process($row) }
while (<$iterator>) { process($_) }
Working with iterators is especially interesting when dealing with large Excel files, because rows can be processed one at a time instead of being loaded all at once in memory. For example a typical pattern for loading the Excel content into a database would be :
my $iter = $valuereader->ivalues('MySheet');
my $sth = $dbh->prepare("INSERT INTO MYTABLE(col1, col2, col3) VALUES (?, ?, ?)");
while (my $row = $iter->()) {
$sth->execute(@$row);
}
As another example, suppose a large population table, from which we want to produce a list of list of minor girls. This can be done with a combination of iterator operations :
use Iterator::Simple qw/igrep imap/;
use Iterator::Simple::Util qw/ireduce/;
my $minor_girls = ireduce {"$a, $b"} # successive results joined with ", "
imap {"$_->{firstname} $_->{lastname}"} # produce a flat string from an input record with first/last name
igrep {$_->{gender} eq 'F' && $_->{age} < 18} # filter input records
$valuereader->itable('Population'); # source iterator
my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename_or_handle, %options); # .. or with syntactic sugar : my $reader = Excel::ValueReader::XLSX->new($filename_or_handle, %options);
The "xlsx" argument is mandatory and points to the ".xlsx" file to be parsed, or to an open filehandle.
Options are :
my @sheets = $reader->sheet_names;
Returns the list of worksheet names, in the same order as in the Excel file. The first name in the list corresponds to sheet number 1.
my $active_sheet_number = $reader->active_sheet;
Returns the numerical index (starting at 1) of the sheet that was active when the file was last saved. May return "undef".
my ($ref, $grid) = $reader->values($sheet); # or my $grid = $reader->values($sheet);
Returns a pair where
The $sheet argument can be either a sheet name or a sheet position (starting at 1).
When called in scalar context, this method only returns the grid of values.
Unlike the original Excel cells, positions in the grid are zero-based, so for example the content of cell B3 is in "$grid->[1][2]". The grid is sparse : the size of each row depends on the position of the last non-empty cell in that row. Thanks to Perl's auto-vivification mechanism, any attempt to access a non-existent cell will automatically create the corresponding cell within the grid. The number of rows and columns in the grid can be computed like this :
my $nb_rows = @$grid;
my $nb_cols = max map {scalar @$_} @$grid; # must import List::Util::max
Alternatively, these numbers can also be obtained through the "range_from_ref" method.
my ($ref, $iterator) = $reader->ivalues($sheet);
# or
my $iterator = $reader->ivalues($sheet);
while (my $row = $iterator->()) {
say join ", ", @$row;
}
Like the "values" method, except that it returns an iterator instead of a fully populated data grid. Data rows are retrieved through successive calls to the iterator.
my @table_names = $reader->table_names;
Returns the list of names of tables registered in this workbook.
my $rows = $reader->table(name => $table_name); # or just : $reader->table($table_name)
# or
my ($columns, $rows) = $reader->table(name => $table_name);
# or
my ($columns, $rows) = $reader->table(sheet => $sheet [, ref => $ref]
[, columns => \@columns]
[, no_headers => 1]
[, with_totals => 1]
[, want_records => 0]
);
In its simplest form, this method returns the content of an Excel table referenced by its table name (in Excel, the table name appears and can be modified through the ribbon tab entry "Table tools / Design"). The table name is passed either through the named argument "name", or positionally as unique argument to the method.
In list context, the method returns a pair, where the first element is an arrayref of column names, and the second element is an arrayref of rows. In scalar context, the method just returns the arrayref of rows.
Rows are normally returned as hashrefs, where keys of the hashes correspond to column names in the table. Under option "want_records => 0>>, rows are returned as arrayrefs, and it is up to the client to make the correspondance with column names in $columns."
Instead of specifying a table name, it is also possible to give a sheet name or sheet number. By default, this considers the whole sheet content as a single table, where column names are on the first row. However, additional arguments can be supplied to change the default behaviour :
my $col_num = $reader->A1_to_num('A'); # 1
$col_num = $reader->A1_to_num('AZ'); # 52
$col_num = $reader->A1_to_num('AA'); # 26
$col_num = $reader->A1_to_num('ABC'); # 731
Converts a column expressed as a sequence of capital letters (in Excel's "A1" notation) into the corresponding numeric value.
The module also has a global hash $Excel::ValueReader::XLSX::A1_to_num_memoized where results from such conversions are memoized.
my ($col1, $row1, $col2, $row2) = $reader->range_from_ref("C4:BB123");
Returns the coordinates of the topleft and bottomright cells corresponding to a given Excel range.
my $info = $reader->table_info->{$table_name};
Returns information about an Excel table in the form of a hashref with keys
my $date = $reader->formatted_date($numeric_date, $excel_date_format);
Given a numeric date, this method returns a string date formatted according to the date formatter routine explained in the next section. The $excel_date_format argument should be the Excel format string for that specific cell; it is used only for for deciding if the numeric value should be presented as a date, as a time, or both. Optionally, a custom date formatter callback could be passed as third argument.
In Excel, date and times values are stored as numeric values, where the integer part represents the date, and the fractional part represents the time. What distinguishes such numbers from ordinary numbers is the numeric format applied to the cells where they appear.
Numeric formats in Excel are complex to reproduce, in particular because they are locale-dependent; therefore the present module does not attempt to faithfully interpret Excel formats. It just infers from formats which cells should be presented as date and/or time values. All such values are then presented through the same date_formatter routine. The default formatter is based on "strftime" in POSIX; other behaviours may be specified through the "date_formatter" parameter (explained below).
When using the default strftime formatter, the following parameters may be passed to the constructor :
A custom algorithm for date formatting can be specified as a parameter to the constructor
my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename,
date_formatter => sub {...});
If this parameter is "undef", date formatting is canceled and therefore date and time values will be presented as plain numbers.
If not "undef", the date formatting routine will we called as :
$date_formater->($excel_date_format, $year, $month, $day, $hour, $minute, $second, $millisecond);
where
The official reference for OOXML-SpreadsheetML format is in <https://www.ecma-international.org/publications/standards/Ecma-376.htm>.
Introductory material on XLSX file structure can be found at <http://officeopenxml.com/anatomyofOOXML-xlsx.php>.
Concurrent modules Spreadsheet::ParseXLSX or Data::XLSX::Parser.
Another unpublished but working module for parsing Excel files in Perl can be found at <https://github.com/jmcnamara/excel-reader-xlsx>. Some test cases were borrowed from that distribution.
Conversions from and to Excel internal date format can also be performed through the DateTime::Format::Excel module.
Below are some comparative figures. The task computed here was to read a large Excel file with 800131 rows of 7 columns, and report the total number of rows. Reported figures are in seconds.
Spreadsheet::ParseXLSX 1272 elapsed, 870 cpu, 4 system Data::XLSX::Parser 125 elapsed, 107 cpu, 1 system Excel::ValueReader::XLSX::Regex 40 elapsed, 32 cpu, 0 system Excel::ValueReader::XLSX::Regex, iterator 34 elapsed, 30 cpu, 0 system Excel::ValueReader::XLSX::LibXML 101 elapsed, 83 cpu, 0 system Excel::ValueReader::XLSX::LibXML, iterator 91 elapsed, 80 cpu, 0 system
Laurent Dami, <dami at cpan.org>
Copyright 2020-2025 by Laurent Dami.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
Hey! The above document had some coding errors, which are explained below:
| 2025-04-19 | perl v5.40.1 |