Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Missmatch in decimal places when loading xls file|
|Status:||CLOSED DUPLICATE||QA Contact:||issues@sc <issues>|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
Description cactuscomputing 2009-08-20 20:42:23 UTC
In the attached file cell A:6 returns the cosine of 5. When viewing under MS Office 2007 the cell value is displayed as 0.283662185, but in OO its 0.28. I know that the option 'OpenOffice.org Calc->Calculate->Decimal Places controls' the number of decimal places that are displayed. It would however be nice if on reading in an MS document that the decimal place settings in that document are honored. Note we are importing documents via an automated process so the appropriateness of the decimal places cannot be assessed. I suspect you'll say its some miss-match between ODF and MS formats, but I'm hoping for the best!
Comment 1 cactuscomputing 2009-08-20 20:43:04 UTC
Created attachment 64282 [details] Sample xls file
Comment 2 Rainer Bielefeld 2009-08-21 07:34:25 UTC
I checked "Terms.xlsx" with "Ooo 3.1.0 WIN XP multilingual version German UI activated [OOO310m11 (Build 9399)]" and can confirm the reported effect. My OOo showed 2 decimals in A6, to decimals is setting for most of my CALC documents (and may be OOo CALC default?) Excel Viewer 12.0.6334.500 MSO 12.0.6333.5000 shows 9 decimals. It seems that OOo uses the settings that have been inserted in Menu 'Tools -> Options -> OOo Calc -> Calculate -> Decimal places' the last time, may be because there is no other information in "Terms.xlsx"? Steps to reproduce: 0. Dwonload and unpack "TestKit.zip" 1. Open "master3.ods" 2. In menu 'Tools -> Options -> OOo Calc -> Calculate -> Decimal places' modify entry from 3 to 4 3. Save document 4. Click "Link" to open "Terms1.xlsx" A6 will be shown with 4 decimals 11. Open "master7.ods" 12. In menu 'Tools -> Options -> OOo Calc -> Calculate -> Decimal places' modify entry from 7 to 6 13. Save document 14. Click "Link" to open "Terms6.xlsx" A6 will be shown with 6 decimals My knowledge is too poor to decide whether "Terms.xlsx" contains useful information concerning desired decimal places for cell A6, which should be used by OOo, or whether the current way OOo decides number of decimal places is the only way that can be handled (OOo can't know anything concerning an unknown user's EXCEL settings). Yo can see this using my "TestKit.zip", what contains 2 OOo documents with links to "Terms1.xlsx" and "Terms1.xlsx", what are identical copies of "Terms.xlsx". If No. of decimal places is important, user should have used cell formattings?! @sgreenhow: Please contribute information concerning your Platformand OS!
Comment 3 Rainer Bielefeld 2009-08-21 07:35:51 UTC
Created attachment 64287 [details] Pls. see comments from rainerbielefeld Fri Aug 21 06:34:25
Comment 4 Rainer Bielefeld 2009-08-21 07:36:35 UTC
Comment 5 cactuscomputing 2009-08-24 15:52:24 UTC
Platform = PC and OS= Vista
Comment 6 nightcrawler27 2009-10-01 05:23:25 UTC
Greetings all, I did not want to open a new bug report for what appears to be a similar issue to this one. I too am having problems with decimal point precision when opening Excel 2007 spreadsheets in OpenOffice. I have concluded that if the decimal precision has not been explicitly set on each cell containing a number when the number was entered in Excel, then OO will only display decimals based on the setting in 'OpenOffice.org Calc->Calculate->Decimal Places controls'. Note that in OpenOffice, if I click on the cell that contains the truncated number, the actual number appears in the formula bar at the top of the window. Apparently if an Excel 2007 user enters a number and does not know enough to set cell formatting, especially to change the decimal places in the numerical cell format settings, Excel will display the number as entered anyway, overriding the default cell format and application settings. I have confirmed this by taking the same Excel file with the cell format problems, opening it in 2007 and OO, and then comparing the cell format settings. OO will override the setting in 'OpenOffice.org Calc->Calculate->Decimal Places controls' if and only if the cell is specifically formatted to use more decimal places. Otherwise it will honor the number of decimal places as specified in the previously mentioned configuration setting. To simplify: Assume both Excel 2007 and OO.o Calc 3.0.1 are installed with their default configuration (which both by default are set to display only two decimal places): If user enters number 1.234 into Excel 2007 and formats the cell appropriately: Open file in Excel 2007 Result: Cell displays 1.234, formula bar displays 1.234 (overriding default) Open file in Calc Result: Cell displays 1.234, formula bar displays 1.234 (overriding default) If user enters number 1.234 into Excel 2007 and does not apply any formatting to the cell: Open file in Excel 2007 Result: Cell displays 1.234, formula bar displays 1.234 (Overriding default and cell format setting) Open file in Calc Result: Cell displays 1.23, formula bar displays 1.234 (Not overriding default, but overriding cell format setting) It is a very interesting problem. I will be honest and say that the user should realize that they need to format their cells to display their data accurately. However I am also a realist and understand not everybody is a computer wiz and may not know all the ins and out of Excel. Ultimately, what will happen is two users that are looking at the same spreadsheet under different applications will see different figures, and neither will understand why they are different. Since OpenOffice is the underdog in the world of Office productivity suites, the blame will be placed on OpenOffice.org. If the OO.o user is running Linux or Solaris, some blame will probably be flung in that direction as well. My temporary solution: Increase the decimal places setting in the OO.o Calc application to the maximum (20) so it catches any possible issue with unformatted cells from a 2007 Excel spreadsheet. My recommended/requested solution: I request that OpenOffice.org Calc be changed so that if a numerical value is stored in a cell and the cell's number format properties have not been changed from the "General" format, that the exact value of the number will be displayed in the cell regardless of cell or application configuration. Rationale: If a user has changed the cell format from "General" to anything else other than "General", then the user has made a conscious effort to specify the format of the number and it should be displayed as specified. By keeping the number format at "General", the user implies that he/she wants the number entered in the cell to be displayed as entered. I hope the OO.o developers concur with my assessment of the situation and accept my requested change. It is a sensible, logical request that will make the product more user friendly and remove something that causes the perception of incompatibility with Microsoft Office documents. Respectfully, Nightcrawler27
Comment 7 kyoshida 2009-10-01 05:34:33 UTC
I believe this is a duplicate. *** This issue has been marked as a duplicate of 46511 ***
Comment 8 kyoshida 2009-10-01 05:38:42 UTC
Closing a dupe. Please track the other issue instead.