Gnumeric 0.76 experiences (long)

[Nick Lamb <njl98r ecs soton ac uk>]

Did my occasional (seems like it's infrequent due to other obligations)
Gnumeric update to 0.76 and was mostly very pleased indeed. I am mostly
interested in Gnumeric seen as an alternative to Excel, and in particular
in loading XLS files (those who know me from Gimp-devel will understand
the common purpose in these interests)

For the most part Gnumeric loads the documents I have smoothly and does
the Right Thing, but I have identified some exceptions that would be nice
to fix for 0.77 / 1.0, and I have a few questions

1. A crash which occurs only when loading certain XLS file(s) directly
from the command line. I do not own the file in question and I don't
remember where I found it, but I think giving it to one person for the
purpose of finding/fixing the crash would be OK. The crash is 95%
reproducible (once it didn't happen - race condition?)

The XLS file is about 240kb, and I'll mail it to whoever wants to fix
this crash, here's a stacktrace, I am assuming that "libgnomeole2" is
owned by Gnumeric, if not then please tell me who to forward it to.

#0  0x401a82a0 in ms_ole_stream_open () from /usr/lib/libgnomeole2.so.0
#1  0x40af27dc in ms_excel_read_workbook ()
   from /usr/lib/gnumeric/0.76-bonobo/plugins/excel/excel.so
#2  0x40ae1f0f in excel_file_open ()
   from /usr/lib/gnumeric/0.76-bonobo/plugins/excel/excel.so
#3  0x080ba5ec in gnumeric_plugin_loader_module_get_type () at eval.c:41
#4  0x080bc526 in gnumeric_plugin_loader_is_loaded () at eval.c:41
#5  0x0809291a in gnum_file_opener_open () at eval.c:41
#6  0x080fc474 in wb_view_open_custom () at eval.c:41
#7  0x080fc2bd in wb_view_open () at eval.c:41
#8  0x080ab665 in main () at eval.c:41
#9  0x407e3617 in __libc_start_main (main=0x80ab490 <main>, argc=2, 
    ubp_av=0xbffff964, init=0x806d95c <_init>, fini=0x8165510 <_fini>, 
    rtld_fini=0x4000dcc4 <_dl_fini>, stack_end=0xbffff95c)
    at ../sysdeps/generic/libc-start.c:129


2. 1904 date system

The console window shows that Gnumeric knows it can't handle 1904 dates
properly, but the user isn't warned in the GUI. It would be nice to have
such a warning by version 1.0 (especially for Mac Excel users)
[Well it might be nice to include 1904 support, but I know that's hard]

Interestingly Gnumeric seems to use pre-calculated answers when loading
XLS files (that's a guess) and initially it gets 100% on my 1904 tests,
but hitting F9 means most test results change and Gnumeric scores poorly.
Am I right that these pre-calculated results are from Excel? Or is there
some other Voodoo at work which I don't understand?

3. CRITICAL assertions

I have one or more sheets which produce the following assertions, warnings
and dire messages of ill-portent. Presumably many of these are known and
can safely be ignored (and will be commented out of 1.0)
Pick out any that really are problems, and I will provide example XLS files
that trigger them when loaded, or when you press F9.

** CRITICAL **: file sheet.c: line 4456 (sheet_freeze_panes): assertion `unfrozen->col > frozen->col' failed.

Gtk-WARNING **: invalid cast from (NULL) pointer to `GtkObject'

** WARNING **: No small block file, but small block depot start block exists!: ignore depot, since there's no 
small block files after all.

** CRITICAL **: file sheet.c: line 2275 (sheet_col_get): assertion `pos < SHEET_MAX_COLS' failed.

** CRITICAL **: file sheet-control-gui.c: line 2291 (scg_colrow_distance_get): assertion `to <= 
SHEET_MAX_COLS' failed.

** WARNING **: EXCEL: color index (16777343) is out of range (0..56). Defaulting to black


4. Parsing

=datevalue("21-Dec-1975") works in Excel (= 27749) in my locale but when
loaded into Gnumeric it (= 0) and sure enough the cell parser doesn't
recognise it as a date either. Is this configurable, or should Gnumeric be
figuring it out in general or from my locale  (LC_ALL = en_GB.UTF-8) ?

When saving a document with a duff formula, Excel seems to save and restore
it correctly (ie it's still broken exactly the same way), but Gnumeric
pointedly says e.g. "Incorrect number of parsed formula arguments" and
replaces the nearly-correct formula with =WrongArgs(blah) and thus the
error code (which in Excel might be #REF! or #NUM! or something) becomes
#NAME? because the function name is lost.

If the Gnumeric APIs forbid the Excel loader from creating duff formulae
(which makes sense) then it should probably try to 'quote the broken
formula or preserve it some other way.

5. Function compatibility

PERCENTRANK() with values {11.5 12.3 66.3 23.9 } x = 21.5 sig = 1
Excel returns 0.5, Gnumeric says 0.6

Unlike most other function help, the documentation for PERCENTRANK doesn't
say that it is Excel compatible. Is this an intentional compatibility gap
for Gnumeric? Will users (eventually) be warned when their sheets contain
unsupported/ incompatible Excel functions?

X. Early (non OLE) XLS files, not for 1.0

I understand that these files can still be created today, and that there
might be many millions of them out there. I don't know how seamlessly
they load them into Excel XP, but presumably it can salvage at least the
data (text, grid of float values) and simple formulae.

Of course Microsoft engineers have documentation (well maybe) and source
code (probably) for earlier implementations to work from. Gnumeric does
not AFAIK have access to anything on this subject. Is anyone already
working on this, or does anyone have insight to share?

Nick.