issue with implementing cascading dropdown lists in Excel file using the excelize Go package.!

[mohanrajreese]

@pvelder @slashdotdash @jinzhu @mtmcfarl Please help with this
``
I'm trying to create an Excel file with dependent dropdown lists using the excelize Go package. I have a file with three sheets: FruitData, VarietyData, and ColorData.
On the main sheet (Main), I want to have a cascading dropdown:

A Fruit dropdown (populated from FruitData sheet). A Variety dropdown (filtered based on the selected fruit, from the VarietyData sheet). A Color dropdown (filtered based on the selected variety, from the ColorData sheet). However, I am unable to get the dependent dropdowns to work properly. The values for the Variety and Color dropdowns are not being filtered correctly when the Fruit or Variety values are selected.

Here is the Go code I am using:

package main

import (
    "fmt"
    "log"

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

func main() {
    // Initialize the Excel file
    file := excelize.NewFile()

    // Create sheets for data
    fruitDataSheet := "FruitData"
    varietyDataSheet := "VarietyData"
    colorDataSheet := "ColorData"
    mainSheet := "Main"

    // Add sheets for Fruit, Variety, Color, and Main
    file.NewSheet(mainSheet)
    file.NewSheet(fruitDataSheet)
    file.NewSheet(varietyDataSheet)
    file.NewSheet(colorDataSheet)

    // Populate FruitData sheet
    fruits := [][]string{
        {"FruitID", "FruitName"},
        {"1", "Apple"},
        {"2", "Banana"},
        {"3", "Orange"},
    }
    for i, row := range fruits {
        cell, _ := excelize.CoordinatesToCellName(1, i+1)
        file.SetSheetRow(fruitDataSheet, cell, &row)
    }

    // Populate VarietyData sheet
    varieties := [][]string{
        {"VarietyID", "VarietyName", "FruitID"},
        {"1", "Red Apple", "1"},
        {"2", "Green Apple", "1"},
        {"3", "Yellow Banana", "2"},
        {"4", "Green Banana", "2"},
        {"5", "Navel Orange", "3"},
        {"6", "Blood Orange", "3"},
    }
    for i, row := range varieties {
        cell, _ := excelize.CoordinatesToCellName(1, i+1)
        file.SetSheetRow(varietyDataSheet, cell, &row)
    }

    // Populate ColorData sheet
    colors := [][]string{
        {"ColorID", "ColorName", "VarietyID"},
        {"1", "Red", "1"},
        {"2", "Dark Red", "1"},
        {"3", "Green", "2"},
        {"4", "Light Green", "2"},
        {"5", "Yellow", "3"},
        {"6", "Light Yellow", "3"},
        {"7", "Light Orange", "5"},
        {"8", "Orange", "5"},
        {"9", "Blood Red", "6"},
        {"10", "Dark Red", "6"},
    }
    for i, row := range colors {
        cell, _ := excelize.CoordinatesToCellName(1, i+1)
        file.SetSheetRow(colorDataSheet, cell, &row)
    }

    // Set up Main sheet headers
    mainHeaders := []string{"Fruit", "Variety", "Color"}
    file.SetSheetRow(mainSheet, "A1", &mainHeaders)

    // Define formulas for data validation
    fruitFormula := fmt.Sprintf("'%s'!$B$2:$B$%d", fruitDataSheet, len(fruits))
    varietyFormula := "=FILTER(VarietyData!$B$2:$B$100, VarietyData!$C$2:$C$100=A2)"
    colorFormula := "=FILTER(ColorData!$B$2:$B$100, ColorData!$C$2:$C$100=B2)"

    // Add Fruit dropdown validation
    if err := file.AddDataValidation(mainSheet, &excelize.DataValidation{
        Type:             "list",
        Formula1:         fruitFormula,
        Sqref:            "A2:A1048576",
        ShowErrorMessage: true,
        ErrorTitle:       stringPtr("Invalid Fruit"),
        Error:            stringPtr("Please select a valid Fruit from the list."),
    }); err != nil {
        log.Fatalf("Failed to add Fruit validation: %v", err)
    }

    // Add Variety dropdown validation
    if err := file.AddDataValidation(mainSheet, &excelize.DataValidation{
        Type:             "list",
        Formula1:         varietyFormula,
        Sqref:            "B2:B1048576",
        ShowErrorMessage: true,
        ErrorTitle:       stringPtr("Invalid Variety"),
        Error:            stringPtr("Please select a valid Variety based on Fruit."),
    }); err != nil {
        log.Fatalf("Failed to add Variety validation: %v", err)
    }

    // Add Color dropdown validation
    if err := file.AddDataValidation(mainSheet, &excelize.DataValidation{
        Type:             "list",
        Formula1:         colorFormula,
        Sqref:            "C2:C1048576",
        ShowErrorMessage: true,
        ErrorTitle:       stringPtr("Invalid Color"),
        Error:            stringPtr("Please select a valid Color based on Variety."),
    }); err != nil {
        log.Fatalf("Failed to add Color validation: %v", err)
    }
    _ = file.DeleteSheet("Sheet1")

    // Save the file
    if err := file.SaveAs("DependentDropdowns.xlsx"); err != nil {
        log.Fatalf("Failed to save file: %v", err)
    }

    fmt.Println("Excel file with dependent dropdowns created successfully!")
}

func stringPtr(s string) *string {
    return &s
}

Issue:

The dependent dropdowns are not working as expected. The Variety dropdown does not get filtered based on the selected Fruit, and similarly, the Color dropdown is not filtered based on the selected Variety.

What I've tried:

I've defined formulas for filtering the Variety and Color lists based on the selected fruit and variety. The formulas are written as =FILTER(...), but they do not seem to work properly when selecting items from the first dropdown.

Question:

How can I implement dependent dropdown lists in Excel using the excelize Go package? Why are my dropdown lists not filtering based on the selected values? Is there any way to properly filter the lists dynamically in Excel using excelize?

[imirkin]

Are you using LibreOffice 24.8+? The FILTER function was only recently implemented there. (https://bugs.documentfoundation.org/show_bug.cgi?id=126573)

Does the same setup work when you manually input it into the spreadsheet program (i.e. the validation rules).