[webpac2] / lib / WebPAC / Input / Excel.pm

package WebPAC::Input::Excel;

use warnings;
use strict;

use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::Utility qw/int2col/;
use base qw/WebPAC::Common/;
use Text::Unaccent::PurePerl qw/unac_string/;
use Data::Dump qw/dump/;

=head1 NAME

WebPAC::Input::Excel - support for Microsoft Excel and compatibile files

=cut

our $VERSION = '0.06';


=head1 SYNOPSIS

Open Microsoft Excell, or compatibile format (for e.g. from OpenOffice.org
or Gnuemeric) in C<.xls> format.

=head1 FUNCTIONS

=head2 new

Returns handle to database and size

  my $excel = new WebPAC::Input::Excel(
        path => '/path/to/workbook.xls'
        worksheet => 'name of sheet',
        from => 42,
        to => 9999,
  }

C<worksheet> is case and white-space insensitive name of worksheet in Excel
file to use. If not specified, name of input is used. If none of those
methods returned sheet, first worksheet in file is used instead.

C<from> and C<to> specify row numbers to start and finish import.

=cut

sub new {
        my $class = shift;
        my $self = {@_};
        bless($self, $class);

        my $log = $self->_get_logger();

        $log->logdie("can't open excel file $self->{path}: $!") unless (-r $self->{path} && -f $self->{path});

        my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($self->{path});

        my $sheet;
        my $wanted_worksheet = $self->{worksheet} || $self->{name};

        if ($wanted_worksheet) {
                my $name;
                do {
                        $sheet = shift @{ $workbook->{Worksheet} };
                        $log->logdie("can't find sheet '$wanted_worksheet' in $self->{path}\n") unless (defined($sheet));
                        $name = $sheet->{Name};
                        $name =~ s/\s\s+/ /g;
                } until ($name =~ m/^\s*\Q$wanted_worksheet\E\s*$/i);

        }

        $sheet ||= shift @{ $workbook->{Worksheet} };

        $self->{sheet} = $sheet;

        $self->{from} ||= $sheet->{MinRow};
        $self->{to} ||= $sheet->{MaxRow};

        my $size = $self->{to} - $self->{from};
        $self->{size} = $size;

        $log->warn("opening Excel file '$self->{path}', using ",
                $wanted_worksheet ? '' : 'first ',
                "worksheet: $sheet->{Name} [$size rows]"
        );

        $self ? return $self : return undef;
}

=head2 fetch_rec

Return record with ID C<$mfn> from database

  my $rec = $self->fetch_rec( $mfn );

Columns are named C<A>, C<B> and so on...

=cut

sub fetch_rec {
        my $self = shift;

        my $mfn = shift;

        my $log = $self->_get_logger();

        my $sheet = $self->{sheet};
        $log->logdie("can't find sheet hash") unless (defined($sheet));
        $log->logdie("sheet hash isn't Spreadsheet::ParseExcel::Worksheet") unless ($sheet->isa('Spreadsheet::ParseExcel::Worksheet'));

        my $rec;

        my $row = $self->{from} + $mfn - 1;

        $log->debug("fetch_rec( $mfn ) row: $row cols: ",$sheet->{MinCol}," - ",$sheet->{MaxCol});

        foreach my $col ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
                my $v = $sheet->{Cells}->[$row]->[$col]->{_Value};      ## XXX _Value = formatted | Val = unformated !
                $rec->{ int2col($col) } = $v if defined $v;
        }

        # add mfn only to records with data
        $rec->{'000'} = [ $mfn ] if ($rec);
        
        return $rec;
}

=head2 size

Return number of records in database

  my $size = $isis->size;

=cut

sub size {
        my $self = shift;
        return $self->{size};
}

our @labels;
our @names;

sub normalize {
        my ($self,$mfn) = @_;

        my $log = $self->_get_logger();

        my $sheet = $self->{sheet};

        my $ds;

        if ( ! @labels ) {

                my $labels;

                foreach ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
                        my $label = $sheet->{Cells}->[0]->[$_]->{_Value};
                        last if length($label) == 0;
                        push @labels, $label;
                }
                @names = map {
                        my $t = unac_string($_);
                        $t =~ s{[^a-z0-9]+}{_}gi;
                        $t =~ s{_+$}{};
                        $t =~ s{^_+}{};
                        $t = lc($t);
                        $labels .= "$t\t$_\n";
                        $t;
                } @labels;

                $log->info("columns = ", dump( @names ), " labels = ", dump( @labels ) );

                $ds = {
                        '_labels' => [ @labels ],
                        '_names' => [ @names ],
                };

                my $path = $self->{labels} || 'var/labels.txt';
                {
warn $labels;
                        open(my $fh, '>:raw', $path) || die "$path: $!";
                        print $fh $labels;
                        close $fh;
                }
                $log->info("created labels $path ", -s $path, " bytes");
        }


        my $row = $self->{from} + $mfn - 1;

        my $data;
        foreach ( $sheet->{MinCol} ... $sheet->{MaxCol} ) {
                my $name = $names[$_];
                next unless $name;
                my $v = $sheet->{Cells}->[$row]->[$_]->{_Value};
                $data->{ $name } = $v;
                $ds->{ $name } = { search => [ $v ] } if defined $v;
        }

        $ds->{'_rows'} = { $self->{sheet}->{Name} => [ $data ] };

        return $ds;
}

=head1 AUTHOR

Dobrica Pavlinusic, C<< <dpavlin@rot13.org> >>

=head1 COPYRIGHT & LICENSE

Copyright 2005-2006 Dobrica Pavlinusic, All Rights Reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=cut

1; # End of WebPAC::Input::Excel