GNOME Bugzilla – Bug 739679
Extended float field in lotus wk4 spreadsheets
Last modified: 2014-11-06 19:05:52 UTC
I needed to get some data out of an old wk4 spreadsheet saved by Lotus 1-2-3. On examination, most of the numbers were stored with opcode 0x17, which Gnumeric doesn't currently handle. This turned out to store an extended-precision 80 bit floating point number. The attached patch is the current state of my work on this. It's loading most of the numbers in my spreadsheet correctly, but I'm hoping someone can help me with two, probably related problems: - By trial and error, I found that I needed a bitshift right 32 to get the correct numbers. The Python code I wrote while working this out works correctly without that, but when I tried to translate it to C, it went wrong. I'm not very good at C, so I may well be missing something obvious. - Small numbers, <2.0, are wrong - numbers 1.0 <= n < 2.0 are negated, and numbers <1.0 appear to run into some kind of overflow and become huge. Again, my Python code doesn't have this issue. For reference, here's my Python code: a = int.from_bytes(r.data[-2:], 'little') sign = -1 if (a & (1<<15)) else 1 e = a - (a & (1 << 15)) - 16383 m = int.from_bytes(r.data[4:-2], 'little') val = m / (1<<(63-e))
Interesting. I would guess that what you need is the x86 format described here: http://en.wikipedia.org/wiki/Extended_precision Can you get me the bit patterns for a few sample numbers? pi is always a good choice if you can control it.
And a sample file would be good too.
See also... http://blogs.perl.org/users/rurban/2012/09/reading-binary-floating-point-numbers-numbers-part2.html Look for function cvt_num10_num8.
Created attachment 290047 [details] [review] WIP patch for extended float data Apparently the attachment didn't work - here's the patch. I can't share the file, unfortunately (it contains private data), and I don't know how to create new ones without the relevant version of lotus. But here's a couple of sample patterns: b'\x00\x00\x00\x00\x00\x00\xb0\x8a\n@' == 2219.0 b'3333333\x8b\x04@' == 34.8 (Byte patterns in Python repr format)
I think your problem is 1 << (63 - exp) This is only well defined when the shift count is between 0 and 31 inclusive. What you need here is ldexp, except that I would like you to use the gnm_ldexp form.
GnmValue * lotus_extfloat (guint64 mant, guint16 signexp) { int exp = (signexp & 0x7fff) - 16383; int sign = (signexp & 0x8000) ? -1 : 1; /* FIXME: Special values may indicate NaN, +/- inf */ return lotus_value (sign * gnm_ldexp (mant, exp - 63)); }
Created attachment 290064 [details] [review] Revised patch with Morten's changes Thanks! With that change, all the values look right. I've incorporated it into this revised patch.
This problem has been fixed in our software repository. The fix will go into the next software release. Thank you for your bug report.
Thanks, Morten! :-)