Issue 46511

The precise (though tedious) workaround tweak would be to have a format that
works in harmony with a column width to show just as many digits as will fit in
the column.

Column width fits 5 characters -> Format 0.###
Column width fits 6  characters -> Format 0.####
Column width fits 10 characters -> Format 0.########

Tom Haws

some for the requirements team

*** Issue 51278 has been marked as a duplicate of this issue. ***

I've been bitten by this one. Playing with Excel XP, I notice that it will widen
the column to make room for more decimals if it needs them. If I then narrow the
column, it will flip to scientific to indicate the number, round if needed, and
not go to '##' until the column is too narrow to show any approximation of the
number.

This is particularly nasty when the number is small. Enter .001, and OO will
display '0', which is just wrong, a relative error of 100%, an outright bug IMO.
Display '#'s if the number can't be displayed with accuracy and at least the
user knows something is amiss (very large numbers do this), but don't silently
round away the entire number and show zero.

Since this potentially effects every numeric cell with default formatting in
imported spreadsheets, perhaps this merits a "ms_compatibility" keyword?

Actually, dridgway's comments are a separate issue of compatibility.  This issue
is about the inability of OOo to show more than 2 decimal places in General format.

An enhancement of course would be to implement all the features dridgway
suggests.  However, I dispute drigway's assertion that having 123.45678 shrink
to 123.46 if a cell gets too narrow is deficient behavior.

I think the issue 36393 is a bit similiar to this one. What's more important i 
found a following comment made by Fst in it:

"personally I would like to close this Issue as wontfix because our Standard
numberformat just uses the appropriate decimal places entered. So integers are
shown without decimals and results of a calculation resulting in numbers smaller
than zero are displayed with an accurate number of decimals. The user can
explicitly set the format to integer."

But, the amount of decimal places is not appropriate. It is always equal 2. So, 
either
- the Standard format was changed from 1.0.3 to work otherwise
- the Fst was wrong
- the Standard format works incorrectly, now.

-> hawstrom:

"drigway's assertion that having 123.45678 shrink
to 123.46 if a cell gets too narrow is deficient behavior".

I didn't write this. See my comments above for what I think about this issue.

I believe that this is a *SIGNIFICANT* compatibility issue which OOo needs to
address if it wants to be considered a viable Excel alternative.

Consider the following scenario: User 1 creates a small spreadsheet in Excel
which contains Number->General formatted fields like 123.4567, 1.2345, etc. User
2 opens this file in Calc and copy/pastes values from the cells into another
application. Due to this compatibility issue in OO, all of the transferred
values are rounded to 2 decimal places which might affect future analysis.  This
behavioural difference resulting in a truncation of precision is exactly the
type of thing that could get OO banned from use in some shops.

The Excel behaviour isn't perfect, but since it attempts to fill the cell the
user at least has a visual clue that the value may be rounded due to cell width.
In comparison, the OO behaviour lulls the user into thinking that the entire
number is being displayed because there's plenty of whitespace left in the cell.

This issue is really serious if openoffice calc wants to become a replace of MS
excel (or other spreadsheet program).
Often times when we need spread sheet for small numbers' calculation, openoffice
will always display the result as ZERO, which is WRONG.
The workaround is really annoyed also, which you have to change the entire
spread sheet to scientific notation format.
In this case some general numbers just become ugly.
Please get this issue fixed in the next version of openoffice calc.

Created attachment 65071 [details]
patch to implement automatic decimal adjustment

just stumbled across this, and I just happened to have written a patch to
implement a part of what's being asked here.

What is not implemented is the automatic adjustment of decimals per cell size.

Created attachment 65072 [details]
forgot there was another patch, for svtools

*** Issue 104378 has been marked as a duplicate of this issue. ***

I had posted this in another bug report, but it got closed as a duplicate of
this issue. So here it goes:

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

It's far worse than just different displays.  it also means that calc fails in
its ability to be a generic "csv file editor".

an Excel user sends a csv file to a Calc user.  The calc user opens it.  Makes
one change to a cell, saves the file (as csv), and sends it back.

now all hell breaks loose.  after the decimal point, all digits after the
hundredths have been deleted.  that means that everything small (e.g., quoted in
percent) has become junk now.

the excel user tells everyone what a piece of s... calc is.  10 other potential
switchers now decide against it.

I just do not understand why this has not been fixed after so many years.  I now
need to use other spreadsheets.  Pity.  Calc is so nice in so many other ways. 

/iaw

>I just do not understand why this has not been fixed after so many years.

I can at least answer this part.  We have quite literally thousands of bug
reports where a comment like this is thrown in.  Divide that by a hand full of
Calc developers and you get the picture. ;-)

And quite frankly, I didn't even know about this up until someone in my
organization asked me to look into this last week.

my apologies.  I sometimes think of openoffice as such a marquis open source
project that I assumed it was well taken care of.  time to put a "donate money
to the project" button more prominently at the program startup.

may I also suggest then that "save as .csv" by default save in full precision,
so that a "save as csv", "close", "open" cycle returns the same spreadsheet? 
the current behavior (truncate at visible) could be a (non-default) option.

for me at least, this would be 90% of the way there.

/iaw

ivowel, the issue is much greater than csv formats...I have only been able to
test this with Excel 2007 files, however I would bet that this happens with any
spreadsheet file format that OpenOffice supports. The issue does not seem to be
simply an "xyz format files do this" but an "OpenOffice does this with all file
formats, as a rule".

On a side note, I am glad to see that so many users like you pay attention to
these bug reports, and that the OO.o team seems to be on top of managing and
responding to comments posted here. I first posted my comments last night, and
by the time I woke up someone had addressed my comment, referred me to this
issue number, and has now responded to that new posting.

To kohei, I hear you concerning the lack of developers. I wish I could
contribute code, and am currently making a real effort to learn how to code so
that someday I might be able to share the burden and truly contribute to the
open source community.

Nightcrawler27

The default options for CSV export are completely separate from the display, see
issue 68636.

nn, thank you for clarifying. That makes the issue even more interesting, and it
is curious that the issue I originally posted a comment on was closed as a
duplicate of this issue...please see the following as a reference:

http://www.openoffice.org/issues/show_bug.cgi?id=104378

I would like to give a try on kohei's patch, but would I ask where can I get the
source codes for patching and compiling?

A short glance reveals that this would work only for the default locale:
In ScDrawStringsVars::SetText()
    if (static_cast<NfIndexTableOffset>(nFormat) == NF_NUMBER_STANDARD)

Casting the format index to NfIndexTableOffset is wrong anyway, if at
all this should be

    NfIndexTableOffset nOffset = pFormatter->GetIndexTableOffset( nFormat );

instead. However, as the General format is always the first format for
a given locale, the shortcut to determine whether the format index is
the one for the General format of any locale would be

    if (nFormat % SV_COUNTRY_LANGUAGE_OFFSET == 0)


Apart from that I have some nitpicks:

1. The approach overrides the "Decimal places" Calc option.

2. The display string may easily get a length that makes it not fit into
   the current column width, resulting in ### being displayed. Users may
   be confused by this. Excel automatically widens the column as long as
   it has the default width, which may or may not be regarded as
   favorable though. When changing the column width in Excel it adapts
   the display string to a precision representable in the width.

3. Existing OOo documents will have their displayed values changed. For
   fractional formula results this may be annoying, especially in
   financial calculations that usually display 2 decimals.

4. If the "Precision as shown" Calc option is enabled, displayed and
   calculated values will differ as the calculation still will use the
   lower document standard precision.

#4 clearly is a no-no but could be easily adapted in
ScDocument::RoundValueAsShown()

#3 may be circumvented by still using ScDocOptions::GetStdPrecision()
and for newly created documents additionally have a document option that
disables this standard precision to create full precision display
strings. UI and stored documents will require this extra flag.

The GetInputLineString() approach should be changed back for that,
SvNumberFormatter::GetOutputString() already uses the precision set from
the document options. This would also resolve #1 again. Internally we
could use a special value 0xffff to flag absence of standard precision.
GetOutputString() already does similar with an arbitrary value of 300 to
switch to ImpGetOutputInputLine(), would simply have to be adapted to
the new value.

#2 may need discussion with UX whether the simple approach is
acceptable. Personally I wouldn't mind if ### were displayed instead of
having to automatically widen the column, but ...

If the precision displayed would have to change with the column width,
interaction with the "precision as shown" option would have to be
considered. This would unnecessarily complicate a solution and I would
opt for a later resolution if desired.

Hi, just wanted to see if there was anything new going on with this issue...


Nightcrawler27

Well, as er articulated in his comment above, this needs to be brought to the
user experience folks and have them make the call.  Meanwhile, I will fix item
1, which is easy to fix.  Item 2 can be solved by implementing the "show as many
decimals as the column width allows" functionality.  The issue with "Decimal
places" option can be solved by perhaps introducing an 'Auto' option there
(again, this needs UX's blessing).

If the column width doesn't influence the display, isn't the patch rather
pointless? You could just configure more "Decimal places" instead.

Created attachment 65347 [details]
patch to fix the offset lookup (but nothing more)

@nn: heh, harsh word, but it's not "pointless".  Each cell may have different
decimals and what the patch tries to do is to automatically assess the
appropriate number of decimals given the cell value.  It's a little more
sophisticated than the current Decimal places option.

But that's also what the "Decimal places" option does (in the options dialog,
not the number format dialog). It doesn't add zeroes.

Ah, in that case, we could implement this by treating the automatic decimal
placement as equivalent of the Decimal places being "infinity".  That would make
the implementation a bit easier.

[picked up Kohei's request on the UX list for discussion]

Created attachment 65372 [details]
new Calculate options page

This is a screenshot of the new Calculate options page.  It sports a new check
box "Limit decimals for general number format", and the existing Decimal places
spin box moved down to be aligned with this new check box option.

When this check box is checked, the spin control is enabled and you can specify
decimal places (as you can currently).  When this box is *not* checked, the spin
control becomes disabled, and Calc applies the maximum allowable decimal places
to display cell values with the 'General' format.

I've also managed to implement automatic decimal placement by available column
width.  So, that part is forthcoming as well.

I propose this new check box be checked by default, and the decimal places value
be set to 2.  This is the current default behavior.  When the user changes this
setting, the setting will be stored in the user configuration.

>I propose this new check box be checked by default, and the decimal places value
be set to 2.

Actually I take that back.  IMO by default the option should be off, so that
Calc uses all available decimals given the column width and the cell value.  To
me that behavior seems more natural, than always limiting decimals to 2 *by
default*.

For the existing users with existing configuration, the old value of 2 decimal
places will be set anyway, so there should be no disruption in case they upgrade
to the new version with this enhancement in.

*** Issue 38741 has been marked as a duplicate of this issue. ***

*** Issue 76515 has been marked as a duplicate of this issue. ***

Grabbin'it.

started.

This issue is now being working on in koheiautodecimal cws.

Great! I'm thrilled to see that this issue is being worked, thanks for everyone
involved.

Hi. I fixed this in KSpread by connecting the formula in context.xml;

<table:table-cell table:style-name="ce1" table:formula="of:=ROUNDDOWN(123.456789;3)"
office:value-type="float" office:value="123.456">

to following style in styles.xml;

<number:number-style style:name="N116">
<number:number number:decimal-places="3" />
</number:number-style>

So, using the number:decimal-places attribute.

@dipesh: That is unrelated to this issue. You just assigned a fixed decimals
number format.

@er
Is it? The issue here is that the second argument in ROUND, ROUNDUP and ROUNDDOWN does
differ in Excel vs OO.org/KSpread.

In excel something like ROUNDUP(1.2345,3) will result in "1.235" while in Calc and KSpread
everything >=2 is always "1.23".

So, to earn the same result the like excel if the second argument is n then the number of decimal
places needs to be n.

I guess the real problem here is to keep backward compatibility...

fixed in koheiautodecimal cws.

Spec is being worked on here:
http://wiki.services.openoffice.org/wiki/Calc/Features/Automatic_decimal_place_adjustment

re-assigning to oc for qa verification.

verified in internal build cws_koheiautodecimal