There are several matters here. First, the rules for OpenFormula apply to literals as argument and to references to cells where the value is of type text and the expected type is Number.
It does not apply to how *entered* values are converted. In fact, there is no guidance at all about how *entered* values are converted. Likewise, how cell values are formatted for the spreadsheet's presentation, for export in CSV, etc., are independent of these rules in OpenFormula. It strikes me that entry of values through an user interface is the time to deal with these issues and that may resolve most cases. For use of literals in formulas, there are ways for users to ensure that the desired interpretation is made by explicit conversions. The same conditions apply when a cell value is text and it is intended to be used as a number. Safe practice could be encouraged even if a given product provides the intended result, since there is no assurance of consistent treatment in an interchange situation. (Multi-cell operandsare more complicated, when some cell entries in a range have values of different type.) Without too much thought, I image three things that can be done, along with user guidance: 1. The rules for conversion of entered values based on the specified cell format need to be clear, and that might also include understanding the cell display formatting in properly handling the cell input conversion. Since the storage of Number types is canonical and not subject to locale considerations, this can isolate much of the problem. The same applies to export into non-OpenFormula forms, such as CSV. 2. Users need to be cautioned about what happens when cells and literals are of different types than the expected values. It might also be useful for there to be warnings when text for Number expected arises. There can also be user guidance on how to detect and prevent pathological cases. I recommend making the default behavior consistent with widespread practice, including consideration of the importance of Excel interoperability. If there seems to be no simple way out of it, I think Error values are the appropriate response, so that an user is given the opportunity to provide an explicit resolution rather than contend with silent discrepancies in their own use or when their spreadsheet is interchanged. 3. There are probably additional functions that could be introduced into OpenFormula, even as implementation-specified extensions (in accordance with provisions for that), to provide functions that will pass Numbers intact and will apply some formatting rule or function when text is found instead. (The conversion of cells of explicit date and time types is already straightforward although it might need review.) A few new functions may be sufficient to achieve complete, straightforward control on behalf of users, including forcing of an Error result. The extensions could be agreed among the different implementations of OpenFormula, including in Apache OpenOffice, LibreOffice, Gnumeric, Excel 2013, Google Docs, etc. (But first, it should be verified whether there are means enough already.) - Dennis -----Original Message----- From: Lei Wang [mailto:[email protected]] Sent: Thursday, July 26, 2012 22:33 To: [email protected]; [email protected] Subject: [DISCUSS][CALC] String content to numeric value In ODFF standard 6.3.5 Conversion to Number, If the expected type is Number, then if value is of type: ● Text: The specific conversion is implementation-defined; an evaluator may return 0, an Error value, or the results of its attempt to convert the Text value to a Number (and fall back to 0 or Error if it fails to do so). Evaluators may apply VALUE() or some other function to do this conversion, should they choose to do so. Conversion depends on the actual locale the application runs in, especially if group or decimal separators are involved. It leave the implementation to decide whether to convert a text value to a number, in which extent to convert a text value to a number. AOO now only support convert string content to numeric, which is integer numbers including exponent, and ISO 8601 dates and times in their extended formats with separators. This cause issue 118942( https://issues.apache.org/ooo/show_bug.cgi?id=118942). In my point of view it is better to support more scenarioes to convert a text value to a number, such as decimal. Though different locale has different decimal separator, AOO can support decimal separator for current locale and standard decimal separator, ".". It will be more complicated for date format. What's your suggestion?
