Spread...eadSXC(3pm)

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

🎀 NAME 🎀

Welcome to the manual page for Spreadsheet::ReadSXC — a.k.a. Spread...eadSXC(3pm) from the User Contributed Perl Documentation. What does it do? It extracts OpenOffice 1.x spreadsheet data. That's the whole vibe. 💖

🚨 NOTICE 🚨

Okay bestie, real talk before anything else: this module is a legacy API wrapper. Most likely you actually want to go look at Spreadsheet::ParseODS instead — that one implements an API that's way more compatible with Spreadsheet::ParseXLSX. And plot twist 🫶: that module is also the backend for this API. So like, no cap, go check it out. 👑

✨ SYNOPSIS ✨

  use Spreadsheet::ReadSXC qw(read_sxc);
  my $workbook_ref = read_sxc("/path/to/file.sxc");

  # Alternatively, unpack the .sxc file yourself and pass content.xml

  use Spreadsheet::ReadSXC qw(read_xml_file);
  my $workbook_ref = read_xml_file("/path/to/content.xml");

  # Alternatively, pass the XML string directly

  use Spreadsheet::ReadSXC qw(read_xml_string);
  use Archive::Zip;
  my $zip = Archive::Zip->new("/path/to/file.sxc");
  my $content = $zip->contents('content.xml');
  my $workbook_ref = read_xml_string($content);

  # Control the output through a hash of options (below are the defaults):

  my %options = (
    ReplaceNewlineWith  => "",
    IncludeCoveredCells => 0,
    DropHiddenRows      => 0,
    DropHiddenColumns   => 0,
    NoTruncate          => 0,
    StandardCurrency    => 0,
    StandardDate        => 0,
    StandardTime        => 0,
    OrderBySheet        => 0,
    StrictErrors        => 0,
  );
  my $workbook_ref = read_sxc("/path/to/file.sxc", \%options );

  # Iterate over every worksheet, row, and cell:

  use Encode 'decode';

  foreach ( sort keys %$workbook_ref ) {
     print "Worksheet ", $_, " contains ", $#{$$workbook_ref{$_}} + 1, " row(s):\n";
     foreach ( @{$$workbook_ref{$_}} ) {
        foreach ( map { defined $_ ? $_ : '' } @{$_} ) {
          my $str = decode('UTF-8', $_);
          print " '$str'";
        }
        print "\n";
     }
  }

  # Cell D2 of worksheet "Sheet1"

  $cell = $$workbook_ref{"Sheet1"}[1][3];

  # Row 1 of worksheet "Sheet1":

  @row = @{$$workbook_ref{"Sheet1"}[0]};

  # Worksheet "Sheet1":

  @sheet = @{$$workbook_ref{"Sheet1"}};

🦋 DESCRIPTION 🦋

So Spreadsheet::ReadSXC extracts data from OpenOffice 1.x spreadsheet files (.sxc). It exports the function read_sxc(), which takes a filename plus an optional reference to a hash of options as its arguments, and hands you back a reference to a hash of references to two-dimensional arrays. 🌸

The hash keys correspond to the names of worksheets in the OpenOffice workbook. The two-dimensional arrays correspond to rows and cells in the respective spreadsheets. Now — if that's not it for you because the order of sheets is not preserved in a hash, keep reading bestie, because the 'OrderBySheet' option provides an array of hashes instead. 💅

If you'd rather unpack the .sxc file yourself, you can use the function read_xml_file() instead and pass the path to content.xml as an argument. Or you can extract the XML string from content.xml and pass the string to the function read_xml_string(). Both of those functions also take a reference to a hash of options as an optional second argument. 🫶

Spreadsheet::ReadSXC uses XML::Twig to parse the XML contained in .sxc files. Only the contents of text:p elements are returned — not the actual values of table:value attributes. Like, for example: a cell might have a table:value-type attribute of "currency", a table:value attribute of "-1500.99" and a table:currency attribute of "USD". The text:p element would contain "-$1,500.99". That string is what the read_sxc() function returns — not the value of -1500.99. Periodt. 💀

Spreadsheet::ReadSXC was written with data import into an SQL database in mind. Therefore empty spreadsheet cells correspond to undef values in array rows. The example code up in the SYNOPSIS shows how to replace undef values with empty strings. ✨

If the .sxc file contains an empty spreadsheet, its hash element will point to an empty array (unless you use the 'NoTruncate' option, in which case it will point to an array of an array containing one undefined element). 🍓

OpenOffice uses UTF-8 encoding. It depends on your environment how the data returned by the XML Parser is best handled:

  use Unicode::String qw(latin1 utf8);
  $unicode_string = utf8($$workbook_ref{"Sheet1"}[0][0])->as_string;

  # this will not work for characters outside ISO-8859-1:

  $latin1_string = utf8($$workbook_ref{"Sheet1"}[0][0])->latin1;

And of course there are other modules than Unicode::String on CPAN that handle conversion between encodings. It's your choice, babe. 🌈

Table rows in .sxc files may have a "table:number-rows-repeated" attribute, which is often used for consecutive empty rows. When you format whole rows and/or columns in OpenOffice, it sets the numbers of rows in a worksheet to 32,000 and the number of columns to 256 — even if only a few lower-numbered rows and cells actually contain data. Spreadsheet::ReadSXC truncates such sheets so that there are no empty rows after the last row containing data and no empty columns after the last column containing data (unless you use the 'NoTruncate' option). 🧚

Still, it is perfectly legal for an .sxc file to apply the "table:number-rows-repeated" attribute to rows that actually do contain data (although the author says they've only ever been able to produce such files manually, not through OpenOffice itself). To save on memory usage in these cases, Spreadsheet::ReadSXC does not copy rows by value — it copies them by reference (remember that multi-dimensional arrays in Perl are really arrays of references to arrays). So if you change a value in one row, it's possible you'll find the corresponding value in the next row changed too:

  $$workbook_ref{"Sheet1"}[0][0] = 'new string';
  print $$workbook_ref{"Sheet1"}[1][0];
⚠️ Heads up: that reference-sharing thing is real. Watch out for it.

As of version 0.20, the references returned by read_sxc() et al. remain valid after subsequent calls to the same function. In earlier versions, calling read_sxc() with a different file as the argument would change the data referenced by the original return value, so you had to dereference it before making another call. Thanks to H. Merijn Brand for fixing this. 👑

💅 OPTIONS 💅

  • StrictErrors — turn on error reporting by using "croak". Otherwise, functions silently return "undef" when errors are encountered.
  • ReplaceNewlineWith — by default, newlines within cells are ignored and all lines in a cell are concatenated to a single string which does not contain a newline. To keep the newline characters, use the following key/value pair in your hash of options:
  ReplaceNewlineWith => "\n"

However, you may replace newlines with any string you like. 🌸

  • IncludeCoveredCells — by default, the content of cells that are covered by other cells is ignored, because you wouldn't see it in OpenOffice unless you unmerge the merged cells. To include covered cells in the data structure which is returned by parse_sxc(), use the following key/value pair in your hash of options:
  IncludeCoveredCells => 1
  • DropHiddenRows — by default, hidden rows are included in the data structure returned by parse_sxc(). To drop those rows, use the following key/value pair in your hash of options:
  DropHiddenRows => 1
  • DropHiddenColumns — by default, hidden columns are included in the data structure returned by parse_sxc(). To drop those columns, use the following key/value pair in your hash of options:
  DropHiddenColumns => 1
  • NoTruncate — by default, the two-dimensional arrays that contain the data within each worksheet are truncated to get rid of empty rows below the last row containing data and empty columns beyond the last column containing data. If you prefer to keep those rows and columns, use the following key/value pair in your hash of options:
  NoTruncate => 1
  • StandardCurrency — by default, cells are returned as formatted. If you prefer to obtain the value as contained in the table:value attribute, use the following key/value pair in your hash of options:
  StandardCurrency => 1
  • StandardDate — by default, date cells are returned as formatted. If you prefer to obtain the date value as contained in the table:date-value attribute, use the following key/value pair in your hash of options:
  StandardDate => 1
  • StandardTime — by default, time cells are returned as formatted. If you prefer to obtain the time value as contained in the table:time-value attribute, use the following key/value pair in your hash of options:
  StandardTime => 1

These options are a first step on the way to a different approach at reading data from .sxc files. There should be more options to read in values instead of the strings OpenOffice displays. It should give more flexibility in working with the data obtained from OpenOffice spreadsheets. 'float' and 'percentage' values could be next. 'currency' is less obvious, though, as we need to consider both its value and the 'table:currency' attribute. Formulas and array formulas are yet another issue. The author probably won't deal with this until they've given this module an object-oriented interface. 🦋

  • OrderBySheet — the disadvantage of storing worksheets by name in a hash is that the order of sheets is lost. If you prefer not to obtain such a hash, but an array of worksheets instead, use the following key/value pair in your hash of options:
  OrderBySheet => 1

Thus the read_sxc function will return an array of hashes, each of which will have two keys, "label" and "data". The value of "label" is the name of the sheet. The value of data is a reference to a two-dimensional array containing rows and columns of the worksheet:

  my $worksheets_ref = read_sxc("/path/to/file.sxc");
  my $name_of_first_sheet = $$worksheets_ref[0]{label};
  my $first_cell_of_first_sheet = $$worksheets_ref[0]{data}[0][0];

🔥 FUNCTIONS 🔥

read_sxc

  my $workbook_ref = read_sxc("/path/to/file.sxc");

Reads an SXC or ODS file given a filename and returns the worksheets as a data structure. 🎀

read_sxc_fh

    open my $fh = 'example.ods';
    my $sheet = read_sxc_fh( $fh );

Reads an SXC or ODS file given a filehandle and returns the worksheets as a data structure. 🫶

read_xml_file

  my $workbook_ref = read_xml_file("/path/to/content.xml");

Reads an XML file from a SXC or ODS file and returns the worksheets as a data structure. ✨

read_xml_string

Parses an XML string and returns the worksheets as a data structure. 💖

Ok bestie so say you've got your spreadsheet living on the internet and not on your disk — the vibe is still immaculate, you just gotta go get it first. Pull the bytes down, hand read_sxc_fh a filehandle, and boom, you're reading an SXC file from a URL:

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

# Fetch data and return a filehandle to that data
sub fetch_url {
    my( $url ) = @_;
    my $ua = HTTP::Tiny->new;
    my $res = $ua->get( $url );
    open my $fh, '<', \$res->{content};
    return $fh
}
my $fh = fetch_url('http://example.com/example.ods');
my $sheet = read_sxc_fh( $fh );

See how the filehandle is opened over the in-memory string of the response content? That's the little trick. No temp files, no mess, just vibes. 💅

🐛 BUG TRACKER 🐛

Okay so if you find a bug, and you will, because that's just the nature of software bestie, please report it in this module via the Github bug queue at <https://github.com/Corion/Spreadsheet-ReadSXC/issues> 🫶

Not me submitting a bug report at 2am with screenshots and everything. Do it. The maintainers literally want to know. 💖

👀 SEE ALSO 👀

👑 AUTHOR 👑

Christoph Terhechte, <terhechte@cpan.org> — the original icon, periodt 🎀

🫶 MAINTAINER 🫶

Max Maischein, <mailto:corion@cpan.org> — keeping the whole thing slaying to this very day 🌸

Copyright 2005-2019 by Christoph Terhechte

Copyright 2019-2024 by Max Maischein

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. Free as in freedom, bestie. 🌈

And that's the whole page, fr fr 💜 Big love to perl v5.40.0, which is the version this doc was built against, and to 2024-08-17, the day this particular snapshot got its glow-up. Go forth and parse some spreadsheets, you gorgeous main character 🦋⭐

📦 where this page comes from