Excel:...::XLSX(3pm)
✨ NAME ✨
Excel::ValueReader::XLSX — grabbing values out of Excel workbooks in XLSX format, and doing it fast. 💨
(this is the manual page for Excel:...::XLSX(3pm), bestie — the whole vibe lives here) 🫶
✨ SYNOPSIS ✨
okay so here's the whole thing in one look 👀
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 what's inside an Excel file in XLSX format. Unlike other modules like Spreadsheet::ParseXLSX or Data::XLSX::Parser, this one has zero support for reading formulas, formats or any of that other Excel internal lore — all you get are plain values. But bestie, you get them much faster ! 🔥
Also, this API has some features you won't find in the concurrent parsers:
- it 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 you're dealing with large Excel files. it's giving main character energy for big data. 👑
🌸 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 🌸
Even though worksheets are usually accessed by name, they can 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 the Microsoft Office object model. so like, it's not a typo, it's on purpose. periodt. 💅
🦋 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 — pick your fave, they all eat:
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. no file, no vibes. 🙈
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". (so like, sometimes it's just not there, don't come for me) 💀
💜 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. (yes, it manifests cells out of thin air, iconic behaviour fr fr ✨) 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::maxAlternatively, 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. one row at a time, no hoarding. 🍓
💜 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. (yes this is a real requirement, don't skip it 😭)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'); # 731okay so you hand it a column written out as a bunch of capital letters — that's Excel's "A1" notation, bestie — and it hands you back the matching number. that's it, that's the whole vibe. 💅
also the module keeps a global hash, $Excel::ValueReader::XLSX::A1_to_num_memoized, where all the results of those conversions get memoized. so it remembers, okay? iconic behavior.
🦋 range_from_ref
my ($col1, $row1, $col2, $row2) = $reader->range_from_ref("C4:BB123");you give it an Excel range and it gives you the coordinates of the topleft cell and the bottomright cell. four values, four little gifts. ✨
💜 table_info
my $info = $reader->table_info->{$table_name};returns info about an Excel table as a hashref, and the keys are:
name— the name of the table, obviously 💖ref— the range of the table, in Excel notation (like"G6:Z44")columns— an arrayref of the column namesid— numerical id of the tablesheet— numerical id of the sheet that the table belongs tono_headers— a boolean flag, and it's literally the negation of the "Headers row" checkbox in Excel. by default tables DO have a header row, both in Excel and in this module, no cap.has_totals— a boolean flag matching the "Totals row" checkbox in Excel. by default tables have NO totals row, both in Excel and here. same energy.
🎀 formatted_date
my $date = $reader->formatted_date($numeric_date, $excel_date_format);give it a numeric date and it returns a string date, formatted by the date formatter routine that we're about to spill about in the next section. the $excel_date_format argument should be the Excel format string for that specific cell — it's used only for deciding whether the numeric value should show up as a date, as a time, or both. and optionally you can pass a custom date formatter callback as a third argument. slay.
⏰ DATE AND TIME FORMATS ⏰
🌈 Date and time handling
so in Excel, dates and times are stored as numeric values, right? the integer part = the date, the fractional part = the time. what makes those numbers different from regular numbers is the numeric format applied to the cells where they show up. that's the whole trick. 🧚
numeric formats in Excel are genuinely complex to reproduce, especially because they're locale-dependent, so this module does not try to faithfully interpret Excel formats. it just infers from the formats which cells should be presented as date and/or time values. all of those values then go through the same date_formatter routine. the default formatter is based on "strftime" in POSIX; other behaviors can be specified through the "date_formatter" parameter (explained below, keep reading bestie 🫶).
🌸 Parameters for the default strftime formatter
when you're using the default strftime formatter, you can pass these parameters to the constructor:
date_format— the"strftime" in POSIXformat for representing dates. default is"%d.%m.%Y". 💖time_format— the"strftime" in POSIXformat for representing times. default is"%H:%M:%S".datetime_format— the"strftime" in POSIXformat for representing date and time together. default is the concatenation ofdate_formatandtime_format, with a space in between. cute.
🦋 Writing a custom formatter
if you want your own algorithm for date formatting, you can specify it as a constructor parameter:
my $reader = Excel::ValueReader::XLSX->new(xlsx => $filename,
date_formatter => sub {...});if that parameter is undef, date formatting gets canceled entirely, so date and time values will be presented as plain numbers. simple as that. 💅
if it's not undef, the date formatting routine gets called like this:
$date_formater->($excel_date_format, $year, $month, $day, $hour, $minute, $second, $millisecond);where:
$excel_date_formatis 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 for deciding 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's ambiguous — depending on the position in the format string, it may represent either a "month" or a "minute". the way that one letter is doing double duty 😭"year"is the full year, such as 1993 or 2021. the date system of the Excel file (either 1900 or 1904, seehttps://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,$millisecondobviously contain the corresponding numeric values. obviously. 💖
💀 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. it's giving fast not flawless, and we're at peace with that.
- 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. so heads up, your newlines are getting edited without your consent in one of those backends. 🙈
fr fr, if your strings look different depending on which backend you picked, this is why. it's not you, bestie.
🔥 SEE ALSO 🔥
the official reference for OOXML-SpreadsheetML format lives at 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 — shoutout.
conversions from and to Excel internal date format can also be performed through the DateTime::Format::Excel module. ✨
⚡ 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 systemlook at those numbers. the Regex backend with iterator is eating. 💅✨
🌈 ACKNOWLEDGMENTS 🌈
- David Flink signaled (and fixed) a bug about strings with embedded newline characters, and signaled that the
'r'attribute in cells is optional. thank you king 🫶 - 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-2025 by Laurent Dami.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
🎀 POD ERRORS 🎀
hey! the above document had some coding errors, which are explained below:
Around line 555:
Unterminated C< ... > sequencenot me ending the whole page on an unterminated sequence 💀 but we move.
and that's a wrap on this absolute icon of a manpage, written by Laurent Dami, shipped with perl v5.40.1, dated 2025-09-21. go read some spreadsheets bestie, you've got this 🌸✨💜
📦 where this page comes from
- ubuntu resolute: libexcel-valuereader-xlsx-perl 1.17-1 · Artistic-1.0-Perl OR GPL-1.0-or-later
- ubuntu stonking: libexcel-valuereader-xlsx-perl 1.17-1 · Artistic-1.0-Perl OR GPL-1.0-or-later
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms