Custom Conditional Data Validation

[exfur]

Description

Thanks a lot for this package! I can't figure out how to set custom data validation based on bool formula like this:

=IF(AND(SUM(A1:A5)<=B1,SUM(C1:C5)<=D1)),TRUE,FALSE)

In excel if you put this formula in custom data validation it would allow your input if only condition is true.

I looked through the docs and found only guides for list validations or simple ones(like: less than, greater than)

Excelize version or commit ID: v2.8.1

Environment details (Windows 11, Microsoft Excel™ )

[xuri]

Thanks for your issue. You can create data validation with custom settings, including error style, alert title, and message. Note that, there are duplicate back brackets in your formula. In addition, you need to escape the equal symbol when setting the formula text. For example:

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)
        }
    }()
    dv := excelize.NewDataValidation(true)
    dv.SetError(excelize.DataValidationErrorStyleWarning, "Alert Title",
        "This value doesn't match the data validation restrictions defined for this cell.")
    dv.SetRange("IF(AND(SUM(A1:A5)<=B1,SUM(C1:C5)<=D1),TRUE,FALSE)", "",
        excelize.DataValidationTypeCustom, excelize.DataValidationOperatorEqual)
    dv.SetSqref("E1:E6")
    if err := f.AddDataValidation("Sheet1", dv); err != nil {
        fmt.Println(err)
        return
    }
    if err := f.SaveAs("Book1.xlsx"); err != nil {
        fmt.Println(err)
    }
}

I've closed this issue. If you have any questions, please let me know, and you can reopen this anytime.