Iv'e just received a spreadsheet from a user of excel (2010) and it looked like LibreOffice couldn't read parts of it. It was filled with ### and #VALUE! where I expected values. Turns out that the problem is careless users and different ways of treating cell contents. * In LO if a cell is formatted as text and contains a number it will cause a failure in calculations * In Excel (2010) if a cell is formatted as text and contains a number the formular will treat it as a number, despite the formatting saying to expect text. Excel puts a little notification next to the cell offering a message that the formatting might be wrong and offering to change the text to a number. I think LO should do the same - if a formula finds a cell formatted as text it should check to see if it contains a number, if it does it should be treated as a number (and perhaps offer some kind of warning) To solve this in a poorly constructed spreadsheet the cells have to be selected and the formatting changed to 'Number'. This changes cells with a number (1.5) to cells formatted for numbers but containing strings ('1.5). These must be manually retyped as numbers. Then the sheet should work if that was the only problem. This is actually quite important because for a user switching from Excel, or receiving an excel file this will look like a bug. I have given up on many excel spreadsheets in the past which I now suspect have this basic error in their formatting.
Calc calculates with textual numbers if they represent integer values or ISO 8601 date values. It refuses to calculate with textual numbers if they contain separator characters, e.g. seem to be floating point values, as those are locale dependent and there is no way of telling what would be correct. Also in Excel you may get calculation errors if, for example, in an en_US locale the textual number is 1.000 and the document is loaded in an de_DE locale, as there the ',' comma is used as a decimal separator and the '.' is the group separator. In older Excel versions the results even differed and gave 1 in en_US and 1000 in de_DE ... So instead of producing different results in different locales, Calc is stricter in what it accepts in all locales.
Thanks for your explanation Eike. If this is the way Calc users want it then that's fine for ODS files. But ... When we open an excel file we expect it to work - so we need to find a way to make this just work. - If we need to ask the user for the locale of the document let's do that - If we need to add comments on ambiguous cells let's do that
I'm working on an option to select between different string conversion models during calculation.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c52f3ea0eb327343b1945290c43d3b66f546dfe9 user selectable string conversion models, related fdo#37132 fdo#74622 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=af88ab2ee9167279cb70a577fb399d23f2ce136f unit test for string conversion feature, related fdo#37132 fdo#74622 The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bc43fc2fb27eab02a949832626ce1a9dc380ee6b added Formula/Syntax/StringConversion config item, fdo#37132 related The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.