多选列表

使用 Go 语言在 Excel 文档中无需 VBA 创建可多选菜单列表:

使用 Go 语言在 Excel 文档中无需 VBA 创建可多选菜单列表

  1. package main
  3. import (
  4.     "fmt"
  6.     "github.com/xuri/excelize/v2"
  7. )
  9. func main() {
  10.     // 创建工作簿
  11.     f := excelize.NewFile()
  12.     var (
  13.         sheetName = "Sheet1"
  14.         selection = []string{"red", "blue", "green", "yellow"}
  15.         // 定义单元格的值
  16.         data = [][]interface{}{
  17.             {"Element", "Picklist", nil, "Select below"},
  18.             {selection[0] + " "},
  19.             {selection[1] + " "},
  20.             {selection[2] + " "},
  21.             {selection[3] + " "},
  22.         }
  23.         cell string
  24.         err  error
  25.     )
  26.     // 按行赋值
  27.     for r, row := range data {
  28.         if cell, err = excelize.JoinCellName("A", r+1); err != nil {
  29.             fmt.Println(err)
  30.             return
  31.         }
  32.         if err = f.SetSheetRow(sheetName, cell, &row); err != nil {
  33.             fmt.Println(err)
  34.             return
  35.         }
  36.     }
  37.     // 设置自定义名称
  38.     for index, value := range selection {
  39.         if cell, err = excelize.CoordinatesToCellName(1, index+2, true); err != nil {
  40.             fmt.Println(err)
  41.             return
  42.         }
  43.         if err = f.SetDefinedName(&excelize.DefinedName{
  44.             Name:     value,
  45.             RefersTo: fmt.Sprintf("%s!%s", sheetName, cell),
  46.             Scope:    sheetName,
  47.         }); err != nil {
  48.             fmt.Println(err)
  49.             return
  50.         }
  51.         if cell, err = excelize.CoordinatesToCellName(2, index+2); err != nil {
  52.             fmt.Println(err)
  53.             return
  54.         }
  55.         formula := fmt.Sprintf("=IF(ISNUMBER(FIND(%s,D2)),\"\",D2&%s)", value, value)
  56.         if err := f.SetCellFormula(sheetName, cell, formula); err != nil {
  57.             fmt.Println(err)
  58.             return
  59.         }
  60.     }
  61.     // 设置数据验证
  62.     dvRange := excelize.NewDataValidation(true)
  63.     dvRange.Sqref = "D2:D2"
  64.     dvRange.SetSqrefDropList("$B$2:$B$5", true)
  65.     if err = f.AddDataValidation(sheetName, dvRange); err != nil {
  66.         fmt.Println(err)
  67.         return
  68.     }
  69.     // 自定义列宽
  70.     for col, width := range map[string]float64{"A": 10, "B": 18, "D": 18} {
  71.         if err = f.SetColWidth(sheetName, col, col, width); err != nil {
  72.             fmt.Println(err)
  73.             return
  74.         }
  75.     }
  76.     // 创建表格
  77.     if err = f.AddTable(sheetName, "A1", "B5", `{
  78.         "table_name": "table",
  79.         "table_style": "TableStyleMedium2"
  80.     }`); err != nil {
  81.         fmt.Println(err)
  82.         return
  83.     }
  84.     // 保存工作簿
  85.     if err := f.SaveAs("Book1.xlsx"); err != nil {
  86.         fmt.Println(err)
  87.     }
  88. }