Bug 61908 - FILESAVE: XLSX formula cell range is not exported for matrix/array formulas
Summary: FILESAVE: XLSX formula cell range is not exported for matrix/array formulas
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: Other Linux (All)
: high major
Assignee: Eike Rathke
URL:
Whiteboard: target:5.1.0 target:4.4.6 target:5.0.3
Keywords:
: 61157 94196 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-03-06 16:06 UTC by kvetzal
Modified: 2020-08-26 14:26 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
xlsx file showing incorrect array formula (6.12 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-03-06 16:06 UTC, kvetzal
Details
Another simple test case (4.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-03-11 18:02 UTC, Jim Avera
Details
Another simple test case with embedded instructions (4.75 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-03-11 18:18 UTC, Jim Avera
Details
Another simple test case with embedded instructions (5.35 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-03-11 18:20 UTC, Jim Avera
Details
Array multiplication example (8.64 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-06-01 11:54 UTC, Yogesh Bharate
Details
MMULT-RT.xlsx (6.54 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-06-01 12:45 UTC, Yogesh Bharate
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kvetzal 2013-03-06 16:06:07 UTC
Created attachment 76026 [details]
xlsx file showing incorrect array formula

My test case (see attached file) was created in LibreOffice 3.5.7.2 running under Ubuntu 10.04.  However, a friend has confirmed it also happens with the most recent versions of LibreOffice and Ubuntu.

Steps to reproduce:

1. Enter a 2x3 array in cells A1:B3.
2. Enter a 3x1 array in cells E1:E3.
3. Enter array formula MMULT(A1:B3;E1:E3) in cells E5:E6.
4. Save as test.ods.  Reopen, and everything is fine.
5. Save as test.xlsx.  Reopen, and the entries in cells E5:E6 are incorrect.  The value in E5 is correct, but it is no longer an array formula.  The value in E6 is incorrect, since it is just =E5.  The test.xlsx file is attached below.
Comment 1 crazyskeggy 2013-03-06 16:36:06 UTC
Comment on attachment 76026 [details]
xlsx file showing incorrect array formula

Note: I have updated the attachment's mime type so that downloading is possible
Comment 2 A (Andy) 2013-03-30 08:36:50 UTC
I could not reproduce it with LO 4.0.1.2 (Win7 Home, 64bit).
But is this a misunderstanding from my side, what should be inserted in cell E6?

Does this issue still persist for you with LO 4.0.1.2?
Comment 3 kvetzal 2013-04-01 02:47:57 UTC
LO 4.0.1.2 does not fix this issue for me (running on ubuntu 12.04), 
though there is a slight change  compared to LO 3.5.7.2.

Let me give you the specifics of my example again:

Cell A1:  1  Cell B1:  2  Cell C1:  3
Cell A2:  4  Cell B2:  5  Cell C2:  6

Cells E1:E3  2.8, 3.5 and 4.7 respectively.

Save as .ods, and re-open:  values in cells E5 and E6 and 23.9 and 56.9 
respectively.  For either cell E5 or cell E6, the formula bar displays 
(correctly)

{=MMULT(A1:C2,E1:E3)}

This was fine in both LO 3.5.7.2 and LO 4.0.1.2.

Save as .xlsx, and re-open:

1.  Under LO 3.5.7.2,  values in both cells E5 and E6 were 23.9. Formula 
bar gives
=MMULT(A1:C2,E1:E3) (note the absence of curly braces) in cell E5, and 
=E5 in E6.

2. Under LO 4.0.1.2, values in cells E5 and E6 are 23.9 and 56.9 
respectively.  However, the formula bar gives the same behaviour as 
under LO 3.5.7.2, indicating that this is no longer an array formula 
(due to the absence of curly braces).

Moreover, suppose I change the entry in cell C2 from 6 to 7.  This 
should have no effect on the value in cell E5, but it should change the 
value in cell E6 to 61.6.  This happens for the .ods file. However, 
making this change in the .xlsx file, the entry in cell E6 changes to 
23.9.  In other words, as soon as the spreadsheet recalculates, the 
entry is incorrect under LO 4.0.1.2.


On 03/30/2013 04:36 AM, bugzilla-daemon@freedesktop.org wrote:
>
> *Comment # 2 <https://bugs.freedesktop.org/show_bug.cgi?id=61908#c2> 
> on bug 61908 <https://bugs.freedesktop.org/show_bug.cgi?id=61908> from 
> A <mailto:stgohi-lobugs@yahoo.de> *
> I could not reproduce it with LO 4.0.1.2 (Win7 Home, 64bit).
> But is this a misunderstanding from my side, what should be inserted in cell
> E6?
>
> Does this issue still persist for you with LO 4.0.1.2?
> ------------------------------------------------------------------------
> You are receiving this mail because:
>
>   * You reported the bug.
>
Comment 4 V. Wood 2013-04-28 13:53:22 UTC
LO 4.0.1.2 does not fix this issue for me, running on Windows 7.
Comment 5 V. Wood 2013-04-28 13:54:13 UTC
It's nothing to do with the actual MMULT function, it's to do with the array formulas.  All array formulas fail when saved to xlsx.
Comment 6 Joel Madero 2013-05-25 17:18:30 UTC
Thank you for reporting this issue! I have been able to confirm the issue on:
Version: 4.0.3.3 release
Platform: Bodhi Linux 2.2 x64

+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
As I've been able to confirm this problem I am marking as:

New (confirmed)
Major - loss of data
High - default, seems appropriate although a case for highest could probably be made as arrays are essentially useless with probably the most common format around these days (I believe xlsx is surpassing or has surpassed xls format)


+ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link:
https://wiki.documentfoundation.org/QA/BugTriage and join us on freenode at #libreoffice-qa

There are also other ways to get involved including with marketing, UX, documentation, and of course developing -  http://www.libreoffice.org/get-help/mailing-lists/. 

Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Comment 7 GerardF 2013-05-31 12:44:32 UTC
*** Bug 61157 has been marked as a duplicate of this bug. ***
Comment 8 sdaniele78 2013-06-17 20:15:08 UTC
Check also file in:
https://bugs.freedesktop.org/show_bug.cgi?id=65835 

with alot of arrays formula.
Comment 9 Jim Avera 2015-03-11 17:59:12 UTC
I also confirm using 4.3.3.2 on Ubuntu linux.   

Attaching a slightly different test case which just uses SUM(range-range)
Comment 10 Jim Avera 2015-03-11 18:02:10 UTC
Created attachment 114045 [details]
Another simple test case
Comment 11 Jim Avera 2015-03-11 18:18:22 UTC
Created attachment 114047 [details]
Another simple test case with embedded instructions
Comment 12 Jim Avera 2015-03-11 18:20:37 UTC
Created attachment 114048 [details]
Another simple test case with embedded instructions
Comment 13 Yogesh Bharate 2015-05-28 10:37:37 UTC
- This is not right way to calculate matrix multiplication.
If array A1 = R1 x C1 and array A2 = R2 x C2 (R = row and C = Column) then the resultant matrix(there must be C1=R2) will be R1 x C2, but here it allocate only one cell for matrix multiplication result instead of two. 
- Please refer this link for more details : 
http://www.excelfunctions.net/Excel-Mmult-Function.html
- Suppose if we allocate single cell for array matrix multiplication, resultant matrix will display the first row multiplication with first column which is correct.(i.e G5 in our case.)
And if we make G6 formula as equal to G5 which shows right result in LibreOffice.
- Thus, there is problem while exporting MMULT() i.e matrix multiplication.
Comment 14 Yogesh Bharate 2015-06-01 11:54:48 UTC
Created attachment 116220 [details]
Array multiplication example
Comment 15 Yogesh Bharate 2015-06-01 12:45:42 UTC
Created attachment 116223 [details]
MMULT-RT.xlsx
Comment 16 Commit Notification 2015-06-12 14:42:36 UTC
yogesh.bharate001 committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=beb8e2830dc9e1c771e196fcaf08cdfd6bf3dde3

tdf#61908:XLSX formula cell range is not exported for MMULT.

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 17 m_a_riosv 2015-09-14 14:37:43 UTC
*** Bug 94196 has been marked as a duplicate of this bug. ***
Comment 18 Commit Notification 2015-09-15 11:10:39 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=92df7db85a3da10f18a5a06fb53a9cb69910e835

array formulas do not consist only of multiple rows, tdf#61908 follow-up

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 19 Commit Notification 2015-09-15 13:37:12 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f501fe4da88e1d64fcc88a492a52911113d28f6a

do not write MM_REFERENCE formulas to OOXML, tdf#61908 follow-up

It will be available in 5.1.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 20 Eike Rathke 2015-09-15 14:53:47 UTC
Squashed patches and an additional fix pending review
https://gerrit.libreoffice.org/18592 for 5-0
https://gerrit.libreoffice.org/18593 for 4-4
Comment 21 Commit Notification 2015-09-17 08:09:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=464d1ef1868ce879e1bd7542167df8e4b9c68d1e&h=libreoffice-4-4

tdf#61908 OOXML export cell range for matrix/array formula

It will be available in 4.4.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 22 Commit Notification 2015-09-17 08:11:17 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=225bd5f46367ef0955c5cf5037a7f31cd8da3adc&h=libreoffice-5-0

tdf#61908 OOXML export cell range for matrix/array formula

It will be available in 5.0.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.