Hi Eike,
Eike Rathke schrieb:
Hi Regina,
On Wednesday, 2009-08-05 16:06:45 +0200, Regina Henschel wrote:
I'am currently looking at issue 102957 "ODFF: FLOOR and CEILING should
work with a single argument."
Now OOo allows CEILING(N;Significance;Mode) and CEILING(N;Significance).
I have made some changes to allow CEILING(N) too. Thereby I noticed the
following problem:
OOo allows CEILING(N;;Mode) where the second parameter is missing. It
does this already in version OOo2.4.2
Presumably for Excel interoperability because there the functions have
only 2 parameters, but Excel allows CEILING(value,) and during import we
have to add the 3rd parameter to indicate the mode.
Problems see below.
But that is not allowed in spec (chapter 6.16.1).
We'll have to change the spec for interoperability, I created issue
http://tools.oasis-open.org/issues/browse/OFFICE-2006
Unfortunately I can not do anything in ScCeil, because
in this case nParamCount is already 3 and it seems that the missing
parameter is set to 0. Besides the fact, that a missing second parameter
is not allowed, the default for the second parameter has to be +1 or -1
depending on the sign of N.
After the 3rd argument was popped from the stack you can check with
IsMissing() whether the 2nd argument is of type svMissing, then pop the
value with GetDouble() as usual, and after having obtained the 1st
argument change the value of the 2nd argument accordingly to +1 or -1 if
it was missing.
Ah, IsMissing() is new to me. I can now set the missing second parameter
to +1 or -1 depending on N. That works at least for CEILING on which I'm
currently working.
But Excel Import and Export is confusing.
(1) Import:
In cases CEILING(value;) and CEILING(;value) the result in Excel2007 is
always 0.
On import of CEILING(value;) OOo does this:
.xls turn to CEILING(value;;1) and show cell content 0.
.xlsx turn to CEILING(value;;1) too, but show cell content Err:511
.xml turn to CEILING(value;) and show cell content Err:511
On import of CEILING(;value) OOo does this:
.xls turn to CEILING(;value;1) and show cell content 0.
.xlsx turn to CEILING(;value;1) too, but show cell content Err:511
.xml turn to CEILING(;value) and show cell content Err:511
The errors are shown left as if they are text. The cell content is only
recalculated, if you edit the cell, but that is another issue.
Because Excel returns 0 in this cases, I think an import to
CEILING(value;0) and CEILING(0;value) would be a better solution.
Although I not yet know how to do it.
(2) Export:
Currently OOo drops the third parameter. So the value differs from Excel
in the cases of (N<0 and Mode=0).
When now the second parameter it optional too, how should it be
exported? Using +1 gives the same result as in OOo for all cases but
(N<0 and Mode=0). So I would prefer that.
Gnumeric goes a total different way. It generates a new expression to
simulate the behavior with it's two parameter version of CEILING. You
can see it, when you open an ods-file in Gnumeric. For example
CEILING(A2;B2;C2) becomes
=if(0=C2;if(A2<0;floor(A2;B2);ceiling(A2;B2));ceiling(A2;B2))
What is the desired behavior on import from Excel and export to Excel?
kind regards
Regina
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org
For additional commands, e-mail: dev-h...@sc.openoffice.org
