NAME

Spreadsheet::XLSX - Perl extension for reading MS Excel 2007 files.

SYNOPSIS

use Text::Iconv;
my $converter = Text::Iconv->new("utf-8", "windows-1251");

# Text::Iconv is not really required.
# This can be any object with the convert method. Or nothing.

use Spreadsheet::XLSX;

my $excel = Spreadsheet::XLSX->new('test.xlsx', $converter);

foreach my $sheet (@{$excel->{Worksheet}}) {

    printf("Sheet: %s\n", $sheet->{Name});
    
    $sheet->{MaxRow} ||= $sheet->{MinRow};
    $sheet->{MaxCol} ||= $sheet->{MinCol};
    
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
         
        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
        
            my $cell = $sheet->{Cells}[$row][$col];
    
            if ($cell) {
                printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
            }
    
        }
    
    }

}

DESCRIPTION

This module is a (quick and dirty) emulation of Spreadsheet::ParseExcel for Excel 2007 (.xlsx) file format. It supports styles and many of Excel's quirks, but not all. It populates the classes from Spreadsheet::ParseExcel for interoperability; including Workbook, Worksheet, and Cell.

NUMBER FORMATS

Built-in number formats use the formatter's definition table; explicit custom format codes take precedence. value() returns the formatted representation, while numeric unformatted() values retain the stored number. For example, the number 14 with format 0.00 is displayed as 14.00 without changing its raw value.

An explicit cell style (including s="0") overrides default styles. Otherwise, a row style enabled by customFormat takes precedence over a column style, followed by the workbook's default cell style. These defaults apply to cells read from the worksheet; empty cells are not synthesized.

STRING CELLS

Both shared strings (t="s") and inline strings (t="inlineStr") are supported. Inline text is read from the cell's is element without requiring a shared-string table or a v element. Rich-text runs are concatenated in order, whitespace is preserved, and the optional converter is applied to each text fragment for both string representations.

The five predefined XML entities (&, <, >, ", ') and decimal or hexadecimal character references are decoded once per text fragment before the converter runs. Numeric references use the same UTF-8 byte representation as literal text when no converter is supplied. For example, < becomes the literal text <, not another decoded character. Invalid numeric XML character references cause an exception. The same decoding applies to phonetic hint text.

PHONETIC HINTS

Phonetic hints in shared strings and inline strings are not included in value() or unformatted(). Cells containing such strings expose the hints as $cell->{RPh}, an array of hashes with Val, Sb (start offset) and Eb (end offset). The optional converter is applied to hint text as well as ordinary cell text; offsets retain their original XLSX values.

If present, $cell->{PhoneticPr} contains FontId and the optional Type and Alignment properties. Metadata is copied for each cell.

LIMITATIONS AND PARSE ERRORS

Worksheet relationship targets are resolved relative to xl/workbook.xml, or from the ZIP package root when they begin with /. Dot segments are normalized. Missing relationships, missing target members, paths escaping the package root, and external worksheet targets cause an exception naming the worksheet and relationship. External resources are never fetched. Chartsheets remain empty worksheet placeholders; their chart data is not interpreted as cell data.

ZIP input is limited to 10,000 entries, 64 MiB per uncompressed member, and 256 MiB of total declared uncompressed data, including unused members. Members used by the parser are decompressed incrementally and their actual sizes are checked against the limits and ZIP metadata. Exceeding a limit or encountering inconsistent sizes raises an exception.

Applications needing different limits can scope them to a parsing operation:

local $Spreadsheet::XLSX::MAX_MEMBER_SIZE = 128 * 1024 * 1024;
local $Spreadsheet::XLSX::MAX_TOTAL_SIZE = 512 * 1024 * 1024;
local $Spreadsheet::XLSX::MAX_MEMBERS = 20000;
my $excel = Spreadsheet::XLSX->new($filename);

These limits bound uncompressed input, not the total memory used by parsed cells and XML processing.

The module uses regular expressions to extract workbook data. It does not perform full XML validation or XLSX schema validation. Some obvious syntax errors, such as malformed attributes or unterminated tags, comments and CDATA sections, cause an exception naming the XML member and byte position. Other malformed XML (for example, incorrectly nested elements or invalid entity references) may not be detected. DTD declarations are not supported. No external entities are loaded or expanded.

Unprefixed elements, including elements in the default SpreadsheetML namespace, are supported. Explicit prefixes on recognized data elements, such as x:worksheet or p:Relationship, cause an exception naming the element and XML member. Such prefixes can be valid XLSX syntax; this is a limitation of Spreadsheet::XLSX, not evidence that the file is invalid. Other extension elements continue to be ignored. Namespace declarations and attributes such as r:id remain supported. Prefix declarations are not resolved or validated; recognition is based on the local element names used by this parser. Use Spreadsheet::ParseXLSX for valid XLSX files using explicit element prefixes or when proper XML parsing is required.

Cell positions must use A1-style addresses within A1:XFD1048576. Defined names such as Artikelnummer1 cannot replace an address in a cell's r attribute. Missing or invalid addresses cause an exception identifying the worksheet and XML member. The parser does not attempt to repair malformed XML or infer positions from invalid addresses.

SEE ALSO

Spreadsheet::ParseXLSX

This module (Spreadsheet::XLSX) has some serious issues with the way it uses regexs for parsing the XML. I would strongly encourage switching to Spreadsheet::ParseXLSX which takes a more reliable approach.

Text::CSV_XS, Text::CSV_PP
Spreadsheet::ParseExcel
Spreadsheet::ReadSXC
Spreadsheet::BasicRead

for xlscat likewise functionality (Excel only)

Spreadsheet::ConvertAA

for an alternative set of cell2cr() / cr2cell() pair

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's not high on my todo-list.

xls2csv

https://metacpan.org/release/KEN/xls2csv-1.07 offers an alternative for my xlscat -c, in the xls2csv tool, but this tool focusses on character encoding transparency, and requires some other modules.

Spreadsheet::Read

read the data from a spreadsheet (interface module)

AUTHOR

Dmitry Ovsyanko

Patches by:

Steve Simms
Joerg Meltzer
Loreyna Yeung	
Rob Polocz
Gregor Herrmann
H.Merijn Brand
endacoe
Pat Mariani
Sergey Pushkin

ACKNOWLEDGEMENTS

Thanks to TrackVia Inc. (http://www.trackvia.com) for paying for Rob Polocz working time.

COPYRIGHT AND LICENSE

Copyright (C) 2008 by Dmitry Ovsyanko

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.