#!/pro/bin/perl

# xls2csv: Convert Microsoft Excel spreadsheet to CSV
#	   (m)'17 [07-06-2017] Copyright H.M.Brand 2008-2017

use strict;
use warnings;

our $VERSION = "3.3";
(my $cmd = $0) =~ s{.*/}{};

use Text::CSV_XS;
use Spreadsheet::Read qw( ReadData row );

sub usage {
    my $err = shift and select STDERR;
    print "usage: $cmd [ --all [ --no-pfx ] | -o file.csv ] file.xls\n";
    @_ and print join "\n", @_, "";
    exit $err;
    } # usage

use Getopt::Long qw( :config bundling noignorecase passthrough );
GetOptions (
    "help|?"    => sub { usage 0; },
    "V|version" => sub { print "$cmd [$VERSION]\n"; exit 0; },

    "o|c|out=s" => \my $csv,
    "f|force!"  => \my $opt_f,

    "A|all!"    => \my $opt_a,
    "N|no-pfx!" => \my $opt_N,
    ) or usage 1;

my $xls;
foreach my $i (reverse 0 .. $#ARGV) {
    -f $ARGV[$i] or next;
    $xls = splice @ARGV, $i, 1;
    last;
    }

   $xls or usage 1, "No input file";
-r $xls or usage 1, "Input file unreadable";
-s $xls or usage 1, "Input file empty";

if ($opt_a) {
    my $ss = ReadData ($xls)  or die "Cannot read/parse $xls\n";
    $csv and $xls = $csv;
    $xls =~ s/\.(csv|xlsx?)$//i;
    $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\r\n" });
    foreach my $si (1 .. $ss->[0]{sheets}) {
	my $s  = $ss->[$si]   or next;
	my $mc = $s->{maxcol} or next;
	my $mr = $s->{maxrow} or next;
	my $sn = $s->{label} || "sheet-$si";
	   $sn =~ s/\s+$//;
	   $sn =~ s/^\s+//;
	   $sn =~ s/[^-\w.]+/_/g; # remove any special chars from worksheet name
	my $fn = $opt_N ? "$sn.csv" : "$xls-$sn.csv";
	-f $fn && !$opt_f and die "$fn already exists\n";
	warn "Saving sheet to $fn ...\n";
	open my $fh, ">:encoding(utf-8)", $fn or die "$fn: $!\n";
	$csv->print ($fh, [ row ($s, $_) ]) for 1 .. $mr;
	close $fh;
	}
    exit;
    }

$csv or ($csv = $xls) =~ s/\.xlsx?$/.csv/i;
if (-f $csv) {
    $opt_f or die "$csv already exists\n";
    unlink $csv;
    }

warn "Converting $xls to $csv ...\n";
open STDOUT, ">", $csv or die "$csv: $!\n";
exec "xlscat", "-c", @ARGV, $xls;