#! /usr/bin/perl
use warnings;
use strict;

use Getopt::Long;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;

$|++;

my $filename;
my $visible   = 0;
my $workSheetNumber = 1;
my $headline  = 0;
my $maxEmptyRows = 20;
my $perlCode = "";

GetOptions('file=s'        => \$filename,        # filename
           'visible'       => \$visible,         # see what you're doing
           'sheetnumber=i' => \$workSheetNumber, # use which worksheet
           'headline=i'    => \$headline,        # not yet used
           'maxemptyrows'  => \$maxEmptyRows,    # how many rows may be empty?
           'ane=s'         => \$perlCode,        # code to execute at @F
       );
my $xls = XlsPerl->new( filename        => $filename,
                        visible         => $visible,
                        worksheetnumber => $workSheetNumber,
                        headline        => $headline,
                   )
    ->open;

while( my @F = $xls->getNextRowData ) {

    # setting up some variables
    my $R = $xls->currentRow;
    # ...

    eval "$perlCode"; die $@ if $@;

    # update row in excel sheet
    # TODO

} # for $row
# ============================================================
package XlsPerl;
use Carp qw(croak);
# ------------------------------------------------------------
sub new {
    my( $class, %params ) = @_;

    my $self = bless( {}, $class );
    foreach my $param (keys %params) {
        $self->_getSet( $param, $params{$param} );
    } # foreach

    return $self;
} # new
# ------------------------------------------------------------
sub open {
    my( $self ) = @_;

    my $filename = $self->filename;

    defined $filename or croak( "Error: no filename given");
    -f $filename or croak( "Error: filename '$filename' doesn't exist");

    my $excel; # try to re-use running instance of Excel
    eval { $excel = Win32::OLE->GetActiveObject( 'Excel.Application' ) };
    die "Error: no Excel installed\n" if $@;

    unless( defined $excel ) { # if not running, start excel
        $excel = Win32::OLE->new( 'Excel.Application', sub { $_[0]->Quit } )
            or die "Error: can't start Excel\n";
    } # unless
    $self->excel( $excel );

    # set window visible if it is wanted
    $self->setVisible;

    my $book = $excel->Workbooks->Open( $self->filename );
    $self->workbook( $book );

    my $sheet = $book->Worksheets( $self->worksheetnumber );
    $self->worksheet( $sheet );

    $self->currentRow( 1 ); # TODO: headline
    $self->emptyRowCount( 0 );

    return $self;
} # open
# ------------------------------------------------------------
sub setVisible {
    my $self = shift;
    $self->excel->{Visible} = $self->visible if $self->excel;
} # setVisible
# ------------------------------------------------------------
sub getNextRowData {
    my( $self ) = @_;

    my $row = $self->currentRow;
    my $sheet = $self->worksheet;
    return if $row > $sheet->Rows->Count;

#    my $maxCols = $sheet->Columns->Count;
    my $rowData = $sheet->Range("A$row:IV$row")->{Value}; # dirty

    # "trimright" range (remove undefs at the end)
    my( $itemsFound, @row) = (0);
    foreach my $value ( reverse @{ $rowData->[0] } ) {
        if( defined $value ) { unshift( @row, $value ); $itemsFound++  }
        else                 { unshift( @row, ''     ) if scalar @row  }
    } # foreach

    # check
    unless( $itemsFound ) {
        $self->increaseEmptyRowCount;
        return if $self->emptyRowCount > $maxEmptyRows;
        return $self->getNextRowData();
    } # unless
    else {
        $self->emptyRowCount( 0 );
    } # else

    $self->increaseCurrentRow;
    return @row;
} # getNextRowData
# ------------------------------------------------------------
sub increaseCurrentRow {
    my $self = shift;
    $self->currentRow( 1 + $self->currentRow );
} # increaseCurrentRow
# ------------------------------------------------------------
sub increaseEmptyRowCount {
    my $self = shift;
    $self->emptyRowCount( 1 + $self->emptyRowCount );
} # increaseEmptyRowCount
# ------------------------------------------------------------

# ------------------------------------------------------------
# Object Interface Methods
# ------------------------------------------------------------
sub filename {
    my $self = shift;
    $self->_getSet( 'filename', @_ );
} # filename
# ------------------------------------------------------------
sub excel {
    my $self = shift;
    $self->_getSet( 'excel', @_ );
} # excel
# ------------------------------------------------------------
sub workbook {
    my $self = shift;
    $self->_getSet( 'workbook', @_ );
} # worksheet
# ------------------------------------------------------------
sub worksheet {
    my $self = shift;
    $self->_getSet( 'worksheet', @_ );
} # worksheet
# ------------------------------------------------------------
sub worksheetnumber {
    my $self = shift;
    $self->_getSet( 'worksheetnumber', @_ );
} # worksheetnumber
# ------------------------------------------------------------
sub visible {
    my $self = shift;
    $self->_getSet( 'visible', @_ );
} # visible
# ------------------------------------------------------------
sub headline {
    my $self = shift;
    $self->_getSet( 'headline', @_ );
} # visible
# ------------------------------------------------------------
sub currentRow {
    my $self = shift;
    $self->_getSet( 'currentRow', @_ );
} # currentRow
# ------------------------------------------------------------
sub emptyRowCount {
    my $self = shift;
    $self->_getSet( 'emptyRowCount', @_ );
} # emptyRowCount
# ------------------------------------------------------------

# ------------------------------------------------------------
# private subs
# ------------------------------------------------------------
sub _getSet {
    my( $self, $attribute, @values) = @_;

    $self->{ $attribute} = $values[0] if scalar @values;

    return $self->{ $attribute };
} # _getSet
# ------------------------------------------------------------
sub DESTROY {
    my $self = shift;
    $self->excel->Quit if $self->excel;
} # DESTROY
# ------------------------------------------------------------

=pod

=head1 Prerequisites

=over 1

=item Win32::OLE

=item Excel needs to be installed

=back


=head1 Description

This script xlsPerl.pl loops over an excel file row by row (like
perl -ne), but skips empty rows. It splits the cells into an array
with name @F (like perl -ane), and executes a given bit of perl code
for each row.

Since I didn't find an easy way to find out how many rows are in this
excel sheet, I told the script to stop after -maxemptyrows empty lines
in a sequence.

Only the reading part is coded. A way to modify the Excel sheet when you
modify @F needs to be written.

=head1 Params:

  -file            String:  Name of excel file (full path)
  -visible         Boolean: make excel visible (funny, but not yet useful)
  -sheetnumber     Integer: which sheet shell I read (1..n, default: 1)
  -headline        Integer: not yet implemented (line number of headline)
  -maxemptyrows    Integer: stop after how many empty rows (default: 20)
  -ane             String:  Perl-Code to execute

Abbreviations of params are allowed, as long as they are unique, e.g

  xlsPerl.pl -s 2 -m 10 -ane "print qq~$R: $F[0] $F[5]\n" -f c:\test.xls

=head1 Special Variables:

  $R               Row number
  @F               Array containing the values of one complete row

=head1 Bugs/...

This code is very, very, very experimental, and some parts are dirty!

=head1 Author

Martin Fabiani L<http://www.fabiani.net/>

=cut