view release on metacpan or  search on metacpan

scripts/xlsx2csv  view on Meta::CPAN

    print "usage: $cmd [-A [-N | -J c] | -o file.csv] [-s sep] [-f] [-i] file.xls\n";
    print "       $cmd --help | --man | --info\n";
    print "          --list    List supported spreadsheet formats and exit\n";
    print "    -A    --all     Export all sheets        (filename-sheetname.csv)\n";
    print "    -N    --no-pfx  No filename prefix on -A (sheetname.csv)\n";
    print "    -Z    --zip     Convert sheets to CSV's in ZIP\n";
    print "    -J s  --join=s  Use s to join filename-sheetname (-)\n";
    print "    -o f  --out=f   Set output filename\n";
    print "    -i f  --in=f    Set input  filename\n";
    print "    -f    --force   Force overwrite output if exists\n";
    print "    -s s  --sep=s   Set CSV separator character\n";
    print "Unless -A is used, all other options are passed on to xlscat\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; },
      "man"		=> sub { pod_nroff    (); },
      "info"		=> sub { pod_text     (); },
      "list"		=> sub { list_parsers (); },

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

    "s|sep=s"		=> \ my $opt_s,

    "A|all!"		=> \ my $opt_A,
    "N|no-pfx!"		=> \ my $opt_N,
    "Z|zip!"		=> \ my $opt_Z,
    "J|join=s"		=> \(my $opt_J = "-"),

    "v|verbose:1"	=> \(my $opt_v = 0),
    ) or usage 1;

sub list_parsers {
    print "Ext   Parser module             Req   Has Def\n";
    print "----- ----------------------- ----- ----- ---\n";
    for (Spreadsheet::Read::parsers ()) {
	printf "%-5s %-23s %5s %5s %s\n",
	    $_->{ext}, $_->{mod}, $_->{min}, $_->{vsn}, $_->{def} ? "<==" : "";
	}
    exit 0;
    } # list_parsers

sub pod_text {
    require Pod::Text::Color;
    my $m = $ENV{NO_COLOR} ? "Pod::Text" : "Pod::Text::Color";
    my $p = $m->new ();
    open my $fh, ">", \my $out;
    $p->parse_from_file ($0, $fh);
    close $fh;
    print $out;
    exit 0;
    } # pod_text

sub pod_nroff {
    first { -x "$_/nroff" } grep { -d } split m/:+/ => $ENV{PATH} or pod_text ();

    require Pod::Man;
    my $p = Pod::Man->new ();
    open my $fh, "|-", "nroff", "-man";
    $p->parse_from_file ($0, $fh);
    close $fh;
    exit 0;
    } # pod_nroff

unless ($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";

my @known_ext = Spreadsheet::Read::parses (0);
   @known_ext or @known_ext = qw( csv ods sc sxc xls xlsx );
my $valid_ext = do { local $" = "|"; qr{ \. (?: @known_ext )}xi };

if ($opt_Z) {
    require Archive::Zip;
    $@ and die "--zip requires Archive::Zip, which could not be loaded\n";
    }

my %e = (n => "\n", t => "\t", e => "\e", r => "\r");
$opt_s and $opt_s =~ s/\\+([nter])/$e{$1}/g;

if ($opt_A || $opt_Z) {
    $opt_v and warn "Reading $xls ...\n";
    my $ss = ReadData ($xls) or die "Cannot read/parse $xls\n";
    my $az = $opt_Z ? Archive::Zip->new : undef;
    $csv and $xls = $csv;
    $xls =~ s/$valid_ext $//ix;
    $csv = Text::CSV_XS->new ({ binary => 1, auto_diag => 1, eol => "\r\n" });
    $opt_s and $csv->sep_char ($opt_s);
    foreach my $si (1 .. $ss->[0]{sheets}) {
	my $s  = $ss->[$si]   or next;
		 $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
	if ($opt_Z) {
	    open my $fh, ">:encoding(utf-8)", \my $data;
	    $csv->print ($fh, [ row ($s, $_) ]) for 1 .. $mr;
	    close $fh;
	    $az->addString ($data, "$sn.csv");
	    next;
	    }
	my $fn = $opt_N ? "$sn.csv" : "$xls$opt_J$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";