#!/usr/bin/perl -w

# text2xls.pl
# pod at tail

use strict;
use Spreadsheet::WriteExcel;
use Getopt::Long;

# Crank it up
print("\nStarting $0\n");

# Get command line switches and arguments
my %option = ('defaultdelim' => ',',
              'defaultencoding' => 'cp1252',
              );
GetOptions(
    'infile=s'      => \$option{csvin},
    'outfile=s'     => \$option{xlsout},
    'delim=s'       => \$option{delim},
    'header!'       => \$option{header},
    'encoding=s'    => \$option{csvenc},
    'zerolead!'     => \$option{zerolead},
    'forcetext=s'   => \$option{forcetext},
    );
unless (defined ($option{csvin} && $option{xlsout})) {
    &USAGE();
    exit;
    }
unless (defined $option{delim}) {
    $option{delim} = $option{defaultdelim};
    }
unless (defined $option{header}) {
    $option{header} = 0;
    }
unless (defined $option{csvenc}) {
    $option{csvenc} = $option{defaultencoding};
    }
unless (defined $option{zerolead}) {
    $option{zerolead} = 0;
    }
# Do the dirty work
open CSVFILE, "<:encoding(".$option{csvenc}.")", $option{csvin} or die "Error opening $option{csvin} +: $!";

my $workbook  = Spreadsheet::WriteExcel -> new($option{xlsout});
my $worksheet = $workbook -> addworksheet();
if($option{zerolead} == 1) {
   $worksheet -> keep_leading_zeros();
   }
my @textcols = split ',', $option{forcetext};

# Load the columns into the hash for search later...
my %textcols_h;
foreach (@textcols) {
    $textcols_h{$_} = 1;
};

my $row       = 0;
while (<CSVFILE>) {
    chomp;
    my @field = split("$option{delim}", $_);
    my $column = 0;
    foreach my $token (@field) {
        if (exists $textcols_h{ $column+1 }) {
          $worksheet -> write_string($row, $column, $token);
        } else {
          $worksheet -> write($row, $column, $token);
        }
        $column++;
        }
    $row++;
    }

# Prettify row as header
if($option{header} == 1) {
    my $header = $workbook -> addformat();
        $header -> set_bold();
        $header -> set_align('center');
        $header -> set_bg_color('tan');
        $header -> set_border();
    $worksheet -> set_row(0, undef, $header);
    }

# Optional, unless doing some external action against xls file
$workbook  -> close()         or die "Error closing $workbook: $!";

# Go tell it on the mountain
print("  infile  = $option{csvin}\n");
print("  outfile = $option{xlsout}\n");
print("Finished $0\n\n");

########################################################
sub USAGE {
print <<EOF

Required switches:
  -i or --infile csv_infilename
  -o or --output xls_outfilename

Optional switches:
-d or --delim alt_delimiter
  default delimiter is ','
-e or --encoding encoding_format
  default encoding is 'cp1252'
-h or --header
  Accepts no arguments.
  Bolds and background colors contents of topmost row.
-z or --zerolead
  Accepts no arguments.
  Leading zeros in values are preserved.
-f or --forcetext
  Accepts comma separated list of columns ie: '1,3,7'.
  Each column listed will be forced to be text.

Example:
  text2xls.pl -i file1.csv -o file2.xls -d * -h -z

EOF
;
}
########################################################

=head1 Name

 text2xls.pl

=head1 Description

 Convert text file in csv format to Excel binary format

=head1 Usage

 Required switches:
   -i or --infile csv_infilename
   -o or --output xls_outfilename

 Optional switches:
 -d or --delim alt_delimiter
   default delimiter is ','
 -e or --encoding encoding_format
   default delimiter is 'cp1252'
 -h or --header
   Accepts no arguments.
   Bolds and background colors contents of topmost row.
 -z or --zerolead
   Accepts no arguments.
   Leading zeros in values are preserved.
 -f or --forcetext
   Accepts comma separated list of columns ie: '1,3,7'.
   Each column listed will be forced to be text.

 Example:
   text2xls.pl -i file1.csv -o file2.xls -d * -h -z -f '8'

=head1 Tested

 Spreadsheet::WriteExcel 0.31
 Getopt::Long            2.19
 Perl                    5.00503
 Debian                  2.2r2 "Espy"
 Excel                   9.0.3821 SR-1
                         97 SR-2
 Win32                   5.0.2195 SP-1
                         4.0 SP-6a

=head1 Updated
 2011-11-09   10:10  Added support for --zerolead & --forcetext
                     Corrected the default encoding
                     Corrected the Usage text
 2010-05-15   18:56  Added support of encoding (FVC)  
 2001-04-17   10:45  Renamed as text2xls - avoid conflict with existin
+g csv2xls.
                     Tweaked todos re feedback from jmcnamara (S::WE a
+uthor).
 2001-04-16          Initial working code & posted to PerlMonks

=head1 Todos

 Use Text::xSV by tilly or Text::CSV_XS for parsing text infile
 Review and fix "prettify row header" which somewhat works, but not qu
+ite right.
   Support for "freeze pane" anticipated in future S::WE
 Test with different delimiters.
 Use Pod::Usage to instead of &USAGE(). But appears to only be in Perl
+ 5.6+ 8^(
 Use @headerparms and foreach loop in headers section to reduce duplic
+ation.
 Accept multiple infiles.
 Save as "file.xls" (provided infile is "file") with -r switch.
 Add support for "page delimiter" character for multiple worksheets.
 Add support for $VERSION.

=head1 Author

 ybiC

=head1 Credits

 Thanks to jmcnamara, Petruchio and zdog for suggestions,
 and to jmcnamara for excellent pod in Spreadsheet::WriteExcel,
 and vroom, of course for PM.

=cut