package RS::Spreadsheet::XLSX;

use 5.006000;
use strict;
use warnings;

our @ISA = qw();

our $VERSION = '0.13';

use Archive::Zip;
use File::Temp;
use XML::Parser::Expat;
use Spreadsheet::XLSX::Fmt2007;
use Spreadsheet::ParseExcel;

################################################################################

sub new {
    my ( $class, $filename, $converter, $handler ) = @_;

    my $self = {};
    bless( $self, $class );

    $self->{cellHandler} = $handler;
    $self->{zip}         = Archive::Zip->new();

    if ( ref $filename ) {

        $self->{zip}->readFromFileHandle($filename) == Archive::Zip::AZ_OK or die("Cannot open data as Zip archive");

    } else {

        $self->{zip}->read($filename) == Archive::Zip::AZ_OK or die("Cannot open $filename as Zip archive");

    }

    my $member_shared_strings = $self->{zip}->memberNamed('xl/sharedStrings.xml');

    my @shared_strings = ();

    if ($member_shared_strings) {

        my $mstr = $member_shared_strings->contents;
        $mstr =~ s/<t\/>/<t><\/t>/gsm;    # this handles an empty t tag in the xml <t/>
        foreach my $si ( $mstr =~ /<si.*?>(.*?)<\/si/gsm ) {
            my $str;
            foreach my $t ( $si =~ /<t.*?>(.*?)<\/t/gsm ) {
                $t = $converter->convert($t) if $converter;
                $str .= $t;
            }
            push @shared_strings, $str;
        }
    }

    my $member_styles = $self->{zip}->memberNamed('xl/styles.xml');

    my @styles = ();

    my %style_info = ();

    if ($member_styles) {

        foreach my $t ( $member_styles->contents =~ /xf\ numFmtId="([^"]*)"(?!.*\/cellStyleXfs)/gsm ) {    #"
                # $t = $converter -> convert ($t) if $converter;
            push @styles, $t;

        }

        foreach my $t1 (@styles) {
            my $formatCode;
            if ( $member_styles->contents =~ /numFmtId="$t1" formatCode="([^"]*)/ ) {
                $formatCode = $1;
            }
            if ( !defined($formatCode) ) {
                if    ( $t1 == 9  || $t1 == 10 ) { $formatCode = "0.00000%"; }
                elsif ( $t1 == 14 || $t1 == 22 ) { $formatCode = "m-d-yy"; }  # Doesn't mean they're strictly this format, just that they're
                else {                                                        # Excel serial dates and we want them treated like dates.
                    $formatCode = "";
                }
            }
            $style_info{$t1} = $formatCode;
        }

    }

    my $member_rels = $self->{zip}->memberNamed('xl/_rels/workbook.xml.rels') or die("xl/_rels/workbook.xml.rels not found in this zip\n");

    my %rels = ();

    foreach ( $member_rels->contents =~ /\<Relationship (.*?)\/?\>/g ) {
        my ( $id, $type );

        ($id)   = /Id="(.*?)"/i;
        ($type) = /Target="(.*?)"/i;

        next unless ( $id && $type );
        $rels{$id} = $type;
    }

    my $member_workbook = $self->{zip}->memberNamed('xl/workbook.xml') or die("xl/workbook.xml not found in this zip\n");
    my $oBook = Spreadsheet::ParseExcel::Workbook->new;
    $oBook->{SheetCount} = 0;
    $oBook->{FmtClass}   = Spreadsheet::XLSX::Fmt2007->new;
    $oBook->{Flg1904}    = 0;
    if ( $member_workbook->contents =~ /date1904="1"/ ) {
        $oBook->{Flg1904} = 1;
    }
    my @Worksheet = ();

    foreach ( $member_workbook->contents =~ /\<(.*?)\/?\>/g ) {
        /^(\w+)\s+/;

        my ( $tag, $other ) = ( $1, $' );

        my @pairs = split /\" /, $other;

        $tag eq 'sheet' or next;

        my $sheet = {
            MaxRow => 0,
            MaxCol => 0,
            MinRow => 1000000,
            MinCol => 1000000,
        };

        foreach ( $other =~ /(\S+=".*?")/gsm ) {

            my ( $k, $v ) = split /=?"/;    #"

            if ( $k eq 'name' ) {
                $sheet->{Name} = $v;
                $sheet->{Name} = $converter->convert( $sheet->{Name} ) if $converter;
            } elsif ( $k eq 'r:id' ) {

                $sheet->{path} = $rels{$v};

            }

        }
        my $wsheet = Spreadsheet::ParseExcel::Worksheet->new(%$sheet);
        push @Worksheet, $wsheet;
        $oBook->{Worksheet}[ $oBook->{SheetCount} ] = $wsheet;
        $oBook->{SheetCount} += 1;

    }

    $oBook->{_currSheet}    = 0;
    $self->{oBook}          = $oBook;
    $self->{style_info}     = \%style_info;
    $self->{styles}         = \@styles;
    $self->{shared_strings} = \@shared_strings;
    foreach my $sheet (@Worksheet) {
        my $fh = File::Temp->new( SUFFIX => '.xml' );

        my $name = "$sheet->{path}";
        $name =~ s/\/xl\///g;
        $name = "xl/$name";
        my $member_sheet = $self->{zip}->memberNamed("$name") or next;
        ( $member_sheet->extractToFileNamed( $fh->filename ) == Archive::Zip::AZ_OK )
          or die "Unable to create tempfile copy for sheet $name\n";

        my $parser = XML::Parser::Expat->new;
        $parser->setHandlers(
            Start => sub { $self->_start(@_) },
            End   => sub { $self->_end(@_) },
            Char  => sub { $self->_char(@_) },
        );
        $parser->parse($fh);

        $sheet->{MinRow} = 0;
        $sheet->{MinCol} = 0;

        $oBook->{_currSheet}++;
    }
    foreach my $stys ( keys %style_info ) {
    }

    return $oBook;

}

sub _start {
    my ( $self, $parser, $element, %attrs ) = @_;

    if ( $element eq 'c' ) {
        $self->{parse}{c} = \%attrs;
    } elsif ( $element eq 't' ) {
        $self->{parse}{t} = 1;
    } elsif ( $element eq 'v' ) {
        $self->{parse}{v} = 1;
    }
}

sub _end {
    my ( $self, $parser, $element ) = @_;

    if ( $element eq 'c' ) {
        my ( $c1, $c2, $r ) = $self->{parse}{c}{r} =~ /([A-Z])([A-Z]?)(\d+)/;
        my $col = ord($c1) - ord('A');
        if ($c2) {
            $col++;
            $col *= 26;
            $col += ord($c2) - ord('A');
        }
        my $row   = $r - 1;
        my $t     = $self->{parse}{c}{t} || "";
        my $type  = "Text";
        my $style = "";
        my $value = "";
        if ( $t eq "s" ) {
            $value = $self->{shared_strings}[ $self->{parse}{value} ] if defined $self->{parse}{value};
        } elsif ( $t eq "str" ) {
            $value = $self->{parse}{value};
        } elsif ( $t eq "e" ) {
            $value = $self->{parse}{value};
        } elsif ( $t eq "inlineStr" ) {
            $value = $self->{parse}{text};
        } elsif ( defined( $self->{parse}{value} ) ) {
            $type  = "Numeric";
            $value = $self->{parse}{value};
            if ( $self->{parse}{c}{s} ) {
                $style = $self->{style_info}{ $self->{styles}[ $self->{parse}{c}{s} ] }
                    if exists $self->{styles}[ $self->{parse}{c}{s} ];
                if ( $style && $style =~ /(?<!Re)d|m|y/ ) {
                    $type = "Date";
                }
            }
        }

        my $sheet = $self->{oBook}{Worksheet}[ $self->{oBook}{_currSheet} ];
        $sheet->{MaxRow} = $row if $sheet->{MaxRow} < $row;
        $sheet->{MaxCol} = $col if $sheet->{MaxCol} < $col;
        $sheet->{MinRow} = $row if $sheet->{MinRow} > $row;
        $sheet->{MinCol} = $col if $sheet->{MinCol} > $col;

        if ( $value && $value =~ /(.*)E\-(.*)/gsm && $type eq "Numeric" ) {
            $value = $1 / ( 10**$2 );    # this handles scientific notation for very small numbers
        }

        my $cell = Spreadsheet::ParseExcel::Cell->new( Val => $value, Format => $style, Type => $type );
        $cell->{_Value} = $self->{oBook}{FmtClass}->ValFmt( $cell, $self->{oBook} );
        if ( $type eq "Date" && $value < 1 ) {    # then this is just an Excel time field
            $cell->{Type} = "Text";
            $cell->{Val}  = $cell->{_Value};
        }

        if ( defined $self->{cellHandler} ) {
            $self->{cellHandler}( $self->{oBook}, $self->{oBook}{_currSheet}, $row, $col, $cell );
        } else {
            $sheet->{Cells}[$row][$col] = $cell;
        }

        delete $self->{parse};
    } elsif ( $element eq 't' ) {
        $self->{parse}{t} = 0;
    } elsif ( $element eq 'v' ) {
        $self->{parse}{v} = 0;
    }
}

sub _char {
    my ( $self, $parser, $string ) = @_;

    if ( $self->{parse}{v} ) {
        $self->{parse}{value} .= $string;
    } elsif ( $self->{parse}{t} ) {
        $self->{parse}{text} .= $string;
    }
}

1;
__END__

=head1 NAME

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

=head1 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};

         foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {

 		$sheet -> {MaxCol} ||= $sheet -> {MinCol};

 		foreach my $col ($sheet -> {MinCol} ..  $sheet -> {MaxCol}) {

 			my $cell = $sheet -> {Cells} [$row] [$col];

 			if ($cell) {
 			    printf("( %s , %s ) => %s\n", $row, $col, $cell -> {Val});
 			}

 		}

 	}

 }

=head1 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.

=head1 SEE ALSO

=over 2

=item Text::CSV_XS, Text::CSV_PP

http://search.cpan.org/~hmbrand/

A pure perl version is available on http://search.cpan.org/~makamaka/

=item Spreadsheet::ParseExcel

http://search.cpan.org/~kwitknr/

=item Spreadsheet::ReadSXC

http://search.cpan.org/~terhechte/

=item Spreadsheet::BasicRead

http://search.cpan.org/~gng/ for xlscat likewise functionality (Excel only)

=item Spreadsheet::ConvertAA

http://search.cpan.org/~nkh/ for an alternative set of cell2cr () /
cr2cell () pair

=item Spreadsheet::Perl

http://search.cpan.org/~nkh/ 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.

=item xls2csv

http://search.cpan.org/~ken/ offers an alternative for my C<xlscat -c>,
in the xls2csv tool, but this tool focusses on character encoding
transparency, and requires some other modules.

=item Spreadsheet::Read

http://search.cpan.org/~hmbrand/ read the data from a spreadsheet (interface
module)

=back

=head1 AUTHOR

Dmitry Ovsyanko, E<lt>do@eludia.ru<gt>, http://eludia.ru/wiki/

Patches by:

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

=head1 ACKNOWLEDGEMENTS

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

=head1 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.

=cut