in reply to MS Excel One-Liner Challenge
and that does in fact work well -- except when the input xls file hasXLSperl -lane 's/fubar/foobar/ for(@F); XLSprint @F' < old.xls > new.x +ls
I noticed the problem when trying verify the results of such an edit process, using a one-liner like this:
That turns out to be very handy for listing cell-value differences between two xls files -- provided that they both contain the same worksheets and are mostly similar except for value differences in particular cells.XLSperl -lne '$h{"$WS:$CELL: $_"}++;END{print for(grep {$h{$_}==1} key +s %h)}' old.xls new.xls
But if the two files don't have the same quantity and names of worksheets, this sort of comparison is useless (or rather, it reports an awful lot of diffs, which actually makes it good for discovering problems, like the loss of worksheet boundaries caused by simple cell-editing operations.)
UPDATE: Here's a slightly more verbose but more informative version of the one-liner for comparing two xls files:
It sorts the cells in the output listing, and tells which file contained each of the unique cell values.XLSperl -lne '$h{"$WS:$CELL:$_"}.="in $ARGV"; END{print "$_ ($h{$_})" for(grep{$h{$_}!~/\.xlsin /}sort keys %h)}' +a.xls b.xls
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^2: MS Excel One-Liner Challenge
by spectre9 (Beadle) on Apr 22, 2009 at 13:43 UTC |