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

use Getopt::Long;
use File::Basename;
use XlsPerl;

$|++; # no suffering from buffering

my( $file, $perlCode );
my $worksheetNumber = 1;
my $headline        = 0;
my $visible         = 0;
my $colSeparator    = "\t";

GetOptions( 'file=s'       => \$file,
            'sheet=i'      => \$worksheetNumber,
            'headline=i'   => \$headline,
            'ane=s'        => \$perlCode,
            'visible'      => \$visible,
            'colseparator' => \$colSeparator,
       ) or die &PrintUsage();

my $xlsPerl = XlsPerl->new( excelFile       => $file,
                            worksheetNumber => $worksheetNumber,
                       )
    ->open;

#if( $visible ) {
    $xlsPerl->excel->{Visible} = 1;
#} # if

# skip all lines until -headline if available
$xlsPerl->currentRow( $headline ) if $headline;

# TODO: the global @F is dirty... use a better way, e.g. with alias
use vars qw(@F);
while( $xlsPerl->getNextRow ) {
    my $R = $xlsPerl->currentRow();
    local $_ = join($colSeparator, @F ) . "\n";

    eval $perlCode;
    die $@ if $@;

} # while

# ------------------------------------------------------------
sub PrintUsage {

    my $bin = File::Basename::basename($0);
    die <<EOH;
$bin -file filename -sheet 1 -headline 1 -visible

try perldoc $bin for more details

EOH

} # PrintUsage
# ------------------------------------------------------------

=pod

=head1 xlsPerl

You know about the perl parameters -ane? This script is a way to try to do
the same thing to Excel files. But although working somehow, it isn't finished.

=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). It splits the cells into an array with name @F (like perl -ane),
and executes a given bit of perl code for each row.

If you modify elements of @F, you modify the excel cell the value comes from.
Beware: this feature is still very experimental

=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: line number of headline (used for skipping headline)
  -ane             String:  Perl-Code to execute
  -colseparator    String:  how to join columns in $_ (default: \t)

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
  $_               Row joined by -colseparator, with \n at the end

=head1 Bugs/...

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

If you do changes, you have to save the workbook by yourself, that's the
reason why the parameter -visible always is on.

=head1 Author

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

=cut