Golang自定义excel导出

导出实体类

使用 excel 关键字，获取表头

package task

type TaskLogExcel struct {
	CreteTime string `excel:"时间" json:"createTime"`

	Content string `excel:"内容" json:"content"`
}

工具类

package utils

import (
	"encoding/json"
	"errors"
	"fmt"
	"net/url"
	"reflect"

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

// excel 工具类
// dataList  导出列表
// exportInterface  到处结构体类型
// fileName 导出文档名称
// sheetName  sheet名称
func Export(dataList []interface{}, exportInterface interface{}, fileName, sheetName string, c *gin.Context) error {

	if exportInterface == nil {
		return errors.New("导出对象类型不能为空")
	}
	if len(dataList) == 0 {
		return nil
	}
	exportInterfaceType := reflect.TypeOf(exportInterface)
	var titlesCn []interface{}
	var titlesEn []string
	// 新建excel
	excel := excelize.NewFile()
	// 新建sheet
	sw, err := excel.NewStreamWriter("Sheet1")
	if err != nil {
		fmt.Println(err)
		return err
	}
	if err != nil {
		fmt.Println(err)
		return err
	}

	for i := 0; i < exportInterfaceType.NumField(); i++ {
		field := exportInterfaceType.Field(i)
		titlesEn = append(titlesEn, string(field.Tag.Get("json")))
		// 根据上述导出实体类中的excel 关键字获取表头
		titlesCn = append(titlesCn, string(field.Tag.Get("excel")))
	}
	// 设置表头
	if err := sw.SetRow("A1",
		titlesCn,
		excelize.RowOpts{Height: 20, Hidden: false}); err != nil {
		fmt.Println(err)
		return err
	}
	dataCount := len(dataList)
	colCount := len(titlesCn)
	// 循环写入数据
	for rowID := 2; rowID < dataCount+2; rowID++ {
		row := make([]interface{}, colCount)
		var dataMap map[string]string
		data := dataList[rowID-2]
		fmt.Println(data)
		dataJson, _ := json.Marshal(data)
		err1 := json.Unmarshal(dataJson, &dataMap)
		if err1 != nil {
			fmt.Println(err1)
		}
		for colID := 0; colID < colCount; colID++ {
			row[colID] = dataMap[titlesEn[colID]]
		}
		// 坐标转换
		cell, err := excelize.CoordinatesToCellName(1, rowID)
		if err != nil {
			fmt.Println(err)
			break
		}
		if err := sw.SetRow(cell, row); err != nil {
			fmt.Println(err)
			break
		}
	}
	if err := sw.Flush(); err != nil {
		fmt.Println(err)
		return err
	}

	// 设置响应头
	c.Writer.Header().Set("Content-Transfer-Encoding", "binary")
	c.Writer.Header().Set("Content-Type", "application/octet-stream")
	c.Writer.Header().Set("Content-Disposition", fmt.Sprintf("attachment; filename*=utf-8''%s.xlsx", url.QueryEscape(fileName)))

	_ = excel.Write(c.Writer)
	return nil
}

导出效果