Download it now!
Bug 130108 - FILESAVE XLSX Conditional Format not applied
Summary: FILESAVE XLSX Conditional Format not applied
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.3.4.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Caolán McNamara
URL:
Whiteboard: target:7.0.0 target:6.4.5 target:6.4.4
Keywords: bibisected, bisected, filter:xlsx, regression
Depends on:
Blocks: XLSX-Conditional-Formatting
  Show dependency treegraph
 
Reported: 2020-01-21 11:41 UTC by jpopelka
Modified: 2020-05-12 15:10 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Conditional format for a date cell (69.07 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2020-01-21 11:43 UTC, jpopelka
Details
Conditional format in ods file (95.33 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-01-22 09:00 UTC, jpopelka
Details
Better test file (9.94 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-02 08:00 UTC, Buovjaga
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jpopelka 2020-01-21 11:41:26 UTC
Description:
When I create conditional format for instance I want to color to red date in a cell that is greater than today and save the file in a xlxs format, next time I open the file the format is not saved. Yes it can be edited  but with saving it disappear again. When I save the file in ods form, everything is OK.
On attempt to open the xlxs file created with LibreOffice Calc, excel reports error on conditional format.

Steps to Reproduce:
1. Set conditional format for a cell and save the file as xlxs extension.
2. Open it in LO Calc, you will see that the format disappear
3. Save it in ods and open it in LO Calc, format is OK.

Actual Results:
As described above.

Expected Results:
I do not understand this item.


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 jpopelka 2020-01-21 11:43:32 UTC
Created attachment 157299 [details]
Conditional format for a date cell
Comment 2 Xisco Faulí 2020-01-21 12:18:45 UTC
I can't reproduce it in

Version: 6.5.0.0.alpha0+
Build ID: b92293b3943423324064a8513c2e114d18817179
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

it seems you're using an old version of LibreOffice.
Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Comment 3 jpopelka 2020-01-21 15:04:40 UTC
Well I have just installed the latest version as per recommendation but the problem described persists.
Comment 4 jpopelka 2020-01-21 15:06:33 UTC
Simply the conditional format is not saved in xlxs file just in ods.
Comment 5 m.a.riosv 2020-01-21 20:52:21 UTC
I can't see either with
Version: 6.4.0.2 (x64)
Build ID: 08d19fecdc7a2298d051e19cfdb7c35544855fc3
CPU threads: 4; OS: Windows 10.0 Build 19546; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US Calc: CL

Please can you attach the ods file working fine, to test saving as xlsx.
Comment 6 jpopelka 2020-01-22 07:14:47 UTC
Very glad to attach the ods file but please instruct me how to do it. I attached xlsx format when writing the first complaint but now I do not see a link for uploading files.
Comment 7 m.a.riosv 2020-01-22 08:56:25 UTC
On the top of the first comment there it is.
Add an attachment (proposed patch, testcase, etc.)
Comment 8 jpopelka 2020-01-22 09:00:16 UTC
Created attachment 157312 [details]
Conditional format in ods file

Conditional format in ods file is flawless
Comment 9 m.a.riosv 2020-01-22 09:09:25 UTC
No issue with:
Version: 6.4.0.2 (x64)
Build ID: 08d19fecdc7a2298d051e19cfdb7c35544855fc3
CPU threads: 4; OS: Windows 10.0 Build 19546; UI render: GL; VCL: win; 
Locale: es-ES (es_ES); UI-Language: en-US Calc: CL

Are you with 4.4.5.2 version?
It is very old, and a lot of improvements for xlsx compatibility have been done since. Please test with a newer version.
https://www.libreoffice.org/download/download/
Comment 10 jpopelka 2020-01-22 16:27:33 UTC
No no, I thougt that 4.4.5.2 is a Linux core, sorry.
Of course I use the latest version of LibreOffice
Comment 11 QA Administrators 2020-01-23 04:18:21 UTC Comment hidden (obsolete)
Comment 12 Buovjaga 2020-05-01 18:12:30 UTC
(In reply to jpopelka from comment #8)
> Created attachment 157312 [details]
> Conditional format in ods file
> 
> Conditional format in ods file is flawless

Not reproduced. Save to xlsx, reloaded and the condition list stays the same in Manage.

Jpopelka: to be clear, can we see the problem in LibreOffice or do we need to check in MS Excel?

Arch Linux 64-bit
Version: 7.0.0.0.alpha0+
Build ID: 23ddc3811d4d1890e4024f4e0f6bb5129a694fdc
CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: kf5; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 30 April 2020
Comment 13 Buovjaga 2020-05-02 08:00:08 UTC
Created attachment 160214 [details]
Better test file

The reporter initiated private contact via email. It made me realise that the original test file was not good, because it used "(date) value is greater than TODAY()", so the file became obsolete as time went on. It is better to use "less than".

I bibisected the problem to https://git.libreoffice.org/core/+/16309a9516c1f173056fc103c6428e74217c7927%5E!/
weld StylesPropertyPanel and SvxStyleToolBoxControl, etc.

The result sounds weird, but the thing is: this problem is not reproducible when converting to xlsx via command line! It only happens when using the gui.

So this always produced a good result for me:
instdir/program/soffice --convert-to xlsx ~/libobugs/vboxshare/conditionalformat.ods --outdir ~/libobugs/vboxshare
Comment 14 Buovjaga 2020-05-02 08:07:07 UTC
Bibisected with linux-64-6.5

Adding Cc: to Caolán McNamara

I know the (very recent) result conflicts with the fact that this issue was reported against 6.3, but that's just the way it is. I tried reverting the commit, but ran into merge conflicts.
Comment 15 Caolán McNamara 2020-05-02 16:59:24 UTC
A bug reported on 21 Jan, but a commit merged on 24 April identified, quite the mystery!
Comment 16 Buovjaga 2020-05-02 17:02:28 UTC
(In reply to Caolán McNamara from comment #15)
> A bug reported on 21 Jan, but a commit merged on 24 April identified, quite
> the mystery!

Sorry, I did not intend this to be some mindfsck - I just wanted to confirm *something* :D
Comment 17 Caolán McNamara 2020-05-02 19:13:24 UTC
a diff on the outputs between working and not-working seems to show a missing dxfs section, which I imagine is connected to sc/source/filter/excel/xestyle.cxx and XclExpDxfs
Comment 18 Caolán McNamara 2020-05-03 15:39:51 UTC
SfxStyleSheetBase* pStyle = rRoot.GetDoc().GetStyleSheetPool()->Find(aStyleName)

that "Find" turns into

SfxStyleSheetBase*          Find(const OUString& rStr)                                                                                          
{ return Find(rStr, nSearchFamily, nMask); }

i.e. uses the nSearchFamily and nMask set in the SfxStyleSheetBasePool
and I think that's basically arbitrary depending on whoever last used the SfxStyleSheetBasePool and called SetSearchMask etc on it.

If we used...

SfxStyleSheetBase* pStyle = rRoot.GetDoc().GetStyleSheetPool()->Find(aStyleName, SfxStyleFamily::Para) (like ScTable::CopyConditionalFormat does) then we'd get the 

virtual SfxStyleSheetBase*  Find( const OUString&, SfxStyleFamily eFam, SfxStyleSearchBits n=SfxStyleSearchBits::All );

variant, which has "All" set as the default 3rd arg

which sort of explains how the bisect could identify a seemingly unrelated commit which probably changed the timing of setting the arbitrary SfxStyleSheetBasePool values which got reused by the export and why it could happen in other cases prior to that
Comment 19 Commit Notification 2020-05-03 18:08:29 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/454a3c945fdc02d706b0a5ad49ca13e0443fa8e5

Resolves: tdf#130108 ensure we don't reuse the last SetSearchMask setting

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 20 Buovjaga 2020-05-03 18:16:10 UTC
Verified fix, thanks!

Arch Linux 64-bit
Version: 7.0.0.0.alpha0+
Build ID: 454a3c945fdc02d706b0a5ad49ca13e0443fa8e5
CPU threads: 8; OS: Linux 5.6; UI render: default; VCL: kf5; 
Locale: fi-FI (fi_FI.UTF-8); UI-Language: en-US
Calc: threaded
Built on 3 May 2020
Comment 21 Caolán McNamara 2020-05-03 18:20:45 UTC
fixed in master, backport to 6-4 in gerrit
Comment 22 Commit Notification 2020-05-03 19:29:42 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e3517eefed2d4767648cb52d4a9863908cadc3d8

Related: tdf#130108 expand single arg SfxStyleSheetBasePool::Find uses

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 23 Commit Notification 2020-05-04 09:25:55 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/c8a4fa577e5daec6520a810b4df753a67d9dd5a8

Resolves: tdf#130108 ensure we don't reuse the last SetSearchMask setting

It will be available in 6.4.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 24 Xisco Faulí 2020-05-04 11:02:07 UTC
Verified in

Version: 7.0.0.0.alpha0+
Build ID: 800085d4fb0831f2065e86bfd99164cd89998fcd
CPU threads: 4; OS: Linux 4.19; UI render: default; VCL: gtk3; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

@Caolán, thanks for fixing this issue!
Comment 25 Commit Notification 2020-05-04 16:17:37 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/0eac1549c9b6d84142238e62f629bd58321da727

related: tdf#130108: sc: Add unittest

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 26 Commit Notification 2020-05-05 09:58:01 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/8e53551db00d325dd638d45e50b32984dc778f17

Related: tdf#130108 ensure we don't reuse the last SetSearchMask setting

It will be available in 7.0.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 27 Commit Notification 2020-05-12 15:10:13 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-4-4":

https://git.libreoffice.org/core/commit/636e1dff386d130e46ab39417d0d447241f33dde

Resolves: tdf#130108 ensure we don't reuse the last SetSearchMask setting

It will be available in 6.4.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.