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 188.8.131.52 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.
Reopen the file and the cells have reverted to no-background colour.
Reopen the file and the cells retain formatting.
User Profile Reset: Yes
Version: 184.108.40.206 (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
Created attachment 185686 [details]
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.
(In reply to Stéphane Guillou (stragu) from comment #2)
> please share an original ODS document for us to test, not the resulting XLS
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.
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.
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.
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.
(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:
...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.
(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.
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.
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.
Incorrect work with applying styles and values
Steps to Reproduce:
1. Run Calc
2. Select more than 5 columns
3. Apply Border Borders
4. Press Ctrl+Z
5. look Calc_list
The undo action was performed only on the first 1 columns
Cancel all border selection
User Profile Reset: No
On version 220.127.116.11 Reverse history. And apparently it was corrected.
The entire sheet in such cases should be single, and not partial.
Version: 18.104.22.168 (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: default; VCL: win
Locale: ru-RU (ru_RU); UI: ru-RU
Calc: CL threaded
Created attachment 187390 [details]
User Profile Reset: Yes
(Roman, what you describe is bug 154044, a different issue. I marked the comments as "off-topic" to keep this report focused.)
(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 человек регистрирует эту проблему.
(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?
Yes, I'm sorry about that topic
Is this still happening in 7.6.alpha?
(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: 22.214.171.124.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
Interestingly, I bibisected the fix to:
author Attila Szűcs <email@example.com> Tue Nov 29 09:45:36 2022 +0100
committer Andras Timar <firstname.lastname@example.org> Mon Dec 19 06:16:08 2022 +0000
tdf#151755 fix export of borders of contentless cells
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!