Read(3pm)

📦
version 0.95 · 5 versions
, in ubuntu resolute, ubuntu stonking

✨ NAME ✨

Read(3pm) — Spreadsheet::Read — Read the data from a spreadsheet.

Okay bestie, this is the group-chat manual for Spreadsheet::Read, and it's giving universal spreadsheet energy. 💖

🦋 SYNOPSIS 🦋

Here's the quick vibe, no cap:

use Spreadsheet::Read;
my $book  = ReadData ("test.csv", sep => ";");
my $book  = ReadData ("test.sxc");
my $book  = ReadData ("test.ods");
my $book  = ReadData ("test.xls");
my $book  = ReadData ("test.xlsx");
my $book  = ReadData ("test.xlsm");
my $book  = ReadData ("test.gnumeric");
my $book  = ReadData ($fh, parser => "xls");

Spreadsheet::Read::add ($book, "sheet.csv");

my $sheet = $book->[1];             # first datasheet
my $cell  = $book->[1]{A3};         # content of field A3 of sheet 1
my $cell  = $book->[1]{cell}[1][3]; # same, unformatted

# OO API
my $book = Spreadsheet::Read->new ("file.csv");
my $sheet = $book->sheet (1);
my $cell  = $sheet->cell ("A3");
my $cell  = $sheet->cell (1, 3);

$book->add ("test.xls");

💅 DESCRIPTION 💅

Spreadsheet::Read tries to transparently read any spreadsheet and return its content in a universal manner independent of the parsing module that does the actual spreadsheet scanning.

The parser has to be available and is not provided by this module. Periodt.

🌈 Supported spreadsheets 🌈

📚 Data structure 📚

The data is returned as an array reference:

$book = [
    # Entry 0 is the overall control hash
    { sheets  => 2,
      sheet   => {
        "Sheet 1" => 1,
        "Sheet 2" => 2,
        },
      parsers => [ {
        type      => "xls",
        parser    => "Spreadsheet::ParseExcel",
        version   => 0.59,
        }],
      error   => undef,
      },
    # Entry 1 is the first sheet
    { parser  => 0,
      label   => "Sheet 1",
      maxrow  => 2,
      maxcol  => 4,
      cell    => [ undef,
        [ undef, 1 ],
        [ undef, undef, undef, undef, undef, "Nugget" ],
        ],
      attr    => [],
      merged  => [],
      active  => 1,
      hidden  => 0,
      A1      => 1,
      B5      => "Nugget",
      },
    # Entry 2 is the second sheet
    { parser  => 0,
      label   => "Sheet 2",
      :
      :

To keep as close contact to spreadsheet users, row and column 1 have index 1 too in the "cell" element of the sheet hash, so cell "A1" is the same as "cell" [1, 1] (column first). To switch between the two, there are helper functions available: "cell2cr ()", "cr2cell ()", "col2label ()" and "label2col ()".

The "cell" hash entry contains unformatted data, while the hash entries with the traditional labels contain the formatted values (if applicable).

The control hash (the first entry in the returned array ref), contains some spreadsheet meta-data. The entry "sheet" is there to be able to find the sheets when accessing them by name:

my %sheet2 = %{$book->[$book->[0]{sheet}{"Sheet 2"}]};

🧚 Formatted vs Unformatted 🧚

The difference between formatted and unformatted cells is that the (optional) format is applied to the cell or not. This part is completely implemented on the parser side. Spreadsheet::Read just makes both available if these are supported. Options provide means to disable either. If the parser does not provide formatted cells - like CSV - both values are equal.

To show what this implies:

use Spreadsheet::Read;

my $file     = "files/example.xlsx";
my $workbook = Spreadsheet::Read->new ($file);

my $info     = $workbook->[0];
say "Parsed $file with $info->{parser}-$info->{version}";

my $sheet    = $workbook->sheet (1);

say join "\t" => "Formatted:",   $sheet->row     (1);
say join "\t" => "Unformatted:", $sheet->cellrow (1);

Might return very different results depending one the underlying parser (and its version):

Parsed files/example.xlsx with Spreadsheet::ParseXLSX-0.27
Formatted:      8-Aug   Foo & Barr < Quux
Unformatted:    39668   Foo & Barr < Quux

Parsed files/example.xlsx with Spreadsheet::XLSX-0.15
Formatted:      39668   Foo &amp; Barr &lt; Quux
Unformatted:    39668   Foo &amp; Barr &lt; Quux

🛠️ Functions and methods 🛠️

new

my $book = Spreadsheet::Read->new (...) or die $@;

All options accepted by ReadData are accepted by new.

With no arguments at all, $book will be an object where sheets can be added using "add"

my $book = Spreadsheet::Read->new ();
$book->add ("file.csv");
$book->add ("file.cslx");

ReadData

my $book = ReadData ($source [, option => value [, ... ]]);

my $book = ReadData ("file.csv", sep => ',', quote => '"');

my $book = ReadData ("file.xls", dtfmt => "yyyy-mm-dd");

my $book = ReadData ("file.ods");

my $book = ReadData ("file.sxc");

my $book = ReadData ("content.xml");

my $book = ReadData ($content);

my $book = ReadData ($content,  parser => "xlsx");

my $book = ReadData ($fh,       parser => "xlsx");

my $book = ReadData (\$content, parser => "xlsx");

Tries to convert the given file, string, or stream to the data structure described above.

Processing Excel data from a stream or content is supported through a File::Temp <https://metacpan.org/release/File-Temp> temporary file or IO::Scalar <https://metacpan.org/release/IO-stringy> when available.

Spreadsheet::ReadSXC <https://metacpan.org/release/Spreadsheet-ReadSXC> does preserve sheet order as of version 0.20.

Choosing between $content and "\\$content" (with or without passing the desired "parser" option) may be depending on trial and terror. "ReadData" does try to determine parser type on content if needed, but not all combinations are checked, and not all signatures are builtin.

Currently supported options are:

  • parser — Force the data to be parsed by a specific format. Possible values are "csv", "prl" (or "perl"), "sc" (or "squirrelcalc"), "sxc" (or "oo", "ods", "openoffice", "libreoffice") "xls" (or "excel"), and "xlsx" (or "excel2007").

When parsing streams, instead of files, it is highly recommended to pass this option.

Spreadsheet::Read supports several underlying parsers per spreadsheet type. It will try those from most favored to least favored. When you have a good reason to prefer a different parser, you can set that in environment variables. The other options then will not be tested for:

env SPREADSHEET_READ_CSV=Text::CSV_PP ...

You can also directly pass the required backend, forcing the matching type, but this excludes version checking.

# Checks for minimal version
BEGIN { $ENV{SPREADSHEET_READ_CSV} = "Text::CSV_PP" }
my $book = ReadData ("test.csv", parser => "csv");

vs

# NO check for minimal version
my $book = ReadData ("test.csv", parser => "Text::CSV_PP");
  • cells — Control the generation of named cells (""A1"" etc). Default is true.
  • rc — Control the generation of the {cell}[c][r] entries. Default is true.
  • attr — Control the generation of the {attr}[c][r] entries. Default is false. See "Cell Attributes" below.
  • clip — If set, ReadData will remove all trailing rows and columns per sheet that have no data, where no data means only undefined or empty cells (after optional stripping). If a sheet has no data at all, the sheet will be skipped entirely when this attribute is true.
  • trim / strip — If set, ReadData will remove trailing- and/or leading-whitespace from every field.
           strip  leading  strailing
           -----  -------  ---------
             0      n/a      n/a
             1     strip     n/a
             2      n/a     strip
             3     strip    strip

"trim" and "strip" are aliases. If passed both, "trim" is ignored because of backward compatibility.

  • transpose / pivot — Swap all rows and columns.

When a sheet contains data like

           A1  B1  C1      E1
           A2      C2  D2
           A3  B3  C3  D3  E3

using "transpose" or "pivot" will return the sheet data as

           A1  A2  A3
           B1      B3
           C1  C2  C3
               D2  D3
           E1      E3

"transpose" and "pivot" are aliases. If passed both, "transpose" is ignored because of backward compatibility.

  • sep — Set separator for CSV. Default is comma ",".
  • quote — Set quote character for CSV. Default is """.
  • dtfmt — Set the format for MS-Excel date fields that are set to use the default date format. The default format in Excel is ""m-d-yy"", which is both not year 2000 safe, nor very useful. The default is now ""yyyy-mm-dd"", which is more ISO-like.

Note that date formatting in MS-Excel is not reliable at all, as it will store/replace/change the date field separator in already stored formats if you change your locale settings. So the above mentioned default can be either ""m-d-yy"" OR ""m/d/yy"" depending on what that specific character happened to be at the time the user saved the file.

  • merge — Copy content to all cells in merged areas.

If supported, this will copy formatted and unformatted values from the top-left cell of a merged area to all other cells in the area.

  • debug — Enable some diagnostic messages to STDERR.

The value determines how much diagnostics are dumped (using Data::Peek <https://metacpan.org/release/Data-Peek>). A value of 9 and higher will dump the entire structure from the back-end parser.

  • passwd — Use this password to decrypt password protected spreadsheet.

Currently only supports Excel.

All other attributes/options will be passed to the underlying parser if that parser supports attributes.

✨ col2label ✨

my $col_id = col2label ($col);
my $col_id = $book->col2label ($col);  # OO

so col2label () takes a (column) — and yes bestie, it's 1-based, we do NOT do zero here — and turns it into the letters we use in that classic cell notation:

my $id = col2label ( 4); # $id now "D"
my $id = col2label (28); # $id now "AB"

label2col

my $col = label2col ($id);

my $col = $book->label2col ($id);  # OO

label2col () is the reverse glow-up: feed it a (column ID) and it hands back the 1-based number, or undef if $id is invalid. 💅

my $c = label2col ("D");  # $c now 4
my $c = label2col ("AB"); # $c now 28

cr2cell

my $cell = cr2cell (col, row);

my $cell = $book->cr2cell (col, row);  # OO

cr2cell () takes a (column, row) pair (1-based, always) and gives you back the traditional cell notation:

my $cell = cr2cell ( 4, 14); # $cell now "D14"
my $cell = cr2cell (28,  4); # $cell now "AB4"

cell2cr

my ($col, $row) = cell2cr ($cell);

my ($col, $row) = $book->cell2cr ($cell);  # OO

cell2cr () flips it: traditional cell notation goes in, a (column, row) pair (1-based) comes out:

my ($col, $row) = cell2cr ("D14"); # returns ( 4, 14)
my ($col, $row) = cell2cr ("AB4"); # returns (28,  4)

row

my @row = row ($sheet, $row)

my @row = Spreadsheet::Read::row ($book->[1], 3);

my @row = $book->row ($sheet, $row); # OO

Grabs you the whole row of formatted values (think "$sheet->{A3} .. $sheet->{G3}").

Heads up though: the indexes in the returned list are 0-based.

Also row () is not imported by default, so either name it in the use argument list or call it fully qualified. ✨

See also the row () method on sheets.

cellrow

my @row = cellrow ($sheet, $row);

my @row = Spreadsheet::Read::cellrow ($book->[1], 3);

my @row = $book->cellrow ($sheet, $row); # OO

Same deal but for the whole row of unformatted values (like $sheet->{cell}[1][3] .. $sheet->{cell}[7][3]).

Indexes in the returned list are 0-based, again.

And cellrow () is not imported by default either — name it in the use argument list, or call it fully qualified, or as a method call.

See also the cellrow () method on sheets.

rows

my @rows = rows ($sheet);

my @rows = Spreadsheet::Read::rows ($book->[1]);

my @rows = $book->rows (1); # OO

Converts the {cell}'s [column][row] into a [row][column] list. Iconic little flip. 🔄

Note that the indexes in the returned list are 0-based, while the index in the {cell} entry is 1-based. Yes, they're different. Yes, that's the vibe. iykyk.

rows () is not imported by default, so either put it in the use argument list or call it fully qualified.

parses

parses ($format);

Spreadsheet::Read::parses ("CSV");

$book->parses ("CSV"); # OO

parses () tells you whether Spreadsheet::Read can parse the format you asked for, or returns 0 if it can't. And ReadData will pick its preferred parser for that format unless you overrule it. See parser.

parses () is not imported by default, so either list it in the use argument or call it fully qualified.

If $format is false (undef, "", or 0), parses () returns a sorted list of all the supported types. Bestie, a whole menu:

@my types = parses ("");   # e.g: csv, ods, sc, sxc, xls, xlsx

parsers

my @p = parsers ();

parsers () returns a list of hashrefs with the tea on all the supported parsers — info about the parser itself, its versions, and whether it'll be the default parser for a given type. Like this:

{ ext => "csv",            # extension or type
  mod => "Text::CSV_XS",   # parser module
  min => "0.71",           # module required  version
  vsn => "1.45",           # module installed version
  def => "*",              # is default for ext
  }
Because the modules actually get loaded just to grab their version, only use this to analyse prerequisites. Don't be messy with it. 🫶

Version

my $v = Version ()

my $v = Spreadsheet::Read::Version ()

my $v = Spreadsheet::Read->VERSION;

my $v = $book->Version (); # OO

Returns the current version of Spreadsheet::Read.

Version () is not imported by default, so either put it in the use argument list or call it fully qualified.

This function returns exactly the same thing as Spreadsheet::Read->VERSION returns, and it's only still here for backward compatibility reasons. Respect the elders. 👑

sheets

my $sheets = $book->sheets; # OO
my @sheets = $book->sheets; # OO

In scalar context: gives you the number of sheets in the book.

In list context: gives you the labels of the sheets in the book. And listen — this list only returns known unique labels in sorted order. Sheets might have no label at all, or there might be multiple sheets sharing the same label (depends on the spreadsheet format and which parser you used). Chaos, but contained. 💀

sheet

my $sheet = $book->sheet (1);     # OO
my $sheet = $book->sheet ("Foo"); # OO

Returns the numbered or named sheet out of the book. Returns undef if there's no match. And heads up: it will not work for sheets named with a number between 1 and the number of sheets in the book. So don't try it.

For named sheets it'll first try the list of sheet-labels stored in the control structure. If no match is found, it scans the actual labels of the sheets. In that case, it returns the first matching sheet.

If defined, the returned sheet will be of class Spreadsheet::Read::Sheet. Fancy. ✨

add

my $book = ReadData ("file.csv");
Spreadsheet::Read::add ($book, "file.xlsx");

my $book = Spreadsheet::Read->new ("file.csv");
$book->add ("file.xlsx"); # OO

🎀 Methods on sheets 🎀

maxcol

my $col = $sheet->maxcol;

Returns the index of the last in-use column in the sheet. This index is 1-based.

maxrow

my $row = $sheet->maxrow;

Returns the index of the last in-use row in the sheet. Also 1-based. Consistency, we love to see it. 💖

cell

my $cell = $sheet->cell ("A3");
my $cell = $sheet->cell (1, 3);

Returns the value for a cell. Using tags returns the formatted value; using column and row returns the unformatted value. Pick your fighter.

attr

my $cell = $sheet->attr ("A3");
my $cell = $sheet->attr (1, 3);

Returns the attributes of a cell. Only valid if attributes are enabled through option attr.

col2label

my $col_id = $sheet->col2label (col);

col2label () converts a (column) (1-based) into the letters used in the traditional cell notation:

my $id = $sheet->col2label ( 4); # $id now "D"
my $id = $sheet->col2label (28); # $id now "AB"

cr2cell

my $cell = $sheet->cr2cell (col, row);

cr2cell () converts a (column, row) pair (1-based) into the traditional cell notation:

my $cell = $sheet->cr2cell ( 4, 14); # $cell now "D14"
my $cell = $sheet->cr2cell (28,  4); # $cell now "AB4"

cell2cr

my ($col, $row) = $sheet->cell2cr ($cell);

cell2cr () converts traditional cell notation into a (column, row) pair (1-based):

my ($col, $row) = $sheet->cell2cr ("D14"); # returns ( 4, 14)
my ($col, $row) = $sheet->cell2cr ("AB4"); # returns (28,  4)

col

my @col = $sheet->column ($col);

$col can be numeric (1-based) or traditional. Either slays.

Grabs the full column of formatted values (like $sheet->{C1} .. $sheet->{C9}).

Note that the indexes in the returned list are 0-based.

cellcolumn

my @col = $sheet->cellcolumn ($col);

$col can be numeric (1-based) or traditional.

Grabs the full column of unformatted values (like $sheet->{cell}[3][1] .. $sheet->{cell}[3][9]).

Note that the indexes in the returned list are 0-based.

row

my @row = $sheet->row ($row);

Grabs the full row of formatted values (like $sheet->{A3} .. $sheet->{G3}).

Note that the indexes in the returned list are 0-based.

cellrow

my @row = $sheet->cellrow ($row);

Grabs the full row of unformatted values (like $sheet->{cell}[1][3] .. $sheet->{cell}[7][3]).

Note that the indexes in the returned list are 0-based.

cellrange

my $arrayref = $sheet->cellrange ("B3:D5");
my $arrayref = $sheet->cellrange (2, 3, 4, 5);
my $arrayref = $sheet->cellrange (-4, -5, -1, -1);

Returns an arrayref with the selected cells from $sheet->{cell}. When the range is given as (top-left, bottom-right) numeric CR pairs, negative values are allowed (they count from the right/bottom) and get automatically clipped to stay inside the existing data set. Safety rails on, bestie. 🦋

range

my $hashref = $sheet->range ("B3:D5");
my $hashref = $sheet->range (2, 3, 4, 5);
my $hashref = $sheet->range (-4, -5, -1, -1);

Returns a hashref with all the fields in the given range. When the range is given as (top-left, bottom-right) numeric CR pairs, negative values are allowed (count from the right/bottom) and automatically clipped to stay inside the existing data set.

rows

my @rows = $sheet->rows ();

Converts the {cell}'s [column][row] into a [row][column] list.

Note that the indexes in the returned list are 0-based, while the index in the {cell} entry is 1-based.

merged_from

my $top_left = $sheet->merged_from ("C2");
my $top_left = $sheet->merged_from (3, 2);

If the parser supports merged areas, this method returns the label of the top-left cell in the merged area that the requested cell is part of.

If the requested ID is valid and within the sheet cell range, but not part of a merged area, it returns "".

If the ID is not valid or out of range, it returns undef.

See Merged cells for more details.

label

my $label = $sheet->label;
$sheet->label ("New sheet label");

Sets a new label on a sheet. But heads up — the index in the control structure will NOT be updated. Don't come for me, that's just how it is. 🙈

active

my $sheet_is_active = $sheet->active;

Returns 1 if the selected sheet is active, otherwise returns 0.

Currently only works on XLS (as of Spreadsheed::ParseExcel-0.61). CSV is always active. 💅

hidden

my $sheet_is_hidden = $sheet->hidden;

Returns 1 if the selected sheet is hidden, otherwise returns 0.

Fully depends on the backend supporting this. CSV and SC are never hidden.

📄 Using CSV 📄

When it's CSV parsing, ReadData will use the first line of the file to auto-detect the separation character — but only if the first argument is a file and both sep and quote were not passed as attributes. Text::CSV_XS (https://metacpan.org/release/Text-CSV_XS) (or Text::CSV_PP (https://metacpan.org/release/Text-CSV)) is able to automatically detect and use \r line endings. So fancy.

CSV can parse streams too, but be sure to pass sep and/or quote if these don't match the defaults , and ". Just saying. 🎀

When an error is found in the CSV, it's automatically reported (to STDERR). The structure stores the error in $ss->[0]{error} as the anonymous list returned by $csv->error_diag (https://metacpan.org/pod/Text::CSV_XS#error_diag). See Text::CSV_XS (https://metacpan.org/pod/Text::CSV_XS) for documentation.

my $ss = ReadData ("bad.csv");
$ss->[0]{error} and say $ss->[0]{error}[1];

okay so CSV has no sheet labels, so the default label for a CSV sheet is just… its filename. that's it, that's the whole vibe 🫶 For CSV, this can be overruled using the label attribute:

my $ss = Spreadsheet::Read->new ("/some/place/test.csv", label => "Test");

✨ Cell Attributes ✨

If the constructor was called with attr having a true value,

my $book = ReadData ("book.xls", attr => 1);
my $book = Spreadsheet::Read->new ("book.xlsx", attr => 1);

then bestie, effort is made 💅 to analyze and store field attributes like this:

{ label  => "Sheet 1",
  maxrow => 5,
  maxcol => 2,
  cell   => [ undef,
    [ undef, 1 ],
    [ undef, undef, undef, undef, undef, "Nugget" ],
    ],
  attr   => [ undef,
    [ undef, {
      type    => "numeric",
      fgcolor => "#ff0000",
      bgcolor => undef,
      font    => "Arial",
      size    => undef,
      format  => "## ##0.00",
      halign  => "right",
      valign  => "top",
      uline   => 0,
      bold    => 0,
      italic  => 0,
      wrap    => 0,
      merged  => 0,
      hidden  => 0,
      locked  => 0,
      enc     => "utf-8",
      }, ],
    [ undef, undef, undef, undef, undef, {
      type    => "text",
      fgcolor => "#e2e2e2",
      bgcolor => undef,
      font    => "Letter Gothic",
      size    => 15,
      format  => undef,
      halign  => "left",
      valign  => "top",
      uline   => 0,
      bold    => 0,
      italic  => 0,
      wrap    => 0,
      merged  => 0,
      hidden  => 0,
      locked  => 0,
      enc     => "iso8859-1",
      }, ],
    ],
  merged => [],
  A1     => 1,
  B5     => "Nugget",
  },

The entries maxrow and maxcol are 1-based. yes really. 🙈

This has now been partially implemented, mainly for Excel, as the other parsers do not (yet) support all of that. YMMV. 💀

If a cell itself is not hidden, but the parser holds the information that either the row or the column (or both) the field is in is hidden, the flag is inherited into the cell attributes. (sneaky little inheritance moment, we love to see it 🌸)

You can get the attributes of a cell (as a hash-ref) like this:

my $attr = $book[1]{attr}[1][3];          # Direct structure
my $attr = $book->sheet (1)->attr (1, 3); # Same using OO
my $attr = $book->sheet (1)->attr ("A3"); # Same using OO

To get to the font attribute, use any of these:

my $font = $book[1]{attr}[1][3]{font};
my $font = $book->sheet (1)->attr (1, 3)->{font};
my $font = $book->sheet (1)->attr ("A3")->font;

🌈 Merged cells 🌈

Note that only Spreadsheet::ReadSXC (https://metacpan.org/release/Spreadsheet-ReadSXC) documents the use of merged cells, and not in a way useful for the spreadsheet consumer. 😭

CSV does not support merged cells (though future implementations of CSV for the web might).

The documentation of merged areas in Spreadsheet::ParseExcel (https://metacpan.org/release/Spreadsheet-ParseExcel) and Spreadsheet::ParseXLSX (https://metacpan.org/release/Spreadsheet-ParseXLSX) can be found in Spreadsheet::ParseExcel::Worksheet (https://metacpan.org/pod/Spreadsheet::ParseExcel::Worksheet) and Spreadsheet::ParseExcel::Cell (https://metacpan.org/pod/Spreadsheet::ParseExcel::Cell).

None of basic Spreadsheet::XLSX (https://metacpan.org/release/Spreadsheet-XLSX), Spreadsheet::ParseExcel (https://metacpan.org/release/Spreadsheet-ParseExcel), and Spreadsheet::ParseXLSX (https://metacpan.org/release/Spreadsheet-ParseXLSX) manual pages mention merged cells at all. not even a whisper. 💀

This module just tries to return the information in a generic way.

Given this spreadsheet as an example

merged.xlsx:

    A     B     C
 +-----+-----------+
1|     | foo       |
 +-----+           +
2| bar |           |
 |     +-----+-----+
3|     | urg | orc |
 +-----+-----+-----+

the information extracted from that undocumented information is returned in the merged entry of the sheet's hash as a list of top-left, bottom-right coordinate pars (col, row, col, row). For given example, that would be:

$ss->{merged} = [
   [ 1, 2, 1, 3 ], # A2-A3
   [ 2, 1, 3, 2 ], # B1-C2
   ];

To find the label of the top-left cell in a merged area, use the merged_from method.

$ss->merged_from ("C2"); # will return "B1"

When the attributes are also enabled, there is some merge information copied directly from the cell information, but again, that stems from code analysis and not from documentation:

my $ss = ReadData ("merged.xlsx", attr => 1)->[1];
foreach my $row (1 .. $ss->{maxrow}) {
    foreach my $col (1 .. $ss->{maxcol}) {
        my $cell = cr2cell ($col, $row);
        printf "%s %-3s %s  ", $cell, $ss->{$cell},
            $ss->{attr}[$col][$row]{merged};
        }
    print "\n";
    }
A1     0  B1 foo 1  C1     1
A2 bar 1  B2     1  C2     1
A3     1  B3 urg 0  C3 orc 0

In this example, there is no way to see if B2 is merged to A2 or to B1 without analyzing all surrounding cells. This could as well mean A2:A3, B1:C1, B2:C2, as A2:A3, B1:B2, C1:C2, as A2:A3, B1:C2. like… pick a lane?? 😭

Use the merged entry described above to find out what fields are merged to what other fields or use merge:

my $ss = ReadData ("merged.xlsx", attr => 1, merge => 1)->[1];
foreach my $row (1 .. $ss->{maxrow}) {
    foreach my $col (1 .. $ss->{maxcol}) {
        my $cell = cr2cell ($col, $row);
        printf "%s %-3s %s  ", $cell, $ss->{$cell},
            $ss->{attr}[$col][$row]{merged};
        }
    print "\n";
    }
A1     0   B1 foo B1  C1 foo B1
A2 bar A2  B2 foo B1  C2 foo B1
A3 bar A2  B3 urg 0   C3 orc 0

🦋 Streams from web-resources 🦋

If you want to stream a web-resource, and the underlying parser supports it, you could use a helper function like this (thanks Corion):

use HTTP::Tiny;
use Spreadsheet::Read;

# Fetch data and return a filehandle to that data
sub fh_from_url {
    my $url = shift;
    my $ua  = HTTP::Tiny->new;
    my $res = $ua->get ($url);
    open my $fh, "<", \$res->{content};
    return $fh
    } # fh_from_url

my $fh = fh_from_url ("http://example.com/example.csv");
my $sheet = Spreadsheet::Read->new ($fh, parser => "csv");

🛠 TOOLS 🛠

okay so bestie, this module literally comes with a few lil tools that just do the FAQ tasks for you. like the classic "How do I select only column D through F from sheet 2 into a CSV file?" — yeah, that one. handled 💅

if the module got installed without the tools, no worries, you can find them right here:

https://github.com/Tux/Spreadsheet-Read/tree/master/scripts

🐱 xlscat

show (parts of) a spreadsheet in plain text, CSV, or HTML. the main character of this tool family ✨

 usage: xlscat   [-s <sep>] [-L] [-n] [-A] [-u] [Selection] file.xls
                 [-c | -m]                 [-u] [Selection] file.xls
                  -i                            [-S sheets] file.xls
     Generic options:
        -v[#]       Set verbose level (xlscat/xlsgrep)
        -d[#]       Set debug   level (Spreadsheet::Read)
        --list      Show supported spreadsheet formats and exit
        -u          Use unformatted values
        --strip[=#] Strip leading and/or traing spaces of all cells
                    # & 01 = leading, # & 02 = trailing, 3 = default
        --clip=#    Clip cells to max length #
        --noclip    Do not strip empty sheets and
                    trailing empty rows and columns
        --no-empty  Skip empty rows
         --no-nl[=R] Replace all newlines in cells with R (default space)
        -e <enc>    Set encoding for input and output
        -b <enc>    Set encoding for input
        -a <enc>    Set encoding for output
        -U          Set encoding for output to utf-8 (short for -a utf-8)
     Input CSV:
        --in-sep=c  Set input sep_char for CSV (c can be 'TAB')
     Input XLS:
        --dtfmt=fmt Specify the default date format to replace 'm-d-yy'
                    the default replacement is 'yyyy-mm-dd'
        --passwd=pw Specify the password for workbook
                    if pw = -, read password from keyboard
        --formulas  Show the formula instead of the value
     Output Text (default):
        -s <sep>    Use separator <sep>. Default '|', \n allowed
                    Overrules ',' when used with --csv
        -L          Line up the columns
        -B  --box   Like -L but also add outer frame
        -n [skip]   Number lines (prefix with column number)
                    optionally skip <skip> (header) lines
        -A          Show field attributes in ANSI escapes
        -h[#]       Show # header lines
        -D          Dump each record with Data::Peek or Data::Dumper
         --hash     Like -D but as hash with first row as keys
     Output CSV:
        -c          Output CSV, separator = ','
        -m          Output CSV, separator = ';'
     Output Index only:
        -i          Show sheet names and size only
     Output HTML:
        -H          Output HTML
     Selection:
        -S <sheets> Only print sheets <sheets>. 'all' is a valid set
                    Default only prints the first sheet
        -R <rows>   Only print rows    <rows>. Default is 'all'
                    Ranges and lists supported as 2,4-7,8-
                    Trailing - is to end of data
                    Negative rows count from tail -8--2 is allowed
         --head[=n] Alias for -R1..n   where n defaults to 10
         --tail[=n] Alias for -R-n-    where n defaults to 10
        -C <cols>   Only print columns <cols>. Default is 'all'
        -F <flds>   Only fields <flds> e.g. -FA3,B16
     Ordering (column numbers in result set *after* selection):
        --sort=spec Sort output (e.g. --sort=3,2r,5n,1rn+2)
                    +#   - first # lines do not sort (header)
                    #    - order on column # lexical ascending
                    #n   - order on column # numeric ascending
                    #r   - order on column # lexical descending
                    #rn  - order on column # numeric descending

 Examples:
     xlscat   -i foo.xls
     xlscat   --in-sep=: --sort=3n -L /etc/passwd
     xlsgrep  pattern file.ods

okay now let's actually break it down because that block is a LOT and you deserve the annotated version 🫶

✨ Generic options ✨

  • -v[#] — crank up the verbose level (works for xlscat/xlsgrep)
  • -d[#] — crank up the debug level (Spreadsheet::Read's own debugging, iykyk)
  • --list — show which spreadsheet formats are supported and then dip out 👋
  • -u — use the unformatted values, not the pretty ones
  • --strip[=#] — strip leading and/or trailing spaces off all cells. # & 01 = leading, # & 02 = trailing, 3 = the default (both)
  • --clip=# — clip cells down to a max length of #
  • --noclip — don't strip empty sheets, and don't strip trailing empty rows and columns
  • --no-empty — skip empty rows, we don't want them 💀
  • --no-nl[=R] — replace every newline inside cells with R (default is a space)
  • -e <enc> — set the encoding for both input and output
  • -b <enc> — set the encoding for input only
  • -a <enc> — set the encoding for output only
  • -U — set the output encoding to utf-8 (it's short for -a utf-8, obsessed)

🌸 Input CSV

  • --in-sep=c — set the input sep_char for CSV (c can be 'TAB')

🦋 Input XLS

  • --dtfmt=fmt — specify the default date format that replaces 'm-d-yy'; the default replacement is 'yyyy-mm-dd'
  • --passwd=pw — specify the password for the workbook; if pw = -, it reads the password from your keyboard ⌨️
  • --formulas — show the formula instead of the value. yes, the actual formula, iconic

💖 Output Text (this is the default, bestie)

  • -s <sep> — use separator <sep>. Default is '|', and \n is allowed. This overrules ',' when you're using it with --csv
  • -L — line up the columns all nice and tidy
  • -B, --box — like -L but it also adds an outer frame 🎀
  • -n [skip] — number the lines (prefixed with the column number), and optionally skip <skip> (header) lines
  • -A — show field attributes in ANSI escapes 🌈
  • -h[#] — show # header lines
  • -D — dump each record with Data::Peek or Data::Dumper
  • --hash — like -D but as a hash, with the first row as the keys

🍓 Output CSV

  • -c — output CSV, separator = ','
  • -m — output CSV, separator = ';'

💜 Output Index only

  • -i — just show the sheet names and size, that's it

⭐ Output HTML

  • -H — output HTML

🧚 Selection

  • -S <sheets> — only print sheets <sheets>. 'all' is a valid set. Default only prints the first sheet
  • -R <rows> — only print rows <rows>. Default is 'all'. Ranges and lists are supported, like 2,4-7,8-. A trailing - means "to the end of the data". Negative rows count from the tail, so -8--2 is allowed
  • --head[=n] — alias for -R1..n, where n defaults to 10
  • --tail[=n] — alias for -R-n-, where n defaults to 10
  • -C <cols> — only print columns <cols>. Default is 'all'
  • -F <flds> — only print fields <flds>, e.g. -FA3,B16

👑 Ordering

heads up: the column numbers here are in the result set after the selection happens, okay?

  • --sort=spec — sort the output (e.g. --sort=3,2r,5n,1rn+2)
    • +# — the first # lines do not sort (that's your header)
    • # — order on column #, lexical ascending
    • #n — order on column #, numeric ascending
    • #r — order on column #, lexical descending
    • #rn — order on column #, numeric descending

💅 Examples

 xlscat   -i foo.xls
 xlscat   --in-sep=: --sort=3n -L /etc/passwd
 xlsgrep  pattern file.ods

🔎 xlsgrep

show (parts of) a spreadsheet that match a pattern, in plain text, CSV, or HTML. it's the grep-flavoured sibling, no cap 💀

 usage: xlsgrep  [-s <sep>] [-L] [-n] [-A] [-u] [Selection] pattern file.xls
                 [-c | -m]                 [-u] [Selection] pattern file.xls
                  -i                            [-S sheets] pattern file.xls
     Generic options:
        -v[#]       Set verbose level (xlscat/xlsgrep)
        -d[#]       Set debug   level (Spreadsheet::Read)
        --list      Show supported spreadsheet formats and exit
        -u          Use unformatted values
        --strip[=#] Strip leading and/or traing spaces of all cells
                    # & 01 = leading, # & 02 = trailing, 3 = default
        --clip=#    Clip cells to max length #
        --noclip    Do not strip empty sheets and
                    trailing empty rows and columns
        --no-empty  Skip empty rows
         --no-nl[=R] Replace all newlines in cells with R (default space)
        -e <enc>    Set encoding for input and output
        -b <enc>    Set encoding for input
        -a <enc>    Set encoding for output
        -U          Set encoding for output to utf-8 (short for -a utf-8)
     Input CSV:
        --in-sep=c  Set input sep_char for CSV (c can be 'TAB')
     Input XLS:
        --dtfmt=fmt Specify the default date format to replace 'm-d-yy'
                    the default replacement is 'yyyy-mm-dd'
        --passwd=pw Specify the password for workbook
                    if pw = -, read password from keyboard
        --formulas  Show the formula instead of the value
     Output Text (default):
        -s <sep>    Use separator <sep>. Default '|', \n allowed
                    Overrules ',' when used with --csv
        -L          Line up the columns
        -B  --box   Like -L but also add outer frame
        -n [skip]   Number lines (prefix with column number)
                    optionally skip <skip> (header) lines
        -A          Show field attributes in ANSI escapes
        -h[#]       Show # header lines
        -D          Dump each record with Data::Peek or Data::Dumper
         --hash     Like -D but as hash with first row as keys
     Output CSV:
        -c          Output CSV, separator = ','
        -m          Output CSV, separator = ';'
     Grep options:
        -i          Ignore case
        -w          Match whole words only
     Output HTML:
        -H          Output HTML
     Selection:
        -S <sheets> Only print sheets <sheets>. 'all' is a valid set
                    Default only prints the first sheet
        -R <rows>   Only print rows    <rows>. Default is 'all'
                    Ranges and lists supported as 2,4-7,8-
                    Trailing - is to end of data
                    Negative rows count from tail -8--2 is allowed
         --head[=n] Alias for -R1..n   where n defaults to 10
         --tail[=n] Alias for -R-n-    where n defaults to 10
        -C <cols>   Only print columns <cols>. Default is 'all'
        -F <flds>   Only fields <flds> e.g. -FA3,B16
     Ordering (column numbers in result set *after* selection):
        --sort=spec Sort output (e.g. --sort=3,2r,5n,1rn+2)
                    +#   - first # lines do not sort (header)
                    #    - order on column # lexical ascending
                    #n   - order on column # numeric ascending
                    #r   - order on column # lexical descending
                    #rn  - order on column # numeric descending

 Examples:
     xlscat   -i foo.xls
     xlscat   --in-sep=: --sort=3n -L /etc/passwd
     xlsgrep  pattern file.ods

✨ Generic options ✨

  • -v[#] — set the verbose level (xlscat/xlsgrep)
  • -d[#] — set the debug level (Spreadsheet::Read)
  • --list — show the supported spreadsheet formats and exit
  • -u — use unformatted values
  • --strip[=#] — strip leading and/or trailing spaces of all cells. # & 01 = leading, # & 02 = trailing, 3 = default
  • --clip=# — clip cells to a max length of #
  • --noclip — do not strip empty sheets and trailing empty rows and columns
  • --no-empty — skip empty rows
  • --no-nl[=R] — replace all newlines in cells with R (default space)
  • -e <enc> — set the encoding for input and output
  • -b <enc> — set the encoding for input
  • -a <enc> — set the encoding for output
  • -U — set the encoding for output to utf-8 (short for -a utf-8)

🌸 Input CSV

  • --in-sep=c — set the input sep_char for CSV (c can be 'TAB')

🦋 Input XLS

  • --dtfmt=fmt — specify the default date format to replace 'm-d-yy'; the default replacement is 'yyyy-mm-dd'
  • --passwd=pw — specify the password for the workbook; if pw = -, read the password from the keyboard
  • --formulas — show the formula instead of the value

💖 Output Text (default)

  • -s <sep> — use separator <sep>. Default '|', \n allowed. Overrules ',' when used with --csv
  • -L — line up the columns
  • -B, --box — like -L but also adds an outer frame
  • -n [skip] — number lines (prefix with column number), optionally skipping <skip> (header) lines
  • -A — show field attributes in ANSI escapes
  • -h[#] — show # header lines
  • -D — dump each record with Data::Peek or Data::Dumper
  • --hash — like -D but as a hash with the first row as keys

🍓 Output CSV

  • -c — output CSV, separator = ','
  • -m — output CSV, separator = ';'

🔥 Grep options

careful bestie, -i means something totally different here than it does in xlscat!! it's the grep one now, not "index only" 🙈
  • -i — ignore case
  • -w — match whole words only

⭐ Output HTML

  • -H — output HTML

🧚 Selection

  • -S <sheets> — only print sheets <sheets>. 'all' is a valid set. Default only prints the first sheet
  • -R <rows> — only print rows <rows>. Default is 'all'. Ranges and lists supported like 2,4-7,8-. A trailing - means to the end of the data. Negative rows count from the tail — -8--2 is allowed
  • --head[=n] — alias for -R1..n, where n defaults to 10
  • --tail[=n] — alias for -R-n-, where n defaults to 10
  • -C <cols> — only print columns <cols>. Default is 'all'
  • -F <flds> — only fields <flds>, e.g. -FA3,B16

👑 Ordering

again: the column numbers are in the result set after selection 💅

  • --sort=spec — sort the output (e.g. --sort=3,2r,5n,1rn+2)
    • +# — the first # lines do not sort (header)
    • # — order on column #, lexical ascending
    • #n — order on column #, numeric ascending
    • #r — order on column #, lexical descending
    • #rn — order on column #, numeric descending

💅 Examples

 xlscat   -i foo.xls
 xlscat   --in-sep=: --sort=3n -L /etc/passwd
 xlsgrep  pattern file.ods

📄 xlsx2csv

convert a spreadsheet to CSV. it's literally just a small wrapper over xlscat, that's the whole vibe 🎀

 usage: xlsx2csv [-A [-N | -J c] | -o file.csv] [-s sep] [-f] [-i] file.xls
        xlsx2csv --help | --man | --info
           --list    List supported spreadsheet formats and exit
     -A    --all     Export all sheets      (filename-sheetname.csv)
     -N    --no-pfx  No filene prefix on -A (sheetname.csv)
     -Z    --zip     Convert sheets to CSV's in ZIP
     -J s  --join=s  Use s to join filename-sheetname (-)
     -o f  --out=f   Set output filename
     -i f  --in=f    Set input  filename
     -f    --force   Force overwrite output if exists
     -s s  --sep=s   Set CSV separator character
  • --list — list the supported spreadsheet formats and exit
  • -A, --all — export all sheets (filename-sheetname.csv)
  • -N, --no-pfx — no filename prefix on -A (sheetname.csv)
  • -Z, --zip — convert sheets to CSV's inside a ZIP 🗜️
  • -J s, --join=s — use s to join filename-sheetname (-)
  • -o f, --out=f — set the output filename
  • -i f, --in=f — set the input filename
  • -f, --force — force overwriting the output if it already exists
  • -s s, --sep=s — set the CSV separator character

also there's --help, --man and --info in the usage line, and just so you know: unless -A is used, all the other options get passed straight on to xlscat 🫶

📄 xls2csv

convert a spreadsheet to CSV. this one is identical to xlsx2csv — same tool, different name, twins fr fr 👯

🪟 ss2tk

show a spreadsheet in a perl/Tk spreadsheet widget ✨ (yes, an actual window, very main character energy)

 usage: ss2tk [options] [X11 options] file.xls [<pattern>]
        -w <width> use <width> as column width
        -L         Add spreadsheet tags to top (A, B, ..Z, AB, ...)
                   and left (1, 2, ...)
        --fs[=7]   Set font size (default 7 if no value)
        --fn=name  Set font Face name (default is DejaVu Sans Mono
                   if font size is given
  • -w <width> — use <width> as the column width
  • -L — add spreadsheet tags to the top (A, B, ..Z, AB, ...) and to the left (1, 2, ...)
  • --fs[=7] — set the font size (defaults to 7 if you give no value)
  • --fn=name — set the font Face name (defaults to DejaVu Sans Mono if a font size is given)

🆚 ssdiff

show the differences between two spreadsheets 👀 the gossip tool, honestly

 usage: ssdiff [--verbose[=1]] file.xls file.xlsx

🚨 Vulnerabilities 🚨

okay bestie, real talk for a sec, no jokes here.

since this is just a wrapper over the actual parsers, it can't vouch for vulnerabilities in those parsers. they try to keep up with the CVE's as published, and check for weaknesses. for a more thorough report, see this security-posting:

https://security.metacpan.org/2024/02/10/vulnerable-spreadsheet-parsing-modules.html

for vulnerabilities in this module, please read SECURITY.md. 🫶

🌈 TODO 🌈

okay bestie, here's the wishlist — the stuff that might happen someday. no promises, we're just dreamin' out loud 💭✨

✨ Options ✨

🎀 Module Options 🎀

New Spreadsheet::Read options are lowkey inevitable — they're bound to happen, periodt. The one currently living rent-free in my head: an option that disables the reading of the data entirely, so an index request goes zoom zoom — like, how many sheets/fields/columns. Peep xlscat -i. 👑

💅 Parser options 💅

The goal here is to transparently support as many options as the encapsulated modules support — we're talking (un)formatted values, (date) formats, hidden columns, rows or fields, all of it. These could be implemented like attr above, just named meta, or honestly they could just be new values in the attr hashes. either way? slay. 🦋

🦋 Other parsers 🦋

Adding support for new(er) parsers for formats we already do, like:

  • Data::XLSX::Parser — this one gives a faster way to parse Microsoft Excel's .xlsx files. The implementation of this module is highly inspired from Python's FastXLSX library. It's a SAX based parser, so you can parse very large XLSX files with lower memory usage. Last commit 2021-02-16, so PR's will be taken but it won't be done personally — there just seems to be little gain in supporting this. 😭💔

🌸 Other spreadsheet formats 🌸

Any spreadsheet interface that offers a usable API is fair game, fr fr. Under investigation:

💜 Kspread (.ksp) 💜

Now known as Calligra Sheets. No existing CPAN module has been seen yet. It is XML in ZIP. 🎀

⭐ Alternative parsers for existing formats ⭐

As long as the alternative has a good reason for its existence, and the API of that parser reasonably fits in this approach, the glue layer will be considered for implementation — or patches will be applied to do so — as long as these match what CONTRIBUTING.md describes. no cap. ✨

👑 SEE ALSO 👑

Text::CSV_XS, Text::CSV_PP

See Text::CSV_XS <https://metacpan.org/release/Text-CSV_XS> , Text::CSV_PP <https://metacpan.org/release/Text-CSV> , and Text::CSV <https://metacpan.org/release/Text-CSV> documentation.

Text::CSV <https://metacpan.org/release/Text-CSV> is a wrapper over Text::CSV_XS (the fast XS version) and/or Text::CSV_PP <https://metacpan.org/release/Text-CSV> (the pure perl version). 🫶

Spreadsheet::ParseExcel

Spreadsheet::ParseExcel <https://metacpan.org/release/Spreadsheet-ParseExcel> is the best parser for old-style Microsoft Excel (.xls) files. Most recent commit was Dec 2023. Please use version 0.66 or higher to prevent possible memory bombs. 💥

Spreadsheet::ParseXLSX

Spreadsheet::ParseXLSX <https://metacpan.org/release/Spreadsheet-ParseXLSX> is like Spreadsheet::ParseExcel <https://metacpan.org/release/Spreadsheet-ParseExcel>, but for new Microsoft Excel 2007+ files (.xlsx). They have the same API.

This module uses XML::Twig <https://metacpan.org/release/XML-Twig> to parse the internal XML. Most recent commit was in Dec 2023. Please use version 0.29 or higher to prevent possible memory bombs. 💥

Spreadsheet::XLSX

See Spreadsheet::XLSX <https://metacpan.org/release/Spreadsheet-XLSX> documentation.

bestie this module is dead and deprecated. It is buggy and unmaintained (Most recent commit was Oct 2014). Please use Spreadsheet::ParseXLSX <https://metacpan.org/release/Spreadsheet-ParseXLSX> instead. 💀🙈

Excel::ValueReader::XLSX

See Excel::ValueReader::XLSX <https://metacpan.org/release/Excel-ValueReader-XLSX> documentation.

This module aims at speed-reading ignoring all attributes and formatting.

heads up: using this backend does not, and will not, support parsing strings, string-refs, or globs. Only filenames and file handles are supported. 😤

Spreadsheet::ParseODS

Spreadsheet::ParseODS <https://metacpan.org/pod/Spreadsheet::ParseODS> is a parser for OpenOffice/LibreOffice (.sxc and .ods) spreadsheet files. It is the successor of Spreadsheet::ReadSXC <https://metacpan.org/release/Spreadsheet-ReadSXC>. 🌈

Spreadsheet::ReadSXC

Spreadsheet::ReadSXC <https://metacpan.org/release/Spreadsheet-ReadSXC> is a parser for OpenOffice/LibreOffice (.sxc and .ods) spreadsheet files. 🍓

Spreadsheet::ReadGnumeric

Spreadsheet::ReadGnumeric <https://metacpan.org/release/Spreadsheet-ReadGnumeric> is a parser for Gnumeric <http://www.gnumeric.org/> (.gnumeric) spreadsheet files. 🧚

Spreadsheet::BasicRead

See Spreadsheet::BasicRead <https://metacpan.org/release/Spreadsheet-BasicRead> for xlscat-like functionality (Excel only). 💜

Spreadsheet::ConvertAA

See Spreadsheet::ConvertAA <https://metacpan.org/release/Spreadsheet-ConvertAA> for an alternative set of cell2cr/cr2cell pair. ✨

Spreadsheet::Perl

Spreadsheet::Perl <https://metacpan.org/release/Spreadsheet-Perl> offers a Pure Perl implementation of a spreadsheet engine. Users that want this format to be supported in Spreadsheet::Read are hereby motivated to offer patches. It is not high on my TODO-list. 👀

Spreadsheet::CSV

Spreadsheet::CSV <https://metacpan.org/release/Spreadsheet-CSV> offers the interesting approach of seeing all supported spreadsheet formats as if it were CSV, mimicking the Text::CSV_XS <https://metacpan.org/release/Text-CSV_XS> interface. ate. 🔥

xls2csv

xls2csv <https://github.com/Tux/Spreadsheet-Read/blob/master/scripts/xls2csv> offers an alternative for xlscat -c, in the xls2csv tool, but this tool focuses on character encoding transparency, and requires some other modules. 💅

👑 AUTHOR 👑

H.Merijn Brand <perl5@tux.freedom.nl> — the icon, the legend, the main character of this whole page. 🌟

Copyright (C) 2005-2025 H.Merijn Brand

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

and that's the whole page, bestie!! 🎉 this was Read(3pm) from perl v5.40.1, dated 2026-01-30 — go forth and read them spreadsheets, you absolute legend 👑💅🦋

📦 where this page comes from