Issue 104378 - Missmatch in decimal places when loading xls file
Summary: Missmatch in decimal places when loading xls file
Status: CLOSED DUPLICATE of issue 46511
Alias: None
Product: Calc
Classification: Application
Component: open-import (show other issues)
Version: OOO310m9
Hardware: PC Windows Vista
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Keywords: needhelp, oooqa
Depends on:
Reported: 2009-08-20 20:42 UTC by cactuscomputing
Modified: 2009-10-01 05:38 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Sample xls file (8.55 KB, application/
2009-08-20 20:43 UTC, cactuscomputing
no flags Details
Pls. see comments from rainerbielefeld Fri Aug 21 06:34:25 (25.75 KB, application/x-compressed)
2009-08-21 07:35 UTC, Rainer Bielefeld
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
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 ' 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 ""
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 "", 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?!

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 '
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
' 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 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 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

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.


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.