Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium major
Assignee: Not Assigned
Whiteboard: BSA (target:4.0.0)
: 58509 (view as bug list)
Depends on:
Blocks: mab3.6
  Show dependency treegraph
Reported: 2012-11-16 05:30 UTC by rh_libre
Modified: 2013-07-06 12:58 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:

Example balance sheet / cash till float spreadsheet (23.77 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-11-21 15:48 UTC, rh_libre
LibO files with bug using conditional formatting (137.83 KB, application/zip)
2012-11-21 16:37 UTC, ledoux
Simple Conditional Format paste bug demo with instructions in worksheet (14.38 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2013-06-12 16:33 UTC, rh_libre

Note You need to log in before you can comment on or make changes to this bug.
Description rh_libre 2012-11-16 05:30:06 UTC
Problem description: I have a spreadsheet with one cell (with an =IF(condition,"CORRECT","INCORRECT") function having one format dependent on its own text value (or else its default format, making it go red or green). Another column of cells each have formatting (Blue Green or Red background) dependent on whether their value is greater, equal or less than the value three cells to the right. Saving as ODF (or opening a Libre Office 3.4-created file with this formatting) the column of cells have only background formatting and no Conditional Formatting shows up while the single condition has correct formatting.

Also if re-formatted as Conditional, then you Format Paint this format onto the same cell dragging down one cell too far, hitting undo to try again will wipe the original conditional format and there's no redo available. Similarly, hitting the Delete key (or Delete Contents without deleting formatting) has the same effect.

Steps to reproduce:
1. ....
2. ....
3. ....

Current behavior:

Expected behavior: Same as Libre Office 3.4 - Conditional Format is retained.

Platform (if different from the browser):
Browser: Opera/9.80 (Windows NT 6.1; Edition Campaign 21) Presto/2.12.388 Version/12.10
Comment 1 Cor Nouws 2012-11-20 22:23:55 UTC
Hi rh,

thanks for your report.
Could you pls try the following:
- try 3.6.4 release candidate 1

- try a daily build

If the issue is not fixed in those, pls attach an example file?

thanks - Cor
Comment 2 rh_libre 2012-11-21 15:48:03 UTC
Created attachment 70378 [details]
Example balance sheet / cash till float spreadsheet

Thanks. I tried version 3.6.4RC1 and after my PC hung on me mid-install leaving a 'program' folder with no access and no owner which I cannot delete or take ownership of even as administrator, I managed to install it in a different folder. (Windows 7 Professional 32 bit on AMD Turion 64-bit processor with 4GB RAM)

Still have some problems, so I attach Cash_Check_test.ods saved using that version of LibreOffice Calc

Cells K2 to K13 have three-way conditional formatting depending on whether lower, equal or higher than cell N2 to N13.

For example, change K4 from 30 to 40 and notice it becomes light-blue. Change it to 20 and it becomes light-red (pink). Set it back to 30 and it becomes green.

Now, still on K4, hit Delete. It's treated as zero, and goes light red.

Hit Undo, and it reverts to 30 and green.

Now select two cells, K4:K5 and hit delete. This time all formatting is lost.

Not to worry, you might think, just hit Undo. The values return.

But, Oh, where has my conditional formatting gone!?

It seems to lose the Conditional Formatting when deleting a multiple selection and the Undo doesn't realise it has gone, so I don't think it's an intended feature of hitting delete on a multi-cell selection.

There doesn't seem to be a problem with Paste Special / Numbers changing the formats now, but at least I can demonstrate a bug of the sort I mentioned with steps to try to replicate it.


Comment 3 ledoux 2012-11-21 16:33:33 UTC
I've tried the Version (Build ID: a9a0717) on MacOS 10.7.5.
I still lose the Conditional Formatting when deleting a multiple selection.
See attachment
Comment 4 ledoux 2012-11-21 16:37:40 UTC
Created attachment 70380 [details]
LibO files with bug using conditional formatting
Comment 5 Markus Mohrhard 2012-11-23 16:39:50 UTC
Undo/Redo fixed in master for 4.0
Comment 6 Markus Mohrhard 2012-11-23 22:25:25 UTC
Fixed the autofill problem in master.
Comment 7 Markus Mohrhard 2012-11-23 22:28:19 UTC
Sorry marked the wrong bug as fixed.
Comment 8 Rainer Bielefeld Retired 2012-12-04 06:34:38 UTC
This problem can be reproduced easily with "SampleSource.ods" in test kit Attachment 68944 [details] of Bug 56316.

Steps how to reproduce:
0. download / Unzip Attachment 68944 [details]
1. Open "SampleSource.ods" from Test kit
2. With menu 'Format -> Conditional Formatting ->  Manage' you see that there 
   is some CF for Cell I5
3. Click row heading for row 3 
  > row 3 selected
4. <control+c> for copy
5. Click row heading for row 5
6. <control+v> for paste 
   > only empty cells in row 5
7. With menu 'Format -> Conditional Formatting ->  Manage' you see that there 
   is NO CF for Cell I5
8. Click the "Undo" Icon in the Standard Toolbar
   >  Contents of row 5 reappears
9. With menu 'Format -> Conditional Formatting ->  Manage' you see
   BUG: still NO CF for Cell I5                                          !!!

Other possibility to reproduce the bug (as observed in <https://bugs.freedesktop.org/show_bug.cgi?id=56316#c13>:
11. Open Attachment 70975 [details] Details for Bug 56316
12. Click common rows / Columns heading at top left corner
    all cells become selected
13. <control+c> for copy
14. Click A1
15. <control+v> for paste 
    > Complete sheet contents of sheet sheet "replaced by itself"
      But Conditional Formatting is lost
16. Click the "Undo" Icon in the Standard Toolbar
    > a lot of flickering seems to restore the old contents 
    BUG: still NO CF (cells with contents "8" without 
         blue background                                            !!!

Already [Reproducible] with 
- Server Installation of "LibreOffice rc  English UI/ German Locale [Build-ID:  ba822cc] on German WIN7 Home Premium (64bit) and Sample of
Step 1, Sample of step 11 crashes this version. 

This still worked fine with  
- Server Installation of  "LibreOffice  English UI/ German Locale [Build-ID:  932b512] on German WIN7 Home Premium (64bit) 
- Server Installation of  "LibreOffice  German UI/Locale [Build-ID: f010139] on German WIN7 Home Premium (64bit) (already new CF dialog, but without "Manage" option

Works for me with both examples in this comment and with parallel installation of  "LOdev   -  ENGLISH UI / German Locale  [Build ID: 6aabe09ac092c51d4b394bde9c7ea0055b952e3)]"  {tinderbox: Win-x86@6, pull time 2012-11-26 00:29:34} on German WIN7 Home Premium (64bit) with own separate User Profile 

We will need a backport to 3.6.
Comment 9 Rainer Bielefeld Retired 2012-12-04 06:39:21 UTC
3.6.2 (?) regression
Comment 10 andis.lazdins 2012-12-04 07:13:26 UTC
In Version (Build ID: e14b5f33ceae3280b46eb97a88b949d00263956) on Ubuntu 12.04 after copy/paste whole sheet as described in comment 8, the conditional formatting records survives after pasting. 
However, new problem arises; on open file in 4.0 original conditional formatting setting (if cell value is > 0, than cell style = Number) is changed to empty value (if cell value is equal = (empty box), then Default style).

Probably another kind of problem. 

Libreoffice also still hangs, when there are 12 monthly sheets in a file, when I try to copy/paste content of the whole sheet.
Comment 11 andis.lazdins 2012-12-04 07:16:30 UTC
(In reply to comment #10)

> Libreoffice also still hangs, when there are 12 monthly sheets in a file,
> when I try to copy/paste content of the whole sheet.

Update! It is not hanging, it just takes several minutes to paste. Few seconds in Libreoffice 3.5.5.
Comment 12 Markus Mohrhard 2012-12-05 13:17:14 UTC
(In reply to comment #11)
> (In reply to comment #10)
> > 
> > Libreoffice also still hangs, when there are 12 monthly sheets in a file,
> > when I try to copy/paste content of the whole sheet.
> Update! It is not hanging, it just takes several minutes to paste. Few
> seconds in Libreoffice 3.5.5.

Can you please open new bug reports for new issues. I'll have a hard time to track bug reports otherwise. As long as the title contains conditional formatting it will show up in my search.

Adding additional bug reports to an existing bug report leads to me loosing track of the different problems mentioned in the bug report.
Comment 13 john.pratt 2012-12-19 10:36:16 UTC
*** Bug 58509 has been marked as a duplicate of this bug. ***
Comment 14 john.pratt 2012-12-19 10:37:16 UTC
I can confirm that this issue still exists in 4.0 beta 1.
Comment 15 ledoux 2013-01-02 15:56:51 UTC
I've tried the Version on MacOS 10.7.5.
I still lose the Conditional Formatting when deleting a multiple selection.
Comment 16 Christopher M. Penalver 2013-01-14 01:23:39 UTC
Reproducible in:
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002
Version (Build ID: 527dba6f6e0cfbbc71bd6e7b88a52699bb48799)

lsb_release -rd
Description: Ubuntu 12.10
Release: 12.10

apt-cache policy libreoffice-calc
  Installed: 1:3.6.2~rc2-0ubuntu4
  Candidate: 1:3.6.2~rc2-0ubuntu4
  Version table:
 *** 1:3.6.2~rc2-0ubuntu4 0
        900 http://archive.ubuntu.com/ubuntu/ quantal-updates/main i386 Packages
        100 /var/lib/dpkg/status
     1:3.6.2~rc2-0ubuntu3 0
        500 http://archive.ubuntu.com/ubuntu/ quantal/main i386 Packages
Comment 17 john.pratt 2013-02-03 11:41:12 UTC
Not reproducible in Version (Build ID: 28b7359e00fec9fd3bfab3c9105cf250c4320a2)
TinderBox: Win-x86@6, Branch:master, Time: 2013-02-02_00:14:06 using either of Rainer Bielefeld's methods.

Can this be marked as resolved?
Comment 18 Christopher M. Penalver 2013-02-03 14:28:44 UTC
Reproducible in:
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002
Version (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89)

Using original reporter's attachment https://bugs.freedesktop.org/attachment.cgi?id=70378 highlight cells K4:K5 -> click Delete -> Undo and the Background Color of cells K4:K5 are not restored.
Comment 19 john.pratt 2013-02-03 15:04:33 UTC
@Christopher M. Penalver
I agree that this is not working with the original attachment (I had misunderstood the instructions earlier).

Tested on Windows XP with:
Version (Build ID: 28b7359e00fec9fd3bfab3c9105cf250c4320a2)
TinderBox: Win-x86@6, Branch:master, Time: 2013-02-02_00:14:06
Comment 20 rh_libre 2013-03-15 15:49:55 UTC
I've just tested and the bug is still present in
Version (Build ID: 84102822e3d61eb989ddd325abf1ac077904985)

I'd been hopeful when I saw in Release Notes that some other Conditional Formatting bugs had been resolved.

Thanks to the devs for your continuing efforts.
Comment 21 Mikeyy - L10n HR 2013-03-20 21:45:28 UTC
This is still present in, I just wanted to report it.
You cannot undo / redo conditional formatting, like it never happened.
Comment 22 Rainer Bielefeld Retired 2013-03-21 06:52:56 UTC
We had some reports where we had to learn that problems looking similar had different roots so that a fix did not solve all problems. May be we here have a similar situation, I will do some tests today.
Comment 23 Rainer Bielefeld Retired 2013-03-23 09:22:37 UTC
I decided to mark this one as DUP of "Bug 57661 - CONDITIONAL FORMATTING of range lost after deletion the contents of range":

a) reporter's comment 2 matches with But 57661
b) That bug is already Assigned

For any other bug not matching with Bug 57661 please open new Bugs (if not already reported).

If the problem in that new report can be reproduced with an existing Att. Sample document please cite it due to <https://wiki.documentfoundation.org/QA-FAQ#How_to_use_attached_sample_documents_for_multiple_Bug_Reports> instead of attaching it newly.

*** This bug has been marked as a duplicate of bug 57661 ***
Comment 24 Christopher M. Penalver 2013-05-09 00:48:13 UTC
Unmarking as duplicate as the problem noted in the Description https://bugs.freedesktop.org/show_bug.cgi?id=57176#c0 is reproducible in:
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002
Version (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)
Comment 25 Michael Meeks 2013-06-12 10:57:35 UTC
I can no longer reproduce: https://bugs.freedesktop.org/show_bug.cgi?id=57176#c2 on either the 4.0 or 4.1 / master branches.

Isolating and back-porting (if possible) the conditional formatting code to 3.6 seems unlikely to happen before the last 3.6 release (3.6.7), but if some developer wants to do that they are more than welcome to find / push a patch to gerrit for double review.

Christopher - if you can reproduce this with a 4.0.4 pre-release snapshot, can you attach a sample document with easier to reproduce steps like comment#2. Or preferably a set of simple instructions for an absolutely minimal test - with the relevant UI paths to commands highlighted in each case: Format->Character->Foo etc. :-) I found comment#0 somewhat impenetrable myself.

Thanks !
Comment 26 john.pratt 2013-06-12 15:32:27 UTC
I can still reproduce (in (Build ID: 7e5467ff8f30d821f4fbf69cb2769163eb64c2c)) the problem I was having in #58509 and it doesn't relate to deleting, but editing conditional formatting.

Would it be better if I reopen this bug too?
Comment 27 rh_libre 2013-06-12 16:33:24 UTC
Created attachment 80735 [details]
Simple Conditional Format paste bug demo with instructions in worksheet

New Attachment CFpasteBug.ods
obsoletes attachment 70378 [details].

This is simpler than attachment 70378 [details]

It includes instructions to reproduce the bug in cells A5:A13

It shows that the Conditional Format is still present but not displayed: Use Format Paintbrush to paint format onto the same range (effectively forcing a recalculate of the conditions and a refresh of their display) to make it display properly.

Tested with Windows 7 Professional 32-bit, latest Dev beta
Build ID: 33224f4f11a05cfad2249e812fcc2975fbb61f6
Comment 28 Christopher M. Penalver 2013-06-13 10:54:35 UTC
Unreproducible in:
Build ID: 979def88090633bfee0e0445b19999a1dac71ed
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002
Comment 29 rh_libre 2013-06-14 16:48:34 UTC
I've re-opened this bug.

(While I'm here, please accept my apologies for using a complicated attachment originally, which has for a while been obsoleted and I've replaced it with the second of the two non-obsolete attachments, the 14.38 KiB one):

The bug still exists in last night's Win-86@6 build if you use that second attachment, the one named
"Simple Conditional Format paste bug demo with instructions in worksheet"
(actual filename = CFpasteBug.ods)

Build ID: 6372309a0d285f4a176dbcded3f18b72220e123
That's the current nightly build from
Windows 7 Professional, 32-bit. AMD Turion 64 dual core. 4GB RAM.

You only need to get to step 4 to confirm the bug from these instructions (included also in the spreadsheet)
1. Select range B1:E1
2. Edit/Copy
3. Select cell B2
4. Right click on B2, Paste Only ► Number (click Yes to confirm paste)

The range B2:E2 does not display its conditional formatting (red or green coloured background) as it should, only the underlying bold, centered plain formatting on a "No Fill" background.

The CF is still associated with the range B2:E2 (see Format/Conditional Format/Manage to confirm).
You can easily restore it to be visible using Format Paintbrush to paint the format from B2:E2 back into B2, which acts as a "Refresh" function for the Conditional Format in that range. Calling the Format Paint in this way would be a suitable fix, though it's probably not very efficient.

The same applies using Paste Special... and pasting anything that doesn't include pasting the formatting from the source range to destination range seems to prevent the Conditional Formatting from being displayed.

I think this tells us what is missing from the code. My pure guess is that some function call that was not obviously important was not replicated during a code rewrite that was intended either to reduce Java dependency from the old OpenOffice.org codebase or to allow new and fancy CF such as Color Scales around about the time of LibO 3.6.

After all, why would you need to concern yourself with formatting in the target cells if you're not pasting the formatting from Source to Target. (For normal formatting this is true - not for formatting that's Conditional on the cell value).

I'm familiar enough with the program behaviour in this regard (the edge cases where the bug is or is not present) that I've suggested below how I'd go about trying to trace and fix the bug in principle, albeit that I am not capable of achieving it myself in practice any time soon.

Bug 57661 has been fixed for a while, and had exhibited similar behaviour (when using Delete to clear the values from cells with Conditional Format, the CF did not display and Undo would not fix it either). Perhaps the patches used there can reveal the correct functions to call to refresh/recalculate the Conditional Format display, or that code can itself be made into a function or method we can use for this fix to avoid code duplication.

I have not developed a full software application in 16 years. I've never worked on a team software project, so I would need a huge learning curve to set up a dev environment for myself and delve into the LibO codebase myself. I intend to set up a Linux dual-boot in the next month or two, and will probably attempt to set up a dev environment then and try a little tinkering, but for now, I'm unable to pull my weight.

So please recognise my limitations, and that all the LibO developers have my utmost respect and admiration. I may be making incorrect assumptions about the code structure and I do not wish to "teach my grandmother to suck eggs" or make out that debugging is easy. You clearly are all far better programmers and software engineers than I ever was. I merely try to make a suggestion that may be helpful to someone who knows what they are doing and has set up the right debugging tools.

It may also be the case that the correct code is already in there but a bug in the conditional branching (e.g. IF... THEN) isn't making it execute when it needs to.


I've made many assumptions about the code structure, object.method and condition statements and have written in some kind of object-oriented pseudocode

(please forgive my syntax, I've a nasty feeling my comment delimiters are back-to-front - it's been a long time - and I'm making no attempt to trace or handle exceptions):

At the end of any Paste Special... action which pastes from a SOURCE Range to a TARGET Range, before returning control to the user interface or calling function/method, include the following steps:

*/ START of object-oriented pseudocode /*

*/ all the rest of the Paste Special... function goes above here */

*/ Recalculate Conditional Format based on new contents of Target Range if format was not overwritten during Paste and if Conditional Formatting is present within the Target Range /*

 IF (PasteSpecialDialog.FormatCheckbox.state == FALSE) THEN
   IF (TargetRange.Format.conditionalFormatIsPresent == TRUE) THEN
 return returnCode;
}  /* return from Paste Special... function */

/* END of pseudocode */

(As we're dealing with a range of many target cells, some of which might intersect with ranges that include CF and some of which might not, it might require some loop and only require recalculation for target cells where CF is present to avoid slowing down copy/paste where CF is not present, which probably represents most spreadsheet users. I wouldn't want this fix to slow down a copy and paste of 10 000 cells with normal formatting only)

I'd hope it would be possible in a debugger to step through and find the functions called when using Format Painter onto the same target range as the source range (which I presume is the same as Paste Special... with only the Format box selected).

It seems clear that if there is CF in the target cells it must be reevaluated whenever the values or text in those cells are changed, and it appears that is not happening unless the format from the source range is also pasted.

I also realise that it may require additional changes to ensure that the Undo function will work properly and refresh the CF when the original values in those cells are restored (I've no idea how Undo is implemented)

Sorry for the length of the post, but I hope I've clarified the edge cases where this bug does and does not seem to occur, in the hope this may narrow down the hunt.
Comment 30 rh_libre 2013-07-06 12:58:31 UTC
I think there was a duplicate bug that I've now marked as Resolved and realised that this one was marked REOPENED.

I'll mark this RESOLVED also, though I have since identified another bug on Paste Special... Add... which does not handle blank cells as if they were zero and ends up putting things in the wrong cells. I'll try to find the time to knock up a simple example and post a new bug for that later today.