Issue 101573 - Open xlsx with multiline formula in a cell
Summary: Open xlsx with multiline formula in a cell
Status: CONFIRMED
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOO300m9
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords: interop_OOXML, ms_interoperability
Depends on:
Blocks:
 
Reported: 2009-05-05 16:56 UTC by eniwetok
Modified: 2017-08-21 13:22 UTC (History)
4 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 4.1.3
Developer Difficulty: ---


Attachments
test file. formula =A4+B4 +C4 (7.23 KB, application/vnd.ms-excel)
2009-10-11 18:12 UTC, raal
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description eniwetok 2009-05-05 16:56:22 UTC
When I open a xlsx file with calc, if a cell contains a formula defined with
multiple lines , calc only shows and use the first line of the formula
Comment 1 raal 2009-10-11 18:10:41 UTC
I can confirm within version DEV320m1,WinXP.
Only first line of formula is imported.
Comment 2 raal 2009-10-11 18:12:36 UTC
Created attachment 65300 [details]
test file. formula =A4+B4 +C4
Comment 3 Edwin Sharp 2013-09-09 10:10:11 UTC
Calc Rev. 1520602 gives correct answer of 3 (=1+2)
Excel 2007 gives wrong answer of 6 (cell C4 is not part of the formula)

Win 7
Comment 4 Daniel Jankovics 2017-08-21 09:58:05 UTC
to reproduce:
- open a new sheet in MS Excel 
- fill cell A1 with: 1
- fill cell B1 with: 2
- fill cell C1 with: 3
- add formula to D1: =A1+B1 (here use Alt+Enter to add a new line within the cell) +C1
- Excel calculates 6 to cell D1
- save the file, open it with OO Calc
- Calc calculates 3 to cell D1 (because processing only the first line of the multiline formula)

i use Excel 2013 (15.0.4953.1000 MSO 64-bit) and OO 4.1.3, Rev. 1761381 on windows 7.
Comment 5 mroe 2017-08-21 13:12:16 UTC
Checked with new AOO versions.
Comment 6 mroe 2017-08-21 13:22:18 UTC
Unzip the test file and open <folder>/xl/worksheets/sheet1.xml with a text editor.
There one find the code:
--- code xlsx begin ---
<c r="D4"><f>A4+B4
+C4</f><v>6</v></c>
--- code xlsx end ---

Between “B4” and “+C4” there is simply a “newline”. Maybe the parser stops at this newline.

AOO Calc doesn't allow a newline inside a formula. If one enter a newline it will always changed to a space.
--- code ods begin ---
table:formula="of:=[.A4]+[.B4] +[.C4]"
--- code ods end ---