Conditional Formatting: support new Icon Sets, custom breakpoints

[imirkin]

There's currently functionailty to support an IconSet on a conditional format. However if one doesn't want to use a built-in scheme, Excel allows a custom scheme instead where icons are individually picked out. This is currently not supported in Excelize. A sample xml block for such a conditional format rule might be

          <x14:cfRule type="iconSet" priority="7" id="{EFADC4A8-BD1B-48CB-BBAC-95556150EFE4}">
            <x14:iconSet showValue="0" custom="1">
              <x14:cfvo type="percent">
                <xm:f>0</xm:f>
              </x14:cfvo>
              <x14:cfvo type="num">
                <xm:f>0</xm:f>
              </x14:cfvo>
              <x14:cfvo type="num" gte="0">
                <xm:f>0</xm:f>
              </x14:cfvo>
              <x14:cfIcon iconSet="3Triangles" iconId="0"/>
              <x14:cfIcon iconSet="3Triangles" iconId="1"/>
              <x14:cfIcon iconSet="3Triangles" iconId="2"/>
            </x14:iconSet>
          </x14:cfRule>

I may or may not end up needing this, but wanted to log it in case others find it useful as a starting point.

[imirkin]

Actually it looks like the reason for this explicitly custom icon set is dubious? Just need to make "3Triangles" a recognized icon set? But I also need the cutoffs to be negative/zero/positive rather than 0/33/67 cutoffs... maybe that's why I need the icon set to be custom? Just need to be able to pass in a list of xlsxCfRule somehow?

Based on https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xliconset?view=excel-pia it looks like the following were added at some point:

• 3Stars
• 3Triangles
• 5Boxes

[imirkin]

I've done a bit more research on this. Looks like the "new" icon sets can't simply be added. They must appear in the x14 namespace, which has to go in the extLst. The full thing might be

  <conditionalFormatting sqref="B1:B5">
    <cfRule type="iconSet" priority="1">
      <iconSet iconSet="5Quarters" showValue="0">
        <cfvo type="percent" val="0"/>
        <cfvo type="percent" val="20"/>
        <cfvo type="percent" val="40"/>
        <cfvo type="percent" val="60"/>
        <cfvo type="percent" val="80"/>
      </iconSet>
    </cfRule>
  </conditionalFormatting>
  <extLst>
    <ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}">
      <x14:conditionalFormattings>
        <x14:conditionalFormatting xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
          <x14:cfRule type="iconSet" priority="6" id="{C380A90B-4D44-4E7C-A0A8-F210DF177E76}">
            <x14:iconSet iconSet="5Boxes">
              <x14:cfvo type="percent">
                <xm:f>0</xm:f>
              </x14:cfvo>
              <x14:cfvo type="percent">
                <xm:f>20</xm:f>
              </x14:cfvo>
              <x14:cfvo type="percent">
                <xm:f>40</xm:f>
              </x14:cfvo>
              <x14:cfvo type="percent">
                <xm:f>60</xm:f>
              </x14:cfvo>
              <x14:cfvo type="percent">
                <xm:f>80</xm:f>
              </x14:cfvo>
            </x14:iconSet>
          </x14:cfRule>
          <xm:sqref>A1:A5</xm:sqref>
        </x14:conditionalFormatting>
      </x14:conditionalFormattings>
    </ext>
  </extLst>

This is just with the basic 5Quarters vs 5Boxes being done.