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.FilesheetName 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 + "/" + nameerr := 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 := defaultSheetNameindex := 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 stringvar widthRow stringfor 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 := 1for _, 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 []stringfor 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.TUserdb := 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.TUserdb := 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
以上就是Go怎么结合Gin导出Mysql数据到Excel表格的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/161803.html
微信扫一扫 
支付宝扫一扫 
![Go语言接口与切片：如何识别和操作[]interface{}](https://cdn.chuangxiangniao.com/www/2025/12/176369856569294-1.jpg?imageMogr2/crop/480x300/gravity/center)
