#!C:\Perl\bin\perl.exe 
#line  15 

use strict;
use warnings;
use Cwd;
use English qw( -no_match_vars );
use File::Basename;
use File::Spec::Functions;
use Getopt::Long;
use Pod::Usage;
use Readonly;
use Spreadsheet::ParseExcel;
use Encode 'encode', 'decode';
use Data::Dumper;
use Date::Simple (':all');

Readonly my $VERSION   => '0.01';
Readonly my $EMPTY_STR => q{};

my $delimiter = ";";
my ( $help, $man_page, $show_version );
 
GetOptions(
    'd|ofs:s' => \$delimiter,
    'help'    => \$help,
    'man'     => \$man_page,
    'version' => \$show_version,
) or pod2usage( 2 );

if ( $help || $man_page ) {
    pod2usage({
        -exitval =>  0 ,
        -verbose => $man_page ?  2  :  1 
    });
}; 

if ( $show_version ) {
    my $prog = basename( $PROGRAM_NAME );
    print "$prog v$VERSION\n";
    exit  0 ;
}

my @files = @ARGV or pod2usage('No input files');
my $cwd   = cwd();
my ( $num_files_processed, $num_out_files ) = (  0 ,  0  );

my $parse_excel = new Spreadsheet::ParseExcel(CellHandler => \&cell_handler,
                                              NotSetCell  =>  1 );

INPUT_FILE:
for my $file ( @files ) {
    unless ( -e $file && -s _ && -r _ ) {
        warn "'$file' doesn't exist, is zero-length or unreadable, skipping.\n";
        next INPUT_FILE;
    }
    
    my $prev_row        = 0;
    my $prev_col        = 0;
    my $prev_sheet      = -1;
    my $ws_worked_count = 0;
    my $out_fh;
    my $ws_name;
    my $max_row = 0;
    my $temp_value = '';
    my $check_value;
    my $last_row = 0;

    my $workbook = $parse_excel->Parse( $file );

    if ( ref $workbook->{'Worksheet'} ne 'ARRAY' ) {
        warn "'$file' has no worksheets (not an Excel spreadsheet?)\n";
        next INPUT_FILE;
    }

    sub cell_handler {

        my $workbook    = $_[0];
        my $sheet_index = $_[1];
        my $row         = $_[2];
        my $col         = $_[3];
        my $cell        = $_[4];
        
        if ($sheet_index != $prev_sheet) {
            my $ws = $workbook->{Worksheet}[$sheet_index];
            $ws_name = $ws->{Name};
            $max_row = $ws->{'MaxRow'};
        }
        
        if ( 
             (encode('cp1251',$ws_name) ne "Список")               and
             (encode('cp1251',$ws_name) ne "Справочники")          and
             (encode('cp1251',$ws_name) ne "Справочник налоговых") and
             (encode('cp1251',$ws_name) ne "Банки")                and
             (encode('cp1251',$ws_name) ne "Назначения")           and
             (encode('cp1251',$ws_name) ne "Методы платежа")       and
             (encode('cp1251',$ws_name) ne "Остатки")              and
             (encode('cp1251',$ws_name) ne "Циклические элементы") and
             (encode('cp1251',$ws_name) ne "Данные по вычетам")
           ) {
            
            if ($sheet_index != $prev_sheet) {
                if ($ws_worked_count > 0) {
                    close $out_fh;
                }
                $prev_row     = 0;
                $last_row     = 0;
                $temp_value   = '';
                $ws_worked_count++;
                $ws_name      =~ s/.txt//o;
                $ws_name      =~ tr[a-z][A-Z];
                my $out_file  = encode('cp1251', catfile($cwd, "CONV_$ws_name.csv"));
                open $out_fh, '>', $out_file
                    or die "Can't write to '$out_file': $!\n";
            }
            
            # Reset the col counter between rows
            if ($row != $prev_row) {
                $prev_col =  0 ;
                if ($row >  5 ) {
                    $check_value = $temp_value;
                    $check_value =~ s/$delimiter//g;
                    $check_value =~ s/\ //g;
                    if ($check_value) {
                        if ($row >  6 ) {
                            print {$out_fh} "\n";
                        }
                        print {$out_fh} $temp_value;
                    }
                    $temp_value = '';
                }
            }
            
            # Add delimiter between fields and newlines between rows, pad
            # any missing rows or columns, also print the formatted value
            # of the cell.
            if ($row >  4 ) {
                my $out_value;
                if ($cell->{Type} eq 'Numeric') {
                    $out_value = $cell->{Val};
                    $out_value =~ s/\./\,/g;
                }
                elsif ($cell->{Type} eq 'Date') {
                    $out_value = date('1899-12-30') + $cell->{Val};
                    $out_value++ if $cell->{Val} <  60 ;
                    $out_value = $out_value->format("%d.%m.%Y");
                }
                else {
                    $out_value = $cell->{_Value};
                    if ($out_value =~ m/\"/g) {
                        $out_value =~ s/\"/\"\"/g;
                        $out_value = "\"$out_value\"";
                    }
                }
                if ($row < $max_row) {
                    $temp_value = $temp_value . $delimiter for $prev_col + 1  .. $col;
                    $temp_value = $temp_value . encode('cp1251', $out_value);
                }
                elsif (
                        ($row = $max_row) and
                        ($last_row ==  0 )  and
                        ($out_value)      and
                        ($out_value ne ' ')
                      ) {
                    $last_row =  1 ;
                    print {$out_fh} "\n" . encode('cp1251', $out_value);
                }
                elsif (
                        ($row = $max_row) and
                        ($last_row ==  1 )
                      ) {
                    print {$out_fh} $delimiter for $prev_col + 1  .. $col;
                    print {$out_fh} encode('cp1251', $out_value);
                }
            }
            
            # Keep track of where we are
            $prev_row   = $row;
            $prev_col   = $col;
            $prev_sheet = $sheet_index;
        }
    }
    
    $num_files_processed++;
}

printf "Done, processed %s Excel files, created %s data files.\n",
    $num_files_processed, $num_out_files;

__END__

# ----------------------------------------------------
=head1 NAME

TO_CSV - convert Excel data to delimited text files

=head1 VERSION

This documentation refers to TO_CSV version  1 . 00 / 01 .

=head1 SYNOPSIS

  TO_CSV [options] File1.xls [File2.xls ...]

Options:

  -d|-ofs       Output field delimiter (default is ;)
  --help        Show brief help and exit
  --man         Show full documentation
  --version     Show version and exit

=head1 DESCRIPTION

For each worksheet within an Excel spreadsheet, creates a delimited 
file.  By default, the output files will use a ; character as the 
delimiter.  Use the "-d" switch to specify something else.

=head1 SEE ALSO

Spreadsheet::ParseExcel.

=head1 AUTHOR

Ken Youens-Clark

=head1 COPYRIGHT

Copyright (c)  2005  Ken Youens-Clark

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

=cut

__END__