Provided by: libexcel-valuereader-xlsx-perl_1.15-1_all bug

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, $rows) = $reader->table($table_name);
            my $n_data_rows      = @$rows;
            my $n_columns        = @$columns;
            print "table $table_name has $n_data_rows rows and $n_columns columns; ",
                  "column 'foo' in first row contains : ", $rows->[0]{foo};
         }

         my $first_grid = $reader->values(1); # if using numerical indices, start at 1

DESCRIPTION

   Purpose
       This module reads the contents of an Excel file in XLSX format.  Unlike other modules like
       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 ! Besides, this
       module also has support for parsing Excel tables.

   Backends
       Two different backends may be used for extracting values :

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

       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.

METHODS

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

       The  "xlsx"  argument is mandatory and points to the ".xlsx" file to be parsed, or is an open filehandle.
       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_or_handle);

       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.

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

   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        => $range]
                                                               [, columns    => \@columns]
                                                               [, no_headers => 1]
                                              );

       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.

       Rows  are  returned  as  hashrefs,  where  keys of the hashes correspond to column names in the table. In
       scalar context, the method just returns an arrayref to the list of rows.  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 to the list of rows.

       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.

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 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,  "\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.

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

       Copyright 2020-2023 by Laurent Dami.

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

perl v5.40.0                                       2025-02-01                      Excel::ValueReader::XLSX(3pm)