级联列表

使用 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")
  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)")
  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.     disable := false
  79.     if err := f.SetSheetView("Sheet1", -1, &excelize.ViewOptions{
  80.         ShowGridLines: &disable,
  81.     }); err != nil {
  82.         fmt.Println(err)
  83.     }
  84.     // 定义边框样式
  85.     border := []excelize.Border{
  86.         {Type: "top", Style: 1, Color: "cccccc"},
  87.         {Type: "left", Style: 1, Color: "cccccc"},
  88.         {Type: "right", Style: 1, Color: "cccccc"},
  89.         {Type: "bottom", Style: 1, Color: "cccccc"},
  90.     }
  91.     // 定义单元格样式
  92.     if cellsStyle, err = f.NewStyle(&excelize.Style{
  93.         Font:   &excelize.Font{Color: "333333"},
  94.         Border: border}); err != nil {
  95.         fmt.Println(err)
  96.         return
  97.     }
  98.     // 定义标题行单元格样式
  99.     if headerStyle, err = f.NewStyle(&excelize.Style{
  100.         Font: &excelize.Font{Bold: true},
  101.         Fill: excelize.Fill{
  102.             Type: "pattern", Color: []string{"dae9f3"}, Pattern: 1},
  103.         Border: border},
  104.     ); err != nil {
  105.         fmt.Println(err)
  106.         return
  107.     }
  108.     // 为单元格设置样式
  109.     if err = f.SetCellStyle("Sheet1", "A2", "B6", cellsStyle); err != nil {
  110.         fmt.Println(err)
  111.         return
  112.     }
  113.     if err = f.SetCellStyle("Sheet1", "D3", "E3", cellsStyle); err != nil {
  114.         fmt.Println(err)
  115.         return
  116.     }
  117.     // 为标题行设置样式
  118.     if err = f.SetCellStyle("Sheet1", "A1", "B1", headerStyle); err != nil {
  119.         fmt.Println(err)
  120.         return
  121.     }
  122.     if err = f.SetCellStyle("Sheet1", "D2", "E2", headerStyle); err != nil {
  123.         fmt.Println(err)
  124.         return
  125.     }
  126.     // 保存工作簿
  127.     if err := f.SaveAs("Book1.xlsx"); err != nil {
  128.         fmt.Println(err)
  129.     }
  130. }