Moin,
Regina Henschel schrieb:
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
Well that indicates that the formula parser in Calc does not handle the
missing parameter correctly and does not create the svMissing formula
token. The binary filter (.xls format) inserts that token for its own.
You may also try the Excel 2007 .xlsb format, that should work too (it
does build the token array for its own too).
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 same I think. XML based filters (.ods, .xlsx) use the internal
formula parser that "forgets" to insert the svMissing token. I could fix
that for the .xlsx format when postprocessing the formula, but that
would not be of any help for the .ods import.
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?
In my eyes, binary filters work as expected, and XML based filters need
to be adjusted.
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscr...@sc.openoffice.org
For additional commands, e-mail: dev-h...@sc.openoffice.org
