第Go結(jié)合Gin導(dǎo)出Mysql數(shù)據(jù)到Excel表格目錄1、實現(xiàn)目標(biāo)2、使用的庫3、項目目錄4、主要代碼編寫4.1、excelize.go(主要工具類)4.2、userService.go（接受請求）4.2、測試結(jié)果5、文檔代碼地址

1、實現(xiàn)目標(biāo)

Golang使用excelize導(dǎo)出表格到瀏覽器下載或者保存到本地。

后續(xù)導(dǎo)入的話也會寫到這里

2、使用的庫

goget/xuri/excelize/v2

Git地址：

/qax-os/excelize

文檔地址：

https://xuri.me/excelize/zh-hans/base/installation.html#install

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、主要代碼編寫

gin還不會老師們可以看：/bei_FengBoby/article/details/124847078

讀取配置文件是用的viper

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

ExportExcelByStruct函數(shù)是從網(wǎng)絡(luò)上直接copy的，研究他這個寫法花了好一會兒，所以也寫上去了，提供大家學(xué)習(xí)

import(

"fmt"

"math/rand"

"net/url"

"reflect"

"strconv"

"time"

"/gin-gonic/gin"

"/xuri/excelize/v2"

var(

defaultSheetName="Sheet1"http://默認(rèn)Sheet名稱

defaultHeight=25.0//默認(rèn)行高度

typelzExcelExportstruct{

file*excelize.File

sheetNamestring//可定義默認(rèn)sheet名稱

funcNewMyExcel()*lzExcelExport{

returnlzExcelExport{file:createFile(),sheetName:defaultSheetName}

//導(dǎo)出基本的表格

func(l*lzExcelExport)ExportToPath(params[]map[string]string,data[]map[string]interface{},pathstring)(string,error){

l.export(params,data)

name:=createFileName()

filePath:=path+"/"+name

err:=l.file.SaveAs(filePath)

returnfilePath,err

//導(dǎo)出到瀏覽器。此處使用的gin框架其他框架可自行修改ctx

func(l*lzExcelExport)ExportToWeb(params[]map[string]string,data[]map[string]interface{},c*gin.Context){

l.export(params,data)

buffer,_:=l.file.WriteToBuffer()

//設(shè)置文件類型

c.Header("Content-Type","application/vnd.ms-excel;charset=utf8")

//設(shè)置文件名稱

c.Header("Content-Disposition","attachment;filename="+url.QueryEscape(createFileName()))

_,_=c.Writer.Write(buffer.Bytes())

//設(shè)置首行

func(l*lzExcelExport)writeTop(params[]map[string]string){

topStyle,_:=l.file.NewStyle(`{"font":{"bold":true},"alignment":{"horizontal":"center","vertical":"center"}}`)

varword='A'

//首行寫入

for_,conf:=rangeparams{

title:=conf["title"]

width,_:=strconv.ParseFloat(conf["width"],64)

line:=fmt.Sprintf("%c1",word)

//設(shè)置標(biāo)題

_=l.file.SetCellValue(l.sheetName,line,title)

//列寬

_=l.file.SetColWidth(l.sheetName,fmt.Sprintf("%c",word),fmt.Sprintf("%c",word),width)

//設(shè)置樣式

_=l.file.SetCellStyle(l.sheetName,line,line,topStyle)

word++

//寫入數(shù)據(jù)

func(l*lzExcelExport)writeData(params[]map[string]string,data[]map[string]interface{}){

lineStyle,_:=l.file.NewStyle(`{"alignment":{"horizontal":"center","vertical":"center"}}`)

//數(shù)據(jù)寫入

varj=2//數(shù)據(jù)開始行數(shù)

fori,val:=rangedata{

//設(shè)置行高

_=l.file.SetRowHeight(l.sheetName,i+1,defaultHeight)

//逐列寫入

varword='A'

for_,conf:=rangeparams{

valKey:=conf["key"]

line:=fmt.Sprintf("%c%v",word,j)

isNum:=conf["is_num"]

//設(shè)置值

ifisNum!="0"{

valNum:=fmt.Sprintf("'%v",val[valKey])

_=l.file.SetCellValue(l.sheetName,line,valNum)

}else{

_=l.file.SetCellValue(l.sheetName,line,val[valKey])

//設(shè)置樣式

_=l.file.SetCellStyle(l.sheetName,line,line,lineStyle)

word++

//設(shè)置行高尾行

_=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)

funccreateFile()*excelize.File{

f:=excelize.NewFile()

//創(chuàng)建一個默認(rèn)工作表

sheetName:=defaultSheetName

index:=f.NewSheet(sheetName)

//設(shè)置工作簿的默認(rèn)工作表

f.SetActiveSheet(index)

returnf

funccreateFileName()string{

name:=time.Now().Format("2006-01-02-15-04-05")

rand.Seed(time.Now().UnixNano())

returnfmt.Sprintf("excle-%v-%v.xlsx",name,rand.Int63n(time.Now().Unix()))

//excel導(dǎo)出(數(shù)據(jù)源為Struct)[]interface{}

func(l*lzExcelExport)ExportExcelByStruct(titleList[]string,data[]interface{},fileNamestring,sheetNamestring,c*gin.Context)error{

l.file.SetSheetName("Sheet1",sheetName)

header:=make([]string,0)

for_,v:=rangetitleList{

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)

varlastRowstring

varwidthRowstring

fork,v:=rangeheadStyle{

ifk==length-1{

lastRow=fmt.Sprintf("%s1",v)

widthRow=v

iferr:=l.file.SetColWidth(sheetName,"A",widthRow,30);err!=nil{

fmt.Print("錯誤--",err.Error())

rowNum:=1

for_,v:=rangedata{

t:=reflect.TypeOf(v)

fmt.Print("--ttt--",t.NumField())

value:=reflect.ValueOf(v)

row:=make([]interface{

},0)

forl:=0;lt.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)

iferr!=nil{

returnerr

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")

returnl.file.Write(c.Writer)

//Letter遍歷a-z

funcLetter(lengthint)[]string{

v