NAME
Spreadsheet::ReadGnumeric - read a Gnumeric file, return Spreadsheet::Read
VERSION
Version 0.4
SYNOPSIS
use Spreadsheet::ReadGnumeric;
my $reader = Spreadsheet::ReadGnumeric->new
(rc => 1, cells => 1, # these are the defaults
process_formulas => 0);
my $book = $reader->parse('spreadsheet.gnumeric');
my $n_sheets = $book->[0]{sheets};
my $sheet_1 = $book->[1];
my $b12 = $sheet_1->{B12};
# or equivalently:
my $b12 = $sheet_1->{cell}[2][12];
say $b12; # => "not a vitamin", e.g.DESCRIPTION
Given a source of saved Gnumeric data, Spreadsheet::ReadGnumeric parses it and returns the result in the same format as Spreadsheet::Read. In fact, Spreadsheet::Read has a few more bells and whistles, such as transposing the resulting sheets and providing an object-oriented interface to the resulting data, so it may be easier to access this module through Spreadsheet::Read, even if you only want to parse Gnumeric data.
Note that Gnumeric only saves raw cell values, and not their formatted versions. In particular, Gnumeric saves formulas, and not formula values, never mind formatted formula values.
METHODS
After creating an instance with "new", call "parse" with a file name, a stream, or a string containing the spreadsheet data. The "attr", "cells", "convert_colors" "gzipped_p", "merge", "minimal_attributes", and "rc" slots control how Spreadsheet::ReadGnumeric parses its input. They may also be used as initializer keywords to the "new" method.
The rest of these deal with the XML parsing mechanism and are probably not of interest (unless you need to write a subclass in order to extract more information).
attr
Slot that, if true, directs the parsing methods to retain style information in the returned $sheet->{attr} array of each sheet. If the value of this slot is "keep", an arrayref of each sheet's Spreadsheet::Gnumeric::StyleRegion instances is saved in $sheet->{style_regions}.
The attr value is normally supplied to the new method as an initializer. See the Spreadsheet::Read Cell Attributes documentation for details of the resulting attributes. See Spreadsheet::Gnumeric::StyleRegion for details of how attribute conversion is performed.
See also the "merge" slot, which requests additional processing of cell merge information.
cells
Slot that contains the value of the Spreadsheet::Read "cells" option. When true, this populates the $sheet->{$cell_name} with the corresponding cell data, where $cell_name is an alphanumeric cell name such as "B17" or "AA3". Defaults to a true value.
chars
Slot that contains the text content of an XML element being parsed.
convert_colors
Slot that contains a boolean which, if defined and true, causes Gnumeric colors to be converted to the more common HTML-style format. Gnumeric specifies colors as RGB triples using hex numbers of one to four digits for each primary color, separated by colons, so that white is "FFFF:FFFF:FFFF", and cyan is "0:FC00:F800". Conversion pads each primary to four digits, takes the first two, and prepends "#", producing "#FFFFFF" for white and "#00FCF8" for cyan.
For Spreadsheet::Read compatibility, the default is true. This value is ignored unless "attr" is true.
current_attrs
Slot that contains the attribute of an element being parsed.
current_elt
Slot that contains the name of an element being parsed, including any namespace prefix (see namespaces).
element_stack
Slot that contains an arrayref that is a stack of containing element contexts. The last entry in this is the parent of the current element being parsed, and is itself an arrayref of the values of
[ current_elt, current_attrs, chars, namespaces ]while that containing element was being parsed.
find_style_for_cell
Given zero-based column and row indices, look in "style_regions" for a suitable Spreadsheet::Gnumeric::StyleRegion instance, and return its attribute hash.
formulas
Slot that contains an arrayref indexed by the "ExprID" attribute of formula cells. Each entry is itself an arrayref of four elements, the zero-based column and row indices where the formula is defined, the original formula as a string (complete with leading "="), and the tokenized formula; this is initially "undef" and only filled in if the formula is referenced elsewhere. The "process_formulas" slot describes how this is done.
gzipped_p
Slot that determines whether parse will test whether its argument is gzipped. There are three possibilities: Undefined, other false, and true. If undefined, then parse will open the file, make the test, and then reset the stream to the beginning (see "stream_gzipped_p"). Otherwise, parse will take the caller's word for it.
merge
Slot that requests additional processing of cell merging information. If true, (a) the value of the merged attribute for merged cells is the name of the upper left corner of the rectangle of merged cells, and (b) the contents and attributes of that upper-left cell are copied into all of the others. The default is false.
This flag is ignored unless the "attr" slot is also true.
minimal_attributes
Slot that, if true, causes attributes to be filled in only for cells that have content, i.e. actually appear as a <Cell> in the file with a nonempty string string. Otherwise, attributes are filled in for the full $sheet->{maxrow} vs $sheet->{maxcol} array. The default is false.
This flag is ignored unless the "attr" slot is also true.
namespaces
Slot that contains a hash of namespace prefix to defining URL, for all prefixes in scope. These are never modified by side effect, so that they can be copied into inner scopes. We use this to decide whether any given name is really in the schema we care about.
new
Creates a new Spreadsheet::ReadGnumeric instance. Keywords are any of the method names listed as slots.
parse
Given an input source, reads it and returns the Spreadsheet::Read-compatible data structure, constructed according to the options set up at instantiation time. If the "gzipped_p" slot is defined and true or the content starts with gzip magic (see "stream_gzipped_p"), which is the normal state of saved Gnumeric spreadsheets, it is uncompressed while being read. If gzipped_p is not defined, the stream is read to see how it starts and then rewound.
The input can be a reference (which is taken to be an open stream), a file name, or a literal string that contains the data. If given an non-seekable stream, the stream_gzipped_p slot must be defined in order to skip the test for compressed input.
process_formulas
Slot which, if true, requests that spreadsheet parsing should go to the trouble of reconstituting formulas. The default is false, which may change, since formula processing probably doesn't cost very much.
When Gnumeric copies a formula, it offsets the cell names referenced in the formula by the change in location. When the spreadsheet is saved, if neither formula is modified, the first time the equation is mentioned it is saved as a string with a unique "ExprID" and the second time the same "ExprID" is cited and the string is empty. If process_formulas is false, the second and all subsequent references to the formula are left empty, but if process_formulas is true, the correct equation is reconstituted for each cell in the same way the copies were created by Gnumeric.
rc
Slot that contains the value of the Spreadsheet::Read "rc" option. When true, this populates the $sheet->{cell}[$col][$row] arrays. Defaults to a true value.
sheet
Slot that contains the sheet currently under construction.
sheets
Read-only slot that contains the resulting Spreadsheet::Read-compatible data structure. This slot is initialized when its value is first requested; it is expected that the value is then updated by side-effect.
stream_gzipped_p
Given a stream open to the start of the data and an optional file name, determine whether we need to uncompress the data and then reset the stream back to the beginning. If the gzipped_p slot is defined, then we just return that and leave the stream untouched. The file name is only used for error messages.
style_attributes
Internal slot used to store a hashref of spreadsheet attributes during parsing.
style_regions
Slot that contains an arrayref of Spreadsheet::Gnumeric::StyleRegion instances, built by the style parsing code for each sheet and cleared when the sheet is completed.
INTERNALS
Spreadsheet::ReadGnumeric uses XML::Parser::Lite to decode the XML into its component elements, calling the _process_Foo_elt method at each </Foo> closing tag (explicit or implicit), where "Foo" is the case-sensitive element name. If no such method exists, the element is ignored, though its content may already have been processed. For instance, we don't care about <Sheets> because it exists only as a container for <Sheet> elements, which we do process.
Elements with XMLNS prefixes (using the ":" separator) must be associated with the Gnumeric schema in order to be visible to this parsing mechanism. Elements without prefixes are assumed to be part of the Gnumeric schema (but the only Gnumeric files I've seen so far use prefixes on all elements). Note that element names stored in current_elt and element_stack have already been stripped of any Gnumeric-specific prefixes.
When the _process_Foo_elt method is called, it can examine the "current_attrs" hash for its attributes and the "chars" string for its character content; all element content will already have been processed. If necessary, it can examine the "element_stack" slot to check context. For example, _process_Name_elt needs to do this because it only processes Sheet element names. The element_stack also makes the attributes of enclosing elements available.
Consequently, extending Spreadsheet::ReadGnumeric to extract more information out of Gnumeric files should be a simple matter of defining a subclass with suitable _process_*_elt methods that examine their context, extract their content, and stuff the result into the sheet and/or sheets slot values accordingly. At least that's my theory, and I'm sticking to it.
TODO
Note that the "clip", "strip", and "pivot" options of Spreadsheet::Read are handled by its _clipsheets sub, so I do not intend to do anything about them here.
BUGS
Please report any bugs or feature requests to bug-spreadsheet-readgnumeric at rt.cpan.org, or through the web interface at https://rt.cpan.org/NoAuth/ReportBug.html?Queue=Spreadsheet-ReadGnumeric.
SEE ALSO
- The Gnumeric Spreadsheet
- Spreadsheet::Read
-
Frontend to multiple spreadsheet formats, with additional options for manipulating the result, plus the option to give them object capabilities. This module also describes the output data structure in depth.
- The Gnumeric File Format
-
This is a PDF file by David Gilbert dated 2001-11-05, available at https://www.jfree.org/jworkbook/download/gnumeric-xml.pdf (GFDL). I am not aware of any more recent version.
AUTHOR
Bob Rogers <rogers at rgrjr.com>, based heavily on the Spreadsheet::Read architecture.
LICENSE AND COPYRIGHT
This software is Copyright (c) 2023 by Bob Rogers.
This is free software, licensed under:
The Artistic License 2.0 (GPL Compatible)