To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=5658
------- Additional comments from mux2...@openoffice.org Fri May 22 10:05:03 +0000 2009 ------- How about implementing the following things: 1. If the user enters a formula that contains arithmetic on text cells, then add explicit conversion functions to it. I.e. the user enters "A1+10" and A1 contains text, then when the user submits the formula, write "convertonumber(A1,...) + 10" into the cell, where converttonumber is a conversion function (I don't what the actual function in Calc is called, assuming it exists) that accepts a parameter that specifies the number format. The number format parameter would be filled with the format appropriate for the current locale. That way, we fix the user's mistake and create a portable formula that will work consistently even when the spreadsheet is ported to a different locale (because the conversion is completely explicit). It may be useful to introduce an informational popup message such as "The formula you have just entered tries to perform arithmetic computations on string cells. Appropriate string -> number conversion functions have been automatically added to the formula". I would advise against making this a "Do you want this?" choice dialog, because there is no sensible reason for the user to reject this transformation. 2. When opening/importing, fix all formulas with string arithmetic according to 1. Since we do not usually know the creating environment (creating program, locale,...) and as such cannot with 100% accuracy predict the proper number format to use for conversions, a dialog such as the following would be nice: "The document you are trying to open contains formulas that try to do arithmetic computations with strings. OpenOffice.org can attempt to guess the proper text -> number conversion parameters. However, this may lead to incorrect results in affected cells. If you choose 'No' below, then all affected cells will be flagged with an error and you will have to fix them manually. Do you want OOo to attempt to fix cells automaticall? Yes/No". If the user chooses "Yes", introduce conversion functions as in 1, making a best effort based on whatever information we have about the document to guess the conversion parameters. If the user chooses "No", treat the affected formulas as if they had syntax errors. -------- This approach will - prevent the user from creating documents with implicit string arithmetic (ambiguous or undefined behaviour) - allow the user to open/import documents based on implicit string conversions and have (most of the time) a working spreadsheet (that no longer contains implicit conversions) - allow the user to open/import documents based on implicit string conversions and go the safe route of flagging all problematic cells as error for manual inspection and fixing. --------------------------------------------------------------------- Please do not reply to this automatically generated notification from Issue Tracker. Please log onto the website and enter your comments. http://qa.openoffice.org/issue_handling/project_issues.html#notification --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@sc.openoffice.org For additional commands, e-mail: issues-h...@sc.openoffice.org --------------------------------------------------------------------- To unsubscribe, e-mail: allbugs-unsubscr...@openoffice.org For additional commands, e-mail: allbugs-h...@openoffice.org