Bug 109061 - xlsx import : large ranges in formula shows error on recalc
Summary: xlsx import : large ranges in formula shows error on recalc
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.4.0
Keywords: filter:xlsx
: 134550 136546 146861 (view as bug list)
Depends on:
Blocks: Calc-large-spreadsheets
  Show dependency treegraph
 
Reported: 2017-07-11 09:24 UTC by Dennis Francis
Modified: 2022-02-14 14:10 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
XLSX file with formula containing large range (8.85 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-07-11 09:24 UTC, Dennis Francis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dennis Francis 2017-07-11 09:24:05 UTC
Created attachment 134583 [details]
XLSX file with formula containing large range

Steps to reproduce :

1) Open the attached xlsx file
2) Select cell B4 and do "ReCalculate" (Data > Calculate > ReCalculate)
3) B4 now shows error : #NAME? instead of the value 6.


In the attachment the cell B4 has the formula =SUM(A2:BZZ2) and row 2 has values only upto column C. LO supports only 1024 columns(upto AMJ), but I think for large ranges in formulas should be truncated by the available columns(ie in this case, A2:BZZ2 needs to be truncated to A2:AMJ2) while importing from xlsx rather than reporting an error.
Comment 1 Xavier Van Wijmeersch 2017-07-11 12:38:50 UTC
i did the steps and yes confirmed, but looking in the formula the second part of the sum is lowercase(=SUM(A2:bzz2)), this is because the BZZ2 column does not exist; it most be ABZ2 and you can recalculate without the #NAME error

Version: 6.0.0.0.alpha0+
Build ID: 959be1b5a9cd522394dba9366686a1256588223b
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-07-11_00:18:59
Locale: nl-BE (en_US.UTF-8); Calc: group
Comment 2 Xavier Van Wijmeersch 2017-07-11 12:57:47 UTC
i am sorry for the noise because it did not read the description properly;
but one thing i can't understand is why LibreOffice must be a clone of or in the worst case the same as M$Office
Comment 3 Xisco Faulí 2017-07-11 14:50:45 UTC
Moving to ASSIGNED since there is a patch in gerrit for this bug: https://gerrit.libreoffice.org/#/c/39815/
Comment 4 Eike Rathke 2017-07-11 17:55:25 UTC
Truncating ranges to actually supported columns is not a good idea. While it works for SUM() if cells in excess columns were empty, it fails and silently changes the result if they were not empty. It also does not work for functions that take the argument as array and empty cells are evaluated as 0 to be calculated with. Already COLUMNS(Range) delivers a different result, and something like COLUMN(Range) in array context with Range truncated truncates also the resulting array with column numbers, so for example {=SUM(COLUMN(A2:BZZ2))} would give a different result instead of Error.
Comment 5 Dennis Francis 2017-07-12 12:21:48 UTC
(In reply to Eike Rathke from comment #4)
> Truncating ranges to actually supported columns is not a good idea. While it
> works for SUM() if cells in excess columns were empty, it fails and silently
> changes the result if they were not empty. It also does not work for
> functions that take the argument as array and empty cells are evaluated as 0
> to be calculated with. Already COLUMNS(Range) delivers a different result,
> and something like COLUMN(Range) in array context with Range truncated
> truncates also the resulting array with column numbers, so for example
> {=SUM(COLUMN(A2:BZZ2))} would give a different result instead of Error.

Yes makes sense, I see lots of problems that way. I guess now the only feasible way is to push forward the work on tdf#50916. Thanks.
Comment 6 Xisco Faulí 2017-10-11 08:04:34 UTC
Dear  Dennis Francis,
This bug has been in ASSIGNED status for more than 3 months without any activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.
Comment 7 QA Administrators 2018-10-12 03:05:06 UTC Comment hidden (obsolete)
Comment 8 Roman Kuznetsov 2018-10-12 12:31:04 UTC
still repro in 

Version: 6.2.0.0.alpha0+
Build ID: d9ad59da50c1172fe98f94370221c9c1b688200a
CPU threads: 4; OS: Windows 6.1; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2018-10-08_23:34:44
Locale: ru-RU (ru_RU); Calc: threaded
Comment 9 NISZ LibreOffice Team 2020-10-02 17:15:09 UTC
Now very large sheets are somewhat supported, but enabling that and opening this file makes Calc instantly crash in current 7.1 master.

Version: 7.1.0.0.alpha0+ (x64)
Build ID: dda71436b8594fb0669b3ac06c02a3997a0674be
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: CL
Comment 10 Roman Kuznetsov 2022-02-11 14:36:16 UTC
no crash and no repro the problem from the description in

Version: 7.4.0.0.alpha0+ / LibreOffice Community
Build ID: 96e1be11540bada172fbdbfbbe3f9b7dc3e58462
CPU threads: 4; OS: Mac OS X 10.16; UI render: default; VCL: osx
Locale: ru-RU (ru_RU.UTF-8); UI: en-US
Calc: threaded Jumbo

Note: Jumbo feature should be enabled.

Should we close this one as WFM now?
Comment 11 Xisco Faulí 2022-02-14 09:50:41 UTC
Issue fixed by

https://cgit.freedesktop.org/libreoffice/core/commit/?id=8bb457d17ef970676f60976cc4e2de9c9f5340c0

author	Luboš Luňák <l.lunak@collabora.com>	2022-02-07 16:09:58 +0100
committer	Luboš Luňák <l.lunak@collabora.com>	2022-02-10 11:59:39 +0100
commit 8bb457d17ef970676f60976cc4e2de9c9f5340c0 (patch)
tree fa9d46cde0577f4b26bd9bedada7ef309ad59ea9
parent a6eddceda5d376cd73922123a3bb3a5683307c41 (diff)
dynamic logarithmic columns in ScBroadcastAreaSlotMachine

Closing as RESOLVED FIXED
Comment 12 Xisco Faulí 2022-02-14 10:02:14 UTC
*** Bug 134550 has been marked as a duplicate of this bug. ***
Comment 13 Luboš Luňák 2022-02-14 12:23:18 UTC
*** Bug 136546 has been marked as a duplicate of this bug. ***
Comment 14 Luboš Luňák 2022-02-14 13:27:40 UTC
*** Bug 146861 has been marked as a duplicate of this bug. ***
Comment 15 Commit Notification 2022-02-14 14:10:34 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/b4c131cf4237ed7d4e4be1212318c818dc943fce

tdf#109061: sc_jumbosheets: Add unittest

It will be available in 7.4.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.