Go怎么结合Gin导出Mysql数据到Excel表格-平凯数据库

Go怎么结合Gin导出Mysql数据到Excel表格

网友投稿 2194 2023-07-08

Go怎么结合Gin导出Mysql数据到Excel表格

1、实现目标

Golang 使用excelize 导出表格到浏览器下载或者保存到本地。后续导入的话也会写到这里

2、使用的库

go get github.com/xuri/excelize/v2

3、项目目录

go-excel├─ app│ ├─ excelize│ │ └─ excelize.go│ ├─ model│ │ └─ sysUser.go│ └─ service│ └─ userService.go├─ common│ └─ mysql.go├─ go.mod├─ go.sum├─ main.go└─ setting.json登录后复制

4、主要代码编写

读取配置文件是用的viper

4.1、excelize.go(主要工具类)

ExportExcelByStruct 函数是从网络上直接copy的，研究他这个写法花了好一会儿，所以也写上去了，提供大家学习

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","***l":"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","***l":"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":{"***l":"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的，可以直接用，数据改成自己的就好，注意的事项里面我也写了，避雷！！

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