Issue 123668

Summary: Excel import: newlines in formulas not supported
Product: Calc Reporter: Prosper Uniger <firstofnone>
Component: open-importAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Normal    
Priority: P3 CC: barrie, elish, issues, rb.henschel
Version: 4.0.1Keywords: interop_OOXML
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
test case, borrowed from Apache POI
none
A subset of an MS Excel app using line feeds in formulas none

Description Prosper Uniger 2013-11-09 21:37:41 UTC
Created attachment 81904 [details]
test case, borrowed from Apache POI

in Excel newlines are valid in formulas, like:

=B1  (Alt + Enter)
+B2

gets the same result as =B1+B2


the sample file is taken from: http://svn.apache.org/repos/asf/poi/trunk/test-data/spreadsheet/NewlineInFormulas.xlsx

in Excel the cells are:
A1 =SUM(
1;2
)

B3 =B1
+B2

AOO shows:
A1 =SUM()

B3 =B1
Comment 1 Edwin Sharp 2013-11-11 06:12:31 UTC
Excel 2007:
A1 =SUM(
B3 =B1

IMHO Calc shouldn't mimic this useless feature.
Comment 2 Prosper Uniger 2013-11-11 20:05:40 UTC
(In reply to Edwin Sharp from comment #1)
> Excel 2007:
> A1 =SUM(
> B3 =B1
That's odd, my ancient but fully patched Excel 2003 can read and write formulas with newlines.

here the link to the POI bug report and how they fixed it:
https://issues.apache.org/bugzilla/show_bug.cgi?id=50440

added interop_OOXML since the bug is with .xlsx-files only 


> IMHO Calc shouldn't mimic this useless feature.
But the current behavior suggests the user everything went fine, while parts of the formula are missing.
Comment 3 Regina Henschel 2013-11-11 20:55:17 UTC
The line feed character belongs to the white spaces, that are allowed in ODF1.2. see section 5.14 Whitespace in ODF1.2 part 2,
"Evaluators shall treat SPACE (U+0020), CHARACTER TABULATION (U+0009), LINE FEED (U+000A), and CARRIAGE RETURN (U+000D) as whitespace characters."
and even more,
"Evaluators should retain whitespace entered by the original formula creator and use it when saving or presenting the formula, and should not add additional whitespace unless directed to do so during the process of editing a formula."

For details see
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html

So this is not only a valid request for interoperability with OOXML but also a needed feature for ODF1.2.

Currently UI replaced entered line feeds with a space character. This makes sense because AOO hasn't got a 'multiple line' input line yet.

But opening too replaces line feeds with space characters and if I understand section 5.14 correctly, that should not happen.
Comment 4 Barrie 2013-11-13 14:31:49 UTC
I find this defect to be a big headache.  I create applications in MS Excel and I liberally use the alt + enter feature when creating large formulas.  The line feeds provide a lot more readability.  Some of my user community like to use OO Spreadsheet but the apps I send them are rendered useless because all these long formulas are decapitated to show only the first line.
Comment 5 Edwin Sharp 2013-11-13 15:15:37 UTC
(In reply to Barrie from comment #4)
> I find this defect to be a big headache.  I create applications in MS Excel
> and I liberally use the alt + enter feature when creating large formulas. 
> 
Please do share an example.
Comment 6 Barrie 2013-11-13 23:05:36 UTC
Created attachment 81916 [details]
A subset of an MS Excel app using line feeds in formulas
Comment 7 Barrie 2013-11-13 23:09:37 UTC
Comment on attachment 81916 [details]
A subset of an MS Excel app using line feeds in formulas

Edwin,
This may be a repeat of an earlier comment - not sure.  The attached .xlsx file contains a fragment of an MS Excel app that uses line feeds to clarify long formulas.  Without the line feeds, parsing the formula makes me both cross eyed and cross.  When this file is opened in OO only the first line of the formulas are captured and usually is leads to an Err condition.   Thanks for your interest