Download it now!
Bug 57661 - CONDITIONAL FORMATTING of range lost after deletion the contents of range.
Summary: CONDITIONAL FORMATTING of range lost after deletion the contents of range.
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.6.0.4 release
Hardware: x86-64 (AMD64) All
: high major
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.1.0 target:4.0.2
Keywords: regression
: 60279 (view as bug list)
Depends on:
Blocks: mab3.6
  Show dependency treegraph
 
Reported: 2012-11-28 20:24 UTC by jean-paul.hahn
Modified: 2013-05-09 14:15 UTC (History)
13 users (show)

See Also:
Crash report or crash signature:


Attachments
Original conditional formatting range (34.31 KB, image/png)
2012-11-28 20:24 UTC, jean-paul.hahn
Details
Cells to be deleted (33.45 KB, image/png)
2012-11-28 20:26 UTC, jean-paul.hahn
Details
Cells after the deletion but before saving (18.77 KB, image/png)
2012-11-28 20:26 UTC, jean-paul.hahn
Details
Cells and Conditional formatting range after reopening (38.67 KB, image/png)
2012-11-28 20:27 UTC, jean-paul.hahn
Details
spreadsheet used to show the issue (7.89 KB, application/x-vnd.oasis.opendocument.spreadsheet)
2013-02-08 10:07 UTC, Michel Rudelle
Details
CF bug in 4.0 demonstration (7.74 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-02-19 17:52 UTC, 9dz83inRE61FoLF
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jean-paul.hahn 2012-11-28 20:24:49 UTC
Created attachment 70748 [details]
Original conditional formatting range

I selected cells A4:I10000 and added conditional formatting as follows:

Condition 1: Formula is ISEVEN(ROW()), background yellow
Condition 2: Formula is ISODD(ROW()), background white

Everything looks fine.

I enter numbers in the cells and all still looks fine.

Now I delete the contents of cells that contain references like =IF(ISBLANK(B96);"";$O$6) or calculations like =IF(ISBLANK(B96);"";(B96-E96)*$K$6). The contents of the cells is deleted and everything still looks fine,

When I save the file and reopen it, the conditional formatting for the deleted cells is lost and the conditional formatting looks funny. Please see the attached files.
Comment 1 jean-paul.hahn 2012-11-28 20:26:01 UTC
Created attachment 70749 [details]
Cells to be deleted
Comment 2 jean-paul.hahn 2012-11-28 20:26:54 UTC
Created attachment 70750 [details]
Cells after the deletion but before saving
Comment 3 jean-paul.hahn 2012-11-28 20:27:45 UTC
Created attachment 70751 [details]
Cells and Conditional formatting range after reopening
Comment 4 Michel Rudelle 2013-02-08 10:07:52 UTC
Created attachment 74418 [details]
spreadsheet used to show the issue

Hi,
I reproduce.
Use the attached file and follow the instructions in the sheet and repeated below:

1/ initial state:
  CF in cells A3:C7
  condition : if $A$1 = 1, then background → red
2) initial checks :
  1 is in A1 :
    A3:C7 - > background red   ok
  enter 0 in cell A1
    A3:C7 - > background yellow   ok
3/ to reproduce the bug:
  select A3:C7
  delete (Del key on the keyboard)
  unexpected result : CF is no longer applied in cells A3:C7

It is not necessary to save the file, the loss is immediate.
Note that according to the document used, it is not always necessary to clear the whole area, sometimes a few cells are enough.

Observed on:
Version 3.6.5.2 (Build ID: 5b93205) - Vista
Version 4.0.0.3 (Build ID: 7545bee9c2a0782548772a21bc84a9dcc583b89) - Vista
Version 4.0.1.0+ (Build ID: f26d3e43e8ead5c978a7efd1c7a84c8cd0a2758)

It works fine with 3.5.7.2 => regression
Importance: high major because this can affect people who are just in charge to manage data in spreadsheet and are not the designer of the spreadsheet (no workaround for them)
Comment 5 Michel Rudelle 2013-02-08 10:13:19 UTC
*** Bug 60279 has been marked as a duplicate of this bug. ***
Comment 6 Rohan 2013-02-08 11:11:38 UTC
Thank you, Michel, for dealing with this and increasing the importance - I fully agree. It is sad to see Libreoffice 4.0 being released without a fix for this, nor for #59843 and a few other little formatting things.

I already read bad comments on heise.de where people are saying that libreoffice is not worth using because now that 4.0 is released it is still breaking their formats. I on the other hand think that it is a *great* tool and with those bugs having a fix already, you could have prevented some bad publicity by waiting just a little longer with the release...
Comment 7 pierre-yves samyn 2013-02-19 08:34:48 UTC
Hello

Easily reproducible with the attached file in comment 4

Steps to reproduce.

1. Open the workbook

A conditional formatting is defined on A3:C7  (A1 = 1)
A1 = 1 so A3:C7 is red: Ok

2. Select A3:C7
3. Press the Delete key

Expected Result A3:C7 remains in red
Actual result: A3:C7 turns yellow (conditional formatting is lost)

Edit> Undo Delete does *not* restore the conditional formatting

Platform: windows XP Pro & Version 4.0.0.2 .0.2 (Build ID: 5991f37846fc3763493029c4958b57282c2597e)

About the importance of bug, there is no loss of data, only the formatting. I agree, however, to define the importance "high". No message warns the user. He may lose many formatting before realizing the problem, need to check many files and reluctant to get back into using it...

Regards
Pierre-Yves
Comment 8 Markus Mohrhard 2013-02-19 15:16:24 UTC
Bug seems to be fixed in 4.0! The bigger problem that is that I have no idea when I fixed it in master. It might just be a result of the last larger refactoring that I did in the handling of conditional formats.

I'll see if I find the commit.
Comment 9 9dz83inRE61FoLF 2013-02-19 17:52:24 UTC
Created attachment 75124 [details]
CF bug in 4.0 demonstration

The bug is *NOT* fixed in 4.0.0.3!

The attached file contains two CF cells:
- one cell with background color yellow, if the cell value is '1'
- one cell with background color green, if the cell value is '2'

Reproduce Bug:
1.) open CF_Bug.ods
2.) mark the two cells
3.) Edit-Delete Contents (only 'Text' and 'Numbers' selected) => Ok
4.) On one of the two cells choose Format -> Conditional Formatting
=> now the CF of the two cells is gone
Comment 10 Michel Rudelle 2013-02-19 17:57:21 UTC
(In reply to comment #8)
Hi Markus,

Because I still reproduce the issue with the attached file in comment #4, using the master 4.1, could you please explain what you mean by:

> Bug seems to be fixed in 4.0!

Bug reproduced with:
4.1.0.0.alpha0 Version + (Build ID: 6c89f2e74aa9b202d6d6a41a178ba9aadb2b10e) - Vista

Thank you very much for your attention to this matter.
Comment 11 Not Assigned 2013-02-20 15:29:51 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=41095e934bcd83e08a472c8fb53743cd3f8e926c

respect nDelFlags, fdo#57661



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 12 Michel Rudelle 2013-02-21 09:47:56 UTC
Tested with the latest daily build:
Version 4.1.0.0.alpha0+ (Build ID: 0a967d4cb4468785ed3d302104642353b93232f)
TinderBox: Win-x86@6, Branch:master, Time: 2013-02-21_00:36:59
(Vista)

The fix is ok with both attached files in comment #4 and comment #9, thank you!

Do you think we can get the patch earlier in the next version 4.0.1?
Comment 13 Jacques Guilleron 2013-02-28 20:16:46 UTC
Hi Markus,

Good work. I was waiting for your patch in
LO 4.0.1.1 or/and LO 4.0.2.0 

Wasn't it your project ?

Thanks for your answer.

Jacques Guilleron
Comment 14 Michel Rudelle 2013-03-07 15:31:22 UTC
@Markus
@libreoffice-bugs
Hi,
Many of us are very frustrated not to see the patch coming in next version. This bug is very boring and depreciate the huge progress you did in CF.
Please, push the fix as soon as possible to the current version.
Thank you very much for taking care of this.
Comment 15 Commit Notification 2013-03-11 08:44:39 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=a7eb431731761e72a343c29276cfbbea0e6f01ec&h=libreoffice-4-0

respect nDelFlags, fdo#57661


It will be available in LibreOffice 4.0.2.

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 16 Michel Rudelle 2013-03-12 09:32:39 UTC
Hi Markus,
Tested with today daily-build:
Version 4.0.2.0+ (Build ID: 9b6797ec124921a60e5d1d654139e0b82818a7c)
It works fine!
Thank you very much for your great job on new CF
Comment 17 Markus Mohrhard 2013-03-12 23:46:52 UTC
*** Bug 62267 has been marked as a duplicate of this bug. ***
Comment 18 p_kongstad 2013-03-14 10:12:49 UTC
I still see that pasting special only text, numbers and Date @ Time from Bug 62267 removing the conditional formatting and cannot be restored by undo.

Version 4.0.3.0+ (Build ID: 6e392dcccd1cd519dd84a084fb538e01a1959d0)
TinderBox: Linux-x86_64@31-Release-Configuration-RHEL5-Baseline, Branch:libreoffice-4-0, Time: 2013-03-13_10:41:47
Comment 19 Michel Rudelle 2013-03-14 18:44:14 UTC
(In reply to comment #18)
> I still see that pasting special only text, numbers and Date @ Time from Bug
> 62267 removing the conditional formatting and cannot be restored by undo.
> Version 4.0.3.0+ (Build ID: 6e392dcccd1cd519dd84a084fb538e01a1959d0)

@p_kongstad
[With Version 4.0.2.0+ (Build ID: 9b6797ec124921a60e5d1d654139e0b82818a7c)-Vista 32b]
I found something that looks like what you report but without loosing the CF:
Copying with "Paste only" or "Paste special" destroyed apparently the CF, but if you look with Manage you will see the CF is still there. Another way to confirm it is to save and reopen your file: the CF is still active.
I only find two ways to restore that:
1/ save an open
2/ with Manage > select the range > Edit > ok > ok (without doing anything)
Do you observe that too ?

@Markus
Maybe bug 62267 is not a duplicate of this one and should be reopened.
What is your idea ?
Thanks
Comment 20 p_kongstad 2013-03-14 22:16:05 UTC
On 03/14/2013 07:44 PM, bugzilla-daemon@freedesktop.org wrote:
>
> *Comment # 19 <https://bugs.freedesktop.org/show_bug.cgi?id=57661#c19> 
> on bug 57661 <https://bugs.freedesktop.org/show_bug.cgi?id=57661> from 
> Michel Rudelle <mailto:mchl.rdll@gmail.com> *
> (In reply tocomment #18  <show_bug.cgi?id=57661#c18>)
> > I still see that pasting special only text, numbers and Date @ Time from Bug
> > 62267 removing the conditional formatting and cannot be restored by undo.
> > Version 4.0.3.0+ (Build ID: 6e392dcccd1cd519dd84a084fb538e01a1959d0)
>
> @p_kongstad
> [With Version 4.0.2.0+ (Build ID:
> 9b6797ec124921a60e5d1d654139e0b82818a7c)-Vista 32b]
> I found something that looks like what you report but without loosing the CF:
> Copying with "Paste only" or "Paste special" destroyed apparently the CF, but
> if you look with Manage you will see the CF is still there. Another way to
> confirm it is to save and reopen your file: the CF is still active.
> I only find two ways to restore that:
> 1/ save an open
works for me
> 2/ with Manage > select the range > Edit > ok > ok (without doing anything)
> Do you observe that too ?
doesn't work for me
>
> @Markus
> Maybebug 62267  <show_bug.cgi?id=62267>  is not a duplicate of this one and should be reopened.
> What is your idea ?
If it is another bug you should reopen it.
> Thanks
> ------------------------------------------------------------------------
> You are receiving this mail because:
>
>   * You are on the CC list for the bug.
>
Comment 21 Rainer Bielefeld Retired 2013-03-21 06:13:46 UTC
The original report with sample 2013-02-08 10:07 UTC, Michel Rudelle is concerning lost CF of a cell range after all cell range (A3:C7 in sample document) has been delete with key <Del>. 

That problem was still reproducible with Server Installation of "LibO  4.0.0.3  - 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  
and is fixed for
Version 4.1.0.0.alpha0+ (Build ID: 094bab7f9097fba62800d3dd578bd42640d8c6e)
TinderBox: Win-x86@6, Branch:master, Time: 2013-03-18_01:35:56 (as already confirmed by several other users)

@9dz83inRE61FoLF:
Nobody said that the bug has been fixed for 4.0.0.3, so I do not understand you comment and why you added a sample document. 
If it shows the same results like  Attachment 74418 [details] in the same case it is superfluous, if it shows different results it is concerning a different bug and needs a different bug report. 
Additionally the original sample document is concerning a problem with a CF range, your document shows separate CF for separate cells, what is something different. I will obsolete the attachment for now to avoid worrying (I oserved some different behavior between CF ranges and CF single cells, bt none of these differences seems related to this bug here.

But of course it will be useful to check whether we have remaining problems with CF in separate cells. A quick test did not show remaining problems with above mentioned 4.1.0.0.alpha0+, but results should be checked for every backport. If results differ between the documents that would show that there is a separate bug needing an additional bug report with reference
<https://wiki.documentfoundation.org/QA-FAQ#How_to_use_attached_sample_documents_for_multiple_Bug_Reports>
to the obsoleted attachment.
Comment 22 Rainer Bielefeld Retired 2013-03-21 06:15:48 UTC
Comment on attachment 75124 [details]
CF bug in 4.0 demonstration

For some tests this document shows different results than in original sample (unrelated to reported bug), so I obsolete it for now to avoid worrying.
Comment 23 Rainer Bielefeld Retired 2013-03-23 09:22:37 UTC
*** Bug 57176 has been marked as a duplicate of this bug. ***
Comment 24 rh_libre 2013-04-08 17:17:15 UTC
This bug is fixed under Windows 7 32-bit Professional LibO Version 4.0.2.2 Version 4.0.2.2 (Build ID: 4c82dcdd6efcd48b1d8bba66bfe1989deee49c3)

Thank you very much for this fix, which solves part of my daily problems that occur when I use Delete on the keyboard to clear cell contents.

However, I still have a daily problem unresolved, which I believe is related to this bug:
Conditional Formatting is still lost when using Context Menu (i.e. Right click on a Windows machine), followed by Paste Only > Number to paste new numbers into cells containing CF (or using the Paste Only Value button which I've added to my toolbar)

I'll be filing a new bug report based on my CashCheck_test.ods spreadsheet from the closed duplicate bug number 57176 with a new version CashCheck_test2.ods

Thanks again for this fix though.
Comment 25 Michel Rudelle 2013-04-08 17:43:08 UTC
Hi rh
(In reply to comment #24)
> Conditional Formatting is still lost when using Context Menu (i.e. Right
> click on a Windows machine), followed by Paste Only > Number to paste new
> numbers into cells containing CF
Could you have a look at comment 19 before filing a new bug please?
I think you observe the same thing I saw.
If you confirm, perhaps it is more useful to reopen bug 62267, which I have not done, awaiting further advice.
Regards,
Comment 26 rh_libre 2013-04-08 19:01:45 UTC
(In reply to comment #25)
> Hi rh
> Could you have a look at comment 19 before filing a new bug please?
> I think you observe the same thing I saw.
> If you confirm, perhaps it is more useful to reopen bug 62267, which I have
> not done, awaiting further advice.
> Regards,

Sorry, Michel. Before reading this and digesting post 19, I just did open a new bug, Bug 63278, mainly to attach a new example file, CashCheck_test2.ods, with instructions to replicate.

I wish I'd read this bug thread, which duplicated my old one, a little more carefully, to realise that you'd also mentioned this point. If I had already read it, I probably assumed both would be fixed by the same patch.

I did indeed find that on my CashCheck_test2.ods file, going into Conditional Format/Manage/select cells K2:K13 and Edit, OK, OK was a way to successfully recover the CF display as you point out. I suspect I'll continue to use my own Format Painter workaround (see Bug 63278) as it's less effort, though if I forget, your method will recover the format even when I forget to preserve a cell without the lost formatting.

Hopefully the two bugs spring from the same cause and this Paste Only Number bug could be a quick win now that the Delete version of the CF bug is fixed.

Thanks again for fixing the Delete version of the bug.
Comment 27 Joel Madero 2013-04-27 17:59:46 UTC
Marking as Fixed as it seems like Markus has solved this one. 

@Markus - apologies if this is not the case
Comment 28 Michel Rudelle 2013-05-09 14:15:51 UTC
Hi,
Having worked a lot with CF for about 2 months, since version 4.0.2, I have never encountered a loss of CF (neither on a cell range nor a single cell).

On the other hand, I can confirm that the bug 62267 is not a duplicate (comment 17), and I reopen this other bug

This bug can therefore be closed if you agree with me.

Latest test:
Version 4.0.3.3 (Build ID: 0eaa50a932c8f2199a615e1eb30f7ac74279539)-Vista-32b