<html>
<head>
<base href="https://bugs.documentfoundation.org/">
</head>
<body>
<p>
<div>
<b><a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - Conditional Formatting CELLIS and BEGINSWITH not working correctly (Xlsx)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=120749#c4">Comment # 4</a>
on <a class="bz_bug_link
bz_status_UNCONFIRMED "
title="UNCONFIRMED - Conditional Formatting CELLIS and BEGINSWITH not working correctly (Xlsx)"
href="https://bugs.documentfoundation.org/show_bug.cgi?id=120749">bug 120749</a>
from <span class="vcard"><a class="email" href="mailto:winfrieddonkers@libreoffice.org" title="Winfried Donkers <winfrieddonkers@libreoffice.org>"> <span class="fn">Winfried Donkers</span></a>
</span></b>
<pre>The document (A) provided in Description (DropBox link) and <span class=""><a href="http://bugs.documentfoundation.org/attachment.cgi?id=146869" name="attach_146869" title="sample file">attachment 146869</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=146869&action=edit" title="sample file">[details]</a></span>
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="<a href="http://schemas.microsoft.com/office/excel/2006/main">http://schemas.microsoft.com/office/excel/2006/main</a>"><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="<a href="http://schemas.microsoft.com/office/excel/2006/main">http://schemas.microsoft.com/office/excel/2006/main</a>"><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.</pre>
</div>
</p>
<hr>
<span>You are receiving this mail because:</span>
<ul>
<li>You are the assignee for the bug.</li>
</ul>
</body>
</html>