| 1 |
#!/usr/bin/perl |
| 2 |
|
| 3 |
# xlscat: show supported spreadsheet file as Text |
| 4 |
# xlsgrep: grep pattern |
| 5 |
# (m)'24 [2024-12-31] Copyright H.M.Brand 2005-2026 |
| 6 |
|
| 7 |
require 5.008001; |
| 8 |
|
| 9 |
use strict; |
| 10 |
use warnings; |
| 11 |
|
| 12 |
our $VERSION = "3.32"; |
| 13 |
(my $CMD = $0) =~ s{.*[\/]}{}; |
| 14 |
|
| 15 |
my $is_grep = $0 =~ m/grep$/; # xlsgrep |
| 16 |
|
| 17 |
sub usage { |
| 18 |
my $err = shift and select STDERR; |
| 19 |
my $p = $is_grep ? " pattern" : ""; |
| 20 |
print join "\n" => |
| 21 |
"usage: $CMD\t[-s <sep>] [-L] [-n] [-A] [-u] [Selection]$p file.xls", |
| 22 |
" \t[-c | -m] [-u] [Selection]$p file.xls", |
| 23 |
" \t -i [-S sheets]$p file.xls", |
| 24 |
" Generic options:", |
| 25 |
" -v[#] Set verbose level (xlscat/xlsgrep)", |
| 26 |
" -d[#] Set debug level (Spreadsheet::Read)", |
| 27 |
" --list Show supported spreadsheet formats and exit", |
| 28 |
" -u Use unformatted values", |
| 29 |
" --strip[=#] Strip leading and/or traing spaces of all cells", |
| 30 |
" # & 01 = leading, # & 02 = trailing, 3 = default", |
| 31 |
" --clip=# Clip cells to max length #", |
| 32 |
" --noclip Do not strip empty sheets and", |
| 33 |
" trailing empty rows and columns", |
| 34 |
" --no-empty Skip empty rows", |
| 35 |
" --no-nl[=R] Replace all newlines in cells with R (default space)", |
| 36 |
" -e <enc> Set encoding for input and output", |
| 37 |
" -b <enc> Set encoding for input", |
| 38 |
" -a <enc> Set encoding for output", |
| 39 |
" -U Set encoding for output to utf-8 (short for -a utf-8)", |
| 40 |
" Input CSV:", |
| 41 |
" --in-sep=c Set input sep_char for CSV (c can be 'TAB')", |
| 42 |
" Input XLS:", |
| 43 |
" --dtfmt=fmt Specify the default date format to replace 'm-d-yy'", |
| 44 |
" the default replacement is 'yyyy-mm-dd'", |
| 45 |
" --passwd=pw Specify the password for workbook", |
| 46 |
" if pw = -, read password from keyboard", |
| 47 |
" --formulas Show the formula instead of the value", |
| 48 |
" Output Text (default):", |
| 49 |
" -s <sep> Use separator <sep>. Default '|', \\n allowed", |
| 50 |
" Overrules ',' when used with --csv", |
| 51 |
" -L Line up the columns", |
| 52 |
" -B --box Like -L but also add outer frame", |
| 53 |
" -n [skip] Number lines (prefix with column number)", |
| 54 |
" optionally skip <skip> (header) lines", |
| 55 |
" -A Show field attributes in ANSI escapes", |
| 56 |
" -h[#] Show # header lines", |
| 57 |
" -D Dump each record with Data::Peek or Data::Dumper", |
| 58 |
" --hash Like -D but as hash with first row as keys", |
| 59 |
" Output CSV:", |
| 60 |
" -c Output CSV, separator = ','", |
| 61 |
" -m Output CSV, separator = ';'", |
| 62 |
$is_grep ? ( |
| 63 |
" Grep options:", |
| 64 |
" -i Ignore case", |
| 65 |
" -w Match whole words only") : ( |
| 66 |
" Output Index only:", |
| 67 |
" -i Show sheet names and size only"), |
| 68 |
" Output HTML:", |
| 69 |
" -H Output HTML", |
| 70 |
" Selection:", |
| 71 |
" -S <sheets> Only print sheets <sheets>. 'all' is a valid set", |
| 72 |
" Default only prints the first sheet", |
| 73 |
" -R <rows> Only print rows <rows>. Default is 'all'", |
| 74 |
" Ranges and lists supported as 2,4-7,8-", |
| 75 |
" Trailing - is to end of data", |
| 76 |
" Negative rows count from tail -8--2 is allowed", |
| 77 |
" --head[=n] Alias for -R1..n where n defaults to 10", |
| 78 |
" --tail[=n] Alias for -R-n- where n defaults to 10", |
| 79 |
" --first=C Only show rows where column C is a 'new' value", |
| 80 |
" (suppress rows where value in column C is seen", |
| 81 |
" before in column C). That includes empty fields", |
| 82 |
" -C <cols> Only print columns <cols>. Default is 'all'", |
| 83 |
" -F <flds> Only fields <flds> e.g. -FA3,B16", |
| 84 |
" Ordering (column numbers in result set *after* selection):", |
| 85 |
" --sort=spec Sort output (e.g. --sort=3,2r,5n,1rn+2)", |
| 86 |
" +# - first # lines do not sort (header)", |
| 87 |
" # - order on column # lexical ascending", |
| 88 |
" #n - order on column # numeric ascending", |
| 89 |
" #r - order on column # lexical descending", |
| 90 |
" #rn - order on column # numeric descending", |
| 91 |
"", |
| 92 |
"Examples:", |
| 93 |
" xlscat -i foo.xls", |
| 94 |
" xlscat --in-sep=: --sort=3n -L /etc/passwd", |
| 95 |
" xlsgrep pattern file.ods", |
| 96 |
""; |
| 97 |
@_ and print join "\n", @_, ""; |
| 98 |
exit $err; |
| 99 |
} # usage |
| 100 |
|
| 101 |
use Encode qw( encode decode ); |
| 102 |
use List::Util qw( first ); |
| 103 |
use Data::Dumper; |
| 104 |
use Spreadsheet::Read; |
| 105 |
use Getopt::Long qw(:config bundling noignorecase passthrough); |
| 106 |
my $opt_c; # Generate CSV |
| 107 |
my $opt_F = ""; # Fields to print |
| 108 |
my $opt_v = 0; # Verbosity for xlscat |
| 109 |
my $opt_d = 0; # Debug level for Spreadsheet::Read |
| 110 |
my $opt_h = 0; # Number of header lines for grep or -L |
| 111 |
my $opt_D = 0; # Dump: 0 = none, 1 = array, 2 = hash |
| 112 |
my $clip = 1; |
| 113 |
my $enc_bom; # CSV input encoding derived from BOM |
| 114 |
my $enc_i; # Input encoding |
| 115 |
my $enc_o; # Output encoding |
| 116 |
GetOptions ( |
| 117 |
"help|?" => sub { usage (0); }, |
| 118 |
"V|version" => sub { print "$CMD [$VERSION] Spreadsheet::Read [$Spreadsheet::Read::VERSION]\n"; exit 0; }, |
| 119 |
"list" => sub { list_parsers (); }, |
| 120 |
|
| 121 |
# Input CSV |
| 122 |
"c|csv" => sub { $opt_c = "," }, |
| 123 |
"m|ms" => sub { $opt_c = ";" }, |
| 124 |
"insepchar". |
| 125 |
"|in-sep". |
| 126 |
"|in-sep-char=s" => \my $sep, # Input field sep for CSV |
| 127 |
|
| 128 |
# Input XLS |
| 129 |
"dtfmt". |
| 130 |
"|date-format=s" => \my $dtfmt, # Default date-format for Excel |
| 131 |
"f|formulas!" => \my $opt_f, # Show the formula instead of the value |
| 132 |
"password=s" => \my $passwd, # For encrypted spreadsheets |
| 133 |
|
| 134 |
# Output |
| 135 |
"i|index". |
| 136 |
"|ignore-case!" => \my $opt_i, # Index (cat) | ignore_case (grep) |
| 137 |
"s|separator|sep". |
| 138 |
"|outsepchar". |
| 139 |
"|out-sep". |
| 140 |
"|out-sep-char=s" => \my $opt_s, # Text separator |
| 141 |
"S|sheets=s" => \my $opt_S, # Sheets to print |
| 142 |
"R|rows=s" => \my $opt_R, # Rows to print |
| 143 |
"head:10" => \my $opt_head, |
| 144 |
"tail:10" => \my $opt_tail, |
| 145 |
"first=s" => \my $opt_first, |
| 146 |
"C|columns=s" => \my $opt_C, # Columns to print |
| 147 |
"F|fields=s" => \ $opt_F, |
| 148 |
"L|fit|align!" => \my $opt_L, # Auto-size/align columns |
| 149 |
"B|box|frame!" => \my $opt_B, # Align and add outer frame |
| 150 |
"P|pivot!" => \my $pivot, |
| 151 |
"n|number:0" => \my $opt_n, # Prefix lines with column number |
| 152 |
"A|ansi|color!" => \my $opt_A, # Show field colors in ANSI escapes |
| 153 |
"u|unformatted!" => \my $opt_u, # Show unformatted values |
| 154 |
"v|verbose:1" => \$opt_v, |
| 155 |
"d|debug:1" => \$opt_d, |
| 156 |
"D|dump!" => \ $opt_D, # Use Data::Peek or Data::Dumper |
| 157 |
"hash!" => sub { $opt_D = 2 }, |
| 158 |
"H|html:1" => \my $opt_H, # Output in HTML |
| 159 |
"noclip" => sub { $clip = 0 }, |
| 160 |
"strip:3" => \my $strip, |
| 161 |
"clip=i" => \my $clip_len, |
| 162 |
"sort=s" => \my $sort_order, |
| 163 |
"no-empty" => \my $skip_empty, |
| 164 |
"N|no-nl:s" => \my $opt_N, |
| 165 |
|
| 166 |
# Encoding |
| 167 |
"e|encoding=s" => sub { $enc_i = $enc_o = $_[1] }, |
| 168 |
"b|encoding-in=s" => \$enc_i, |
| 169 |
"a|encoding-out=s" => \$enc_o, |
| 170 |
"U|utf-8|utf8" => sub { $enc_o = "utf-8" }, |
| 171 |
|
| 172 |
# Grep |
| 173 |
"w|word!" => \my $opt_w, # Grep words |
| 174 |
"h|header:1" => \$opt_h, |
| 175 |
) or usage 1, "GetOpt: $@"; |
| 176 |
|
| 177 |
sub list_parsers { |
| 178 |
my $err = shift || 0; |
| 179 |
print "Ext Parser module Req Has Def\n"; |
| 180 |
print "-------- ------------------------- ----- ----- ---\n"; |
| 181 |
for (Spreadsheet::Read::parsers ()) { |
| 182 |
$_->{ext} =~ m/^zzz/ and next; |
| 183 |
printf "%-8s %-25s %5s %5s %s\n", |
| 184 |
$_->{ext}, $_->{mod}, $_->{min}, $_->{vsn}, $_->{def} ? "<==" : ""; |
| 185 |
} |
| 186 |
exit $err; |
| 187 |
} # list_parsers |
| 188 |
|
| 189 |
$opt_head and $opt_R = "1-$opt_head"; |
| 190 |
$opt_tail and $opt_R = "-$opt_tail-"; |
| 191 |
|
| 192 |
$opt_B and $opt_L++; |
| 193 |
unless ($is_grep) { |
| 194 |
$opt_i && $opt_L and usage 1, "Options i and L are mutually exclusive"; |
| 195 |
$opt_i && $opt_s and usage 1, "Options i and s are mutually exclusive"; |
| 196 |
$opt_i && $opt_c and usage 1, "Options i and c are mutually exclusive"; |
| 197 |
$opt_i && $opt_u and usage 1, "Options i and u are mutually exclusive"; |
| 198 |
$opt_i && $opt_S and usage 1, "Options i and S are mutually exclusive"; |
| 199 |
$opt_i && $opt_R and usage 1, "Options i and R are mutually exclusive"; |
| 200 |
$opt_i && $opt_C and usage 1, "Options i and C are mutually exclusive"; |
| 201 |
$opt_i && $opt_F and usage 1, "Options i and F are mutually exclusive"; |
| 202 |
$opt_i && $opt_H and usage 1, "Options i and H are mutually exclusive"; |
| 203 |
} |
| 204 |
$opt_c && $opt_H and usage 1, "Options c and H are mutually exclusive"; |
| 205 |
$opt_s && $opt_H and usage 1, "Options s and H are mutually exclusive"; |
| 206 |
|
| 207 |
my %e = (a => "\a", b => "\b", e => "\e", f => "\f", n => "\n", r => "\r", t => "\t"); |
| 208 |
$opt_s and $opt_s =~ s/\\+([abefnrt])/$e{$1}/g; |
| 209 |
defined $opt_S or $opt_S = $opt_i || $is_grep ? "all" : "1"; |
| 210 |
defined $opt_N && !length $opt_N and $opt_N = " "; |
| 211 |
$opt_i && !$is_grep && $opt_v < 1 and $opt_v = 1; |
| 212 |
$opt_f and $opt_A = 1; |
| 213 |
|
| 214 |
if ($opt_c) { |
| 215 |
$opt_L = 0; # Cannot align CSV |
| 216 |
if ($opt_s) { |
| 217 |
$opt_c = $opt_s; |
| 218 |
$opt_s = undef; |
| 219 |
} |
| 220 |
$opt_c =~ m/^1?$/ and $opt_c = ","; |
| 221 |
$opt_c = Text::CSV_XS->new ({ |
| 222 |
binary => 1, |
| 223 |
sep_char => $opt_c, |
| 224 |
eol => "\r\n", |
| 225 |
}); |
| 226 |
} |
| 227 |
|
| 228 |
# Debugging. Prefer Data::Peek over Data::Dumper if available |
| 229 |
{ my $dp = eval { require Data::Peek; 1 }; |
| 230 |
sub ddumper { |
| 231 |
$dp ? Data::Peek::DDumper (@_) |
| 232 |
: print STDERR Dumper (@_); |
| 233 |
} # ddumper |
| 234 |
} |
| 235 |
|
| 236 |
my @RDarg; |
| 237 |
for (reverse 0 .. $#ARGV) { |
| 238 |
$ARGV[$_] =~ m/^--([-\w]+)(?:=(.*))?$/ or next; |
| 239 |
push @RDarg, $1, defined $2 ? $2 : 1; |
| 240 |
$RDarg[-2] =~ tr/-/_/; |
| 241 |
splice @ARGV, $_, 1; |
| 242 |
} |
| 243 |
|
| 244 |
my $pattern; |
| 245 |
if ($is_grep) { |
| 246 |
$pattern = shift or usage 1; |
| 247 |
$opt_w and $pattern = "\\b$pattern\\b"; |
| 248 |
$opt_i and $pattern = "(?i:$pattern)"; |
| 249 |
$pattern = qr{$pattern}; |
| 250 |
$opt_v > 1 and warn "Matching on $pattern\n"; |
| 251 |
} |
| 252 |
|
| 253 |
my $file = shift; |
| 254 |
if (defined $file and $file ne "-") { |
| 255 |
$opt_v > 1 and warn "Using $file as input\n"; |
| 256 |
-f $file or usage 1, "the file argument is not a regular file"; |
| 257 |
-s $file or usage 1, "the file is empty"; |
| 258 |
if ($file =~ m/\.csv$/i and open my $fh, "<", $file) { # Auto-BOM |
| 259 |
my $l = <$fh>; |
| 260 |
close $fh; |
| 261 |
if ($l =~ s/^\x00\x00\xfe\xff//) { $enc_bom = "utf-32be" } |
| 262 |
elsif ($l =~ s/^\xff\xfe\x00\x00//) { $enc_bom = "utf-32le" } |
| 263 |
elsif ($l =~ s/^\xfe\xff//) { $enc_bom = "utf-16be" } |
| 264 |
elsif ($l =~ s/^\xff\xfe//) { $enc_bom = "utf-16le" } |
| 265 |
elsif ($l =~ s/^\xef\xbb\xbf//) { $enc_bom = "utf-8" } |
| 266 |
elsif ($l =~ s/^\xf7\x64\x4c//) { $enc_bom = "utf-1" } |
| 267 |
elsif ($l =~ s/^\xdd\x73\x66\x73//) { $enc_bom = "utf-ebcdic" } |
| 268 |
elsif ($l =~ s/^\x0e\xfe\xff//) { $enc_bom = "scsu" } |
| 269 |
elsif ($l =~ s/^\xfb\xee\x28//) { $enc_bom = "bocu-1" } |
| 270 |
elsif ($l =~ s/^\x84\x31\x95\x33//) { $enc_bom = "gb-18030" } |
| 271 |
elsif ($l =~ s/^\x{feff}//) { $enc_bom = "" } |
| 272 |
|
| 273 |
if ($enc_bom and open $fh, "<:encoding($enc_bom)", $file) { |
| 274 |
$opt_v > 1 and warn "Opened $file with encoding $enc_bom after BOM detection\n"; |
| 275 |
read $fh, (my $bom), 1; # Skip BOM |
| 276 |
$file = $fh; |
| 277 |
push @RDarg, parser => "CSV"; |
| 278 |
} |
| 279 |
} |
| 280 |
} |
| 281 |
else { |
| 282 |
$opt_v > 1 and warn "Working as a pipe\n"; |
| 283 |
$file = *ARGV; |
| 284 |
} |
| 285 |
|
| 286 |
if ($opt_c) { |
| 287 |
Spreadsheet::Read::parses ("csv") or die "No CSV module found\n"; |
| 288 |
eval q{use Text::CSV_XS}; |
| 289 |
} |
| 290 |
if ($opt_H) { |
| 291 |
$enc_o = "utf-8"; |
| 292 |
$opt_H = sub { $_[0]; }; |
| 293 |
eval q{ |
| 294 |
use HTML::Entities; |
| 295 |
$opt_H = sub { |
| 296 |
encode_entities (Encode::is_utf8 ($_[0]) ? $_[0] : |
| 297 |
decode ("utf-8", $_[0])); |
| 298 |
}; |
| 299 |
}; |
| 300 |
} |
| 301 |
|
| 302 |
if ($sep) { |
| 303 |
my %sep = ( |
| 304 |
tab => "\t", |
| 305 |
pipe => "|", |
| 306 |
colon => ":", |
| 307 |
semicolon => ";", |
| 308 |
comma => ",", |
| 309 |
); |
| 310 |
$sep = $sep{lc $sep} || $sep; |
| 311 |
} |
| 312 |
|
| 313 |
push @RDarg, debug => $opt_d, clip => $clip; |
| 314 |
$opt_A and push @RDarg, attr => 1; |
| 315 |
defined $sep and push @RDarg, sep => $sep, parser => "csv"; |
| 316 |
defined $dtfmt and push @RDarg, dtfmt => $dtfmt; |
| 317 |
$strip and push @RDarg, strip => $strip; |
| 318 |
$pivot and push @RDarg, pivot => $pivot; |
| 319 |
if ($passwd) { |
| 320 |
if ($passwd eq "-") { |
| 321 |
print STDERR "Password: "; |
| 322 |
eval "use Term::ReadKey;"; |
| 323 |
eval { ReadMode ("noecho"); }; |
| 324 |
chomp ($passwd = <STDIN>); |
| 325 |
eval { ReadMode ("echo"); }; |
| 326 |
} |
| 327 |
push @RDarg, passwd => $passwd; |
| 328 |
} |
| 329 |
$opt_v > 4 and warn "ReadData ($file, @RDarg);\n"; |
| 330 |
my $xls = eval { ReadData ($file, @RDarg) }; |
| 331 |
$opt_v > 7 and ddumper ($xls); |
| 332 |
unless ($xls) { |
| 333 |
warn "cannot read $file\n"; |
| 334 |
if ($@) { |
| 335 |
my $e = $@; |
| 336 |
$e =~ s{\s+at\s+\S+\s+line\s.*}{}s; |
| 337 |
$e =~ s{\s+\(perhaps you forgot to load.*}{}s; |
| 338 |
warn "$e\n"; |
| 339 |
} |
| 340 |
if (my @e = sort grep m/^SPREADSHEET_READ_/ => keys %ENV) { |
| 341 |
warn "\n"; |
| 342 |
warn " $_ is set to $ENV{$_}\n" for @e; |
| 343 |
warn "\n"; |
| 344 |
} |
| 345 |
if ($@ =~ m/cannot|can't/) { |
| 346 |
print "Supported parsers per spreadsheet:\n"; |
| 347 |
list_parsers (-1); |
| 348 |
} |
| 349 |
exit -1; |
| 350 |
} |
| 351 |
if ($xls->[0]{parser} =~ m/Spreadsheet::XLSX/) { |
| 352 |
warn "You parse with Spreadsheet::XLSX, which is deprecated\n"; |
| 353 |
warn "Please use Spreadsheet::ParseXLSX for more reliable results\n"; |
| 354 |
} |
| 355 |
my $sc = $xls->[0]{sheets} or die "No sheets in $file\n"; |
| 356 |
$opt_v and |
| 357 |
warn $opt_v > 1 |
| 358 |
? join " " => |
| 359 |
"Spreadsheet::Read-$Spreadsheet::Read::VERSION", |
| 360 |
"parsed $file with $sc sheets", |
| 361 |
"using $xls->[0]{parser}-$xls->[0]{version}\n" |
| 362 |
: "Parsed $file with $sc sheets\n"; |
| 363 |
|
| 364 |
$opt_S eq "all" and $opt_S = "1..$sc"; # all |
| 365 |
$opt_S =~ s/-$/-$sc/; # 3,6- |
| 366 |
$opt_S =~ s/-/../g; |
| 367 |
my %print; |
| 368 |
eval "%{\$print{sheet}} = map { \$_ => 1 } $opt_S"; |
| 369 |
|
| 370 |
my $v_fmt = $opt_C || $opt_R || $opt_F ? "" : "%6d x %6d%s"; |
| 371 |
|
| 372 |
# New style xterm (based on ANSI colors): |
| 373 |
# 30 Black |
| 374 |
# 31 Red |
| 375 |
# 32 Green |
| 376 |
# 33 Yellow |
| 377 |
# 34 Blue |
| 378 |
# 35 Magenta |
| 379 |
# 36 Cyan |
| 380 |
# 37 White |
| 381 |
sub color_reduce { |
| 382 |
my ($rgb, $base) = @_; |
| 383 |
defined $rgb or return ""; |
| 384 |
my ($r, $g, $b) = map { hex >> 7 } |
| 385 |
($rgb =~ m/^\s*#?([\da-f]{2})([\da-f]{2})([\da-f]{2})/); |
| 386 |
$base + 4 * $b + 2 * $g + $r; |
| 387 |
} # color_reduce |
| 388 |
|
| 389 |
sub ansi_color { |
| 390 |
my ($fg, $bg, $bold, $ul) = @_; |
| 391 |
|
| 392 |
# warn "$fg on $bg $bold $ul\n"; |
| 393 |
my $attr = join ";", 0, grep { /\S/ } |
| 394 |
$bold ? 1 : "", |
| 395 |
$ul ? 4 : "", |
| 396 |
color_reduce ($fg, 30), |
| 397 |
color_reduce ($bg, 40); |
| 398 |
|
| 399 |
"\e[${attr}m"; |
| 400 |
} # ansi_color |
| 401 |
|
| 402 |
sub css_color { |
| 403 |
my ($fg, $bg, $bold, $ul, $ha) = @_; |
| 404 |
|
| 405 |
my @css; |
| 406 |
$bold and push @css, "font-weight: bold"; |
| 407 |
$ul and push @css, "text-decoration: underline"; |
| 408 |
$fg and push @css, "color: $fg"; |
| 409 |
$bg and push @css, "background: $bg"; |
| 410 |
$ha and push @css, "text-align: $ha"; |
| 411 |
|
| 412 |
local $" = "; "; |
| 413 |
@css ? qq{ style="@css"} : ""; |
| 414 |
} # css_color |
| 415 |
|
| 416 |
binmode STDERR, ":encoding(utf-8)"; |
| 417 |
$enc_o and binmode STDOUT, ":encoding($enc_o)"; |
| 418 |
|
| 419 |
if ($opt_H) { |
| 420 |
print <<"EOH"; |
| 421 |
<?xml version="1.0" encoding="utf-8"?> |
| 422 |
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> |
| 423 |
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> |
| 424 |
<head> |
| 425 |
<title>$file</title> |
| 426 |
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> |
| 427 |
<meta name="Author" content="xlscat $VERSION" /> |
| 428 |
<style type="text/css"> |
| 429 |
body, h2, |
| 430 |
td, th { font-family: "Nimbus Sans L", "DejaVu Sans", |
| 431 |
Helvetica, Arial, sans; } |
| 432 |
table { border-spacing: 2px; |
| 433 |
border-collapse: collapse; } |
| 434 |
td, th { vertical-align: top; |
| 435 |
padding: 4px; } |
| 436 |
table > tbody > tr > th, |
| 437 |
table > tr > th { |
| 438 |
background: #e0e0e0; } |
| 439 |
table > tbody > tr > td:not([class]), |
| 440 |
table > tr > td:not([class]) { |
| 441 |
background: #f0f0f0; } |
| 442 |
.odd { background: #e0e0e0; } |
| 443 |
</style> |
| 444 |
</head> |
| 445 |
<body> |
| 446 |
EOH |
| 447 |
} |
| 448 |
|
| 449 |
my %first; |
| 450 |
if ($opt_first) { |
| 451 |
$opt_first =~ m/^([a-zA-Z]+)[0-9]*$/ and |
| 452 |
$opt_first = (cell2cr (uc $1 . "1"))[0]; |
| 453 |
$opt_first =~ m/^[0-9]+$/ && $opt_first > 0 or |
| 454 |
die "--first=C requires a numeric column index or a column index letter\n"; |
| 455 |
} |
| 456 |
|
| 457 |
my $name_len = 30; |
| 458 |
if ($opt_i) { |
| 459 |
my $nl = 0; |
| 460 |
foreach my $sn (keys %{$xls->[0]{sheet}}) { |
| 461 |
length ($sn) > $nl and $nl = length $sn; |
| 462 |
} |
| 463 |
$nl and $name_len = $nl; |
| 464 |
} |
| 465 |
my @opt_F = split m/[^A-Z\d]+/ => $opt_F; |
| 466 |
foreach my $si (1 .. $sc) { |
| 467 |
my @data; |
| 468 |
exists $print{sheet}{$si} or next; |
| 469 |
$opt_v > 1 and warn "Opening sheet $si ...\n"; |
| 470 |
my $s = $xls->[$si] or next; |
| 471 |
$opt_v > 5 and ddumper ($s); |
| 472 |
|
| 473 |
my @r = (1, $s->{maxrow}); |
| 474 |
my @c = (1, $s->{maxcol}); |
| 475 |
my ($sn, $nr, $nc) = ($s->{label}, $r[-1], $c[-1]); |
| 476 |
defined $nr or next; |
| 477 |
defined $nc or next; |
| 478 |
defined $sn or $sn = ""; |
| 479 |
my $active = $s->{active} ? " (Active)" : ""; |
| 480 |
my $hidden = $s->{hidden} ? " (Hidden)" : ""; |
| 481 |
my $eolt = $s->{eolt} ? ", EOL = $s->{eolt}" : ""; |
| 482 |
$eolt =~ s/\n/\\n/g; $eolt =~ s/\r/\\r/g; $eolt =~ s/\t/\\t/g; |
| 483 |
$eolt =~ s/([\x00-\x1f])/sprintf "\\x{%02x}", ord $1/ge; |
| 484 |
$opt_v and printf STDERR "%s - %02d: [ %-*s ] %3d Cols, %5d Rows%s%s%s\n", |
| 485 |
$file, $si, $name_len, $sn, $nc, $nr, $active, $hidden, $eolt; |
| 486 |
$opt_i && !$is_grep and next; |
| 487 |
|
| 488 |
if (@opt_F) { |
| 489 |
foreach my $fld (@opt_F) { |
| 490 |
$is_grep && defined $s->{$fld} && $s->{$fld} !~ $pattern and next; |
| 491 |
print "$fld:",$s->{$fld},"\n"; |
| 492 |
} |
| 493 |
next; |
| 494 |
} |
| 495 |
|
| 496 |
if (my $rows = $opt_R) { |
| 497 |
$rows eq "all" and $rows = "1..$nr"; # all |
| 498 |
$rows =~ s/--(\d+)/-($nr+1-$1)/ge; # 3--2 |
| 499 |
$rows =~ s/-(\d+)(?=-)/($nr+1-$1)/ge; # -3- |
| 500 |
$rows =~ s/-$/-$nr/; # 3,6- |
| 501 |
$rows =~ s/-/../g; |
| 502 |
eval "%{\$print{row}} = map { \$_ => 1 } $rows"; |
| 503 |
} |
| 504 |
if (my $cols = $opt_C) { |
| 505 |
$cols eq "all" and $cols = "1..$nc"; # all |
| 506 |
if ($cols =~ m/[A-Za-z]/) { # -C B,D => -C 2,4 |
| 507 |
my %ct = map { |
| 508 |
my ($cc, $rr) = cell2cr (uc "$_".1); |
| 509 |
($_ => $cc) |
| 510 |
} ($cols =~ m/([a-zA-Z]+)/g); |
| 511 |
$cols =~ s/([A-Za-z]+)/$ct{$1}/g; |
| 512 |
} |
| 513 |
$cols =~ s/-$/-$nc/; # 3,6- |
| 514 |
$cols =~ s/-/../g; |
| 515 |
eval "\$print{col} = [ map { \$_ - 1 } $cols ]"; |
| 516 |
$nc = @{$print{col}}; |
| 517 |
} |
| 518 |
$opt_v >= 8 and ddumper (\%print); |
| 519 |
|
| 520 |
$opt_H and print qq{<h2>$sn</h2>\n\n<table border="1">\n}; |
| 521 |
my $undef = $opt_v > 2 ? "-- undef --" : ""; |
| 522 |
my ($h, @w) = (0, (0) x $nc); # data height, -width, and default column widths |
| 523 |
my @align = ("") x $nc; |
| 524 |
foreach my $r ($r[0] .. $r[1]) { |
| 525 |
exists $print{row} && !exists $print{row}{$r} and next; |
| 526 |
my @att; |
| 527 |
my @row = map { |
| 528 |
my $cell = cr2cell ($_, $r); |
| 529 |
my ($uval, $fval) = map { |
| 530 |
defined $_ ? $enc_i ? decode ($enc_i, $_) : $_ : $undef |
| 531 |
} $s->{cell}[$_][$r], $s->{$cell}; |
| 532 |
if (defined $opt_N) { |
| 533 |
s/\n/$opt_N/go for grep { defined } $uval, $fval; |
| 534 |
} |
| 535 |
if ($clip_len) { |
| 536 |
$_ = substr $_, 0, $clip_len - 1 for grep { length > $clip_len } $uval, $fval; |
| 537 |
} |
| 538 |
$opt_v > 2 and warn "$_:$r '$uval' / '$fval'\n"; |
| 539 |
$opt_A and |
| 540 |
push @att, [ @{$s->{attr}[$_][$r]}{qw( fgcolor bgcolor bold uline halign )} ]; |
| 541 |
$opt_f && $s->{attr}[$_][$r]{formula} |
| 542 |
? "=".$s->{attr}[$_][$r]{formula} |
| 543 |
: defined $s->{cell}[$_][$r] ? $opt_u ? $uval : $fval : ""; |
| 544 |
} $c[0] .. $c[1]; |
| 545 |
$r == 1 && $row[0] && defined $enc_bom and $row[0] =~ s/^\N{BOM}//; |
| 546 |
exists $print{col} and @row = @row[grep{$_<@row}@{$print{col}}]; |
| 547 |
$is_grep && $r > $opt_h && |
| 548 |
! first { defined $_ && $_ =~ $pattern } @row and next; |
| 549 |
$skip_empty && ! first { length } @row and next; |
| 550 |
if ($opt_first) { |
| 551 |
@row >= $opt_first && $first{$row[$opt_first - 1]}++ and next; |
| 552 |
} |
| 553 |
if ($opt_D) { |
| 554 |
ddumper ($opt_D == 1 ? \@row : |
| 555 |
{ map { $s->{cell}[$_ + 1][1] => $row[$_] } 0 .. $#row }); |
| 556 |
next; |
| 557 |
} |
| 558 |
if ($opt_H) { # HTML |
| 559 |
print " <tr>"; |
| 560 |
if (defined $opt_n) { |
| 561 |
my $x = $r - $opt_n; |
| 562 |
$x <= 0 and $x = ""; |
| 563 |
my $c = $r % 2 ? qq{ class="odd"} : ""; |
| 564 |
print qq{<td style="text-align: right" $c>$x</td>}; |
| 565 |
} |
| 566 |
foreach my $c (0 .. $#row) { |
| 567 |
my $css = css_color (@{$att[$c]}); |
| 568 |
$r % 2 and $css .= qq{ class="odd"}; |
| 569 |
my $td = $opt_H->($row[$c]); |
| 570 |
print "<td$css>$td</td>"; |
| 571 |
} |
| 572 |
print "</tr>\n"; |
| 573 |
next; |
| 574 |
} |
| 575 |
if (defined $opt_n) { |
| 576 |
unshift @row, $r; |
| 577 |
unshift @att, [ "#ffffff", "#000000", 0, 0 ]; |
| 578 |
} |
| 579 |
if ($opt_L || $sort_order) { # Autofit / Align / order |
| 580 |
push @data, [ [ @row ], [ @att ] ]; |
| 581 |
next; |
| 582 |
} |
| 583 |
if ($opt_c) { # CSV |
| 584 |
$opt_c->print (*STDOUT, \@row) or die $opt_c->error_diag; |
| 585 |
next; |
| 586 |
} |
| 587 |
if ($opt_A) { |
| 588 |
foreach my $c (0 .. $#row) { |
| 589 |
$row[$c] = |
| 590 |
ansi_color (@{$att[$c]}). |
| 591 |
$row[$c] . |
| 592 |
"\e[0m"; |
| 593 |
} |
| 594 |
} |
| 595 |
line (0, $opt_s => @row); |
| 596 |
} continue { |
| 597 |
++$h % 100 == 0 && $opt_v && $v_fmt and printf STDERR $v_fmt, $nc, $h, "\r"; |
| 598 |
} |
| 599 |
$opt_H and print " </table>\n\n"; |
| 600 |
$v_fmt && $v_fmt and printf STDERR $v_fmt, $nc, $h, "\n"; |
| 601 |
if ($sort_order) { |
| 602 |
my @o; |
| 603 |
my @h; |
| 604 |
$sort_order =~ s/\+([0-9]+)\b// and @h = splice @data, 0, $1; |
| 605 |
for ($sort_order =~ m/([0-9]+[rn]*)/g) { |
| 606 |
m/^([0-9]+)(.*)/; |
| 607 |
push @o, { col => $1 - 1, map { $_ => 1 } split m// => $2 }; |
| 608 |
} |
| 609 |
my $sort = sub { |
| 610 |
my $d = 0; |
| 611 |
foreach my $o (@o) { |
| 612 |
my ($A, $B) = map { $_->[0][$o->{col}] || 0 } $a, $b; |
| 613 |
$d = $o->{n} |
| 614 |
? $o->{r} ? $B <=> $A : $A <=> $B |
| 615 |
: $o->{r} ? $B cmp $A : $A cmp $B |
| 616 |
and return $d; |
| 617 |
} |
| 618 |
return $d; |
| 619 |
}; |
| 620 |
@data = (@h, sort $sort @data); |
| 621 |
} |
| 622 |
if ($opt_c && @data) { # CSV |
| 623 |
$opt_c->print (*STDOUT, $_->[0]) for @data; |
| 624 |
next; |
| 625 |
} |
| 626 |
$opt_L || $sort_order or next; |
| 627 |
if (defined $opt_n) { |
| 628 |
unshift @w, length $data[-1][0][0]; |
| 629 |
unshift @align, ""; |
| 630 |
} |
| 631 |
$opt_n = 0; |
| 632 |
if ($opt_L) { |
| 633 |
foreach my $r (0 .. $#data) { |
| 634 |
my $R = $data[$r][0]; |
| 635 |
foreach my $c (0 .. $#$R) { |
| 636 |
my $d = $R->[$c]; |
| 637 |
my $l = length $d; |
| 638 |
$l > $w[$c] and $w[$c] = $l; |
| 639 |
# Number alignment won't be effective if first row is empty and |
| 640 |
# second row has column headers |
| 641 |
$r && $d =~ m/\S/ or next; |
| 642 |
$d =~ m/^(?:-|\s*(?:-\s*)?[0-9][0-9 .,]*)$/ or $align[$c] = "-"; |
| 643 |
} |
| 644 |
} |
| 645 |
if ($skip_empty) { # Skip empty columns. Rows have already been skipped |
| 646 |
foreach my $i ( reverse 0 .. $#w) { |
| 647 |
$w[$i] and next; |
| 648 |
|
| 649 |
splice @w, $i, 1; |
| 650 |
splice @align, $i, 1; |
| 651 |
splice @$_, $i, 1 for grep { $#$_ >= $i } map { @{$_} } @data; |
| 652 |
} |
| 653 |
} |
| 654 |
} |
| 655 |
|
| 656 |
$opt_B and line (1, $opt_s => map { "-" x $w[$_] } 0..$#{$data[0][0]}); |
| 657 |
for (@data) { |
| 658 |
my ($row, $att) = @$_; |
| 659 |
my @row = @$row; |
| 660 |
for (0 .. $#row) { |
| 661 |
my $l = length $row[$_]; |
| 662 |
my $w = $l < $w[$_] ? " " x ($w[$_] - $l) : ""; |
| 663 |
if ($align[$_]) { |
| 664 |
$_ < $#row || $opt_B and $row[$_] .= $w; |
| 665 |
} |
| 666 |
else { |
| 667 |
substr $row[$_], 0, 0, $w; |
| 668 |
} |
| 669 |
if ($opt_A) { |
| 670 |
substr $row[$_], 0, 0, ansi_color (@{$att->[$_]}); |
| 671 |
$row[$_] .= "\e[0m"; |
| 672 |
} |
| 673 |
} |
| 674 |
line (0, $opt_s => @row); |
| 675 |
++$opt_n == $opt_h and |
| 676 |
line (2, $opt_s => map { "-" x $w[$_] } 0..$#row); |
| 677 |
} |
| 678 |
$opt_B and line (3, $opt_s => map { "-" x $w[$_] } 0..$#{$data[0][0]}); |
| 679 |
} |
| 680 |
$opt_H and print "</body>\n</html>\n"; |
| 681 |
|
| 682 |
sub line { |
| 683 |
my $uh = "\x{2500}"; # BOX DRAWINGS LIGHT HORIZONTAL |
| 684 |
my $uv = "\x{2502}"; |
| 685 |
my @tpl = ($uv, "\x{250c}", "\x{251c}", "\x{2514}"); |
| 686 |
my @tpm = ($uv, "\x{252c}", "\x{253c}", "\x{2534}"); |
| 687 |
my @tpr = ($uv, "\x{2510}", "\x{2524}", "\x{2518}"); |
| 688 |
|
| 689 |
my $type = shift; # 0: data, 1: top, 2: middle, 3: bottom |
| 690 |
my $sep = shift || " $uv "; |
| 691 |
my @dta = @_; |
| 692 |
if ($opt_L) { |
| 693 |
$type > 0 and $sep =~ s/ /-/g; |
| 694 |
if ($sep =~ m/$uv/) { |
| 695 |
$sep =~ s/-/$uh/g; |
| 696 |
$sep =~ s/$uv/$tpm[$type]/; |
| 697 |
if ($type > 0) { |
| 698 |
s/-/$uh/g for grep { length } @dta; |
| 699 |
} |
| 700 |
} |
| 701 |
elsif ($type > 0) { |
| 702 |
$sep =~ s/\|/+/; |
| 703 |
} |
| 704 |
} |
| 705 |
my $line = join $sep => @dta; |
| 706 |
if ($opt_B) { |
| 707 |
# Cannot use s///r for 5.8.x |
| 708 |
(my $s1 = $sep) =~ s/^(?:\s|$uh)[\x{2500}-\x{25ff}]/$tpl[$type]/; |
| 709 |
substr $line, 0, 0, $s1; |
| 710 |
(my $s2 = $sep) =~ s/[\x{2500}-\x{25ff}](?:\s|$uh)$/$tpr[$type]/; |
| 711 |
$line .= $s2; |
| 712 |
} |
| 713 |
!$enc_o && Encode::is_utf8 ($line) and $line = encode ("utf-8", $line); |
| 714 |
print $line; |
| 715 |
print "\n"; |
| 716 |
} # line |
| 717 |
|
| 718 |
END { |
| 719 |
if ($opt_v >= 7) { |
| 720 |
my %seen; |
| 721 |
print "\nNon-CORE modules loaded:\n", "-" x 25, " ", "--------\n"; |
| 722 |
foreach my $mod (sort keys %INC) { |
| 723 |
my $path = $INC{$mod}; |
| 724 |
$mod =~ s{\.pm$}{} or next; |
| 725 |
$mod =~ s{/}{::}g; |
| 726 |
$path =~ s{.*/site_perl/}{} or next; |
| 727 |
grep { $mod =~ m/^${_}::/ } keys %seen and next; |
| 728 |
$seen{$mod}++; |
| 729 |
my $v = $mod->VERSION () || eval "\$${mod}::VERSION" || "?"; |
| 730 |
printf "%-25s %s\n", $mod, $v; |
| 731 |
} |
| 732 |
} |
| 733 |
} |