Red Hat Bugzilla – Bug 419181
Testcase for OpenOffice.org Calc/Excel
Last modified: 2008-01-14 12:16:10 EST
Hello, Description of problem: The attached spreadsheet file (.xls) behaves differently if it is open in Microsoft Excel 2003 or in OpenOffice.org Calc 2.3.1. (At least) totals at the bottom of column I are different. Version-Release number of selected component (if applicable): openoffice.org-calc-2.3.0-6.7.fc8 How reproducible: Always. Steps to Reproduce: 1. Please open the attached .xls file in Excel 2003 and note the bottom totals in column I. 2.Please open the attached .xls file in OpenOffice.org Calc and note the (now different) bottom totals in column I. Actual results: Recalculated formulas give different mathematical answers. Expected results: Recalculated formulas should give the same mathematical answer. Additional info: Please don't shoot me ;-) for the corectness of this file; it was not created by me. The file is provided „as it is”, without any assumption of its corectness, just to serve as a testcase. It is one of the many files an average user creates in his/hers day-by-day work in a large office.
Created attachment 283591 [details] Microsoft Excel file to test.
Hello again, As far as I can figure it out, if one tries to export this Excel file as .ods (either via the Sun plugin for Microsoft Office or by directly opening the file in Calc), numbers get converted to text: an apostrophe is added in front of each digit in columns E and F. Of course, further calculations cannot be performed. I don't know if the above is the only issue that gets an incorrect mathematical calculation. In the mean time, I found no way to (automatically) remove the apostophes or to format the cells in columns E and F as numbers. Can you please provide a way to do this or point to the relevant documentation ? Such an issue is a *serious showstopper* for a less-skilled IT person, let's say an accountant. Regards, Răzvan
I think the correct behaviour of Calc should be to issue a warning when some formula tries to perform calculations with numerals that are treated as text. In Excel 2003's help I've identified a quick procedure for converting a range of cells that are treated as text back to numerals (by multiplying them with 1, using Paste Special from the Edit menu). Regards, Răzvan
Hello, This seems to be a solution, but the problem remains *very* annoying: http://www.ambience.sk/open-office-text-number-format-conversion Besides the steps given at the link above, it requires selecting the affected cells and doing a Format -> Default Formatting; if one doesn't do that, the regular expression replacement won't work. Regards, Răzvan
Indeed (long delay for me to find a copy of excel), excel itself warns about this with little warnings symbols, but it does work. What's strange to me is that it works with text formatted cells of more than one character, but not with text formatted cells of one character. Let's check with upstream, there's more expertise there wrt. calc issues and it affects all versions, not just the fedora one. http://www.openoffice.org/issues/show_bug.cgi?id=85242