qax-os / excelize

Go language library for reading and writing Microsoft Excel™ (XLAM / XLSM / XLSX / XLTM / XLTX) spreadsheets
https://xuri.me/excelize
BSD 3-Clause "New" or "Revised" License
17.64k stars 1.69k forks source link

I have 70 option datas,but can't set dropList as datavalidation #1878

Closed ShowerBandV closed 2 months ago

ShowerBandV commented 2 months ago

Description I got data list which range is 70 and I want to set them as datavalidation,but I got a err :data validation must be 0-255 characters. I want to know if there have other funcs to help me set these data?

here is my code below:

package main

import (
    "fmt"
    "math/rand"

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

func main() {
    var strs []string
    for i := 0; i < 75; i++ {
        strs = append(strs, genOption())
    }
    dv := excelize.NewDataValidation(true)
    dv.SetSqref(fmt.Sprintf("%s:%s", "A1", "A75"))
    if err := dv.SetDropList(strs); err != nil {
        fmt.Println(err.Error())
    }
}

func genOption() string {
    return randomString(5)
}

func randomString(length int) string {
    const charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789"
    b := make([]byte, length)
    for i := range b {
        b[i] = charset[rand.Intn(len(charset))]
    }
    return string(b)
}
xuri commented 2 months ago

This issue was duplicated with #476. The 255 character limit on a list/string validation is an Excel limitation. This library is just enforcing that limitation. Please add the validation list data to a worksheet and refer to it with a formula range. I'll close this issue. If you have any questions, please let me know, and you can reopen this anytime.