NAME
Spreadsheet::ReadSXC - Extract OpenOffice 1.x spreadsheet data
SYNOPSIS
use Spreadsheet::ReadSXC qw(read_sxc);
my $workbook_ref = read_sxc("/path/to/file.sxc");Iterate over every worksheet, row, and cell:
foreach ( sort keys %$workbook_ref ) {
print "Worksheet ", $_, " contains ", $#{$$workbook_ref{$_}} + 1, " row(s):\n";
foreach ( @{$$workbook_ref{$_}} ) {
foreach ( map { defined $_ ? $_ : '' } @{$_} ) {
print " '$_'";
}
print "\n";
}
}Cell D2 of worksheet "Sheet1"
$cell = $$workbook_ref{"Sheet1"}[1][3];Decode the contained XML to latin1
use Unicode::String qw(latin1 utf8);
use XML::Quote;
$cell_text = utf8(xml_dequote($$workbook_ref{"Sheet1"}[1][3]))->latin1;Row 1 of worksheet "Sheet1":
@row = @{$$workbook_ref{"Sheet1"}[0]};Worksheet "Sheet1":
@sheet = @{$$workbook_ref{"Sheet1"}};DESCRIPTION
This is a very simple module for extracting data from OpenOffice 1.x spreadsheet files (.sxc). It exports only one function read_sxc() which takes a filename as an argument and returns 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.
Spreadsheet::ReadSXC requires XML::Parser::Lite::Tree to parse the XML contained in .sxc files. It recursively traverses the XML tree to find spreadsheet cells and collect their data. Only the contents of text:p elements are returned, not the actual values of table:value attributes. 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". This is the string which is returned by the read_sxc() function, not the value of -1500.99.
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 above 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.
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.
Still it is perfectly legal for an .sxc file to apply the "table:number-rows-repeated" attribute to rows that actually contain data (although I have only 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, but by reference (remember that multi-dimensional arrays in Perl are really arrays of references to arrays). Therefore, if you change a value in one row, it is possible that you find the corresponding value in the next row changed, too:
$$workbook_ref{"Sheet1"}[0][0] = 'new string';
print $$workbook_ref{"Sheet1"}[1][0];Keep in mind that after parsing a new .sxc file any reference previously returned by the read_sxc() function will point to the new data structure. Derefence the hash to save your data before parsing a new file. Or derefence when calling the read_sxc() function:
%workbook = %{$workbook_ref};
%new_workbook = %{read_sxc("/path/to/newfile.sxc")};SEE ALSO
http://books.evc-cit.info/book.html has extensive documentation of the OpenOffice 1.x XML file format.
AUTHOR
Christoph Terhechte, <terhechte@cpan.org>
COPYRIGHT AND LICENSE
Copyright 2005 by Christoph Terhechte
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.