Download it now!
Bug 109061 - xlsx import : large ranges in formula shows error on recalc
Summary: xlsx import : large ranges in formula shows error on recalc
Status: NEW
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:
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2017-07-11 09:24 UTC by Dennis Francis
Modified: 2018-12-20 06:36 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


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