Bug 153913 - FILESAVE / FORMATTING: Failure to save / progressive loss of cell background colour of otherwise empty cells
Summary: FILESAVE / FORMATTING: Failure to save / progressive loss of cell background ...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.0.4 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:7.6.0 target:7.5.0.0.beta2 tar...
Keywords: filter:xls, filter:xlsx, notBibisectable, regression
Depends on:
Blocks: Cell-Format-Dialog
  Show dependency treegraph
 
Reported: 2023-03-02 00:05 UTC by Avogato
Modified: 2023-06-07 17:00 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Garden Spreadsheet (26.50 KB, application/vnd.ms-excel)
2023-03-02 00:10 UTC, Avogato
Details
ODF format of same sheet (30.23 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-03-02 12:45 UTC, Avogato
Details
ODF test file (12.41 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-03-02 12:52 UTC, Avogato
Details
А1 (292.40 KB, image/jpeg)
2023-05-19 07:42 UTC, Roman
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Avogato 2023-03-02 00:05:21 UTC
Description:
Repeatedly encountered an issue with the background cell colour not being saved in a .xls file when using Calc. I had been using LibreOffice 7.4.03 and have upgraded to 7.4.5.1 in the hope of fixing this issue but this did not help.

The spreadsheet I've been working on saves the background colour of some cells, but fails to save the background colour for other cells. I'm not entirely sure but I think the cells where it isn't saving the background colour are ones on lines where I inserted rows. However, this is not consistent behaviour if true; error seems to mostly affect sheet 2 and 3 of the file.

This appears to be less of a problem when saving the file as .xlsx but sometimes still causes the error.

Reliable workaround: If I write text in the cells with a background colour, the text and the background colour is saved.

Error persists in safemode.

Possible duplicate of Bug 46738.

Steps to Reproduce:
1) Set background colours for cells.
2) Save using the keyboard shortcut "Ctrl+s" or by clicking the save icon.
3) The file appears to have been saved (no longer shows the "unsaved" symbol).
4) Close the file.

Actual Results:
Reopen the file and the cells have reverted to no-background colour.

Expected Results:
Reopen the file and the cells retain formatting.


Reproducible: Sometimes


User Profile Reset: Yes

Additional Info:
Version: 7.4.5.1 (x64) / LibreOffice Community
Build ID: 9c0871452b3918c1019dde9bfac75448afc4b57f
CPU threads: 4; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded
Comment 1 Avogato 2023-03-02 00:10:32 UTC
Created attachment 185686 [details]
Garden Spreadsheet
Comment 2 Stéphane Guillou (stragu) 2023-03-02 07:02:22 UTC
Sounds like you are right about this being a duplicate of bug 46738, but please share an original ODS document for us to test, not the resulting XLS file.
Thank you!
Comment 3 ady 2023-03-02 12:02:32 UTC
(In reply to Stéphane Guillou (stragu) from comment #2)
> please share an original ODS document for us to test, not the resulting XLS
> file.

Why? The report explicitly says this is about saving to xls.

Steps to reproduce:
1. New empty spreadsheet with only 1 worksheet.
2. Set background color in A1.
3. Set background color in some other random cell, not A1.
3. Save as xls (97-2003) and exit.
4. Reopen. > background color is there for A1, but not for the other cell.

There seems to be some export to xls problem.

IDK whether to set to NEW, or DUPLICATE.
Comment 4 ady 2023-03-02 12:13:55 UTC
This also happens with xlsx. A1 OK, other cells not OK.

But, testing further (LO 7.4.5):

1. A1: background color.
2. Some other area also with background color.
3. Save as xls(x) and exit LO.
4. Reopen. > Part of the area is no longer with BC, part remains.
5. Save and exit.
6. Reopen. > Less area has BC.
7. Repeat 5 and 6. Each SAVE and exit leaves the xls(x) file with less background color.
Comment 5 Avogato 2023-03-02 12:45:51 UTC
Created attachment 185697 [details]
ODF format of same sheet

As mentioned in the ticket, I'd saved as .xls so the ODF is just a copy of the .xls rather than an original file.
Comment 6 Avogato 2023-03-02 12:52:29 UTC
Created attachment 185698 [details]
ODF test file

An ODS file with some words, some inserted rows indicated with words, cells with background colours both with and without text, and two sheets.
Comment 7 Stéphane Guillou (stragu) 2023-03-02 14:10:42 UTC
(In reply to ady from comment #3)
> (In reply to Stéphane Guillou (stragu) from comment #2)
> > please share an original ODS document for us to test, not the resulting XLS
> > file.
> 
> Why? The report explicitly says this is about saving to xls.
> 

Asking because I thought the issue happened when saving from ODS to XLS.

I could reproduce (somewhat inconsistently) with steps in comment 3 and 4.
I could also with the example XLS file:

1. Open attachment 185686 [details]
2. Go to sheet "Visual Growing Year"
3. Colour cell range N27:Y27
4. Save, then File > Reload

Result: coloured cells lose colours.

If you use the range L27:Y27 instead, the coloured range reduces to L27:O27 at first save + reload, then completely gone at second save + reload.

Testing some more on the same row, with subsequent save + reloads, not sure if it helps:

12 cells -> nothing
13 cells -> 2 left -> nothing
14 cells -> 4 left -> nothing
15 cells -> 6 left -> nothing
16 cells -> 8 left -> nothing
17 cells -> 10 left -> 3 left -> nothing

Not all rows behave like this.

Regression, because I could not reproduce in OOo 3.3:
OpenOffice.org 3.3.0
OOO330m20 (Build:9567)
...but might be a very early one, as Rainer points out in bug 46738. So not asking for a bibisect.

Happy to keep this one open because the other one doesn't mention the *progressive* loss of background colour at each save action.
Comment 8 ady 2023-03-02 15:14:31 UTC
(In reply to Stéphane Guillou (stragu) from comment #7)
> I could reproduce (somewhat inconsistently) with steps in comment 3 and 4.

Let me provide simple clear steps, just in case:

1. New Calc.
2. A1: > background color.
3. D10:E15 (or some other area) > background color.
4. G10:H15 (or some other area) > background color.
4.1 Add random areas as you wish with BC.
5. SAVE as XLS and/or XLSX.
6. Menu File > Reload. > See some area without background color now.
7. Save.
8. Menu File > Reload.

Depending on the areas that were previously selected to have background color, at some point in the repeating cycle of SAVE and reload the file shows no new reduction of the BC attribute.

IDK the exact pattern that actually "sticks" the attribute in the saved xls(x) file, but the behavior is consistently reproduced. What is not consistent is which exact area (or under which condition) gets the attribute actually saved "permanently" and which area loses the background color attribute when the file is saved and reloaded.
Comment 9 ady 2023-03-02 16:11:45 UTC
From bug 46738 comment 33 quote:

 - - -
So validation and cell formatting is now saved for the first 1000 empty rows after the last one with actual cell content. 
Of course this is a temporary measure, so let's keep this one open for a proper solution for arbitrarily placed cell formatting.
 - - -

Clearly the current situation is not "1000 empty rows", because I am only testing with some few rows, but the test is only a simplification, with no actual content, while the OP presents a case _with_ content. In either case we are not "1000" rows below the last cell with content.

The second part of that quote is intriguing. Maybe some additional steps were taken, or maybe nothing else was done. I am wondering which permanent or temporal measures are currently valid.
Comment 10 Roman 2023-05-19 07:41:39 UTC Comment hidden (off-topic)
Comment 11 Roman 2023-05-19 07:42:17 UTC Comment hidden (off-topic)
Comment 12 Roman 2023-05-19 07:42:55 UTC Comment hidden (off-topic)
Comment 13 Stéphane Guillou (stragu) 2023-05-19 07:58:49 UTC
(Roman, what you describe is bug 154044, a different issue. I marked the comments as "off-topic" to keep this report focused.)
Comment 14 Roman 2023-05-19 08:41:51 UTC
(In reply to Stéphane Guillou (stragu) from comment #13)
> (Roman, what you describe is bug 154044, a different issue. I marked the
> comments as "off-topic" to keep this report focused.)

Why is this off topic. I attached a screenshot showing that one column A has formatting applied, but not the rest. I understand this should be an addition and input to the mass case since more than 2 people are reporting this issue.
Почему это не по теме. я прикрепил скриншот на котором видно что в одном стобце A форматирование применилось, а на остальных нет. Я так понимаю  это должно быть дополнение и ввод в массовый случай поскольку более 2 человек регистрирует эту проблему.
Comment 15 Stéphane Guillou (stragu) 2023-05-19 10:21:00 UTC
(In reply to Roman from comment #14)
> Why is this off topic. I attached a screenshot showing that one column A has
> formatting applied, but not the rest.

I ran your steps, and the issue you describe is reproducible in 7.4 but not in 7.3. Therefore, it is the regression described in bug 154044 (the steps match exactly too).

The issue described here is different and started a long time ago. Maybe you got the two reports confused?
Comment 16 Roman 2023-05-19 12:41:21 UTC
Yes, I'm sorry about that topic
Comment 17 ady 2023-06-02 17:33:29 UTC
Is this still happening in 7.6.alpha?
Comment 18 Stéphane Guillou (stragu) 2023-06-02 18:13:58 UTC
(In reply to ady from comment #17)
> Is this still happening in 7.6.alpha?

I can't reproduce anymore using the steps in comment 8 or comment 7.

Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: a5c1c674e031087ef0516cebac049341dcdd2fcf
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded
Comment 19 Stéphane Guillou (stragu) 2023-06-07 17:00:16 UTC
Interestingly, I bibisected the fix to:

commit c57d113e9ef8608f5690e8707a97879cb4f6a185
author	Attila Szűcs <attila.szucs@collabora.com>	Tue Nov 29 09:45:36 2022 +0100
committer	Andras Timar <andras.timar@collabora.com>	Mon Dec 19 06:16:08 2022 +0000
tdf#151755 fix export of borders of contentless cells
Reviewed-on: https://gerrit.libreoffice.org/c/core/+/143880

I only checked using the XLS steps in comment 7, so please feel free to test other steps and filetypes to make sure the issue is indeed gone.

In any case, thanks Attila!