After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 163192 - Exceeding Excel bounds in excel format export
Exceeding Excel bounds in excel format export
Status: RESOLVED OBSOLETE
Product: Gnumeric
Classification: Applications
Component: import/export MS Excel (tm)
git master
Other All
: Normal normal
: ---
Assigned To: Jody Goldberg
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2005-01-07 01:02 UTC by Harlan Grove
Modified: 2018-05-22 13:09 UTC
See Also:
GNOME target: ---
GNOME version: ---



Description Harlan Grove 2005-01-07 01:02:59 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.
Comment 1 Andreas J. Guelzow 2005-01-07 05:48:45 UTC
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.)
Comment 2 Morten Welinder 2005-01-07 14:36:39 UTC
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.
Comment 3 Morten Welinder 2005-01-07 14:40:44 UTC
The ROW() issue is unrelated to depth and size, see bug 163236.
Comment 4 Jody Goldberg 2005-01-14 07:10:41 UTC
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.
Comment 5 Jon Kåre Hellan 2005-07-14 08:03:00 UTC
Retitling
Comment 6 Andreas J. Guelzow 2009-09-13 07:38:16 UTC
"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?)
Comment 7 Morten Welinder 2009-09-13 15:56:24 UTC
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.
Comment 8 Andreas J. Guelzow 2011-06-17 20:06:57 UTC
Note that when we exporting to ODF or XLSX we do provide warnings if we seem to exceed what the file format can handle.
Comment 9 Morten Welinder 2011-06-17 22:12:18 UTC
Andreas: we only warn if we exceed the sheet size, not if we exceed (say)
maximum formula depth.
Comment 10 GNOME Infrastructure Team 2018-05-22 13:09:04 UTC
-- 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.