Bug 120749 - Conditional Formatting CELLIS and BEGINSWITH not working correctly (Xlsx)
Summary: Conditional Formatting CELLIS and BEGINSWITH not working correctly (Xlsx)
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.1.0.3 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Tibor Nagy (NISZ)
URL:
Whiteboard: target:7.2.0 target:7.1.0.2
Keywords: filter:xlsx
Depends on:
Blocks: XLSX-Conditional-Formatting
  Show dependency treegraph
 
Reported: 2018-10-21 06:52 UTC by kifni41
Modified: 2021-01-26 15:39 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
sample file (6.45 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-21 13:09 UTC, Xisco Faulí
Details
Document C as mentioned in comment 4 (9.82 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-22 09:17 UTC, Winfried Donkers (retired)
Details
Document D as mentioned in comment 4 (5.51 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-11-22 09:18 UTC, Winfried Donkers (retired)
Details
Screenshot of the document C in Excel and Calc side by side (71.18 KB, image/png)
2019-07-17 08:13 UTC, Gabor Kelemen
Details
Extended version of the document C (13.78 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2019-07-17 10:02 UTC, Gabor Kelemen
Details
Screenshot of the attachment #152826 in Excel and Calc (115.41 KB, image/png)
2019-07-17 10:04 UTC, Gabor Kelemen
Details
Current look of attachment #152826 (106.66 KB, image/png)
2020-12-18 10:06 UTC, NISZ LibreOffice Team
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kifni41 2018-10-21 06:52:38 UTC
Description:
This bug actually come from PHPSpreadsheet issue
https://github.com/PHPOffice/PhpSpreadsheet/issues/531#issuecomment-426942122

After i find some walkaround, i tried to open the file with LibreOffice Calc, the condition formatting not working. 
But when i tried to open the file with other apps (Google Sheet and WPS Office), the  conditional formatting works good.

Here's the file 

https://www.dropbox.com/s/gkj18za4wi1p95b/Issue531PhpSpreadSheetFileTest.xlsx?dl=0

I tried to check some reference about XML format for office. It's seems this xml sniplet should be the correct way to set conditional formatting with CELLIS and BEGINSWITH : 

<conditionalFormatting sqref="A1:Z1048576"><cfRule type="cellIs" dxfId="0" priority="1" operator="beginsWith"><formula>&quot;-&quot;</formula></cfRule></conditionalFormatting>

I tried to fix PHPSpreadSheet bug, and tried to generate that XML sniplet for condition formatting.



Steps to Reproduce:
1. Generate file with conditional formatting CELLIS and BEGINSWITH rule
2. Open the file in Libre Office Calc


Actual Results:
Condition formatting CELLIS and BEGINSWITH not working correctly

Expected Results:
Condition formatting CELLIS and BEGINSWITH should works correctly


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Xisco Faulí 2018-11-21 13:09:09 UTC
Created attachment 146869 [details]
sample file
Comment 2 Xisco Faulí 2018-11-21 13:17:48 UTC
I get an 'Excel found unreadable content' warning with Excel 2010 and the results is the same as in LibreOffice 6.2
Comment 3 raal 2018-11-21 19:49:38 UTC
(In reply to Xisco Faulí from comment #2)
> I get an 'Excel found unreadable content' warning with Excel 2010 and the
> results is the same as in LibreOffice 6.2

Same at my PC- excel error:Removed Records: Conditional formatting from /xl/worksheets/sheet1.xml part
Comment 4 Winfried Donkers (retired) 2018-11-22 09:16:35 UTC
The document (A) provided in Description (DropBox link) and  attachment 146869 [details] have the conditional format saved in xml as:
<conditionalFormatting sqref="A1:Z1048576"><cfRule type="cellIs" dxfId="0" priority="1" operator="beginsWith"><formula>&quot;-&quot;</formula></cfRule></conditionalFormatting>

When document A is opened in Calc (both Windows version 6.1.2 and Linux current master), the range is there, but the condition is gone.
When saved without changes from calc as xlsx, the entire conditional format is gone in xml.

When document A is opened in Calc, with manually added condition and format and saved as xlsx, the conditional format saved in xml is: 
<conditionalFormatting sqref="A1:Z1048576"><cfRule type="beginsWith" priority="2" operator="beginsWith" aboveAverage="0" equalAverage="0" bottom="0" percent="0" rank="0" text="-" dxfId="0"></cfRule></conditionalFormatting>

When document A is opened in Excel2016, Excel reports a defective item and removes the conditional format from the document.

When  document A is opened in Excel 2016, with manually added condition and format and saved as xlsx (document B), the conditional format saved in xml is:
<x14:conditionalFormattings><x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><x14:cfRule type="beginsWith" priority="1" operator="beginsWith" id="{FC07225B-9C22-40C8-9EA5-FCC1FE7CF374}"><xm:f>LEFT(A1,LEN("-"))="-"</xm:f><xm:f>"-"</xm:f><x14:dxf><font><color rgb="FFFF0000"/></font></x14:dxf></x14:cfRule><xm:sqref>A1:Z100</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings>
When opening again document B in Excel2016, Excel reports a defective item and removes a conditional format from the document, but not the manually added one.

When using a new, empty, document (C) in Excel, entering some cell values, with manually added condition and format and saved as xlsx, the conditional format saved in xml is:
<x14:conditionalFormattings><x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main"><x14:cfRule type="beginsWith" priority="1" operator="beginsWith" id="{8885F9B9-BB9A-4A93-9151-3D8DE3DD112F}"><xm:f>LEFT(A1,LEN("-"))="-"</xm:f><xm:f>"-"</xm:f><x14:dxf><font><color rgb="FFFF0000"/></font></x14:dxf></x14:cfRule><xm:sqref>A1:E2</xm:sqref></x14:conditionalFormatting></x14:conditionalFormattings>
When opening again document C in Excel2016, it opens fine.
When opening document C in Calc 6.1.2 (Windows), it opens, but the condition and format are lost, only the range of the conditional format is imported.

When using a new, empty, document (D) in Calc 6.1.2, entering some cell values, with manually added condition and format and saved as xlsx, the conditional format saved in xml is:
<conditionalFormatting sqref="A1:C2"><cfRule type="beginsWith" priority="2" operator="beginsWith" aboveAverage="0" equalAverage="0" bottom="0" percent="0" rank="0" text="-" dxfId="0"><formula>LEFT(A1,LEN(&quot;-&quot;))=&quot;-&quot;</formula></cfRule></conditionalFormatting>
When opening document D in Excel2016, it opens fine.
When opening again document D in Calc 6.1.2 (Windows), it opens fine.

I will attach documents C and D.

Summary:
It appears that the original document (A) is defective (not caused by Calc), cfRuleType has a unusual value.
It also appears that import of conditional formats from xlsx as created by Excel into Calc does not function properly.
Comment 5 Winfried Donkers (retired) 2018-11-22 09:17:35 UTC
Created attachment 146900 [details]
Document C as mentioned in comment 4
Comment 6 Winfried Donkers (retired) 2018-11-22 09:18:16 UTC
Created attachment 146901 [details]
Document D as mentioned in comment 4
Comment 7 Joel Hilsenrath 2019-06-26 06:02:23 UTC
Some of the Conditional Formatting settings will only save correctly in the .ods file format.
Comment 8 Gabor Kelemen 2019-07-17 08:13:07 UTC
Created attachment 152821 [details]
Screenshot of the document C in Excel and Calc side by side

Can confirm that the beginswith condition is not imported correctly if the value is: ="-"

Version: 6.4.0.0.alpha0+ (x86)
Build ID: 49422a469646ad8be43ba828ca24c2484c26b9e8
CPU threads: 4; OS: Windows 6.3; UI render: default; VCL: win; 
TinderBox: Win-x86@42, Branch:master, Time: 2019-07-08_00:50:19
Locale: hu-HU (hu_HU); UI-Language: en-US
Calc: CL
Comment 9 Gabor Kelemen 2019-07-17 10:02:38 UTC
Created attachment 152826 [details]
Extended version of the document C

The "Given text" type can have 4 conditions: contains, does not contain, begins with, ends with. 
I tried to set these for the 4 basic arithmetic operators, and looks like the plus and minus have a special escaping: ="-" and ="+" that we do not import correctly.
Comment 10 Gabor Kelemen 2019-07-17 10:04:30 UTC
Created attachment 152827 [details]
Screenshot of the attachment #152826 [details] in Excel and Calc
Comment 11 NISZ LibreOffice Team 2020-12-18 10:06:49 UTC
Created attachment 168282 [details]
Current look of attachment  #152826 [details]

Looks better in:

Version: 7.2.0.0.alpha0+ (x64)
Build ID: e97a81e94511b52987a50b7bdb72c922899da588
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: en-US
Calc: CL

after bug #122102 the conditions of "contains" and "does not contain" are imported.

Still missing are the "begins with" and "ends with" conditions in cells B10 ; B14 ; C10 ; C14
Comment 12 Commit Notification 2020-12-29 17:28:52 UTC
Tibor Nagy committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/879ea123514ce6808212dfbef7e1d226e9d5fd9b

tdf#120749 XLSX: fix "begins/ends with" conditional

It will be available in 7.2.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 13 Xisco Faulí 2021-01-04 13:20:30 UTC
Verified in

Version: 7.2.0.0.alpha0+
Build ID: 2c9708cbb870483a8a1c93d722085be5f789d234
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

@Tibor Nagy, thanks for fixing this issue!!
Comment 14 Commit Notification 2021-01-04 20:52:36 UTC
Tibor Nagy committed a patch related to this issue.
It has been pushed to "libreoffice-7-1":

https://git.libreoffice.org/core/commit/75596e1254f284fca22efc8e6cba158bd6c50b15

tdf#120749 XLSX: fix "begins/ends with" conditional

It will be available in 7.1.0.2.

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 15 NISZ LibreOffice Team 2021-01-12 10:32:16 UTC
Verified in:

Version: 7.2.0.0.alpha0+ (x64)
Build ID: 96bafa464ebdbce3ef04bec9beae5e745bb37794
CPU threads: 4; OS: Windows 10.0 Build 17134; UI render: Skia/Raster; VCL: win
Locale: hu-HU (hu_HU); UI: hu-HU
Calc: threaded