Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing |
Summary: | ODFF: EmptyCell return for functions, like LOOKUP() | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Product: | Calc | Reporter: | ooo | ||||||||||||||
Component: | ui | Assignee: | oc | ||||||||||||||
Status: | CLOSED FIXED | QA Contact: | issues@sc <issues> | ||||||||||||||
Severity: | Trivial | ||||||||||||||||
Priority: | P3 | CC: | issues, kpalagin, pagalmes.lists | ||||||||||||||
Version: | recent-trunk | Keywords: | ms_interoperability | ||||||||||||||
Target Milestone: | --- | ||||||||||||||||
Hardware: | All | ||||||||||||||||
OS: | All | ||||||||||||||||
Issue Type: | FEATURE | Latest Confirmation in: | --- | ||||||||||||||
Developer Difficulty: | --- | ||||||||||||||||
Attachments: |
|
Description
ooo
2004-07-30 11:51:18 UTC
Accepted. *** Issue 44019 has been marked as a duplicate of this issue. *** *** Issue 56761 has been marked as a duplicate of this issue. *** *** Issue 61759 has been marked as a duplicate of this issue. *** This issues eems to be related to http://www.openoffice.org/issues/show_bug.cgi?id=8088 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. 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. This maybe will be addressed for OOo3.0, but as usual: if time and resources permit. *** Issue 82725 has been marked as a duplicate of this issue. *** ODFF relevant. 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 Created attachment 51312 [details]
testcases for "emptiness"
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 Created attachment 51608 [details]
more complete test cases
Reassigning to QA for verification. Created attachment 51714 [details]
TestCaseSpecification
Verified in internal build cws_odff @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 @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. Created attachment 51750 [details]
Testdocuments for Test Case Specification
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 Created attachment 51836 [details]
MS Excel 97 test case results
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 Created attachment 51838 [details]
MS Excel empty cell behaviour (XL 97)
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 closed because fix available in builds OOO300_m9 and DEV300_m33 |