后续导入的话也会写到这里
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","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的，可以直接用，数据改成自己的就好，
注意的事项里面我也写了，避雷！！
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
以上就是go怎么结合gin导出mysql数据到excel表格的详细内容。