NAME

Excel::ValueReader::XLSX - extracting values from Excel workbooks in XLSX format, fast

SYNOPSIS


            
              
              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) }

DESCRIPTION

Purpose

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 :

has support for parsing Excel tables
iterator methods for getting one row at a time from a worksheet or from a table -- very useful for sparing memory when dealing with large Excel files.

Backends

Two different backends may be used for extracting values :

Regex: using regular expressions to parse the XML content.
LibXML: using XML::LibXML::Reader to parse the XML content. It is probably safer but two to three times slower than the Regex backend (but still much faster than Spreadsheet::ParseXLSX).

The default is the Regex backend.

Sheet numbering

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.

NOTE ON ITERATORS

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

METHODS

new


            
              
              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 :

using: The backend to be used for parsing; default is 'Regex'.
date_format, time_format, datetime_format, date_formatter: Parameters for formatting date and time values; these are described in the "DATE AND TIME FORMATS" section below.

sheet_names


            
              
              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.

active_sheet


            
              
              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.

values


            
              
              my ($ref, $grid) = $reader->values($sheet);
# or 
my $grid = $reader->values($sheet);

Returns a pair where

the first item is a string that describes the range of the sheet, in Excel A1 format (like for example A1:F12
the second item is a bidimensional array of scalars (in other words, an arrayref of arrayrefs of scalars), corresponding to cell values in the specified worksheet.

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.

ivalues


            
              
              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.

table_names


            
              
              my @table_names = $reader->table_names;

Returns the list of names of tables registered in this workbook.

table


            
              
              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 :

ref: a specific range of cells within the sheet that contain the table rows and columns. The range must be expressed using traditional Excel notation, like for example "C9:E23" (columns 3 to 5, rows 9 to 23).
columns: an arrayref containing the list of column names. If absent, column names will be taken from the first row in the table.
no_headers: if true, the first row in the table will be treated as a regular data row, instead of being treated as a list of column names. In that case, since column names cannot be inferred from cell values in the first row, the columns argument to the method must be present.
with_totals: For tables that have a "totals" row (turned on by a specific checkbox in the Excel ribbon), this row is normally not included in the result. To include it as a final row, pass a true value to the with_totals option.

AUXILIARY METHODS

A1_to_num


            
              
              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.

range_from_ref


            
              
              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.

table_info


            
              
              my $info = $reader->table_info->{$table_name};

Returns information about an Excel table in the form of a hashref with keys

name: the name of the table
ref: the range of the table, in Excel notation (e.g "G6:Z44")
columns: an arrayref of column names
id: numerical id of the table
sheet: numerical id of the sheet to which the table belongs
no_headers: boolean flag corresponding to the negation of the checkbox "Headers row" in Excel. By default tables have a header row, both in Excel and in this module.
has_totals: boolean flag corresponding to the checkbox "Totals row" in Excel. By default tables have no totals row, both in Excel and in this module.

formatted_date


            
              
              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.

DATE AND TIME FORMATS

Date and time handling

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

Parameters for the default strftime formatter

When using the default strftime formatter, the following parameters may be passed to the constructor :

date_format: The "strftime" in POSIX format for representing dates. The default is %d.%m.%Y.
time_format: The "strftime" in POSIX format for representing times. The default is %H:%M:%S.
datetime_format: The "strftime" in POSIX format for representing date and time together. The default is the concatenation of date_format and time_format, with a space in between.

Writing a custom formatter

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

$excel_date_format is the Excel numbering format associated to that cell, like for example mm-dd-yy or h:mm:ss AM/PM. See the Excel documentation for the syntax description. This is useful to decide if the value should be presented as a date, a time, or both. The present module uses a simple heuristic : if the format contains d or y, it should be presented as a date; if the format contains h or s, it should be presented as a time. The letter m is not taken into consideration because it is ambiguous : depending on the position in the format string, it may represent either a "month" or a "minute".
year is the full year, such as 1993 or 2021. The date system of the Excel file (either 1900 or 1904, see https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487) is properly taken into account. Excel has no support for dates prior to 1900 or 1904, so the year component will always be above this value.
month is the numeric value of the month, starting at 1
day is the numeric value of the day in month, starting at 1
$hour, $minute, $second, $millisecond obviously contain the corresponding numeric values.

CAVEATS

This module was optimized for speed, not for completeness of OOXML-SpreadsheetML support; so there may be some edge cases where the output is incorrect with respect to the original Excel data.
Embedded newline characters in strings are stored in Excel as \r\n, following the old Windows convention. When retrieved through the Regex backend, the result contains the original \r and \n characters; but when retrieved through the LibXML backend, \r are silently removed by the XML::LibXML package.

BENCHMARKS

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

ACKNOWLEDGMENTS

David Flink signaled (and fixed) a bug about strings with embedded newline characters, and signaled that the 'r' attribute in cells is optional.
Ulibuck signaled bugs several minor bugs on the LibXML backend.
H.Merijn Brand suggested additions to the API and several improvements to the code source.
Ephraim Stevens signaled a bug in the table() method with 'ref' param.

AUTHOR

Laurent Dami, <dami at cpan.org>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

1 POD Error

The following errors were encountered while parsing the POD:

Around line 554:: Unterminated C< ... > sequence

To install Excel::ValueReader::XLSX, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Excel::ValueReader::XLSX

CPAN shell

perl -MCPAN -e shell
install Excel::ValueReader::XLSX

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)