GNOME Bugzilla – Bug 163192
Exceeding Excel bounds in excel format export
Last modified: 2018-05-22 13:09:04 UTC
Both Gnumeric 1.4 and OpenOffice Calc 1.1.3 allow entry of formulas that go beyond Excel's capabilities. For example, with all cells in A1:A40 initially containing the formula =ROW(), both allow entry of the formula C1: =SUM(A1;SUM(A2;SUM(A3;SUM(A4;SUM(A5;SUM(A6;SUM(A7;SUM(A8;SUM(A9;SUM(A10; SUM(A11;SUM(A12;SUM(A13;SUM(A14;SUM(A15;SUM(A16;SUM(A17;SUM(A18;SUM(A19; SUM(A20;SUM(A21;SUM(A22;SUM(A23;SUM(A24;SUM(A25;0))))))))))))))))))))))))) 24 levels of nested function calls C3: =SUM(A1;A2;A3;A4;A5;A6;A7;A8;A9;A10;A11;A12;A13;A14;A15;A16;A17;A18;A19;A20; A21;A22;A23;A24;A25;A26;A27;A28;A29;A30;A31;A32;A33;A34;A35;A36;A37;A38;A39;A40) 40 arguments. In isolation, this is good. However, when OpenOffice Calc saves workbooks containing such formulas in Excel 97/2000/2002/2003 .XLS format, it saves the C1 formula but not the C3 formula. Gnumeric saves both. 'Saves' in the sense that when such .XLS files are opened in Excel, the original formula appears in the formula bar. While Excel can't modify these formulas without them being truncated to 7 levels of nested function calls and 30 arguments, Excel does evaluate the C1 formula saved by OpenOffice Calc. The C3 formula is replaced with =#N/A. If the values in A1:A25 change, so does the calculated value of C1 in Excel. So it appears Excel can evaluate formulas involving more than 7 levels of nested function calls created in OpenOffice Calc. For the .XLS workbook saved by Gnumeric, when opened in Excel and fully recalculated ([Ctrl]+[Alt]+[F9]), all formulas evaluate to #VALUE!, including the =ROW() formulas in A1:A40. Selecting A1:A40 and pressing [F2] and [Ctrl]+ [Enter] in sequence makes A1:A40 return 1..40 again, and C1 evaluates again. C3 comes through as the same formula entered in Gnumeric, but evaluates to #VALUE!. From my perspective, it's probably a good thing the C1 formula is saved in .XLS format files by both Gnumeric and OpenOffice Calc since Excel can calculate it even if it can't modify it. Whether the C3 formula should be saved is problematic. However, it *is* a bug that the =ROW() calls in A1:A40 recalculate as #VALUE! in Excel and need to be re-entered. This behavior also occurs when each of the formulas in A1:A40 is entered as single-cell array formulas then saved in .XLS format. So there appears to be a bug in how the ROW() function is saved in .XLS files.
You wrote: "Whether the C3 formula should be saved is problematic." I wonder why. Just because it is saved in an xls file does not mean that it will be opened by XL. Unfortunately ther are circumstances when xls files have to be used but are likely to be opened again by gnumeric. You wrote: "However, it *is* a bug that the =ROW() calls in A1:A40 recalculate as #VALUE! in Excel and need to be re-entered." I agree. Since Excel can show the formula in its formula bar, it apparently is correctly saved. Did you report that Excel bug to MS? (It definitely looks to be more likely an XL bug than a Gnumeric bug.)
I'm not so sure XL can really evaluate these things. In fact, the reason that row() fails might be that XL is blowing its stack somewhere. Hard to tell without source. The real solution here probably is to pop up a warning at save time telling the user that this or that XL limit has been exceeded and that the file may not load in XL. We need to do something like that for our non-Excel functions anyway.
The ROW() issue is unrelated to depth and size, see bug 163236.
I had not given much thought to these sorts of limits. Given the extremely brittle nature of MS Excel I suspect that the right solution will be to export things that do not fit within the bounds as strings. The interesting question would be to see if the OOo team was interested in sharing specs on an xls 'version' (biff99 ?) that would remove these limits so that there was an option to export to xls without worrying about MS Excel. either way, this is a 1.5.x level change.
Retitling
"From an old limit of 256 columns in Excel 2003, we now (Excel 2007) have a whopping 16,384… and from about 64,000 rows available in the prior version, Excel 2007 allows over 1 million!" So is this still an issue? (Or does Excel 2007 new limits only apply to xlsx files?)
In principle, it's still an issue and not just for Excel formats. We ought to warn when we exceed what the format and/or recipient can handle. In practice, it's not that important. People just don't seem to hit this very often.
Note that when we exporting to ODF or XLSX we do provide warnings if we seem to exceed what the file format can handle.
Andreas: we only warn if we exceed the sheet size, not if we exceed (say) maximum formula depth.
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/34.