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

our $VERSION = 0.10;

use Win32;          # automatically loaded with Activestate Perl
use File::Basename; # for PrintUsage
use Getopt::Long;   # for parsing program arguments
use FindBin;
use lib $FindBin::Bin;
use ExcelPerl;
use Win32::OLE::Const 'Microsoft Excel';

$|++; # no suffering from buffering

my( $file, $perlCode );
my $worksheetNumber = 1;    # use first worksheet as default
my $headline        = 0;    # don't skip a headline as default
my $colSeparator    = "\t"; # standard col separator for $_

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

# check file and convert it to absolute path if necessary
defined $file or die &PrintUsage();
$file = Win32::GetFullPathName( $file );

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

$xlsPerl->excel->{Visible} = 1;

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

# evaluate the perl code from argument -ane ( thanks @pKai for the idea )
my $aneCode = eval "sub {
    my \$xlsPerl = shift;
    my \$c = shift;
    my \@c = \@\$c;
    my \@F = \@_;

    # prepare some other helpful variables
    local \$. = \$xlsPerl->currentRow();
    no warnings \'uninitialized\';
    local \$_ = join( \$xlsPerl->colSeparator, \@F ) . \"\\n\";
    $perlCode;
}";
die $@ if $@;

my @F;
while( my @cells = $xlsPerl->getNextRow( \@F ) ) {
    $aneCode->($xlsPerl, \@cells, @F);
} # while

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

    my $bin = File::Basename::basename($0);
    die <<EOH;
$bin -sheet 1 -headline 1 -ane "print qq~\$.: \$F[0], \$F[1]\\n~" -file excelFile

try perldoc $bin for more details

EOH

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

=pod

=head1 excelPerl

You know about the perl parameters -ane? This script is a way to try
to do the same thing to Excel files.

=head1 Prerequisites

=over 1

=item Win32::OLE

=item Excel needs to be installed

=back

=head1 Description

Like perl -ane with a plaintext file, excelPerl.pl loops over an excel
file and automatically (-a) splits up the columns of one row into an
array with the name @F. Since @F is a ied array, by changing one
element of @F you change the content of the excel cells.

If you need one whole line with a tailing \n, you can use the variable
$_ (like perl -ne) which joins @F by the value given in the parameter
-colseparator (default: \t)

If you need to know the current line number (like $. for while), you
can use the special variable $. (big surprise).

=head1 Params:

  -file            String:  Name of excel file (full path!)
  -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

  excelPerl.pl -s 2 -h 1 -ane "print qq~$.: $F[0] $F[5]\n~" -f c:\test.xls

  excelPerl.pl -ane "$c[0]->{Font}->{Name} = 'Courier New'" -f c:\test.xls

=head1 Special Variables:

  $.    Row number
  @F    Array containing the values of one complete row
  $_    Row joined by -colseparator, with \n at the end
  @c    Advanced: List of cells (e.g. to change a format). You may even
        use excel constants.

=head2 Some examples for $c[$i]-> ($i is index of column):

  {Font}->{Name}           name of font: 'Courier New', 'Arial', ...
  {Font}->{FontStyle}      style of font: 'Bold Italic', ...
  {Font}->{Size}           font size: ... 10 11 12 ...
  {Font}->{Strikethrough}  strike through: 0 or 1
  {HorizontalAlignment}    align: xlCenter, xlRight, xlLeft, xlJustify
  {VerticalAlignment}      align vertically: xlCenter, xlTop, xlBottom
  {WrapText}               wrap text in line: 0 or 1
  {FormulaR1C1}            formula: "=CONCATENATE(RC[-1],$R[-1]C[-1])"

=head1 Bugs/...

This code is very, very, very experimental!

If you do changes, you better save the workbook manually, that's the
reason why the parameter -visible always is on.

=head1 SEE ALSO

I heard about XLSperl from John Allen which is a great tool. But it
only allows to read excel files and not to change them on the fly.
Since I often need this feature, I decided to write it with
Win32::OLE, and it was not difficult. But unlike XLSperl, excelPerl
only runs under Windows and needs an installed version of Excel. But
starting with v0.10, it supports @c which is a list containing the
cells of the actual row. You can query or modify elements of @c to
get or set cell properties like format, alignment, ...

You can find XLSPerl at L<http://perl.jonallen.info/projects/xlstools>

=head1 Author

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

=cut