From: Daniel Rentz on
Hi Regina,

Regina Henschel schrieb:

> No, you cannot use xls, because the functions are not available in xls.

The functions can be written to the XLS format (Excel 2007 and 2010 do
that too). Internally, all new functions of Excel 2010 are stored as
"custom" functions similar to Basic macro calls, e.g.

Name1 = _xlfn.AGGREGATION

formula cell: =EXTERN.CALL(Name1,arg1,arg2,...)

So, once we support these functions, we can also save them to XLS. Of
course, Excel 2007 and earlier will not recognize them and show a
#NAME?, but Excel 2010 can.


Daniel

---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: Mike Scott on
Regina Henschel wrote:
.....
> I notice, that most commentators rail against Microsoft. But that does
> not help our Calc users, when they get such a document. If a user gets
> an xls or xlsx document, it is clear to him, that there might be some
> differences to ods. But if he gets an ods document, the user will be
> upset, if he cannot fully use it.
.....

Indeed, and he'll most likely blame OOo as the 'nearest possible
culprit'. Maybe if such a document is found, Calc should show a pop-up
saying the document is of MS origin and probably only fully compatible
with MS software (assuming that's an accurate assessment - I have to
take others' word for it!). At least the hapless user would have a
better idea of the source of any problem.

---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: Regina Henschel on
Hi Daniel, hi all,

Daniel Rentz schrieb:
> Hi Regina,
>
> Regina Henschel schrieb:
>
>> No, you cannot use xls, because the functions are not available in xls.
>
> The functions can be written to the XLS format (Excel 2007 and 2010 do
> that too). Internally, all new functions of Excel 2010 are stored as
> "custom" functions similar to Basic macro calls, e.g.
>
> Name1 = _xlfn.AGGREGATION
>
> formula cell: =EXTERN.CALL(Name1,arg1,arg2,...)
>
> So, once we support these functions, we can also save them to XLS. Of
> course, Excel 2007 and earlier will not recognize them and show a
> #NAME?, but Excel 2010 can.

I didn't know that before. But it is good, that we would be able to
write those functions in the xls-format. An adapted xls-export would
surely help users, who are forced to exchange documents with Excel users.

I notice, that most commentators rail against Microsoft. But that does
not help our Calc users, when they get such a document. If a user gets
an xls or xlsx document, it is clear to him, that there might be some
differences to ods. But if he gets an ods document, the user will be
upset, if he cannot fully use it.

It seems, that there is consensus to translate all Excel formulas to
OpenFormula, if it is directly possible.

But some questions remain:

(1) What shall we do, when saving such an Excel-ods document as Calc-ods
document? For example currently

<table:table-cell office:value-type="float"
office:value="1.7316533037537034"
table:formula="msoxl:=_xlfn.CONFIDENCE.T(0.05,3.7,20)"
table:style-name="ce1">
<text:p>1,731653304</text:p>
</table:table-cell>

in Excel-ods becomes

<table:table-cell table:formula="of:=_xlfn.confidence.t(0.05;3.7;20)"
office:value-type="float" office:value="0">
<text:p>#NAME?</text:p>
</table:table-cell>

when opening and then saving as Calc-ods document.

That seems questionable to me.
(a) xlfn.confidence.t belongs surely not to OpenFormula and likely will
never do. So the namespace "of" looks wrong to me.
(b) The original value is totally lost.


(2) Shall we support new functions at all, if they have no counter part
in OpenFormula yet? Implement them and using them with msoxl namespace?
Or implement a compatible own function, something like
org.openoffice.confidence.t?

(3) Some functions, for example CEILING.PRECISE(number;significance),
are partly compatible. We would get the same result, if we translate it
to CEILING(number; SIGN(number)*ABS(significance)). Gnumeric had used
such translations in former versions. I don't like doing so
automatically. I would prefer to set up a Wiki page, which explains to
the users, how they can translate the Excel solutions to Calc formulas
and leave it to the user to change the imported formulas manually.

(4) What do you think about my proposal to not convert anything, when
opening an Excel-ods document _readonly_, but show the contained values?
As far as I know, there exists no ods-viewer and therefore a Calc user
is currently not able to see, which values the Excel-ods document has
calculated.

(5) Should there be a warning, when a user opens an Excel-ods document,
which is not fully compatible to Calc-ods?

kind regards
Regina

---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org

From: Barbara Duprey on
Regina Henschel wrote:
> Hi Daniel, hi all,
> <snip>
> (3) Some functions, for example CEILING.PRECISE(number;significance),
> are partly compatible. We would get the same result, if we translate
> it to CEILING(number; SIGN(number)*ABS(significance)). Gnumeric had
> used such translations in former versions. I don't like doing so
> automatically. I would prefer to set up a Wiki page, which explains to
> the users, how they can translate the Excel solutions to Calc formulas
> and leave it to the user to change the imported formulas manually.

Might it be possible to treat it like a spellcheck error, identifying
the problem but supplying the equivalent formulation for acceptance with
manual action, and perhaps the converted value as another possibility?

>
> (4) What do you think about my proposal to not convert anything, when
> opening an Excel-ods document _readonly_, but show the contained
> values? As far as I know, there exists no ods-viewer and therefore a
> Calc user is currently not able to see, which values the Excel-ods
> document has calculated.

If the contained value is available as a "spellcheck" type option, it
could be used here, too. They wouldn't have to accept the value, but
they could see it.

>
> (5) Should there be a warning, when a user opens an Excel-ods
> document, which is not fully compatible to Calc-ods?

I think so -- something like "This spreadsheet was prepared by an
application that does not conform to the [projected] Open Formula
standard. Some issues with formulas are likely."

All this is just my half-cent's worth (I certainly won't claim two
cents, I'm a very low-level user of spreadsheets!). But anything we can
reasonably do to improve interoperability, while making it clear where
the problem actually lies, seems like a "good thing" to me.

>
> kind regards
> Regina



---------------------------------------------------------------------
To unsubscribe, e-mail: discuss-unsubscribe(a)openoffice.org
For additional commands, e-mail: discuss-help(a)openoffice.org