view release on metacpan or  search on metacpan

examples/csv2xlsx  view on Meta::CPAN

use List::Util   qw( first                                    );
use Date::Calc   qw( Delta_Days Days_in_Month                 );
use Encode       qw( from_to                                  );
use Getopt::Long qw(:config bundling passthrough noignorecase );
my $quo = '"';
my $esc = '"';
my $wdt = 4;		# Default minimal column width
my $fac = "diag";	# Formula action (default is warn only)
my $dtf = "dd-mm-yyyy";	# Date format
my $crf = "";		# Currency format, e.g.: $:### ### ##0.00
my $opt_v = 1;
my $dtc;

GetOptions (
    "V|version"		=> sub { say $0 =~ s{.*/}{}r, " [$VERSION]"; exit 0; },
      "help|?"		=> sub { usage (0); },
      "man"		=> sub { pod_nroff (); },
      "info"		=> sub { pod_text  (); },

    "c|s|sep=s"		=> \my $sep, # Set after reading first line in attempt to auto-detect
    "q|quo=s"		=> \$quo,
    "e|esc=s"		=> \$esc,
    "w|width=i"		=> \$wdt,
    "o|x|out=s"		=> \my $xls,
    "d|date-fmt=s"	=> \$dtf,
    "D|date-col=s"	=> \$dtc,
    "C|curr-fmt=s"	=> \$crf,
      "font=s"		=> \my $font,
      "fs|font-size=i"	=> \my $font_size,
    "f|force!"		=> \my $frc,
    "F|formulas!"	=> \my $frm,
      "Fa=s"		=> \$fac,
      "Ft"		=> sub { $fac = "none";		},
      "Fd"		=> sub { $fac = "die";		},
      "Fc"		=> sub { $fac = "croak";	},
      "FD"		=> sub { $fac = "diag";		},
      "Fe"		=> sub { $fac = "empty";	},
      "Fu"		=> sub { $fac = "undef";	},
    "u|utf-8|utf8!"	=> \my $utf,
      "de|fix-utf8!"	=> \my $dutf, # double encoded? \x{c3}\x{ab} => \x{100}
    "m|merge!"		=> \my $mrg,
      "skip-empty!"	=> \my $skip_empty,
    "S|split=s"		=> \my @split,
      "sl|split-label=s"=> \my $split_sl,
    "L|row-limit=i"	=> \my $row_limit,
    "v|verbose:2"	=> \$opt_v,
    ) or usage (1);

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 or die "Cannot generate manual: $!\n";
    $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" or die "Cannot generate manual: $!\n";
    $p->parse_from_file ($0, $fh);
    close $fh;
    exit 0;
    } # pod_nroff

if ($mrg) {
    my @csv;
    for (@ARGV) {
	if (m/\.xlsx?$/i) {
	    $xls and usage (1);
	    $xls = $_;
	    next;
	    }
	if (m/\.(?:csv|png|jpe?g|bmp|gif|tiff|xpm)$/i && -s) {
	    push @csv => $_;
	    next;
	    }
	unless ($skip_empty) {
	    warn "Argument $_ is not an existing (CSV) file\n";
	    usage (1);
	    }
	}
    $xls && @csv or usage (1);
    @ARGV = @csv;
    }

sub col2col {
    my $l = shift;
    $l =~ m/^[0-9]/ and return $l;
    my $c = 0;
    while ($l =~ s/^([A-Za-z])//) {
	$c = 26 * $c + 1 + ord (uc $1) - ord ("A");
	}
    $c;
    } # col2col

foreach my $split (@split) {
    my ($col, $operator, $pat) = ($split =~ m{^
	([0-9]+|[A-Z]+|[a-z]+)	# Column: A, AB, 1, 14
	([=/uU<>])		# Operator
	(.*)			# Pattern/string
	\z}x) or usage (1);
    my $case = $col =~ m/^[a-z]/ ? 1 : 0;
    $split = {
	col => col2col ($col),
	op  => $operator,
	str => $pat,
	ic  => $case,
	lbl => undef,
	};
    if ($split_sl) {
	($col) = ($split_sl =~ m/^([0-9]+|[A-Z]+)$/) or usage (1);
	$split->{lbl} = col2col ($col);
	}
    }