[Libreoffice-bugs] [Bug 120749] Conditional Formatting CELLIS and BEGINSWITH not working correctly (Xlsx)

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120749

--- Comment #4 from Winfried Donkers <winfrieddonkers at libreoffice.org> ---
The document (A) provided in Description (DropBox link) and  attachment 146869
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.

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20181122/210d3a3e/attachment-0001.html>