级联列表

使用 Go 语言在 Excel 文档中创建多级联动菜单列表:

使用 Go 语言在 Excel 文档中创建多级联动菜单列表

  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.         // 定义单元格的值
  14.         data = [][]interface{}{
  15.             {"Fruits", "Vegetables"},
  16.             {"Mango", "Potato", nil, "Drop Down 1", "Drop Down 2"},
  17.             {"Apple", "Tomato"},
  18.             {"Grapes", "Spinach"},
  19.             {"Strawberry", "Onion"},
  20.             {"Kiwi", "Cucumber"},
  21.         }
  22.         addr                    string
  23.         err                     error
  24.         cellsStyle, headerStyle int
  25.     )
  26.     // 按行赋值
  27.     for r, row := range data {
  28.         if addr, err = excelize.JoinCellName("A", r+1); err != nil {
  29.             fmt.Println(err)
  30.             return
  31.         }
  32.         if err = f.SetSheetRow("Sheet1", addr, &row); err != nil {
  33.             fmt.Println(err)
  34.             return
  35.         }
  36.     }
  37.     // 设置数据验证
  38.     dvRange1 := excelize.NewDataValidation(true)
  39.     dvRange1.Sqref = "D3:D3"
  40.     dvRange1.SetSqrefDropList("$A$1:$B$1", true)
  41.     if err = f.AddDataValidation("Sheet1", dvRange1); err != nil {
  42.         fmt.Println(err)
  43.         return
  44.     }
  45.     dvRange2 := excelize.NewDataValidation(true)
  46.     dvRange2.Sqref = "E3:E3"
  47.     dvRange2.SetSqrefDropList("INDIRECT(D3)", true)
  48.     if err = f.AddDataValidation("Sheet1", dvRange2); err != nil {
  49.         fmt.Println(err)
  50.         return
  51.     }
  52.     // 设置自定义名称
  53.     if err = f.SetDefinedName(&excelize.DefinedName{
  54.         Name:     "Fruits",
  55.         RefersTo: "Sheet1!$A$2:$A$6",
  56.         Scope:    "Sheet1",
  57.     }); err != nil {
  58.         fmt.Println(err)
  59.         return
  60.     }
  61.     if err = f.SetDefinedName(&excelize.DefinedName{
  62.         Name:     "Vegetables",
  63.         RefersTo: "Sheet1!$B$2:$B$6",
  64.         Scope:    "Sheet1",
  65.     }); err != nil {
  66.         fmt.Println(err)
  67.         return
  68.     }
  69.     // 自定义列宽
  70.     for col, width := range map[string]float64{
  71.         "A": 12, "B": 12, "C": 6, "D": 12, "E": 12} {
  72.         if err = f.SetColWidth("Sheet1", col, col, width); err != nil {
  73.             fmt.Println(err)
  74.             return
  75.         }
  76.     }
  77.     // 隐藏工作表网格线
  78.     if err = f.SetSheetViewOptions("Sheet1", 0,
  79.         excelize.ShowGridLines(false)); err != nil {
  80.         fmt.Println(err)
  81.         return
  82.     }
  83.     // 定义边框样式
  84.     border := []excelize.Border{
  85.         {Type: "top", Style: 1, Color: "cccccc"},
  86.         {Type: "left", Style: 1, Color: "cccccc"},
  87.         {Type: "right", Style: 1, Color: "cccccc"},
  88.         {Type: "bottom", Style: 1, Color: "cccccc"},
  89.     }
  90.     // 定义单元格样式
  91.     if cellsStyle, err = f.NewStyle(&excelize.Style{
  92.         Font:   &excelize.Font{Color: "333333"},
  93.         Border: border}); err != nil {
  94.         fmt.Println(err)
  95.         return
  96.     }
  97.     // 定义标题行单元格样式
  98.     if headerStyle, err = f.NewStyle(&excelize.Style{
  99.         Font: &excelize.Font{Bold: true},
  100.         Fill: excelize.Fill{
  101.             Type: "pattern", Color: []string{"dae9f3"}, Pattern: 1},
  102.         Border: border},
  103.     ); err != nil {
  104.         fmt.Println(err)
  105.         return
  106.     }
  107.     // 为单元格设置样式
  108.     if err = f.SetCellStyle("Sheet1", "A2", "B6", cellsStyle); err != nil {
  109.         fmt.Println(err)
  110.         return
  111.     }
  112.     if err = f.SetCellStyle("Sheet1", "D3", "E3", cellsStyle); err != nil {
  113.         fmt.Println(err)
  114.         return
  115.     }
  116.     // 为标题行设置样式
  117.     if err = f.SetCellStyle("Sheet1", "A1", "B1", headerStyle); err != nil {
  118.         fmt.Println(err)
  119.         return
  120.     }
  121.     if err = f.SetCellStyle("Sheet1", "D2", "E2", headerStyle); err != nil {
  122.         fmt.Println(err)
  123.         return
  124.     }
  125.     // 保存工作簿
  126.     if err := f.SaveAs("Book1.xlsx"); err != nil {
  127.         fmt.Println(err)
  128.     }
  129. }