Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||Calc/Spreadsheet general format not displaying as Qpro, Excel, Lotus|
|Status:||CLOSED FIXED||QA Contact:||issues@sc <issues>|
|Priority:||P3||CC:||cno, issues, kyoshida, mail, ooo|
|Issue Type:||ENHANCEMENT||Latest Confirmation in:||---|
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
Comment 38 kyoshida 2009-10-16 19:39:50 UTC
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