Display order of conditional formats at the same location

[ShowerBandV]

Description
Use SetConditionalFormat() to set multiple conditional formats for data bars in a location such as A1: A13, such as blue (1), green (2), and yellow (3). When opened in Excel, the effect will be green, while the management conditional format interface will prioritize blue. Set the same number of data bar conditional formats and priorities in the same location in Excel, but their XML structures are the same. After comparing products such as Feishu and Tencent Documents, it was found that their display order is right, and the writing structure of conditional formats is different from that of excelize,Can you refer to their approach to modify SetConditionalFormat to achieve consistent rendering effects and priority ? thanks.

this is excel xml

<conditionalFormatting sqref="D1:D8">
<cfRule type="dataBar" priority="4">
<dataBar>
<cfvo type="min"/>
<cfvo type="max"/>
<color theme="4" tint="-0.249977111117893"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{259B75A4-6F18-4B61-83F0-580C0D906035}</x14:id>
</ext>
</extLst>
</cfRule>
<cfRule type="dataBar" priority="3">
<dataBar>
<cfvo type="min"/>
<cfvo type="max"/>
<color rgb="FF00B050"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{2B952167-3E00-4F90-989C-0122521082A6}</x14:id>
</ext>
</extLst>
</cfRule>
....
</conditionalFormatting>

this is excelize xml

<conditionalFormatting sqref="D1:D8">
<cfRule type="dataBar" priority="1">
<dataBar showValue="true">
<cfvo type="min" val="0.0000000000"/>
<cfvo type="max" val="0.0000000000"/>
<color rgb="FF66D0FB"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{00000000-0000-0000-0001-000000000000}</x14:id>
</ext>
</extLst>
</cfRule>
<cfRule type="dataBar" priority="2">
<dataBar showValue="true">
<cfvo type="min" val="0.0000000000"/>
<cfvo type="max" val="0.0000000000"/>
<color rgb="FFF6D668"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{00000000-0000-0000-0001-000000000000}</x14:id>
</ext>
</extLst>
</cfRule>
</conditionalFormatting>

** this is feishu\Tencent xml**

<conditionalFormatting sqref="D1:D13">
<cfRule priority="6" type="dataBar">
<dataBar>
<cfvo type="min"/>
<cfvo type="max"/>
<color rgb="FF619CD3"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{20ACDD8A-A684-B297-C3B7-D3E013A55537}</x14:id>
</ext>
</extLst>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="D1:D13">
<cfRule priority="4" type="dataBar">
<dataBar>
<cfvo type="min"/>
<cfvo type="max"/>
<color rgb="FFC10002"/>
</dataBar>
<extLst>
<ext xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main" uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}">
<x14:id>{8E54E428-7800-2BC7-D1F0-21DE19FA5269}</x14:id>
</ext>
</extLst>
</cfRule>
</conditionalFormatting>

Diffence
img.xlsx

dd.xlsx
excelize.xlsx

[xuri]

Thanks for your issue. I have fixed some problems with setting and getting conditional formats with multiple rules, please upgrade to the master branch. If it still doesn't work, please let me know.

[ShowerBandV]

Thanks for your issue. I have fixed some problems with setting and getting conditional formats with multiple rules, please upgrade to the master branch. If it still doesn't work, please let me know.

I am using the latest release, but it doesn't seem to be taking effect...

[xuri]

Thanks for your feedback. Could you show us a complete, standalone example program or reproducible demo? I have tested with the following code and it works well. The priority of the conditional formatting rule with a blue color data bar is in the expected:

package main

import (
    "fmt"

    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    var condFmts []excelize.ConditionalFormatOptions
    for _, color := range []string{
        "#264B96", // Blue
        "#F9A73E", // Yellow
        "#006F3C", // Green
    } {
        condFmts = append(condFmts, excelize.ConditionalFormatOptions{
            Type:     "data_bar",
            Criteria: "=",
            MinType:  "num",
            MaxType:  "num",
            MinValue: "0",
            MaxValue: "20",
            BarColor: color,
            BarSolid: true,
        })
    }
    if err := f.SetConditionalFormat("Sheet1", "A1:A20", condFmts); err != nil {
        fmt.Println(err)
        return
    }
    for r := 1; r <= 20; r++ {
        cell, err := excelize.CoordinatesToCellName(1, r)
        if err != nil {
            fmt.Println(err)
            return
        }
        if err := f.SetCellValue("Sheet1", cell, r); err != nil {
            fmt.Println(err)
            return
        }
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

[ShowerBandV]

Thanks for your feedback. Could you show us a complete, standalone example program or reproducible demo? I have tested with the following code and it works well. The priority of the conditional formatting rule with a blue color data bar is in the expected:

package main

import (
    "fmt"

    "github.com/xuri/excelize/v2"
)

func main() {
    f := excelize.NewFile()
    defer func() {
        if err := f.Close(); err != nil {
            fmt.Println(err)
        }
    }()
    var condFmts []excelize.ConditionalFormatOptions
    for _, color := range []string{
        "#264B96", // Blue
        "#F9A73E", // Yellow
        "#006F3C", // Green
    } {
        condFmts = append(condFmts, excelize.ConditionalFormatOptions{
            Type:     "data_bar",
            Criteria: "=",
            MinType:  "num",
            MaxType:  "num",
            MinValue: "0",
            MaxValue: "20",
            BarColor: color,
            BarSolid: true,
        })
    }
    if err := f.SetConditionalFormat("Sheet1", "A1:A20", condFmts); err != nil {
        fmt.Println(err)
        return
    }
    for r := 1; r <= 20; r++ {
        cell, err := excelize.CoordinatesToCellName(1, r)
        if err != nil {
            fmt.Println(err)
            return
        }
        if err := f.SetCellValue("Sheet1", cell, r); err != nil {
            fmt.Println(err)
            return
        }
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

ok,if you set two column,the problem will be shown
here is demo:

package main

import (
	"fmt"

	"github.com/xuri/excelize/v2"
)

func main() {
	f := excelize.NewFile()
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()
	var condFmts []excelize.ConditionalFormatOptions
	for _, color := range []string{
		"#264B96", // Blue
		"#F9A73E", // Yellow
		"#006F3C", // Green
	} {
		condFmts = append(condFmts, excelize.ConditionalFormatOptions{
			Type:     "data_bar",
			Criteria: "=",
			MinType:  "num",
			MaxType:  "num",
			MinValue: "0",
			MaxValue: "20",
			BarColor: color,
			BarSolid: true,
		})
	}
	if err := f.SetConditionalFormat("Sheet1", "A1:A20", condFmts); err != nil {
		fmt.Println(err)
		return
	}
	if err := f.SetConditionalFormat("Sheet1", "B1:B20", condFmts); err != nil {
		fmt.Println(err)
		return
	}
	for r := 1; r <= 20; r++ {
		cell, err := excelize.CoordinatesToCellName(1, r)
		if err != nil {
			fmt.Println(err)
			return
		}
		if err := f.SetCellValue("Sheet1", cell, r); err != nil {
			fmt.Println(err)
			return
		}
		cell, err = excelize.CoordinatesToCellName(2, r)
		if err != nil {
			fmt.Println(err)
			return
		}
		if err := f.SetCellValue("Sheet1", cell, r); err != nil {
			fmt.Println(err)
			return
		}
	}
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

when you go run this demo,you can see the columnA's color is right,but the columnB's color is the second priority's color

[xuri]

Thanks for your issue. I have fixed this. Please upgrade to the master branch code, and this patch will be released in the next version.