| 1 |
#!/usr/bin/perl |
| 2 |
|
| 3 |
# ss2tk: show SpreadSheet file in Tk::TableMatrix::Spreadsheet (*) |
| 4 |
# (m)'22 [2022-01-31] Copyright H.M.Brand 2005-2026 |
| 5 |
|
| 6 |
use strict; |
| 7 |
use warnings; |
| 8 |
|
| 9 |
our $VERSION = "2.81"; |
| 10 |
|
| 11 |
sub usage { |
| 12 |
my $err = shift and select STDERR; |
| 13 |
print |
| 14 |
"usage: ss2tk [options] [X11 options] file.xls [<pattern>]\n", |
| 15 |
" -w <width> use <width> as column width\n", |
| 16 |
" -L Add spreadsheet tags to top (A, B, ..Z, AB, ...)\n", |
| 17 |
" and left (1, 2, ...)\n", |
| 18 |
" --fs[=7] Set font size (default 7 if no value)\n", |
| 19 |
" --fn=name Set font Face name (default is DejaVu Sans Mono\n", |
| 20 |
" if font size is given\n"; |
| 21 |
exit $err; |
| 22 |
} # usage |
| 23 |
|
| 24 |
use Getopt::Long qw(:config bundling passthrough); |
| 25 |
my $opt_fs; |
| 26 |
GetOptions ( |
| 27 |
"help|?" => sub { usage (0); }, |
| 28 |
"w|width=i" => \my $opt_w, |
| 29 |
"L|ss|labels!" => \my $opt_L, |
| 30 |
"a|align!" => \my $opt_a, |
| 31 |
|
| 32 |
"fn|font-name=s" => \my $opt_fn, |
| 33 |
"fs|font-size:7" => \ $opt_fs, |
| 34 |
"fs-4" => sub { $opt_fs = 4; }, |
| 35 |
"fs-5" => sub { $opt_fs = 5; }, |
| 36 |
"fs-6" => sub { $opt_fs = 6; }, |
| 37 |
"fs-7" => sub { $opt_fs = 7; }, |
| 38 |
"fs-8" => sub { $opt_fs = 8; }, |
| 39 |
"fs-9" => sub { $opt_fs = 9; }, |
| 40 |
|
| 41 |
# For restarts |
| 42 |
"G|geometry=s" => \my $geometry, |
| 43 |
"W|column-widths=s" => \my $col_widths, |
| 44 |
) or usage (1); |
| 45 |
|
| 46 |
use Data::Peek; |
| 47 |
use Spreadsheet::Read; |
| 48 |
use Tk; |
| 49 |
use Tk::Balloon; |
| 50 |
use Tk::NoteBook; |
| 51 |
use Tk::TableMatrix::Spreadsheet; |
| 52 |
|
| 53 |
# This will allow ~/.Xdefaults to have lines like |
| 54 |
#ss2tk*font: -misc-fixed-medium-r-semicondensed--12-110-75-75-c-60-iso10646-1 |
| 55 |
Tk::CmdLine->LoadResources (); |
| 56 |
# This will allow calls like # ss2tk -fg Blue4 blah.csv |
| 57 |
Tk::CmdLine->SetArguments (); |
| 58 |
|
| 59 |
@ARGV && -f $ARGV[0] or usage (1); |
| 60 |
my $title = $ARGV[0]; |
| 61 |
|
| 62 |
my $sfn = shift; |
| 63 |
my $ref = Spreadsheet::Read->new ($sfn) or die "Cannot read $sfn\n"; |
| 64 |
$ref->[0]{sheets} or die "No sheets in $title\n"; |
| 65 |
|
| 66 |
fork and exit; |
| 67 |
|
| 68 |
$opt_fn || $opt_fs and $opt_fn = "{".($opt_fn|| "DejaVu Sans Mono")."} ".($opt_fs||7); |
| 69 |
|
| 70 |
my $pat = @ARGV ? qr/$ARGV[0]/i : undef; |
| 71 |
|
| 72 |
my $mw = MainWindow->new (-title => $title); |
| 73 |
$geometry and $mw->geometry ($geometry); |
| 74 |
|
| 75 |
my $balloon = $mw->Balloon ( |
| 76 |
-state => "balloon", |
| 77 |
-initwait => 300, |
| 78 |
-foreground => "#000000", |
| 79 |
-background => "#ffffff", |
| 80 |
); |
| 81 |
|
| 82 |
my $nb = $mw->NoteBook ()->pack (qw(-side top -expand 1 -fill both )); |
| 83 |
my @nb; |
| 84 |
foreach my $sht (1 .. $ref->[0]{sheets}) { |
| 85 |
my $s = $ref->[$sht]; |
| 86 |
$title .= " [ " . $s->{label} . " ]"; |
| 87 |
|
| 88 |
my ($data, @data) = ({}); |
| 89 |
my ($h, $w, @w) = get_data ($data, $s, \@data); |
| 90 |
|
| 91 |
$nb[$sht] = $nb->add ($sht, |
| 92 |
-label => $s->{label}, |
| 93 |
-state => "normal", |
| 94 |
-anchor => "nw"); |
| 95 |
my @ff = $opt_fn ? (-font => $opt_fn) : (); |
| 96 |
my $ss = $nb[$sht]->Scrolled ('Spreadsheet', |
| 97 |
-rows => $h, -cols => $w, |
| 98 |
-width => 10, -height => 20, |
| 99 |
-titlecols => $opt_L ? 1 : 0, |
| 100 |
-titlerows => $opt_L ? 2 : 1, |
| 101 |
|
| 102 |
-selectmode => "extended", |
| 103 |
-resizeborders => "both", |
| 104 |
|
| 105 |
-justify => "left", |
| 106 |
-anchor => "w", |
| 107 |
|
| 108 |
-variable => $data, |
| 109 |
@ff, |
| 110 |
)->pack (-expand => 1, -fill => "both", -side => "top", -anchor => "nw"); |
| 111 |
$ss->Subwidget ("${_}scrollbar")->configure (-width => 6) for qw( x y ); |
| 112 |
$ss->tagConfigure ("title", -bg => "#ffffe0", -justify => "left"); |
| 113 |
$ss->tagConfigure ("active", -bg => "#ffff40", -justify => "left"); |
| 114 |
$ss->tagConfigure ("sel", -bg => "gray95", -justify => "left"); |
| 115 |
|
| 116 |
my ($pv, $sv, $si) = ("", "", 0); |
| 117 |
sub search { |
| 118 |
$sv or return; |
| 119 |
$sv eq $pv && !$_[0] and return; |
| 120 |
$ss->selectionClear ("all"); |
| 121 |
foreach my $i ($_[0] .. $#data, 0 .. ($_[0] - 1)) { |
| 122 |
$data->{$data[$i]} =~ m/$sv/i or next; |
| 123 |
$si = $i; |
| 124 |
$ss->activate ($data[$si = $i]); |
| 125 |
$ss->selectionSet ($data[$si]); |
| 126 |
$ss->see ($data[$si]); |
| 127 |
$pv = $sv; |
| 128 |
last; |
| 129 |
} |
| 130 |
} # search |
| 131 |
|
| 132 |
my $reload = sub { |
| 133 |
$ss->clearCache; |
| 134 |
$data->{$_} = "" for @data; |
| 135 |
@data = (); |
| 136 |
$ref = Spreadsheet::Read->new ($sfn) or die "Cannot read $sfn\n"; |
| 137 |
$s = $ref->[$sht]; |
| 138 |
get_data ($data, $s, \@data); |
| 139 |
}; # reload |
| 140 |
|
| 141 |
# Search frame |
| 142 |
my $sf = $nb[$sht]->Frame ()->pack (-side => "left", -expand => 1, -fill => "both"); |
| 143 |
$sf->Label ( |
| 144 |
-text => "Search", |
| 145 |
)->pack (-side => "left", -anchor => "sw"); |
| 146 |
my $sb = $sf->Entry ( |
| 147 |
-textvariable => \$sv, |
| 148 |
)->pack (-side => "left", -anchor => "sw"); |
| 149 |
$sb->bind ("<Return>" => sub { search ($si = 0); }); |
| 150 |
$sf->Button ( |
| 151 |
-text => "Next", |
| 152 |
-command => sub { search (++$si) }, |
| 153 |
)->pack (-side => "left", -anchor => "sw"); |
| 154 |
|
| 155 |
# Control frame |
| 156 |
my $cf = $nb[$sht]->Frame ()->pack (-side => "right", -expand => 1, -fill => "both"); |
| 157 |
$cf->Button ( |
| 158 |
-text => "\x{2718}", |
| 159 |
-foreground => "#8b0000", |
| 160 |
-command => \&exit, |
| 161 |
)->pack (-side => "right", -anchor => "se"); |
| 162 |
$balloon->attach ($cf->Button ( # Restart |
| 163 |
-text => "\x{21f5}", # Restart |
| 164 |
-foreground => "#ffa500", |
| 165 |
-command => sub { |
| 166 |
my $geo = $mw->geometry; |
| 167 |
# Add column widths here |
| 168 |
exec $0, "--geometry=$geo"; |
| 169 |
}, |
| 170 |
)->pack (-side => "right", -anchor => "se"), |
| 171 |
-balloonposition => "mouse", |
| 172 |
-balloonmsg => "Restart", |
| 173 |
-msg => { |
| 174 |
Background => "Restart", |
| 175 |
separator => "Restart", |
| 176 |
}); |
| 177 |
$balloon->attach ($cf->Button ( # Reload |
| 178 |
-text => "\x{21bb}", |
| 179 |
-foreground => "#008b00", |
| 180 |
-command => $reload, |
| 181 |
)->pack (-side => "right", -anchor => "se"), |
| 182 |
-balloonposition => "mouse", |
| 183 |
-balloonmsg => "Reload", |
| 184 |
-msg => { |
| 185 |
Background => "Reload", |
| 186 |
separator => "Reload", |
| 187 |
}); |
| 188 |
|
| 189 |
my $swss = $ss->Subwidget ("spreadsheet"); |
| 190 |
|
| 191 |
# autosize columns on data (not on headers) |
| 192 |
$swss->colWidth (map { $_ => ($opt_w || $w[$_] || 4) } 0 .. $#w); |
| 193 |
|
| 194 |
$opt_a or next; |
| 195 |
$swss->tagConfigure ("numeric", -justify => "right"); |
| 196 |
foreach my $rc (keys %$data) { |
| 197 |
$data->{$rc} && $data->{$rc} =~ m/^\d+$/ or next; |
| 198 |
$swss->tagCell ("numeric", $rc); |
| 199 |
} |
| 200 |
DDumper $swss; |
| 201 |
} |
| 202 |
|
| 203 |
sub get_data { |
| 204 |
my ($dta, $s, $adta) = @_; |
| 205 |
|
| 206 |
my @c = (1, $s->{maxcol}); |
| 207 |
my ($h, $w, @w) = (0, 1, 0, (0) x $c[1]); # data height, -width, and default column widths |
| 208 |
if ($opt_L) { |
| 209 |
foreach my $c (0 .. $s->{maxcol}) { |
| 210 |
$dta->{"$h,$c"} = $c ? $ref->col2label ($c) : ""; |
| 211 |
push @$adta, "$h,$c"; |
| 212 |
} |
| 213 |
$h++; |
| 214 |
} |
| 215 |
foreach my $r (1 .. $s->{maxrow}) { |
| 216 |
my @row = map { |
| 217 |
defined $s->{cell}[$_][$r] ? $s->{cell}[$_][$r] : ""; |
| 218 |
} 1 .. $s->{maxcol}; |
| 219 |
$pat and "@row" =~ $pat || next; |
| 220 |
$opt_L and unshift @row, $r; |
| 221 |
foreach my $c (0 .. $#row) { |
| 222 |
length $row[$c] or next; |
| 223 |
$c >= $w and $w = $c + 1; |
| 224 |
$dta->{"$h,$c"} = $row[$c]; |
| 225 |
push @$adta, "$h,$c"; |
| 226 |
my $l = length $row[$c]; |
| 227 |
$l > $w[$c] and $w[$c] = $l; |
| 228 |
} |
| 229 |
++$h % 100 or printf STDERR "%6d x %6d\r", $w, $h; |
| 230 |
} |
| 231 |
printf STDERR "%6d x %6d\n", $w, $h; |
| 232 |
#use DP;DDumper { S => $dta, A => $adta }; |
| 233 |
($h, $w, @w); |
| 234 |
} # get_data |
| 235 |
|
| 236 |
MainLoop; |