1、实现目标
Golang 使用excelize 导出表格到浏览器下载或者保存到本地。
后续导入的话也会写到这里
2、使用的库
go get github.com/xuri/excelize/v2
Git地址：
https://github.com/qax-os/excelize
文档地址：
https://xuri.me/excelize/zh-hans/base/installation.html#install
3、项目目录
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
go-excel├─ app│ ├─ excelize│ │ └─ excelize.go│ ├─ model│ │ └─ sysUser.go│ └─ service│ └─ userService.go├─ common│ └─ mysql.go├─ go.mod├─ go.sum├─ main.go└─ setting.json |
4、主要代码编写
gin还不会老师们可以看：https://blog.csdn.net/bei_FengBoby/article/details/124847078
读取配置文件是用的viper
4.1、excelize.go(主要工具类)
ExportExcelByStruct 函数 是从网络上直接copy的，研究他这个写法花了好一会儿，所以也写上去了，提供大家学习
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
import ( "fmt" "math/rand" "net/url" "reflect" "strconv" "time" "github.com/gin-gonic/gin" "github.com/xuri/excelize/v2")var ( defaultSheetName = "Sheet1" //默认Sheet名称 defaultHeight = 25.0 //默认行高度)type lzExcelExport struct { file *excelize.File sheetName string //可定义默认sheet名称}func NewMyExcel() *lzExcelExport { return &lzExcelExport{file: createFile(), sheetName: defaultSheetName}}//导出基本的表格func (l *lzExcelExport) ExportToPath(params []map[string]string, data []map[string]interface{}, path string) (string, error) { l.export(params, data) name := createFileName() filePath := path + "/" + name err := l.file.SaveAs(filePath) return filePath, err}//导出到浏览器。此处使用的gin框架 其他框架可自行修改ctxfunc (l *lzExcelExport) ExportToWeb(params []map[string]string, data []map[string]interface{}, c *gin.Context) { l.export(params, data) buffer, _ := l.file.WriteToBuffer() //设置文件类型 c.Header("Content-Type", "application/vnd.ms-excel;charset=utf8") //设置文件名称 c.Header("Content-Disposition", "attachment; filename="+url.QueryEscape(createFileName())) _, _ = c.Writer.Write(buffer.Bytes())}//设置首行func (l *lzExcelExport) writeTop(params []map[string]string) { topStyle, _ := l.file.NewStyle(`{"font":{"bold":true},"alignment":{"horizontal":"center","vertical":"center"}}`) var word = 'A' //首行写入 for _, conf := range params { title := conf["title"] width, _ := strconv.ParseFloat(conf["width"], 64) line := fmt.Sprintf("%c1", word) //设置标题 _ = l.file.SetCellValue(l.sheetName, line, title) //列宽 _ = l.file.SetColWidth(l.sheetName, fmt.Sprintf("%c", word), fmt.Sprintf("%c", word), width) //设置样式 _ = l.file.SetCellStyle(l.sheetName, line, line, topStyle) word++ }}//写入数据func (l *lzExcelExport) writeData(params []map[string]string, data []map[string]interface{}) { lineStyle, _ := l.file.NewStyle(`{"alignment":{"horizontal":"center","vertical":"center"}}`) //数据写入 var j = 2 //数据开始行数 for i, val := range data { //设置行高 _ = l.file.SetRowHeight(l.sheetName, i+1, defaultHeight) //逐列写入 var word = 'A' for _, conf := range params { valKey := conf["key"] line := fmt.Sprintf("%c%v", word, j) isNum := conf["is_num"] //设置值 if isNum != "0" { valNum := fmt.Sprintf("'%v", val[valKey]) _ = l.file.SetCellValue(l.sheetName, line, valNum) } else { _ = l.file.SetCellValue(l.sheetName, line, val[valKey]) } //设置样式 _ = l.file.SetCellStyle(l.sheetName, line, line, lineStyle) word++ } j++ } //设置行高 尾行 _ = l.file.SetRowHeight(l.sheetName, len(data)+1, defaultHeight)}func (l *lzExcelExport) export(params []map[string]string, data []map[string]interface{}) { l.writeTop(params) l.writeData(params, data)}func createFile() *excelize.File { f := excelize.NewFile() // 创建一个默认工作表 sheetName := defaultSheetName index := f.NewSheet(sheetName) // 设置工作簿的默认工作表 f.SetActiveSheet(index) return f}func createFileName() string { name := time.Now().Format("2006-01-02-15-04-05") rand.Seed(time.Now().UnixNano()) return fmt.Sprintf("excle-%v-%v.xlsx", name, rand.Int63n(time.Now().Unix()))}//excel导出(数据源为Struct) []interface{}func (l *lzExcelExport) ExportExcelByStruct(titleList []string, data []interface{}, fileName string, sheetName string, c *gin.Context) error { l.file.SetSheetName("Sheet1", sheetName) header := make([]string, 0) for _, v := range titleList { header = append(header, v) } rowStyleID, _ := l.file.NewStyle(`{"font":{"color":"#666666","size":13,"family":"arial"},"alignment":{"vertical":"center","horizontal":"center"}}`) _ = l.file.SetSheetRow(sheetName, "A1", &header) _ = l.file.SetRowHeight("Sheet1", 1, 30) length := len(titleList) headStyle := Letter(length) var lastRow string var widthRow string for k, v := range headStyle { if k == length-1 { lastRow = fmt.Sprintf("%s1", v) widthRow = v } } if err := l.file.SetColWidth(sheetName, "A", widthRow, 30); err != nil { fmt.Print("错误--", err.Error()) } rowNum := 1 for _, v := range data { t := reflect.TypeOf(v) fmt.Print("--ttt--", t.NumField()) value := reflect.ValueOf(v) row := make([]interface { }, 0) for l := 0; l < t.NumField(); l++ { val := value.Field(l).Interface() row = append(row, val) } rowNum++ err := l.file.SetSheetRow(sheetName, "A"+strconv.Itoa(rowNum), &row) _ = l.file.SetCellStyle(sheetName, fmt.Sprintf("A%d", rowNum), fmt.Sprintf("%s", lastRow), rowStyleID) if err != nil { return err } } disposition := fmt.Sprintf("attachment; filename=%s.xlsx", url.QueryEscape(fileName)) c.Writer.Header().Set("Content-Type", "application/octet-stream") c.Writer.Header().Set("Content-Disposition", disposition) c.Writer.Header().Set("Content-Transfer-Encoding", "binary") c.Writer.Header().Set("Access-Control-Expose-Headers", "Content-Disposition") return l.file.Write(c.Writer)}// Letter 遍历a-zfunc Letter(length int) []string { var str []string for i := 0; i < length; i++ { str = append(str, string(rune('A'+i))) } return str} |
4.2、userService.go（接受请求）
其中导出的函数都已经测试是ok的，可以直接用，数据改成自己的就好，
注意的事项里面我也写了，避雷！！
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
|
import ( "go-excel/app/excelize" "go-excel/app/model" config "go-excel/common" "github.com/gin-gonic/gin")//获取所有用户数据-excelfunc GetAllUserExportToWeb(ctx *gin.Context) { var users []model.TUser db := config.GetDB() db.Find(&users) //定义首行标题 dataKey := make([]map[string]string, 0) dataKey = append(dataKey, map[string]string{ "key": "id", "title": "索引", "width": "20", "is_num": "0", }) dataKey = append(dataKey, map[string]string{ "key": "username", "title": "用户名", "width": "20", "is_num": "0", }) dataKey = append(dataKey, map[string]string{ "key": "remark", "title": "备注", "width": "20", "is_num": "0", }) //填充数据 data := make([]map[string]interface{}, 0) if len(users) > 0 { for _, v := range users { data = append(data, map[string]interface{}{ "id": v.ID, "username": v.Username, "remark": v.Remark, }) } } ex := excelize.NewMyExcel() // ex.ExportToWeb(dataKey, data, ctx) //保存到D盘 ex.ExportToPath(dataKey, data, "D:/")}//excel 导出func GetUserExcelByMap(ctx *gin.Context) { var users []model.TUser db := config.GetDB() db.Find(&users) titles := []string{"ID", "用户名", "备注"} ex := excelize.NewMyExcel() var datas []interface{} for _, v := range users { //这里最好新建一个struct 和titles一致，不然users里面的多余的字段也会写进去 datas = append(datas, model.TUser{ ID: v.ID, Username: v.Username, Remark: v.Remark, }) } ex.ExportExcelByStruct(titles, datas, "用户数据", "用户", ctx)} |
4.2、测试结果
GetAllUserExportToWeb
GetUserExcelByMap
5、文档代码地址
https://gitee.com/hjx_RuGuoYunZhiDao/strom-huang-go.git —go-excel目录
到此这篇关于Go结合Gin导出Mysql数据到Excel表格的文章就介绍到这了,更多相关Go 导出Mysql数据到Excel内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！
原文链接：https://blog.csdn.net/bei_FengBoby/article/details/126038229
