The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
Excel-ValueReader-XLSX-1.03
River stage zero No dependents
/ Excel::ValueReader::XLSX

NAME

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

SYNOPSIS

  my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename);
  # .. or with syntactic sugar :
  my $reader = Excel::ValueReader::XLSX->new($filename);
  # .. or with LibXML backend :
  my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename,
                                             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];
  }

DESCRIPTION

This module reads the contents of an Excel file in XLSX format; given a worksheet name it returns a bidimensional array of values in that worksheet.

Unlike Spreadsheet::ParseXLSX or Spreadsheet::XLSX, there is no support for reading formulas, formats or other Excel internal information; all you get are plain values -- but you get them much faster !

This front module has two different backends for extracting values :

Regex (default)

this backend uses regular expressions to parse the XML content.

LibXML

this backend uses XML::LibXML::Reader to parse the XML content. It is probably safer but about three times slower than the Regex backend (but still much faster than Spreadsheet::ParseXLSX).

METHODS

new

  my $reader = Excel::ValueReader::XLSX->new(xlsx  => $filename,
                                             using => $backend,
                                             %date_formatting_options);

The xlsx argument is mandatory and points to the .xlsx file to be parsed. The using argument is optional; it specifies the backend to be used for parsing; default is 'Regex'.

As syntactic sugar, a shorter form is admitted :

  my $reader = Excel::ValueReader::XLSX->new($filename);

Optional parameters for formatting date and time values 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.

values

  my $grid = $reader->values($sheet);

Returns a bidimensional array 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).

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

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.

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

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 wil 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, \r are silently removed by the XML::LibXML package.

SEE ALSO

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.

The CPAN module Data::XLSX::Parser is claimed to be in alpha stage; it seems to be working but the documentation is insufficient -- I had to inspect the test suite to understand how to use it.

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.

BENCHMARKS

Below are some benchmarks computed with the program benchmark.pl in this distribution. The task was to parse an Excel file of five worksheets with about 62600 rows in total, and report the number of rows per sheet. Reported figures are in seconds.

  Excel::ValueReader::XLSX::Regex    11 elapsed,  10 cpu, 0 system
  Excel::ValueReader::XLSX::LibXML   35 elapsed,  34 cpu, 0 system
  [unpublished] Excel::Reader::XLSX  39 elapsed,  37 cpu, 0 system
  Spreadsheet::ParseXLSX            244 elapsed, 240 cpu, 1 system
  Data::XLSX::Parser                 37 elapsed,  35 cpu, 0 system

These figures show that the regex version is about 3 times faster than the LibXML version, and about 22 times faster than Spreadsheet::ParseXLSX. Tests with a bigger file of about 90000 rows showed similar ratios.

Modules Excel::Reader::XLSX (unpublished) and Data::XLSX::Parser are based on XML::LibXML like Excel::ValueReader::XLSX::Backend::LibXML; execution times for those three modules are very close.

ACKNOWLEDGEMENTS

  • David Flink signaled (and fixed) a bug about strings with embedded newline characters

AUTHOR

Laurent Dami, <dami at cpan.org>

COPYRIGHT AND LICENSE

Copyright 2020, 2021 by Laurent Dami.

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