Bug 85895 - FILESAVE, FILEOPEN, FORMATTING: Conditional formatting lost after copying sheet, then saving/reopening file
Summary: FILESAVE, FILEOPEN, FORMATTING: Conditional formatting lost after copying she...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: x86-64 (AMD64) Linux (All)
: high critical
Assignee: Umar
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
Reported: 2014-11-05 07:21 UTC by smkbot
Modified: 2017-07-17 09:25 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:

Small example spreadsheet that can be used to reproduce this bug. (17.87 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-05 07:21 UTC, smkbot
Mark list (95.26 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2015-01-19 22:23 UTC, Umar
spred sheet file om which the fault occurs (13.25 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-02-17 12:46 UTC, Heinrich Seebauer
sc1 - spread sheet w/o formatting (72.58 KB, image/png)
2015-02-17 12:48 UTC, Heinrich Seebauer
sc2 - conditional formatting detailed (89.44 KB, image/jpeg)
2015-02-17 12:53 UTC, Heinrich Seebauer
sc-3 cond. formatting corretly working (70.32 KB, image/jpeg)
2015-02-17 12:54 UTC, Heinrich Seebauer
sc-4: spreadsheet after save/close/load (1.84 MB, image/jpeg)
2015-02-17 12:56 UTC, Heinrich Seebauer
sc_5: manually created cond. formattng for A1 preserved (83.72 KB, image/jpeg)
2015-02-17 12:58 UTC, Heinrich Seebauer
sc_6: copied format not preserved (84.17 KB, image/jpeg)
2015-02-17 12:59 UTC, Heinrich Seebauer
Spdsht: Manually entered cond. form. and it is preserved (18.52 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-02-17 13:02 UTC, Heinrich Seebauer

Note You need to log in before you can comment on or make changes to this bug.
Description smkbot 2014-11-05 07:21:10 UTC
Created attachment 108928 [details]
Small example spreadsheet that can be used to reproduce this bug.

I'm using LibreOffice version on Linux Mint 17 "Qiana" MATE. I do not know if this bug reproduces in the current version of LibreOffice but it should not be difficult to check.

Steps to reproduce this bug:

1. Create a LibreOffice Calc sheet with conditional formatting.
2. Duplicate the sheet in the workbook, placing the copied sheet before the sheet to be copied.
3. Save the spreadsheet to a file.
4. Re-open the spreadsheet in LibreOffice Calc.
5. Observe the conditional formatting is lost for the duplicated sheet.

The attached spreadsheet is a simple example that can be used to reproduce the above bug, but any spreadsheet with conditional formatting should work (as far as I've observed).

Interestingly, conditional formatting does *not* seem to be lost if the destination for the copied sheet is placed after the source sheet (step 2 in the repro steps above).

I'm setting the severity to critical because this causes loss of data. Please adjust as necessary.
Comment 1 smkbot 2014-11-05 07:22:47 UTC
Hm, the attachment says it's text/plain, but it's an OpenDocument spreadsheet. If you want to use it, save it as a file with an .ods extension before opening it.
Comment 2 Cor Nouws 2014-11-05 08:43:59 UTC
Comment on attachment 108928 [details]
Small example spreadsheet that can be used to reproduce this bug.

corrected mime type
Comment 3 Cor Nouws 2014-11-05 08:48:48 UTC
Hi smkkbot,
THanks for the report.
It works fine for me in - the latest version.
Did not try in other versions, but this looks as fixed since the that you used.
Could you check that please?
Comment 4 smkbot 2014-11-05 21:17:33 UTC
I just tried with version (Build ID: 430m0(Build:2)) and I cannot reproduce the bug, so it seems fixed in the latest version.
Comment 5 Cor Nouws 2014-11-05 21:35:33 UTC
Thanks for checking indeed.
Usually we then set 'WorksForMe' (and fixed when we know the specific commit, or mark as Duplicate when we know which issue.. But that's not necessary and consumes time ;) )
Comment 6 Umar 2015-01-19 22:23:58 UTC
Created attachment 112502 [details]
Mark list

Conditional foramtting is changed after reopening the file.
In TAR sheet, i used condional formatting for AH4:AH7. It works. But when i reopen the same file after saving, it changes to some other condition that i used for some other cells.
Comment 7 Heinrich Seebauer 2015-02-17 12:44:53 UTC
Think, I observed similar behaviour

Software	LibreOffice Calc
Build ID	430m0(Build:2)
OS    		OpenSuse Linux 13.2
Kernel Version	3.16.7-7-desktop
Desktop		KDE 4.14.4

Conditional formatting copied/paintbrushed is lost during save/reload.

Applied conditional formatting to a single cell, copied format (paintbrush, insert special) to other cells. The conditional format works at first, but is partially destroyed after save/close/reload file.

steps to reproduce (sc_i = screenshot i, attached):

- create spreadsheet in calc
- insert numbers 1...10 in column A (see sc_1)
- create conditional formatting for the first of these numbers
  Menu/Format/Conditional Formatting/Condition..., (see sc_2)
  actual parameters used: value >5, new format "higher number"

copy format to the rest of the cells (2...10), using format paintbrush or insert special - formats only. (see sc_3)

save & close spreadsheet (file name "spdsh_con_form_bug.ods")

reopen spreadsheet

observed: conditional formatting is not shown (see sc_4)
expected: (example attached) all numbers > 6 written in light red

inspecting the cells' format
A1: conditional formatting still there (see sc_5)
A2: conditional formatting no longer (see SC_6)
ditto for A3...A10

The conditional formatting can be reapplied, but is lost during save/reload

I tried several approaches to track down the conditions under which the fault occurs.

I could tell no difference between copying the format by paintbrush or copy/past special (format only).

I expected the fault to act deterministically, but instead found some interesting deviations. The formatting is not always lost on all cells. Unfortunately I could not determine, which circumstances influenced the results.

Manually entering the cond. form. seems to work OK!
I used spdsh_con_form_bug_v2.ods (attached) to manually enter the conditional formatting, instead of copying it. During some save/reload cycles the sheet seemed stable.

If I can help further, just let me know.
Comment 8 Heinrich Seebauer 2015-02-17 12:46:03 UTC
Created attachment 113452 [details]
spred sheet file om which the fault occurs
Comment 9 Heinrich Seebauer 2015-02-17 12:48:06 UTC
Created attachment 113453 [details]
sc1 - spread sheet w/o formatting
Comment 10 Heinrich Seebauer 2015-02-17 12:53:44 UTC
Created attachment 113454 [details]
sc2 - conditional formatting detailed

shows the options chosen for "font effects"
Comment 11 Heinrich Seebauer 2015-02-17 12:54:48 UTC
Created attachment 113455 [details]
sc-3 cond. formatting corretly working

all numbers >5 are correctly displayed in red
Comment 12 Heinrich Seebauer 2015-02-17 12:56:39 UTC
Created attachment 113456 [details]
sc-4: spreadsheet after save/close/load

The consitional formats (at least for numbers >5) are not enacted
Comment 13 Heinrich Seebauer 2015-02-17 12:58:00 UTC
Created attachment 113457 [details]
sc_5: manually created cond. formattng for A1 preserved

sc_5: manually created cond. formattng for A1 preserved during save/close/relad
Comment 14 Heinrich Seebauer 2015-02-17 12:59:21 UTC
Created attachment 113458 [details]
sc_6: copied format not preserved

sc_6: copied cond. formatting not preserved during save/close/reload
Comment 15 Heinrich Seebauer 2015-02-17 13:02:13 UTC
Created attachment 113459 [details]
Spdsht: Manually entered cond. form. and it is preserved

This spreadsheet contains conditionally formatted cells, but the format has not been copied, but been entered manually.
In this case the formats are preserved.
Comment 16 royerjy 2016-04-14 21:20:45 UTC Comment hidden (off-topic)
Comment 17 Justin L 2017-02-03 11:31:25 UTC
(In reply to Heinrich Seebauer from comment #7)
Fixed in bibisect50max for bug 86253, backported to 4.4.0 and 4.3.6:
    commit 1e4fc94c7feff11f28654dcf70cccf77db216143
    Author: Markus Mohrhard <markus.mohrhard@collabora.co.uk>
    CommitDate: Sun Nov 30 00:47:33 2014 +0100
        we need to copy the condition format cell for cell, fdo#86253