Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Conditional Formatting: support new Icon Sets, custom breakpoints #2038

Open
imirkin opened this issue Dec 11, 2024 · 2 comments
Open

Conditional Formatting: support new Icon Sets, custom breakpoints #2038

imirkin opened this issue Dec 11, 2024 · 2 comments
Labels
enhancement New feature or request

Comments

@imirkin
Copy link
Contributor

imirkin commented Dec 11, 2024

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
Copy link
Contributor Author

imirkin commented Dec 11, 2024

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
Copy link
Contributor Author

imirkin commented Dec 11, 2024

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.

@imirkin imirkin changed the title Conditional Formatting: support custom Icon Sets Conditional Formatting: support new Icon Sets, custom breakpoints Dec 11, 2024
@xuri xuri added the enhancement New feature or request label Dec 16, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

2 participants