in reply to MS Excel One-Liner Challenge

I'm impressed. I see that XLSperl even does the right thing when cells contain unicode characters! I managed to find one issue that seems to limit the usefulness of the "XLSprint" function: it looks like it should be easy to do one-liner editing to create a modified xls file, like this:
XLSperl -lane 's/fubar/foobar/ for(@F); XLSprint @F' < old.xls > new.x +ls
and that does in fact work well -- except when the input xls file has two or more non-empty worksheets one or more non-empty worksheets with a name other than "Sheet1", in which case all worksheets get glommed together into "Sheet1" in the output xls file. (If there is a way to preserve worksheet names and boundaries in this sort of edit proces, someone please enlighten me.)

I noticed the problem when trying verify the results of such an edit process, using a one-liner like this:

XLSperl -lne '$h{"$WS:$CELL: $_"}++;END{print for(grep {$h{$_}==1} key +s %h)}' old.xls new.xls
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.

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:

XLSperl -lne '$h{"$WS:$CELL:$_"}.="in $ARGV"; END{print "$_ ($h{$_})" for(grep{$h{$_}!~/\.xlsin /}sort keys %h)}' +a.xls b.xls
It sorts the cells in the output listing, and tells which file contained each of the unique cell values.

Replies are listed 'Best First'.
Re^2: MS Excel One-Liner Challenge
by spectre9 (Beadle) on Apr 22, 2009 at 13:43 UTC
    I truly like the 'find differences' functionality you illustrate in the second code block. This is exactly the sort of contribution I am seeking! Thank you.

    -- Patrick

    UPDATE: The use of END in the second and third examples above is something that, despite 15 years of Perl usage, I have never employed. Therefore this monk has been bless with a serendiptous enlightenment from his Meditation. Much Thanks!

    spectre#9 -- "Strictly speaking, there are no enlightened people, there is only enlightened activity." -- Shunryu Suzuki