Go結合Gin導出Mysql數據到Excel表格
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、項目目錄
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的,研究他這個寫法花瞭好一會兒,所以也寫上去瞭,提供大傢學習
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框架 其他框架可自行修改ctx func (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-z func 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" ) //獲取所有用戶數據-excel func 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內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- Go Excelize API源碼閱讀Close及NewSheet方法示例解析
- Golang的func參數及返回值操作
- Go Excelize API源碼閱讀SetSheetViewOptions示例解析
- Go語言學習函數+結構體+方法+接口
- Go語言操作Excel利器之excelize類庫詳解