1. To achieve the goal

Golang uses excelize to export the table to the browser for downloading or save it locally.
The subsequent import will also be written here

2. The library used

go get github.com/xuri/excelize/v2

3. Project directory

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

4. Main code writing

viper is used to read the configuration file

4.1. excelize.go(main Tools)

The ExportExcelByStruct function was copied directly from the Internet. It took a while to study his writing method, so I also wrote it for everyone to learn

import (
	"fmt"
	"math/rand"
	"net/url"
	"reflect"
	"strconv"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/xuri/excelize/v2"
)

var (
	defaultSheetName = "Sheet1" //默認(rèn)Sheet名稱
	defaultHeight    = 25.0     //默認(rèn)行高度
)

type lzExcelExport struct {
	file      *excelize.File
	sheetName string //可定義默認(rèn)sheet名稱
}

func NewMyExcel() *lzExcelExport {
	return &lzExcelExport{file: createFile(), sheetName: defaultSheetName}
}

//導(dǎo)出基本的表格
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
}

//導(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"}}`)
	var word = 'A'
	//首行寫(xiě)入
	for _, conf := range params {
		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++
	}
}

//寫(xiě)入數(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ù)寫(xiě)入
	var j = 2 //數(shù)據(jù)開(kāi)始行數(shù)
	for i, val := range data {
		//設(shè)置行高
		_ = l.file.SetRowHeight(l.sheetName, i+1, defaultHeight)
		//逐列寫(xiě)入
		var word = 'A'
		for _, conf := range params {
			valKey := conf["key"]
			line := fmt.Sprintf("%c%v", word, j)
			isNum := conf["is_num"]

			//設(shè)置值
			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])
			}

			//設(shè)置樣式
			_ = l.file.SetCellStyle(l.sheetName, line, line, lineStyle)
			word++
		}
		j++
	}
	//設(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)
}

func createFile() *excelize.File {
	f := excelize.NewFile()
	// 創(chuàng)建一個(gè)默認(rèn)工作表
	sheetName := defaultSheetName
	index := f.NewSheet(sheetName)
	// 設(shè)置工作簿的默認(rèn)工作表
	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導(dǎo)出(數(shù)據(jù)源為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("錯(cuò)誤--", 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(&#39;A&#39;+i)))
	}
	return str
}

4.2. userService.go (accepting requests)

The exported functions have been tested and are ok. They can be used directly. Just change the data to your own.
I also included the things to note. Written, lightning protection! !

import (
	"go-excel/app/excelize"
	"go-excel/app/model"
	config "go-excel/common"
	"github.com/gin-gonic/gin"
)



//獲取所有用戶數(shù)據(jù)-excel
func GetAllUserExportToWeb(ctx *gin.Context) {
	var users []model.TUser
	db := config.GetDB()
	db.Find(&users)

	//定義首行標(biāo)題
	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",
	})

	//填充數(shù)據(jù)
	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盤(pán)
	ex.ExportToPath(dataKey, data, "D:/")
}

//excel 導(dǎo)出
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 {
		//這里最好新建一個(gè)struct 和titles一致，不然users里面的多余的字段也會(huì)寫(xiě)進(jìn)去
		datas = append(datas, model.TUser{
			ID:       v.ID,
			Username: v.Username,
			Remark:   v.Remark,
		})
	}
	ex.ExportExcelByStruct(titles, datas, "用戶數(shù)據(jù)", "用戶", ctx)
}

4.2. Test results

GetAllUserExportToWeb

GetUserExcelByMap

The above is the detailed content of How to combine Go with Gin to export Mysql data to Excel table. For more information, please follow other related articles on the PHP Chinese website!