File Coverage

blib/lib/Spreadsheet/ReadSXC.pm

Criterion	Covered	Total	%
statement	13	15	86.6
branch			n/a
condition			n/a
subroutine	5	5	100.0
pod			n/a
total	18	20	90.0

package Spreadsheet::ReadSXC;

use 5.006;
use strict;
use warnings;

require Exporter;

our @ISA = qw(Exporter);
our @EXPORT_OK = qw(read_sxc read_xml_file read_xml_string);
our $VERSION = '0.20';

use Archive::Zip;
use XML::Parser;

my %workbook = ();
my @worksheets = ();
my @sheet_order = ();
my $table = "";
my $row = -1;
my $col = -1;
my $text_p = -1;
my @cell = ();
my $repeat_cells = 1;
my $repeat_rows = 1;
my $row_hidden = 0;
my $date_value = '';
my $time_value = '';
my $max_datarow = -1;
my $max_datacol = -1;
my $col_count = -1;
my @hidden_cols = ();
my %options = ();

sub zip_error_handler {}

sub read_sxc ($;$) {
	my ($sxc_file, $options_ref) = @_;
	-f $sxc_file && -s _ or return undef;
	Archive::Zip::setErrorHandler(\&zip_error_handler);
	eval {
		my $zip = Archive::Zip->new($sxc_file);
		my $xml_string = $zip->contents('content.xml');
		return read_xml_string($xml_string, $options_ref);
	};
}

sub read_xml_file ($;$) {
	my ($xml_file, $options_ref) = @_;
	-f $xml_file && -s _ or return undef;
	local $/;
	open CONTENT, "<$xml_file" or die "$xml_file: $!\n";
	my $xml_string = ;
	close CONTENT;
	return read_xml_string($xml_string, $options_ref);
}

sub read_xml_string ($;$) {
	my ($xml_string, $options_ref) = @_;
	%workbook = ();
	@worksheets = ();
	if ( defined $options_ref ) { %options = %{$options_ref}}
	eval {
		my $p = XML::Parser->new(Handlers => {Start => \&handle_start,
						      End => \&handle_end,
						      Char => \&char_start});
		$p->parse($xml_string);
	};
	if ( $options{OrderBySheet} ) { return [@worksheets] } else { return {%workbook} }
}

sub handle_start {
	my ($expat, $element, %attributes) = @_;
	if ( $element eq "text:p" ) {
# increase paragraph count if not part of an annotation
		if ( ! $expat->within_element('office:annotation') ) { $text_p++ }
	}
	elsif ( ( $element eq "table:table-cell" ) or ( $element eq "table:covered-table-cell" ) ) {
# increase cell count
		$col++;
# if number-columns-repeated is set, set $repeat_cells value accordingly for later use
		if ( exists $attributes{'table:number-columns-repeated'} ) {
			$repeat_cells = $attributes{'table:number-columns-repeated'};
		}
# if cell contains date or time values, set boolean variable for later use
		if (exists $attributes{'table:date-value'} ) {
			$date_value = $attributes{'table:date-value'};
		}
		elsif (exists $attributes{'table:time-value'} ) {
			$time_value = $attributes{'table:time-value'};
		}
	}
	elsif ( $element eq "table:table-row" ) {
# increase row count
		$row++;
# if row is hidden, set $row_hidden for later use
		if ( exists $attributes{'table:visibility'} ) { $row_hidden = 1 } else { $row_hidden = 0 }
# if number-rows-repeated is set, set $repeat_rows value accordingly for later use
		if ( exists $attributes{'table:number-rows-repeated'} ) {
			$repeat_rows = $attributes{'table:number-rows-repeated'};
		}
	}
	elsif ( $element eq "table:table-column" ) {
# increase column count
		$col_count++;
# if columns is hidden, add column number to @hidden_cols array for later use
		if ( exists $attributes{'table:visibility'} ) {
			push @hidden_cols, $col_count;
		}
# if number-columns-repeated is set and column is hidden, add affected columns to @hidden_cols
		if ( exists $attributes{'table:number-columns-repeated'} ) {
			$col_count++;
			if ( exists $attributes{'table:visibility'} ) {
				for (2..$attributes{'table:number-columns-repeated'}) {
					push @hidden_cols, $hidden_cols[$#hidden_cols] + 1;
				}
			}
		}
	}
	elsif ( $element eq "table:table" ) {
# get name of current table
		$table = $attributes{'table:name'};
	}
}

sub handle_end {
	my ($expat, $element) = @_;
	if ( $element eq "table:table") {
# decrease $max_datacol if hidden columns within range
		if ( ( ! $options{NoTruncate} ) and ( $options{DropHiddenColumns} ) ) {
			for ( 1..scalar grep { $_ <= $max_datacol } @hidden_cols ) {
				$max_datacol--;
			}
		}
# truncate table to $max_datarow and $max_datacol
		if ( ! $options{NoTruncate} ) {
			$#{$workbook{$table}} = $max_datarow;
			foreach ( @{$workbook{$table}} ) {
				$#{$_} = $max_datacol;
			}
		}
# set up alternative data structure
		if ( $options{OrderBySheet} ) {
			push @worksheets, (
				{
					label	=> $table,
					data	=> \@{$workbook{$table}},
				}
			);
		}
# reset table, column, and row values to default for next table
		$row = -1;
		$max_datarow = -1;
		$max_datacol = -1;
		$table = "";
		$col_count = -1;
		@hidden_cols = ();
	}
	elsif ( $element eq "table:table-row" ) {
# drop hidden columns from current row
		if ( $options{DropHiddenColumns} ) {
			foreach ( reverse @hidden_cols ) {
				splice @{$workbook{$table}[$row]}, $_, 1;
			}
		}
# drop current row, if hidden
		if ( ( $options{DropHiddenRows} ) and ( $row_hidden == 1 ) ) {
			pop @{$workbook{$table}};
			$row--;
		}
# repeat current row, if necessary
		else {
			for (2..$repeat_rows) {
				$row++;
				$workbook{$table}[$row] = $workbook{$table}[$row - 1]	# copy reference, not data
			}
# set max_datarow, if row not empty
			if ( grep { defined $_ } @{$workbook{$table}[$row]} ) {
				$max_datarow = $row;
			}
		}
# reset row and col values to default for next row
		$repeat_rows = 1;
		$col = -1;
	}
	elsif ( ( $element eq "table:table-cell" ) or ( $element eq "table:covered-table-cell" ) ) {
# assign date or time value to current workbook cell if requested
		if ( ( $options{StandardDate} ) and ( $date_value ) ) {
			$workbook{$table}[$row][$col] = $date_value;
			$date_value = '';
		}
		elsif ( ( $options{StandardTime} ) and ( $time_value ) ) {
			$workbook{$table}[$row][$col] = $time_value;
			$time_value = '';
		}
# join cell contents and assign to current workbook cell
		else {
			$workbook{$table}[$row][$col] = @cell ? join $options{ReplaceNewlineWith} || "", @cell : undef;
		}
# repeat current cell, if necessary
		for (2..$repeat_cells) {
			$col++;
			$workbook{$table}[$row][$col] = $workbook{$table}[$row][$col - 1];
		}
# reset cell and paragraph values to default for next cell
		@cell = ();
		$repeat_cells = 1;
		$text_p = -1;
	}
}

sub char_start {
	my ($expat, $content) = @_;
# don't include paragraph if part of an annotation
	if ( $expat->within_element('office:annotation') ) {
		return;
	}
# don't include covered cells, if not requested
	if ( ( $expat->within_element('table:covered-table-cell') ) and ( ! $options{IncludeCoveredCells} ) ) {
		return;
	}
# add paragraph or textspan to current @cell array
	if ( $table ) {
		$cell[$text_p] .= $content;
# set $max_datarow and $max_datacol to current values
		$max_datarow = $row;
		if ( $col > $max_datacol ) { $max_datacol = $col }
	}
}

1;
__END__