golang execl导出

demo

package util

import (
	"fmt"

	"github.com/sirupsen/logrus"
	"github.com/xuri/excelize/v2"
)

type ExcelizeTool struct {
	f *excelize.File
}

const (
	OutPath         = "./public"
	Sheet1          = "Sheet1"
	DefaultFileName = "out"
	DefaultFileType = ".xlsx"
)

var cols = []string{
	"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z",
	"AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ",
}

func (exc *ExcelizeTool) Init() {
	exc.f = excelize.NewFile()
}

// 设置表头
func (exc *ExcelizeTool) SetTabHeader(sheet string, tit []string) {

	if len(tit) > len(cols) {

		logrus.WithField("AppendCol：", "数据长度超长").Error("exceliz1")
		return
	}

	for k, v := range tit {

		exc.f.SetCellValue(sheet, fmt.Sprintf("%s%d", cols[k], 1), v)
	}
}

// 插入数据
func (exc *ExcelizeTool) AppendCol(sheet string, index int, col []interface{}) {

	if len(col) > len(cols) {

		logrus.WithField("AppendCol：", "数据长度超长").Error("exceliz2")
		return
	}

	for k, v := range col {

		exc.f.SetCellValue(sheet, fmt.Sprintf("%s%d", cols[k], index+2), v)
	}
}

// 导出
func (exc *ExcelizeTool) Output(filename, filetype string) (uploadPth string, err error) {

	if filename == "" {
		filename = DefaultFileName
	}

	if filetype == "" {
		filetype = DefaultFileType
	}

	uploadPth = fmt.Sprintf("%s/%s%s", OutPath, filename, filetype)
	if err = exc.f.SaveAs(uploadPth); err != nil {
		return
	}

	return uploadPth[1:], err
}

调用

var exc util.ExcelizeTool
exc.Init()

exc.SetTabHeader(util.Sheet1, []string{"编号", "名称", "设备种类", "设备类型", "功率", "算力", "品质", "状态", "到库时间", "启动时间", "上次启动时间", "维修时间", "下架时间"})

for i, v := range list {

	exc.AppendCol(util.Sheet1, i, []interface{}{v.MaterialID, v.MaterialName, v.DeviceCategory, v.DeviceType, v.Power, v.Pow, v.QualityType, v.Status.Text(), v.InTime, v.BeginTime, v.LastBeginTime, v.MaintainTime, v.StopTime})
}

path ,err := exc.Output(fmt.Sprintf("%v%d", time.Now().Format(time.RFC3339), actUid), "")