Issue 104378

Created attachment 64282 [details]
Sample xls file

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!

Created attachment 64287 [details]
Pls. see comments from rainerbielefeld Fri Aug 21 06:34:25

Reassigned

Platform = PC and OS= Vista

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

I believe this is a duplicate.

*** This issue has been marked as a duplicate of 46511 ***

Closing a dupe.  Please track the other issue instead.