Issue 32340

Summary: ODFF: EmptyCell return for functions, like LOOKUP()
Product: Calc Reporter: ooo
Component: uiAssignee: oc
Status: CLOSED FIXED QA Contact: issues@sc <issues>
Severity: Trivial    
Priority: P3 CC: issues, kpalagin, pagalmes.lists
Version: recent-trunkKeywords: ms_interoperability
Target Milestone: ---   
Hardware: All   
OS: All   
Issue Type: FEATURE Latest Confirmation in: ---
Developer Difficulty: ---
Attachments:
Description Flags
testcases for "emptiness"
none
more complete test cases
none
TestCaseSpecification
none
Testdocuments for Test Case Specification
none
MS Excel 97 test case results
none
MS Excel empty cell behaviour (XL 97) none

Description ooo 2004-07-30 11:51:18 UTC
Follow-up task of Q-PCD issue 20494.
Implement "EmptyCell" as a result, instead of returning an explicit empty string
or numeric 0.
Comment 1 ooo 2004-07-30 11:52:36 UTC
Accepted.
Comment 2 daniel.rentz 2005-03-23 13:31:16 UTC
*** Issue 44019 has been marked as a duplicate of this issue. ***
Comment 3 frank 2005-10-27 11:13:41 UTC
*** Issue 56761 has been marked as a duplicate of this issue. ***
Comment 4 frank 2006-02-08 11:06:38 UTC
*** Issue 61759 has been marked as a duplicate of this issue. ***
Comment 5 kpalagin 2006-08-31 09:55:53 UTC
This issues eems to be related to
http://www.openoffice.org/issues/show_bug.cgi?id=8088
Comment 6 ooo 2006-08-31 16:25:41 UTC
No, it isn't related. That issue is about finding an empty cell when searching
for a value of 0, which in Excel also works only if Lotus-1-2-3 compatibility is
enabled. This issue here is about returning an empty cell as a result for
further reference, instead of assuming a typecasted value.
Comment 7 kpalagin 2006-08-31 20:08:44 UTC
er,
thanks for your attention. 
Any chance of addressing 32340 earlier than "OOo Later"? Currently it is pain 
to rework spreadsheets during migration, which adds negative to the whole 
process.
Comment 8 ooo 2006-09-04 13:52:02 UTC
This maybe will be addressed for OOo3.0, but as usual: if time and resources permit.
Comment 9 ooo 2007-10-18 11:36:23 UTC
*** Issue 82725 has been marked as a duplicate of this issue. ***
Comment 10 ooo 2007-11-14 13:52:22 UTC
ODFF relevant.
Comment 11 ooo 2008-02-01 17:17:29 UTC
In cws odff:

sc/inc/Attic/formularesult.hxx  1.1.2.1
sc/inc/Attic/formularesult.hxx  1.1.2.2
sc/inc/Attic/formularesult.hxx  1.1.2.3
sc/inc/Attic/formularesult.hxx  1.1.2.4
sc/inc/Attic/formularesult.hxx  1.1.2.5
sc/inc/cell.hxx  1.25.64.3
sc/inc/cell.hxx  1.25.64.4
sc/inc/cell.hxx  1.25.64.5
sc/inc/cell.hxx  1.25.64.6
sc/inc/cell.hxx  1.25.64.7
sc/inc/document.hxx  1.106.4.1
sc/inc/recursionhelper.hxx  1.4.498.1
sc/inc/scmatrix.hxx  1.8.90.2
sc/inc/scmatrix.hxx  1.8.90.3
sc/inc/tokenarray.hxx  1.10.30.4
sc/inc/tokenarray.hxx  1.10.30.5
sc/inc/token.hxx  1.13.60.11
sc/inc/token.hxx  1.13.60.12
sc/inc/token.hxx  1.13.60.13
sc/inc/token.hxx  1.13.60.7
sc/inc/token.hxx  1.13.60.8
sc/source/core/data/cell2.cxx  1.30.90.2
sc/source/core/data/cell2.cxx  1.30.90.3
sc/source/core/data/cell2.cxx  1.30.90.4
sc/source/core/data/cell2.cxx  1.30.90.5
sc/source/core/data/cell2.cxx  1.30.90.6
sc/source/core/data/cell.cxx  1.39.60.4
sc/source/core/data/cell.cxx  1.39.60.6
sc/source/core/data/cell.cxx  1.39.60.7
sc/source/core/data/cell.cxx  1.39.60.8
sc/source/core/data/cell.cxx  1.39.60.9
sc/source/core/data/column.cxx  1.25.36.1
sc/source/core/data/dociter.cxx  1.19.158.2
sc/source/core/data/documen4.cxx  1.18.158.3
sc/source/core/data/documen4.cxx  1.18.158.4
sc/source/core/data/document.cxx  1.81.4.1
sc/source/core/inc/interpre.hxx  1.31.38.7
sc/source/core/inc/interpre.hxx  1.31.38.8
sc/source/core/inc/interpre.hxx  1.31.38.9
sc/source/core/tool/cellform.cxx  1.5.318.1
sc/source/core/tool/compiler.cxx  1.70.56.17
sc/source/core/tool/compiler.cxx  1.70.56.18
sc/source/core/tool/interpr1.cxx  1.51.38.12
sc/source/core/tool/interpr1.cxx  1.51.38.13
sc/source/core/tool/interpr1.cxx  1.51.38.14
sc/source/core/tool/interpr2.cxx  1.33.56.10
sc/source/core/tool/interpr2.cxx  1.33.56.7
sc/source/core/tool/interpr2.cxx  1.33.56.8
sc/source/core/tool/interpr2.cxx  1.33.56.9
sc/source/core/tool/interpr3.cxx  1.20.30.3
sc/source/core/tool/interpr3.cxx  1.20.30.4
sc/source/core/tool/interpr4.cxx  1.51.56.12
sc/source/core/tool/interpr4.cxx  1.51.56.13
sc/source/core/tool/interpr4.cxx  1.51.56.14
sc/source/core/tool/interpr4.cxx  1.51.56.15
sc/source/core/tool/interpr4.cxx  1.51.56.16
sc/source/core/tool/interpr4.cxx  1.51.56.17
sc/source/core/tool/interpr5.cxx  1.27.34.4
sc/source/core/tool/interpr5.cxx  1.27.34.5
sc/source/core/tool/interpr5.cxx  1.27.34.6
sc/source/core/tool/rangenam.cxx  1.24.158.5
sc/source/core/tool/token.cxx  1.29.30.13
sc/source/core/tool/token.cxx  1.29.30.14
sc/source/core/tool/token.cxx  1.29.30.16
sc/source/core/tool/token.cxx  1.29.30.17
sc/source/core/tool/token.cxx  1.29.30.18
sc/source/filter/excel/excform.cxx  1.48.60.3
sc/source/filter/excel/tokstack.cxx  1.12.90.2
sc/source/filter/excel/xeformula.cxx  1.20.8.4
sc/source/ui/view/viewfunc.cxx  1.39.4.1
Comment 12 ooo 2008-02-01 17:21:25 UTC
Created attachment 51312 [details]
testcases for "emptiness"
Comment 13 ooo 2008-02-20 16:44:31 UTC
Additional changes for inherited emptiness:

sc/inc/token.hxx  1.13.60.18
sc/source/core/tool/interpr1.cxx  1.51.38.20
sc/source/core/tool/interpr4.cxx  1.51.56.21
sc/source/core/tool/interpr5.cxx  1.27.34.10
sc/source/core/tool/token.cxx  1.29.30.22
Comment 14 ooo 2008-02-20 17:28:17 UTC
Created attachment 51608 [details]
more complete test cases
Comment 15 ooo 2008-02-20 22:24:25 UTC
Reassigning to QA for verification.
Comment 16 oc 2008-02-26 07:49:22 UTC
Created attachment 51714 [details]
TestCaseSpecification
Comment 17 oc 2008-02-26 07:50:31 UTC
Verified in internal build cws_odff
Comment 18 ooo 2008-02-27 10:51:04 UTC
@oc: in the test case specification please use the more complete test cases of
the second attachment
http://www.openoffice.org/nonav/issues/showattachment.cgi/51608/i32340_variable_result_2.ods
Comment 19 oc 2008-02-27 11:26:35 UTC
@er: I've used the second document and have added a check in cell B36 to make it
easier for the automatic tests which will be written in short. But (ashes on my
head, as it is said in Germany) I've renamed the document back to the first one
to shorten the name and have forgotten to attach the enhanced and renamed
version. Thanks for the hint.
Comment 20 oc 2008-02-27 11:27:43 UTC
Created attachment 51750 [details]
Testdocuments for Test Case Specification
Comment 21 ronleach 2008-03-01 17:09:43 UTC
Though I cannot test the new version, I ran the 'test case' through OOo 2.3, and
through MS Excel 97.  For anyone who hasn't tried it yet, the test case
exercises a number of direct, indirect, and built-in function accesses against
several types of cell content, most especially an 'empty cell'.  The sheet
checks the resulting values against what it 'expects' to see, displaying 'ok'
where the result value is as expected, and 'ERR' if (presumably) the result is
not what is expected by the designers.

As would be anticipated, OOo 2.3 displayed 'ERR' in several cells.  Far from
this being a problem, it underlines the 'correctness' of the test cases, because
they can be seen to be exercising the cell content and reference methods that
are giving problems in the current releases.  And judging from the 'expected'
behaviour, I personally am delighted that the problem I reported in 82725 likely
has been fixed, well done, everyone.

Unexpected, though, were a few 'ERR' displays when running the test case through
MS Excel, (XL 97 in this test).  The cell displays from the Excel 97 run are in
the attachment - these show which specific reference methods give rise to 'ERR'.
 Since 'ERR' (presumably, as mentioned above) indicates a cell result that
differs from the result that the OOo designers expect, 'ERR' displays imply that
the new behaviour of OOo will differ from the behaviour of Excel.  (XL 97, at
least, and I have asked a colleague to check against XL 2003.)

[My test procedure:
1. Open the test case i32340_variable_result.ods in OOo 2.3
2. Save as MS Excel 97/2000/XP with .xls extention
3. Open in Excel 97, view the cells, notice some display 'ERR'
4. Capture the results by converting all cells to their actual displayed value
(select sheet, copy, paste special, values skipping blanks)
5. Save resulting data-only sheet as
i32340_variable_result_testcases_XL97_results.xls
6. Open saved sheet in OOo 2.3 to resave in .ods format
7. Save as i32340_variable_result_testcases_XL97_results.ods to attach to this
report]

I think this finding raises 2 completely different questions.

(1)  Is the test case 'correct' in the sense that it embodies the
logically/mathematically correct behaviour that we wanted OOo to exhibit?
Presumably it is, but before we address any apparent interoperability problems
let's be sure we are dealing with a genuine issue.

(2)  If we do have the intended behaviour in OOo, how, in general, do we solve
any resulting problems of interoperability with MS Office?
I've two suggestions here, though I would guess that tracking MS Office
functionality has created issues before, and perhaps there's already a preferred
approach.  I would suggest:

a. Confirm the existence of an interoperability issue, first.  Create a specific
interoperability test case (let's call it a behaviour case) and confirm it
against Excel (97, 2000, 2003, 2007, separate cases if necessary).  Run these
behaviour cases against the existing OOo releases, and against the new
developments that are believed to address the interoperating problems.  Record
the results so that we know what the confirmed problems are, if any.

b. Consider the feasibility of an MS Office compatibility mode (a little like MS
did for the Intel Pentium bug) which could be switched on or off.

I hope the report makes sense and is useful.  Can I close by again saying thanks
to everyone who has worked on this issue?  As already said, the problem I was
hitting seems to be addressed.

regards, Ron
Comment 22 ronleach 2008-03-01 17:11:16 UTC
Created attachment 51836 [details]
MS Excel 97 test case results
Comment 23 ronleach 2008-03-01 18:35:15 UTC
As mentioned earlier, I thought that a spreadsheet with the Excel empty cell
behaviour would be useful to help us all check specifically for interoperability
issues.

I attach an OOo calc sheet with the observed behaviour of Excel embodied in the
'expectation' cells. The sheet is just the 32340 test case altered to 'expect'
the Excel behaviour I've observed.  Cells in this sheet will display 'ok' if the
OOo version behaves like Excel does, or will display 'ERR' if OOo behaviour
differs from Excel.

It is possible that MS Excel behaviour differs in different versions.  This
attachment is based on the behaviour in Excel 97.  Hopefully we can at least use
it as a starting point.  I have a colleague with a later Excel version, and I
will check whether the behaviour differs in the later version.

I'm particularly keen to focus on solving any residual interoperating problems
because I'd like to switch to OOo in our technical work.  Since our customers,
and our existing models, use Excel, interoperability problems in any commonly
encountered constructions means that we cannot easily migrate, let alone
interest our clients in doing so.  But that's just a bit of background.

Thanks for reading the note, and I hope the attachment is helpful.

regards, Ron
Comment 24 ronleach 2008-03-01 18:37:10 UTC
Created attachment 51838 [details]
MS Excel empty cell behaviour (XL 97)
Comment 25 ooo 2008-03-10 18:09:41 UTC
Ron,

The difference you observed comparing OOo and Excel is described in
http://sc.openoffice.org/servlets/ReadMsg?list=features&msgNo=263

Excel in itself is completely inconsistent in how it handles empty cells and
empty string formula results, see also issue 65221.

  Eike
Comment 26 oc 2008-10-17 14:05:19 UTC
closed because fix available in builds OOO300_m9 and DEV300_m33