Bug 60215 - FILEOPEN: FORMATTING of value-related color, localized decimal separator and Currency VIEWING need Complete Recalculation
Summary: FILEOPEN: FORMATTING of value-related color, localized decimal separator and ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.0.3 release
Hardware: All All
: high major
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.0.3 target:4.2.0 target:4.1....
Keywords: regression
: 60401 60442 60495 60598 60765 60947 61271 61401 61574 61791 61951 61969 62053 62065 62221 62502 62727 62975 63120 63380 63507 63512 63516 63950 64023 64191 64681 65051 66026 66486 68301 (view as bug list)
Depends on:
Blocks: mab4.0
  Show dependency treegraph
 
Reported: 2013-02-03 07:46 UTC by mike.hall
Modified: 2018-08-28 09:08 UTC (History)
41 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample Document (7.77 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2013-02-03 08:20 UTC, Rainer Bielefeld Retired
Details
Another sample document (8.11 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2013-02-09 21:05 UTC, Jouni Kosonen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description mike.hall 2013-02-03 07:46:11 UTC
Create a formula eg   =-1-1   in any cell
Format cell as currency [$£-809]#,##0.00;[RED]-[$£-809]#,##0.00
Cell text turns red
Save and close
Reopen
Text is black (bug)
Ctrl+Shift+F9
Text returns to red

@Marcus
Not sure if this is a regression from RC2.
May well be associated with changes you did re inherited formats bug 59724
Re those, saw a few examples of problems with RC3, fixed by recalculate, save, close, reopen. Suspect this won't appear for users upgrading from 3.x, but will do a test.
Comment 1 Markus Mohrhard 2013-02-03 08:07:51 UTC
No, it is not a regression between RC2 and RC3. We simply can't handle it right now. The cached value import will loose colors of number formats at the moment. We might need to think about a solution for the lost format color.
Comment 2 Rainer Bielefeld Retired 2013-02-03 08:15:04 UTC
Effect  [Reproducible] with "LibO  4.0.0.3 rc   -  GERMAN UI / German Locale  [Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)]"  {tinderbox: @6, pull time 2013-01-31 11:30(?)} on German WIN7 Home Premium (64bit) with User Profile updated from 4.0.0.2; but I doubt that it is a "format loss" problem.

Easy to reproduce: create new blank document, Type formula "=-1-1" into A1 and apply Currency formatting. That should show "-2,00 €" in red color because negative (#.##0,00 [$€-407];[ROT]-#.##0,00 [$€-407]). Save, close and reopen, A1 still is  "-2,00 €", but now in black color, althougg formatting string still is correct.

<control+shift+f9> indeed heals the problem, so I agree that it seems to be a recalculation problem. I already stumbled upon similar, may be more serious related currency problems last days, I will check that.

Can easily be reproduced, simply open attached sample document. If you see A1 contents black your version is affected

@mike.hall:
I think you also tested with WIN?
Comment 3 Rainer Bielefeld Retired 2013-02-03 08:20:13 UTC
Created attachment 74123 [details]
Sample Document

Your version is affected if it shows A1 in black after FILEOPEN
Comment 4 mike.hall 2013-02-03 09:10:44 UTC
@Rainer
Yes, I test only with Win

Regression from both RC1 and 3.6.4 (saved files from each and opened in RC3 - problem occurs.

Regret it probably needs to be fixed before release of 4.0
Comment 5 Markus Mohrhard 2013-02-03 09:15:12 UTC
> Regret it probably needs to be fixed before release of 4.0

Far from it. It is not even close to a blocker.
Comment 6 Markus Mohrhard 2013-02-07 17:01:10 UTC
*** Bug 60401 has been marked as a duplicate of this bug. ***
Comment 7 Markus Mohrhard 2013-02-07 17:05:25 UTC
I had some thoughts about possible solutions and a discussion with Kohei.

The problem here is that we skip the whole number format code to get inherited number formats right which means that colors are just lost until the cell value is marked dirty and the cached value is thrown away. The solution is to get the inherited number format written into ODF and import the number format then from ODF instead of using our current ugly hack. However this requires some more work to get it right and fix all the corner cases.

I hope to get something working into 4.0.1 but depending on the workload it might also take till 4.0.2.
Comment 8 Jouni Kosonen 2013-02-09 21:05:57 UTC
Created attachment 74511 [details]
Another sample document

This only seems to affect cells that have formulas in them, literal cells do retain their coloration at fileopen, at least when the style is *not* inherited. In the attached document all cells in range B3:E5 have the same style, but only B3:D3 have colors without c-s-F9.

Looking at the ODF document, there only difference between the cells that show correct colors and the cells that don't is that the latter have a table:formula -attribute. It doesn't seem to matter if the formula refers to a literal value or another cell.

Since it is unlikely that the literal cells would get their colors by virtue of being marked dirty, this would seem to indicate that the style attribute is ignored entirely for formula cells. This was not the case in 4.0.0.2.
Comment 9 Jouni Kosonen 2013-02-09 21:19:52 UTC
Seen with 4.0.0.3 (32-bit) under 64-bit Win7
Seen with 4.0.0.3 (64-bit) under 64-bit Linux
Comment 10 Markus Mohrhard 2013-02-10 18:51:50 UTC
*** Bug 60598 has been marked as a duplicate of this bug. ***
Comment 11 Rainer Bielefeld Retired 2013-02-12 05:40:14 UTC
*** Bug 60495 has been marked as a duplicate of this bug. ***
Comment 12 Rainer Bielefeld Retired 2013-02-12 05:43:10 UTC
I found following details with Server Installation of "LibO  4.0.0.3 rc   -  GERMAN UI / German Locale  [Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)]"  {tinderbox: @6, pull time 2013-01-31 11:30(?)} on German WIN7 Home Premium (64bit) with separate  new User Profile  

Attachment 74511 [details] for Bug 60215 here shows some other format details what need recalculation:
a) not only currency color codes are affected, any color in formatting string
   might need recalculation if cell value is calculated
b) Also thousands separators might be affected (in Attachment 74511 [details] separator
   in cell B4 and others changes from blank to dot after recalculation)
   if cell value is calculated
	
Attachment 74431 [details] for Bug 60495 shows additional effects, complete recalculation for cells with calculated values required to see correct formatting:
c) Correct localized decimal separator "comma" in C5 and others
Comment 13 Horst 2013-02-13 01:51:51 UTC
Mariosv mentioned a new option in 4.0.0.3
"Seems to be in relation with the new options in:
Menu/Tools/Options/LibreOffice Calc/Formula/Recalculation on file load/ODF Spreadsheet
setting the option to always recalculate show the format cell right."

Maybe the solution is just to set default to "Always Recalculate".
Comment 14 Horst 2013-02-13 01:53:00 UTC
Sorry forgot to mention it was in bug 60495.
Comment 15 Rainer Bielefeld Retired 2013-02-13 07:13:19 UTC
*** Bug 60765 has been marked as a duplicate of this bug. ***
Comment 16 Rainer Bielefeld Retired 2013-02-13 07:17:53 UTC
*** Bug 60442 has been marked as a duplicate of this bug. ***
Comment 17 Jorendc 2013-02-16 16:07:59 UTC
*** Bug 60947 has been marked as a duplicate of this bug. ***
Comment 18 Christian Fries 2013-02-16 22:08:14 UTC
I believe that the use of cached values upon load should be an "option" and not the default. The use of cached values is currently a problem when using UNO plugging, please see 
https://bugs.freedesktop.org/show_bug.cgi?id=60964
https://bugs.freedesktop.org/show_bug.cgi?id=60973
https://bugs.freedesktop.org/show_bug.cgi?id=60974
https://bugs.freedesktop.org/show_bug.cgi?id=60977
Comment 19 Markus Mohrhard 2013-02-18 00:07:53 UTC
(In reply to comment #18)
> I believe that the use of cached values upon load should be an "option" and
> not the default.

It is an option and it is the default for files generated by Libreoffice. There are good reasons for switching to cached value import.

We know that we still might have a few cases we need to adapt but please accept that we are working on fixing them. If you have a case where you think you see an error open a bug report with a test document and put me into CC. I'll take care of the cached value import problems.
Comment 20 Markus Mohrhard 2013-02-18 00:09:14 UTC
Just for the record:

We have a solution for this bug but we are still working out the details as it involves several major changes to inherited number formats.
Comment 21 Markus Mohrhard 2013-02-22 17:30:46 UTC
*** Bug 61271 has been marked as a duplicate of this bug. ***
Comment 22 Graham P Davis 2013-02-23 10:48:27 UTC
The "cure" of changing the settings of Calc/formulae to "always recalculate" is a bit of a pain as you have to close Calc and reopen for any change of formatting to take effect; that's not something I would like to see as a default.

I'm running 4.0.1 from openSUSE ("unstable" repo) at the moment and have just tried the setting "prompt user." This seemed to behave no differently from "always recalculate" in that, on opening, recalculations were made with no prompt appearing. Version 4.0.0.3 from the LO site also shows no prompt but behaves differently to 4.0.1 in that it does no recalculation; 4.0.0.3 shows negative numbers in black whilst 4.0.1 shows them in red. The difference in behaviour could be a little local difficulty but shouldn't "prompt user" prompt the user?
Comment 23 Joel Madero 2013-02-25 05:34:04 UTC
*** Bug 61401 has been marked as a duplicate of this bug. ***
Comment 24 Thomas van der Meulen 2013-02-27 20:12:06 UTC
*** Bug 61574 has been marked as a duplicate of this bug. ***
Comment 25 Thomas van der Meulen 2013-03-04 14:52:01 UTC
*** Bug 61791 has been marked as a duplicate of this bug. ***
Comment 26 heaven988 2013-03-05 10:17:25 UTC
The problem is still there in 4.0.1 RC2
Comment 27 Rainer Bielefeld Retired 2013-03-05 10:27:21 UTC
@heaven988, please consider:
<http://wiki.documentfoundation.org/BugReport_Details#Version>
If you also did this mistake in other Bugs please undo your Version changes there!
Generally such info "still exist in RC for next minor release" is not interesting for us in a Bug with status ASSIGNED - that' expected.
Comment 28 heaven988 2013-03-05 10:46:27 UTC
(In reply to comment #27)
> @heaven988, please consider:
> <http://wiki.documentfoundation.org/BugReport_Details#Version>
> If you also did this mistake in other Bugs please undo your Version changes
> there!
> Generally such info "still exist in RC for next minor release" is not
> interesting for us in a Bug with status ASSIGNED - that' expected.

Ok, sorry :)
I just did that here!
Comment 29 m.a.riosv 2013-03-09 11:54:26 UTC
*** Bug 62053 has been marked as a duplicate of this bug. ***
Comment 30 GerardF 2013-03-09 15:26:10 UTC
*** Bug 62065 has been marked as a duplicate of this bug. ***
Comment 31 Thomas van der Meulen 2013-03-15 14:23:35 UTC
*** Bug 62221 has been marked as a duplicate of this bug. ***
Comment 32 vitriol 2013-03-15 18:54:21 UTC
*** Bug 61951 has been marked as a duplicate of this bug. ***
Comment 33 Rainer Bielefeld Retired 2013-03-17 18:52:11 UTC
OS = All due to various comments
Comment 34 Jorendc 2013-03-19 17:23:12 UTC
*** Bug 62502 has been marked as a duplicate of this bug. ***
Comment 35 Thomas van der Meulen 2013-03-25 15:49:27 UTC
*** Bug 62727 has been marked as a duplicate of this bug. ***
Comment 36 Michael Meeks 2013-03-28 16:08:23 UTC
Tor tried this and it broke stuff - so, sadly closing it.
Comment 37 Markus Mohrhard 2013-03-28 16:09:58 UTC
Michael was trying to close another bug report.
Comment 38 Michael Meeks 2013-03-28 17:31:25 UTC
urgh; I'm so sorry - no idea why that happened to this issue - it was an ICU related one I was looking at (I thought).
Comment 39 Rainer Bielefeld Retired 2013-04-01 06:17:36 UTC
*** Bug 62975 has been marked as a duplicate of this bug. ***
Comment 40 Jorendc 2013-04-04 20:33:41 UTC
*** Bug 63120 has been marked as a duplicate of this bug. ***
Comment 41 Commit Notification 2013-04-09 15:52:00 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d947b1f1ad3571c90991420d500eb81e7cd84fed&h=libreoffice-4-0

disable cached value import for ODS for now, fdo#60215


It will be available in LibreOffice 4.0.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 42 Thomas van der Meulen 2013-04-10 13:45:18 UTC
*** Bug 63380 has been marked as a duplicate of this bug. ***
Comment 43 m.a.riosv 2013-04-13 20:44:28 UTC
*** Bug 63512 has been marked as a duplicate of this bug. ***
Comment 44 m.a.riosv 2013-04-13 20:51:48 UTC
*** Bug 63507 has been marked as a duplicate of this bug. ***
Comment 45 m.a.riosv 2013-04-14 12:33:47 UTC
*** Bug 63516 has been marked as a duplicate of this bug. ***
Comment 46 Rainer Bielefeld Retired 2013-04-16 15:31:18 UTC
Something went wrong here, this one has nothing to do with Bug 63585
Comment 47 Owen Genat (retired) 2013-04-17 02:31:46 UTC
*** Bug 61969 has been marked as a duplicate of this bug. ***
Comment 48 Thomas van der Meulen 2013-04-26 15:49:25 UTC
*** Bug 63950 has been marked as a duplicate of this bug. ***
Comment 49 m.a.riosv 2013-04-28 15:44:44 UTC
*** Bug 64023 has been marked as a duplicate of this bug. ***
Comment 50 Markus Mohrhard 2013-05-03 18:37:45 UTC
*** Bug 64191 has been marked as a duplicate of this bug. ***
Comment 51 m.a.riosv 2013-05-17 11:11:47 UTC
*** Bug 64681 has been marked as a duplicate of this bug. ***
Comment 52 Markus Mohrhard 2013-05-25 13:10:04 UTC
Fixed in feature/inherited-number-format-removal and will be merged to master soon. I'm not sure if I still get it into 4-1 as it is a quite large and scary patch set which surely breaks some other stuff.
Comment 53 Markus Mohrhard 2013-05-27 20:51:33 UTC
*** Bug 65051 has been marked as a duplicate of this bug. ***
Comment 54 Commit Notification 2013-06-02 01:40:08 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=57efd69c22e2c6f5cb4d057345644b6e07a62d48

remove inherited number formats, related fdo#60215



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 55 Commit Notification 2013-06-04 01:35:48 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=2d556a55b136b67fbc010d29ba7f98a8419d1470&h=libreoffice-4-1

remove inherited number formats, fdo#60215


It will be available in LibreOffice 4.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 56 m.a.riosv 2013-06-21 23:41:41 UTC
*** Bug 66026 has been marked as a duplicate of this bug. ***
Comment 57 casimiro 2013-06-22 07:32:21 UTC
The problem is not solved.
The problem is in Version 4.0.4.2 (Build ID: 9e9821abd0ffdbc09cd8c52eaa574fa09eb08f2)
Comment 58 Michel Rudelle 2013-06-22 09:35:13 UTC
(In reply to comment #57)
> The problem is not solved.
> The problem is in Version 4.0.4.2
Hi,
You should try with a new profile, this is what I did and it works fine now on both versions 4.0.3.3. and 4.0.4.2 [Vista-32b]
Comment 59 casimiro 2013-06-22 11:08:29 UTC
hello, you're right, yes it is solved.
thank you very much
regards
Comment 60 Michel Rudelle 2013-06-22 16:39:23 UTC
Unhappily, it is not solved for upper versions.
The bug is still there:
Version: 4.1.0.1 Build ID: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155 [Vista-32b]
and this feedback from French lists:
version : 4.2.0.0.alpha0+ Build ID: c36348f20c4fcb6ae1acb0fd06c19edfa9fb108
[Windows 7 Home Premium]
Comment 61 Markus Mohrhard 2013-06-22 16:50:43 UTC
This bug is only fixed in 4.1.beta2 and later. Of course alpha1 does not contain the fix.

If you can see problems with a beta2 and later build please open a new bug report and put me in CC. They are very likely not related as the feature that was responsible for the bug has been removed.
Comment 62 sophie 2013-06-22 18:10:04 UTC
(In reply to comment #61)
> This bug is only fixed in 4.1.beta2 and later. Of course alpha1 does not
> contain the fix.
> 
> If you can see problems with a beta2 and later build please open a new bug
> report and put me in CC. They are very likely not related as the feature
> that was responsible for the bug has been removed.

Hi Markus, the tests have been done on 4.1 RC1 and 4.2 alpha1, may be you read the comment too fast, your fix should be included in 4.1rc1 right?, i.e. this version 
Version: 4.1.0.1 Build ID: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155 [Vista-32b]
Thanks - Sophie
Comment 63 Markus Mohrhard 2013-06-22 19:07:59 UTC
(In reply to comment #62)
> (In reply to comment #61)
> > This bug is only fixed in 4.1.beta2 and later. Of course alpha1 does not
> > contain the fix.
> > 
> > If you can see problems with a beta2 and later build please open a new bug
> > report and put me in CC. They are very likely not related as the feature
> > that was responsible for the bug has been removed.
> 
> Hi Markus, the tests have been done on 4.1 RC1 and 4.2 alpha1, may be you
> read the comment too fast, your fix should be included in 4.1rc1 right?,
> i.e. this version 
> Version: 4.1.0.1 Build ID: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155
> [Vista-32b]
> Thanks - Sophie

Yes I read it correctly. All I said is every problem looking like that after beta2 is a different bug because the feature responsible for this bug has been removed. There can not be any possible duplicate of this bug with builds after 4.1beta2.

If there are similar issues they are not duplicates but a different problem and should be tracked in a new bug report. Otherwise I and the few other people who need to keep track of the cached value import are loosing the overview.
Comment 64 sophie 2013-06-22 19:23:50 UTC
(In reply to comment #63)
> (In reply to comment #62)
> > (In reply to comment #61)
> > > This bug is only fixed in 4.1.beta2 and later. Of course alpha1 does not
> > > contain the fix.
> > > 
> > > If you can see problems with a beta2 and later build please open a new bug
> > > report and put me in CC. They are very likely not related as the feature
> > > that was responsible for the bug has been removed.
> > 
> > Hi Markus, the tests have been done on 4.1 RC1 and 4.2 alpha1, may be you
> > read the comment too fast, your fix should be included in 4.1rc1 right?,
> > i.e. this version 
> > Version: 4.1.0.1 Build ID: 1b3956717a60d6ac35b133d7b0a0f5eb55e9155
> > [Vista-32b]
> > Thanks - Sophie
> 
> Yes I read it correctly. All I said is every problem looking like that after
> beta2 is a different bug because the feature responsible for this bug has
> been removed. There can not be any possible duplicate of this bug with
> builds after 4.1beta2.
> 
> If there are similar issues they are not duplicates but a different problem
> and should be tracked in a new bug report. Otherwise I and the few other
> people who need to keep track of the cached value import are loosing the
> overview.
Ok, we misunderstand your comment so. Thanks for your explanations, Michel will open a new bug. Sophie
Comment 65 m.a.riosv 2013-07-03 00:21:50 UTC
*** Bug 66486 has been marked as a duplicate of this bug. ***
Comment 66 m.a.riosv 2013-08-20 08:06:11 UTC
*** Bug 68301 has been marked as a duplicate of this bug. ***
Comment 67 powul gel 2016-09-28 09:10:22 UTC Comment hidden (spam)