Issue 46511 - Calc/Spreadsheet general format not displaying as Qpro, Excel, Lotus
Summary: Calc/Spreadsheet general format not displaying as Qpro, Excel, Lotus
Status: CLOSED FIXED
Alias: None
Product: Calc
Classification: Application
Component: formatting (show other issues)
Version: OOo 1.1.3
Hardware: All All
: P3 Trivial with 18 votes (vote)
Target Milestone: ---
Assignee: oc
QA Contact: issues@sc
URL: http://www.oooforum.org/forum/viewtop...
Keywords:
: 38741 51278 76515 104378 (view as issue list)
Depends on:
Blocks:
 
Reported: 2005-04-01 17:10 UTC by hawstom
Modified: 2017-05-20 10:33 UTC (History)
5 users (show)

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


Attachments
patch to implement automatic decimal adjustment (3.95 KB, patch)
2009-10-01 04:32 UTC, kyoshida
no flags Details | Diff
forgot there was another patch, for svtools (4.39 KB, patch)
2009-10-01 04:36 UTC, kyoshida
no flags Details | Diff
patch to fix the offset lookup (but nothing more) (4.13 KB, patch)
2009-10-13 16:56 UTC, kyoshida
no flags Details | Diff
new Calculate options page (56.26 KB, image/png)
2009-10-15 01:59 UTC, kyoshida
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description hawstom 2005-04-01 17:10:43 UTC
Summary
The Spreadsheet Format -> Cells -> Number -> Format "General" does not display
numbers in the customary format historically established by Lotus 1-2-3, Quattro
Pro, and Excel.  Instead, it shows them in "2 decimals max" format:

123 shows as 123
123.4 shows as 123.1
123.45 shows as 123.45
123.456 shows as 123.46
123.456789 shows as 123.46

Workaround
The workaround for this bug is to enter a user-defined format as
0.#############.  However, this workaround has also a flaw, in that excessive #
signs cause a cell width overflow:

1 shows as 1
123.456 shows as 123.456 (assuming cell is wide enough)
123.456789 shows as ### (assuming cell is too narrow for the full number).  But
in historic spreadsheets, in a narrow cell, 123.456789 shows as 123.4567
(whatever the cell can display.

History of discussion
In the forum topic linked and given again here
http://www.oooforum.org/forum/viewtopic.phtml?t=15887

Steps
Simply open a new Spreadsheet document and type 123.456789 into the first cell.
Calc will display 123.46.  Then open any other major spreadsheet and do the same
thing for comparison.
Comment 1 hawstom 2005-04-01 17:17:06 UTC
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
Comment 2 frank 2005-04-14 15:55:47 UTC
some for the requirements team
Comment 3 frank 2005-07-08 13:21:49 UTC
*** Issue 51278 has been marked as a duplicate of this issue. ***
Comment 4 dridgway 2005-11-25 22:07:19 UTC
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?
Comment 5 hawstom 2006-04-15 04:34:41 UTC
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.
Comment 6 forsoft 2007-04-21 10:31:11 UTC
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.
Comment 7 dridgway 2007-04-22 00:34:05 UTC
-> 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.
Comment 8 spwalmsley 2008-09-30 22:23:16 UTC
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.
Comment 9 charlie1kimo 2009-10-01 03:14:24 UTC
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.
Comment 10 kyoshida 2009-10-01 04:32:58 UTC
Created attachment 65071 [details]
patch to implement automatic decimal adjustment
Comment 11 kyoshida 2009-10-01 04:34:47 UTC
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.
Comment 12 kyoshida 2009-10-01 04:36:23 UTC
Created attachment 65072 [details]
forgot there was another patch, for svtools
Comment 13 kyoshida 2009-10-01 05:34:31 UTC
*** Issue 104378 has been marked as a duplicate of this issue. ***
Comment 14 nightcrawler27 2009-10-01 11:39:49 UTC
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
Comment 15 ivowel 2009-10-01 13:29:11 UTC
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
Comment 16 kyoshida 2009-10-01 13:54:49 UTC
>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.
Comment 17 ivowel 2009-10-01 14:01:43 UTC
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
Comment 18 nightcrawler27 2009-10-01 14:17:13 UTC
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
Comment 19 niklas.nebel 2009-10-01 14:22:47 UTC
The default options for CSV export are completely separate from the display, see
issue 68636.
Comment 20 nightcrawler27 2009-10-01 14:35:22 UTC
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
Comment 21 charlie1kimo 2009-10-01 20:53:48 UTC
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?
Comment 22 ooo 2009-10-01 21:25:55 UTC
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.
Comment 23 nightcrawler27 2009-10-13 14:41:51 UTC
Hi, just wanted to see if there was anything new going on with this issue...


Nightcrawler27
Comment 24 kyoshida 2009-10-13 16:04:45 UTC
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).
Comment 25 niklas.nebel 2009-10-13 16:55:17 UTC
If the column width doesn't influence the display, isn't the patch rather
pointless? You could just configure more "Decimal places" instead.
Comment 26 kyoshida 2009-10-13 16:56:19 UTC
Created attachment 65347 [details]
patch to fix the offset lookup (but nothing more)
Comment 27 kyoshida 2009-10-13 17:03:17 UTC
@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.
Comment 28 niklas.nebel 2009-10-13 17:09:14 UTC
But that's also what the "Decimal places" option does (in the options dialog,
not the number format dialog). It doesn't add zeroes.
Comment 29 kyoshida 2009-10-13 17:13:14 UTC
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.
Comment 30 cno 2009-10-14 21:27:36 UTC
[picked up Kohei's request on the UX list for discussion]
Comment 31 kyoshida 2009-10-15 01:59:11 UTC
Created attachment 65372 [details]
new Calculate options page
Comment 32 kyoshida 2009-10-15 02:05:55 UTC
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.
Comment 33 kyoshida 2009-10-15 02:07:57 UTC
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.
Comment 34 kyoshida 2009-10-15 04:24:25 UTC
>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.
Comment 35 Regina Henschel 2009-10-16 18:41:17 UTC
*** Issue 38741 has been marked as a duplicate of this issue. ***
Comment 36 Regina Henschel 2009-10-16 19:01:22 UTC
*** Issue 76515 has been marked as a duplicate of this issue. ***
Comment 37 kyoshida 2009-10-16 19:39:34 UTC
Grabbin'it.
Comment 38 kyoshida 2009-10-16 19:39:50 UTC
started.
Comment 39 kyoshida 2009-11-16 16:42:47 UTC
This issue is now being working on in koheiautodecimal cws.
Comment 40 nightcrawler27 2009-11-18 15:01:24 UTC
Great! I'm thrilled to see that this issue is being worked, thanks for everyone
involved.
Comment 41 dipesh 2009-12-17 23:24:36 UTC
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.
Comment 42 ooo 2009-12-18 14:19:47 UTC
@dipesh: That is unrelated to this issue. You just assigned a fixed decimals
number format.
Comment 43 dipesh 2009-12-21 13:19:03 UTC
@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...
Comment 44 kyoshida 2010-02-04 02:43:48 UTC
fixed in koheiautodecimal cws.
Comment 45 kyoshida 2010-02-04 02:46:11 UTC
Spec is being worked on here:
http://wiki.services.openoffice.org/wiki/Calc/Features/Automatic_decimal_place_adjustment
Comment 46 kyoshida 2010-02-04 16:17:38 UTC
re-assigning to oc for qa verification.
Comment 47 oc 2010-05-21 14:03:50 UTC
verified in internal build cws_koheiautodecimal