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>"-"</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:
Created attachment 146869 [details] sample file
I get an 'Excel found unreadable content' warning with Excel 2010 and the results is the same as in LibreOffice 6.2
(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
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>"-"</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("-"))="-"</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.
Created attachment 146900 [details] Document C as mentioned in comment 4
Created attachment 146901 [details] Document D as mentioned in comment 4
Some of the Conditional Formatting settings will only save correctly in the .ods file format.
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
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.
Created attachment 152827 [details] Screenshot of the attachment #152826 [details] in Excel and Calc
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
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.
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!!
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.
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