Generate Dependent Drop List in Spreadsheet Using Go Language
This tutorial shows you how to generate a dependent drop list in a spreadsheet using Go language with the Excelize library.
Join the DZone community and get the full member experience.
Join For FreeExcelize is a library written in pure Go providing a set of functions that allow you to write to and read from XLSX / XLSM / XLTM files. Supports reading and writing spreadsheet documents generated by Microsoft Excel™ 2007 and later. Supports complex components by high compatibility, and provided streaming API for generating or reading data from a worksheet with huge amounts of data. This library needs Go version 1.10 or later.
- GitHub: https://github.com/360EntSecGroup-Skylar/excelize
- Document: https://xuri.me/excelize
Create dependent drop list in the spreadsheet with Excelize using Go like this:
Go
x
129
1
package main
2
3
import (
4
"fmt"
5
6
"github.com/360EntSecGroup-Skylar/excelize/v2"
7
)
8
9
func main() {
10
// create a new spreadsheet
11
f := excelize.NewFile()
12
var (
13
// cell values
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
// set each cell value
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
// set data validation
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
// set defined name
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
// set custom column width
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
// hide gridlines for the worksheet
78
if err = f.SetSheetViewOptions("Sheet1", 0,
79
excelize.ShowGridLines(false)); err != nil {
80
fmt.Println(err)
81
return
82
}
83
// define the border style
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
// define the style of cells
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
// define the style of the header row
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
// set cell style
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
// set cell style for the header row
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
// save spreadsheet file
126
if err := f.SaveAs("Book1.xlsx"); err != nil {
127
fmt.Println(err)
128
}
129
}
Topics:
go,
excel,
github,
opensource,
spreadsheet,
spreadsheet api
Opinions expressed by DZone contributors are their own.
Comments