Bug 80768 - Cell ranges with the same Conditional Formatting should be consolidated to 1 range.
Summary: Cell ranges with the same Conditional Formatting should be consolidated to 1 ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.4.2 release
Hardware: All All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
: 84036 90025 90346 (view as bug list)
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2014-07-01 18:41 UTC by bonbon
Modified: 2018-08-21 20:57 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
Reproduction steps (846.47 KB, image/jpeg)
2014-07-02 07:58 UTC, bonbon
Details
Test File (10.93 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-07-04 15:23 UTC, ign_christian
Details

Note You need to log in before you can comment on or make changes to this bug.
Description bonbon 2014-07-01 18:41:04 UTC
Note* This is very big problem for our accounting department.

Problem description: 

Steps to reproduce:
1. editing, drag to fill cells
2. crash,begin to automatic recovery
3. after recoved successfully, at this momento we found two things lost:
a.)password setted lost, it´s now under no protection,
b.)conditioning formatting lost.

Current behavior: do them again

Expected behavior: fix these bugs.

              
Operating System: Windows 8
Version: 4.1.4.2 release
Comment 1 Julien Nabet 2014-07-01 20:42:15 UTC
Could you give a try to last LO stable version 4.2.5?
Comment 2 bonbon 2014-07-02 01:35:11 UTC
OK. I´ll try it.Tks.
Comment 3 bonbon 2014-07-02 01:44:41 UTC
I´m trying version 4.2.5 now. One bug still. When no crash, no error. I just drag one single cell, then the conditioning formatting is changed.

E.g. original conditioning formatting:
A2:V300-------cell including "?"

But it´s changed to like this:
A2:V6;A8:V12;A7:D7;F7:V7;A14:V300;A13:D13;F13:V13-------cell including "?"
E7-------cell including "?"

Cells are fixed, seems like change as they like. I´m confusing.

Continue trying.
Comment 4 Julien Nabet 2014-07-02 05:34:35 UTC
bonbonbillo: keep in mind that 1 bug => 1 bugtracker.
So let's focus on this current one.
If you still reproduce this one with 4.2.5, please attach an example file (keep in mind too that any attachment is automatically made public so remove any confidential/private part) + give minimum step by step process to reproduce the problem.
Comment 5 bonbon 2014-07-02 07:58:32 UTC
Created attachment 102115 [details]
Reproduction steps

Attached snapshot of conditioning formatting changes, you can try in any calc files or just creat a new calc file to check. I just creat a new file to reproduce steps and how conditioning formattings change.
Comment 6 bonbon 2014-07-02 07:59:54 UTC
You can see, if we keep editting the file, then the conditioning formatting will keep changing.
Comment 7 bonbon 2014-07-02 18:01:12 UTC
Stable Version 4.2.5,
Bugs not fixed, when crash still lost password, and conditioning formatting still changing when drag cells or do copy/paste cells.
Comment 8 Julien Nabet 2014-07-02 18:47:53 UTC
Thank you for the feedback.
Sorry, I don't get the process, I'll let other people take a look.
Therefore I put it to UNCONFIRMED.
Comment 9 bfoman (inactive) 2014-07-03 19:05:40 UTC
Kevin: sorry to ask to check again, but could you please verify the latest 4.3.0.2 RC version available currently at http://dev-builds.libreoffice.org/pre-releases/win/x86/?
Comment 10 bonbon 2014-07-04 09:51:06 UTC
Just tried 4.3.0.2. Same problems.
Comment 11 bonbon 2014-07-04 09:55:22 UTC
You can also try by yourself. Just creat any test calc file, fill anything, then creat any conditioning formatting and drag any cells, then back to see the conditionging formatting has been changed.
Just do like the file I attached before.
These two bugs are not yet fixed.
Comment 12 ign_christian 2014-07-04 15:23:39 UTC
Created attachment 102275 [details]
Test File

Hi Kevin.. 

Honestly it's quite hard to understand your report. Need some magic to understand that.. :)

I try to provide a simple test case to test conditional format change. As Julien said, 1 bug / report, so we don't talk about crash & lost password here.

In the attached file we only write 1 conditional format: 
Range A1:B6 > Cell value is : greater than : 2 > Apply Style : Heading

Step to reproduce:
1. Open attached file & check there is only 1 condition: A1:B6 Cell value is > 2
2. Move/drag A1:A3 to B4:B6
3. Check that there are 2 conditions:
   - A4:A6;B1:B3 Cell value is > 2
   - B4:B6 Cell value is > 2
4. Undo (ctrl-z)
5. Check that there are 3 conditions:
   - A1:A3 Cell value is > 2
   - A4:A6;B1:B3 Cell value is > 2
   - B4:B6 Cell value is > 2

Em..looks like 2 bugs in above procedures: drag & undo. I have feeling that it's an old bug.

*) Tested with LO 4.2.5.2 - Ubuntu 12.04 x86

@Kevin, please confirm if that's your problem?
Comment 13 ign_christian 2014-07-04 16:38:06 UTC
(In reply to comment #12)
> Em..looks like 2 bugs in above procedures: drag & undo. I have feeling that
> it's an old bug.

Perhaps the only problem is "undo" after reading Gerard's comment:
https://bugs.freedesktop.org/show_bug.cgi?id=71940#c16

Similar with pasting, dragging doesn't make conditional formats wrong, just splitting the condition. As we can see the conditions still valid after dragging.
Comment 14 bonbon 2014-07-05 11:17:04 UTC
Shour be drag and undo problem, not only undo.
Comment 15 ign_christian 2014-07-06 05:41:57 UTC
@Julien,bfoman: what do you think? I think it's only "undo" bug. 

I can't compare it with AOO & Kingsoft since they don't have "Manage Conditional Formatting" feature. Don't know with MSO?
Comment 16 Rainer Bielefeld Retired 2014-07-08 06:42:10 UTC
The report summery is concerning "things" with a "crash", but the "steps comic" seems not to show a crash? 

LibreOffice test with a little variation to Comment 12
1. Open Sample Document
2. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   There is a common CF for A1:B6
3. Close CF dialog
4. Select A1:A3  ► Drag and Drop to C4:C6
5. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   The result is that CF for A4:B6 still does exist, CF for B1:B3
   still does exist, CF of A1:A3 has been moved together with contents
   to C4:C6
   Everything ok!
6. Click Undo icon
   As Expected cell contents jumps back to A1:A3
7. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   CF A1:A6 from step 2 still / again does exist
      (minor problem: splitted into 2 ranges although A1:A3 and A4:A6
       have the same CF)
   CF B1:B3 from step 2 still / again does exist
   CF B4:B6 from step 2 still / again does exist
   Everything as expected

First LibreOffice test with a little variation to Comment 12
11. Open Sample Document
12. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   There is a common CF for A1:B6
13. Close CF dialog
14. Select A1:A3  ► Drag and Drop to B4:B6
15. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   The result is that CF for A4:B6 still does exist, CF for B1:B3
   still does exist, CF of A1:A3 has been moved together with contents
   to B4:B6
   Everything ok, only minor problem that LibO does not recognize that
   A4:A6 and B4:B6 have the same CF and that that range could be shown
   as one range A4:B6 with common CF
16. Click Undo icon
   As Expected cell contents jumps back to A1:A3
17. menu {Format  ►  Conditional Formatting  ►  Manage}: 
   CF A1:B6 from step 2 still / again does exist
   Minor problem: splitted into 4 ranges although All cells A1:B6
   have the same CF

So CF is completely ok, but problem is that Range CF range A1:B6 has been 
devided / fragmented into 4 ranges
My observations are the same as in Comment 12, but my conclusion is a little different.

On a different wIN7 PC with LibreOffice 4.0.0 I found that {Format  ►  Manage} had a bug there: In step 17 CF for A1:A3 was destroyed and after adding a new CF for A1:A3 that was not shown in {Format  ►  Manage}. But that problem does no longer exist.

So here I only see a possible enhancement: Libo should recognize if cell ranges with the same CF could be consolidated to 1 Cell range.

Instead of continuing to try to find out what the reporter's real problem might have been bugs different to the summary I now created should be reported separately.

@ign_christian
You did not state clearly what of your observations you consider as a bug. Fragemntation of range with all the same CF or something else
Comment 17 ign_christian 2014-07-08 09:22:11 UTC
You're right Rainer. All CF still valid after undo. Just look complicated for us if we have many CF and then undoing.

> So here I only see a possible enhancement: Libo should recognize if cell
> ranges with the same CF could be consolidated to 1 Cell range.
Please consider what I said as "bug" before is an "enhancement" :)

So we can mark NEW following your extensive review.
Comment 18 bonbon 2014-07-08 10:36:22 UTC
(In reply to comment #16)
 Exactly rainer. All CF does exist and work, but all into a mess. so I have to keep correcting them to make me understand correctly.
Comment 19 ign_christian 2014-07-09 07:54:55 UTC
*** Bug 81086 has been marked as a duplicate of this bug. ***
Comment 20 ign_christian 2014-09-19 06:24:56 UTC
*** Bug 84036 has been marked as a duplicate of this bug. ***
Comment 21 m.a.riosv 2015-03-30 23:16:57 UTC
*** Bug 90346 has been marked as a duplicate of this bug. ***
Comment 22 m.a.riosv 2015-03-31 11:57:54 UTC Comment hidden (obsolete)
Comment 23 bonbon 2015-03-31 17:13:21 UTC
not yet resolved until today.

When we set a conditional formatting suck like "B2:B200   value >2500", then if I edit more steps such like drag to fill some cells...etc, or others... 
then my conditional formatting will change to be something like this:
B2                                    >2500
B13:B16                               >2500
B64:B66;B59:B61                       >2500
...
...
...
...
...
...
...
...
...

more steps editted, more formatting will appear automatically, and finally the unconditional formatting editor appears in a big mess.
I thought although the uncdonditional formatting editor now is very dificult to see in order, but cells´ value in the workshee of calc are still correct. So I just let it be.

However disaster comes soon, one day I just find the worksheet goes very very very slowly and crashs till I can not use it any more. I think maybe it´s caused by the mess of unconditional formatting. So I start to organize hundreds of unconditional formattings that produced by calc automatically, now it´s only this one again in my unconditonal formatting editor like "B2:B200         value>2500", and this worksheet comes back to go fast again.

My libreoffice Version is 4.4.1.2. I hope this bug could be fixed as soon as possible. Best wishs!
Comment 24 Matthew Francis 2015-04-27 02:51:56 UTC
*** Bug 90025 has been marked as a duplicate of this bug. ***
Comment 25 helplibreoffice 2015-05-07 03:50:05 UTC
Given Kevin's description and additional details, should this be changed from an enhancement to another level of importance?

Also, Kevin's words state "not yet resolved until today".  If I am reading his intent correctly, I believe he actually means "Still not resolved".
Comment 26 bonbon 2015-05-07 18:00:46 UTC
(In reply to helplibreoffice from comment #25)
> Given Kevin's description and additional details, should this be changed
> from an enhancement to another level of importance?
> 
> Also, Kevin's words state "not yet resolved until today".  If I am reading
> his intent correctly, I believe he actually means "Still not resolved".

Yes,"Still not resolved".
Comment 27 helplibreoffice 2015-10-23 07:09:46 UTC
Confirm bug still present in LO Calc 5.0.2 (release).

Tested with Win 7 SP1.
Comment 28 bonbon 2015-10-23 08:25:52 UTC
Yes, still crash when drag to fill cells.
Comment 29 mrelwood 2015-11-27 23:19:57 UTC
Confirmed still on LibreOffice 5.0.3.2 on OSX 10.11.1 El Capitan, filetypes .ods, .xls and .xlsx.

In my mind the main issue is that LibreOffice doesn't consolidate Conditional Formatting ranges. As a result the document will become slow after several edits.

As another result some of the conditions will eventually fail, and formatting no longer takes place. It takes dozens of row movings for it to happen, so I can't track steps to reproduce, but it always happens eventually. Consolidating the ranges should fix this also.
______________________________________________

My attempt for simple and clear steps to reproduce the issue:

1. Type numbers 1-9 to cells A1-A9 respectively in a new Spreadsheet document.
2. Create a Conditional Formatting rule for range A1:A9, for example:
   Cell value less than 5 -> Apply style "Heading".
3. Move row 7 to replace row 3.
4. Delete the now empty row 7.

Now check the Conditional Formatting Manager. There are now two conditions: First for only the cell A3, the second for ranges "A7:A8;A4:A6;A1:A2". Instead, there should only be one condition, for range "A1:A8".
Comment 30 Markus Mohrhard 2016-09-19 21:37:12 UTC
This is not a bug. There is somewhere an enhancement request to allow combining equal conditional formats but doing it automatically is surely completely wrong and will cause bugs.
Comment 31 Lenge 2016-09-19 22:34:42 UTC
(In reply to Markus Mohrhard from comment #30)
> This is not a bug. There is somewhere an enhancement request to allow
> combining equal conditional formats but doing it automatically is surely
> completely wrong and will cause bugs.
While one can argue about this being a bug or rather an enhancement, one thing is clear: NOT automatically combining DOES cause bugs (as seen in the issues marked as duplicates of this bug). So lowering the priority is no good signal IMHO.

There are two fundamental approaches to tackle these bugs:
(1) Automatically combining equally formatted cells (like this bug proposes), or
(2) eliminate the concept of conditionally formatted cell ranges and make all conditional formatting a normal per-cell property.
The first approach is probably somewhat harder to implement, yet the second one might add more bloat if many cells use the same conditional formatting.

However, a separate function for the combining is still a good idea. It'd be guaranteed to not break things unless explicitly called. And combined with an option to call it automatically after any changes to conditional formatting, it would probably satisfy everyone.
Comment 32 bonbon 2016-09-20 08:21:39 UTC
Exactly. I now upgrade to the latest version 5.2.0.4. It´s not solved yet. I wonder if the team is solving this prolem o never like to.
Comment 33 bonbon 2016-09-20 08:23:09 UTC
two years passed, nothing happened!:( this team.
Comment 34 Simon 2018-02-12 23:08:49 UTC
Yeah, new fancy design. But no time for function?
Please give the user the opportunity to press a button to combine same rules.
Cant belief that it takes more than 3,5 years even in Version 6+ the bug is there. Give this bug a higher importance than low, please.
Comment 35 Roman Kuznetsov 2018-08-20 19:31:21 UTC
this bug was fixed in bug 95295
Comment 36 Lenge 2018-08-21 20:57:18 UTC
Really fixed? From the description, it seems more related to bug 87274 than bug 95295.