Apache OpenOffice (AOO) Bugzilla – Issue 35483
Function VALUE() returns error if targeted cell is empty. Excel returns 0 in same case.
Last modified: 2013-08-07 15:13:21 UTC
Discovered when opening working Excel spreadsheet. Function VALUE() returns error if targeted cell is null. Excel returns 0 in same case. Steps: 1. Open spreadsheet. 2. Delete contents of selected cell, let's call it <cell a>. 3. In another cell enter =VALUE(<cell a>). 4. In Excel 2000 when you apply the VALUE function to a cell with null contents you will get 0 back. In Open Office Spreadsheet you get Err:502.
Hi, from a mathematical point of view OOo Calc acts correctly as an empty cell is not identical with zero, so an error message should be shown. Nevertheless I re-assign this Issue as Enhancement request to Bettina. Frank
enhanced summary, set keywords and reassigned issue according to RFE process
This doesn't need to be queued into requirements, it's a simple interpreter compatibility enhancement. Grabbing issue. Btw, I would had preferred if someone of the Calc team would had been informed about this..
Accepted.
Patch for it. --- sc/source/core/tool/interpr1.cxx 2005-11-13 23:30:11.000000000 +0530 +++ sc/source/core/tool/interpr1.cxx 2005-12-19 22:04:52.000000000 +0530 @@ -2387,6 +2387,8 @@ void ScInterpreter::ScValue() double fVal; if (pFormatter->IsNumberFormat(aInputString, nFIndex, fVal)) PushDouble(fVal); + else if(aInputString.Len() == 0) + PushDouble(0.0); else SetIllegalArgument(); }
Hi Muthu, Thanks for the try, but this is not sufficient. Your patch evaluates any empty string as 0, also empty strings of formula results, e.g. ="". Excel evaluates empty strings as #VALUE errors, only real empty cells result in 0. In case of an empty string you'd have to check if it resulted from a cell reference by comparing the remembered GetStackType() with svSingleRef and svDoubleRef. Btw, please always _attach_ diffs instead of placing them in the comment, which changes whitespace and may lead to unintentional line breaks so that the patch doesn't apply anymore. Thanks Eike
Created attachment 33236 [details] Updated patch.
Hi Muthu, You're nearing it ;-) Note that it isn't necessary to implement the same logic twice for svSingleRef and svDoubleRef, as we have a helper method PopDoubleRefOrSingleRef() for use in higher level functions. And while at it, we could already differentiate between string and numeric values, instead of converting numeric values to string and back. The resulting path would be something like (untested) switch (GetStackType()) { case svDouble: PushDouble( PopDouble()); return; break; case svSingleRef: case svDoubleRef: { ScAddress aAdr; if (!PopDoubleRefOrSingleRef( aAdr)) return; ScBaseCell* pCell = GetCell( aAdr); if (!pCell) { PushDouble(0.0); return; } else if (pCell->HasValueData()) { PushDouble( GetCellValue( aAdr, pCell)); return; } GetCellString( aInputString, pCell); } break; default: aInputString = GetString(); } Btw, when creating String objects it isn't necessary to assign them EMPTY_STRING, the default ctor already initializes them as empty. EMPTY_STRING is a static String object and is used when you want to avoid the creation of a temporary String object. Btw2, StackVar stackType is an unused variable. Eike
Created attachment 33305 [details] Up-Updated patch
When cell is empty pCell->HasValueData and pCell->HasStringData are (both) false. checking pCell == NULL when cell is empty doesn't seem to work.
Correct, pCell is not NULL in this case when a single cell is referred, since a dummy note cell exists for broadcasting purposes. Thanks for being patient, I think we got that going now :-) Changing issue type to PATCH and retargeting to OOo2.0.3. Eike
In CWS calc36 sc/source/core/tool/interpr1.cxx 1.37.104.1 Also added handling of svMatrix cases.
er, It will be greate if you can attach the final patch here (or the location of where I can find it). I'll be able to know what I missed (and ofcourse learn from it). Thanks, muthusuba
As I wrote in my previous comment, I committed the change to sc/source/core/tool/interpr1.cxx 1.37.104.1, so cd sc/source/core/tool cvs diff -pu -r1.37 -r1.37.104.1 interpr1.cxx to obtain the patch.
Created attachment 36047 [details] Test cases.
Reassigning to QA. re-open issue and reassign to oc@openoffice.org
reassign to oc@openoffice.org
reset resolution to FIXED
verified in internal build calc36
closed because fix available in OpenOffice.org Developer Snapshot Build src680_m167