gin excel异步导出

最新推荐文章于 2025-10-28 17:21:18 发布

原创最新推荐文章于 2025-10-28 17:21:18 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#gin #excel

该博客围绕新项目的数据导出功能展开，采用异步导出设计，包含添加和执行导出任务、任务中心及文件下载。介绍了系统环境，使用golang 1.18和gin框架，基础库为Excelize。详细给出异步导出接口、Excel导出功能、文件压缩及下载文件接口的代码，并提及相关注意事项。

前言

新项目需要具有据数据导出功能，整体功能设计为异步导出，包括添加导出任务并执行导出(本次设计为一步执行，也可以分两步执行)，任务中心，文件下载功能。

系统环境

golang 1.18
gin框架
基础库: Excelize
文档地址: 介绍 · Excelize 简体字文档

异步导出接口

执行流程：添加导出任务(添加成功)->执行导出任务。

不画流程图了直接上代码。

func Export(ctx *gin.Context) {
	var order ValidateRules.GetOrder
	err := ctx.ShouldBindQuery(&order)
	if err != nil {
		response.Error(ctx, http.StatusInternalServerError, err.Error())
		return
	}

	res := validator.BaseValidate(ctx, order)
	if !res {
		return
	}

    // 创建导出任务,成功执行异步导出,失败返回结果
	ret, taskId := common.CreateExportTask(order)
	if !ret {
		response.Error(ctx, http.StatusInternalServerError, "创建导出任务失败")
        return
	}
    
    // 另开一个线程执行导出任务
	go common.AsyncExport(order, taskId)
	response.Success(ctx, true)
}

Excel导出功能

基础库引入

# 安装命令
go get github.com/xuri/excelize

# 如果您使用 Go Modules 管理软件包，请使用下面的命令来安装最新版本
go get github.com/xuri/excelize/v2

测试代码

package test

import (
	"DeliciousTown/app/util/common"
	"fmt"
	"testing"
)

const defaultSheetName = "Sheet"

func TestExport(t *testing.T) {
	var specialWidth []*common.SpecialWidth
	specialWidth = common.InitSpecialWidth()
	list := common.InitData()
	title := common.InitTitle()
	res := common.Export(title, list, defaultSheetName, specialWidth)
	if res != nil {
		fmt.Print(res.Error())
	}
}

数据导出代码，excel样式文档地址:样式 · Excelize 简体字文档

package common

import (
	"github.com/xuri/excelize/v2"
	"strconv"
	"time"
)

type Data struct {
	Id      int
	OrderNo string
	State   int
	Account string
	Amount  float64
	Time    time.Time
}

type SpecialWidth struct {
	Col   string
	Width float64
}

// 默认宽度
const defaultWidth = 15

func InitData() []*Data {
	payTime, _ := time.Parse("2006-01-02 15:04:05", "2023-08-04 12:00:00")
	return []*Data{
		{1, "OrderNo-20230803000000", 1, "xiaoming", 12.00, payTime},
		{2, "OrderNo-20230803000001", 2, "xiaoming", 0.01, payTime},
		{3, "OrderNo-20230803000002", 1, "xiaohong", 12.00, payTime},
		{4, "OrderNo-20230803000003", 1, "xiaohong", 15.00, payTime},
	}
}

func InitTitle() []string {
	return []string{"ID", "订单号", "状态", "用户", "金额", "时间"}
}

func InitSpecialWidth() []*SpecialWidth {
	return []*SpecialWidth{
		{"B", 30},
		{"F", 20},
	}
}

// 获取表头样式
func GetTitleStyle(file *excelize.File) (titleStyle int, err error) {
	return file.NewStyle(&excelize.Style{
		Border: []excelize.Border{
			{Type: "left", Color: "FF99CC", Style: 2},
			{Type: "top", Color: "FF99CC", Style: 2},
			{Type: "bottom", Color: "FF99CC", Style: 2},
			{Type: "right", Color: "FF99CC", Style: 2},
		},
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#FFFFCC"},
			Pattern: 1,
		},
		Font: &excelize.Font{
			Bold: true,
			Size: 16,
		},
	})
}

// 获取数据样式
func GetDataStyle(file *excelize.File) (titleStyle int, err error) {
	return file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "center",
			Vertical:   "center",
		},
		Border: []excelize.Border{
			{Type: "left", Color: "FFFFFF", Style: 9},
			{Type: "top", Color: "FFFFFF", Style: 9},
			{Type: "bottom", Color: "FFFFFF", Style: 9},
			{Type: "right", Color: "FFFFFF", Style: 9},
		},
		Fill: excelize.Fill{
			Type:    "pattern",
			Color:   []string{"#CCCCCC"},
			Pattern: 3,
		},
		Font: &excelize.Font{
			Size: 13,
		},
	})
}

// 设置表头及表头样式
func SetTitleWithStyle(eFile *excelize.File, sheetName string, title []string, titleLen int) error {
	style, err := GetTitleStyle(eFile)
	if err != nil {
		return err
	}

	hCell, _ := excelize.CoordinatesToCellName(1, 1)
	vCell, _ := excelize.CoordinatesToCellName(titleLen, 1)
	err = eFile.SetCellStyle(sheetName, hCell, vCell, style)
	if err != nil {
		return err
	}

	for key, val := range title {
		col := key + 1
		cellName, _ := excelize.CoordinatesToCellName(col, 1)
		err = eFile.SetCellValue(sheetName, cellName, val)
		if err != nil {
			panic(err)
		}
	}

	return nil
}

// 设置数据样式
func SetListStyle(eFile *excelize.File, sheetName string, dataLen int, titleLen int) error {
	style, err := GetDataStyle(eFile)
	if err != nil {
		return err
	}

	hCell, _ := excelize.CoordinatesToCellName(1, 2)
	vCell, _ := excelize.CoordinatesToCellName(titleLen, dataLen+1)
	err = eFile.SetCellStyle(sheetName, hCell, vCell, style)
	if err != nil {
		return err
	}

	return nil
}

// 设置列宽
func SetWidth(eFile *excelize.File, sheetName string, cellNum int, specialWidth []*SpecialWidth) {
	startCol, _ := excelize.ColumnNumberToName(1)
	endCol, _ := excelize.ColumnNumberToName(cellNum)
	_ = eFile.SetColWidth(sheetName, startCol, endCol, defaultWidth)
	if len(specialWidth) > 0 {
		for _, val := range specialWidth {
			_ = eFile.SetColWidth(sheetName, val.Col, val.Col, val.Width)
		}
	}
}

// 数据写入
func SetData(eFile *excelize.File, list []*Data, sheetName string)  {
	for key, val := range list {
		row := key + 2
		cell := "A" + strconv.Itoa(row)
		cells := []interface{}{val.Id, val.OrderNo, val.State, val.Account, val.Amount, val.Time}
		err := eFile.SetSheetRow(sheetName, cell, &cells)
		if err != nil {
			panic(err)
		}
	}
}

func Export(title []string, list []*Data, sheetName string, specialWidth []*SpecialWidth) error {
	dataLen := len(list)
	titleLen := len(title)
	eFile := excelize.NewFile()
	sheet, err := eFile.NewSheet(sheetName)
	if err != nil {
		return err
	}

	eFile.SetActiveSheet(sheet)
	SetWidth(eFile, sheetName, titleLen, specialWidth)
	err = SetTitleWithStyle(eFile, sheetName, title, titleLen)
	if err != nil {
		return err
	}

	err = SetListStyle(eFile, sheetName, dataLen, titleLen)
	if err != nil {
		return err
	}

	SetData(eFile, list, sheetName)
	if sheetName != "Sheet1" {
		_ = eFile.DeleteSheet("Sheet1")
	}

	err = eFile.SaveAs("test.xlsx")
	if err != nil {
		return err
	}

	return nil
}

emmm~~~不知道要说些什么看下效果图，表格时间是天数(excel使用的是格林威治时间,开始时间1900-01-01)，慎用time.Duration

注意事项

关于Sheet，如果sheet名称不为Sheet1需要把默认的sheet删除掉，不然会多出默认的Sheet1

关于数据类型设置，如无类型修改不需要手动去设置excel表格列数据类型，excelize已经为我们处理过了，以下为相关源码

// 设置单元格值
func (f *File) SetCellValue(sheet, cell string, value interface{}) error {
	var err error
	switch v := value.(type) {
	case int, int8, int16, int32, int64, uint, uint8, uint16, uint32, uint64:
		err = f.setCellIntFunc(sheet, cell, v)
	case float32:
		err = f.SetCellFloat(sheet, cell, float64(v), -1, 32)
	case float64:
		err = f.SetCellFloat(sheet, cell, v, -1, 64)
	case string:
		err = f.SetCellStr(sheet, cell, v)
	case []byte:
		err = f.SetCellStr(sheet, cell, string(v))
	case time.Duration:
		_, d := setCellDuration(v)
		err = f.SetCellDefault(sheet, cell, d)
		if err != nil {
			return err
		}
		err = f.setDefaultTimeStyle(sheet, cell, 21)
	case time.Time:
		err = f.setCellTimeFunc(sheet, cell, v)
	case bool:
		err = f.SetCellBool(sheet, cell, v)
	case nil:
		err = f.SetCellDefault(sheet, cell, "")
	default:
		err = f.SetCellStr(sheet, cell, fmt.Sprint(value))
	}
	return err
}

// 设置一行的值
func (f *File) SetSheetRow(sheet, cell string, slice interface{}) error {
	return f.setSheetCells(sheet, cell, slice, rows)
}


// setSheetCells provides a function to set worksheet cells value.
func (f *File) setSheetCells(sheet, cell string, slice interface{}, dir adjustDirection) error {
	col, row, err := CellNameToCoordinates(cell)
	if err != nil {
		return err
	}
	// Make sure 'slice' is a Ptr to Slice
    // 通过反射获取值信息
	v := reflect.ValueOf(slice)
	if v.Kind() != reflect.Ptr || v.Elem().Kind() != reflect.Slice {
		return ErrParameterInvalid
	}
	v = v.Elem()
	for i := 0; i < v.Len(); i++ {
		var cell string
		var err error
		if dir == rows {
			cell, err = CoordinatesToCellName(col+i, row)
		} else {
			cell, err = CoordinatesToCellName(col, row+i)
		}
		// Error should never happen here. But keep checking to early detect regressions
		// if it will be introduced in the future.
		if err != nil {
			return err
		}
        
        // 此处调用设置单元格值
		if err := f.SetCellValue(sheet, cell, v.Index(i).Interface()); err != nil {
			return err
		}
	}
	return err
}

数据表样式范围设定，根据左上，右下的传参方式，以下为相关源码

func (f *File) SetCellStyle(sheet, hCell, vCell string, styleID int) error {
	hCol, hRow, err := CellNameToCoordinates(hCell)
	if err != nil {
		return err
	}

	vCol, vRow, err := CellNameToCoordinates(vCell)
	if err != nil {
		return err
	}

	// Normalize the range, such correct C1:B3 to B1:C3.
	if vCol < hCol {
		vCol, hCol = hCol, vCol
	}

	if vRow < hRow {
		vRow, hRow = hRow, vRow
	}

	hColIdx := hCol - 1
	hRowIdx := hRow - 1

	vColIdx := vCol - 1
	vRowIdx := vRow - 1

	ws, err := f.workSheetReader(sheet)
	if err != nil {
		return err
	}
	prepareSheetXML(ws, vCol, vRow)
	makeContiguousColumns(ws, hRow, vRow, vCol)
	ws.Lock()
	defer ws.Unlock()

	s, err := f.stylesReader()
	if err != nil {
		return err
	}
	s.Lock()
	defer s.Unlock()
	if styleID < 0 || s.CellXfs == nil || len(s.CellXfs.Xf) <= styleID {
		return newInvalidStyleID(styleID)
	}

	for r := hRowIdx; r <= vRowIdx; r++ {
		for k := hColIdx; k <= vColIdx; k++ {
			ws.SheetData.Row[r].C[k].S = styleID
		}
	}
	return err
}

文件压缩

如果有分很多文件又要一次性下载这种场景就需要生成压缩包了(我的业务场景)，以下是文件压缩相关的代码

// 获取文件夹下的所有文件信息
func GetFilesName(path string) []string {
	var files []string
	_ = filepath.Walk(path, func(path string, info os.FileInfo, err error) error {
		if info.IsDir() || err != nil {
			return nil
		}

		file, err := os.Open(path)
		if err != nil {
			return nil
		}

		defer file.Close()
		files = append(files, file.Name())
		return nil
	})

	return files
}

func Zip(name string, files []string) bool {
	zipPack, err := os.Create(name)
	if err != nil {
		fmt.Print("创建文件失败!!!")
		return false
	}

	defer zipPack.Close()
	z := zip.NewWriter(zipPack)
	for _, file := range files {
		src, err := os.Open(file)
		if err != nil {
			return false
		}

		info, err := src.Stat()
		if err != nil {
			return false
		}

		hdr, err := zip.FileInfoHeader(info)
		if err != nil {
			return false
		}

		// Write only the base name in the header
		hdr.Name = filepath.Base(file)
		dst, err := z.CreateHeader(hdr)
		if err != nil {
			return false
		}

		_, err = io.Copy(dst, src)
		if err != nil {
			return false
		}

		src.Close()
	}

	// 关闭 zip.Writer
	err = z.Close()
	if err != nil {
		fmt.Print("关闭写入失败!!!")
	}

	return true
}

下载文件接口

直接上代码

func DownloadExportFile(ctx *gin.Context) {
	taskId := ctx.Query("id")
	Id, err := strconv.Atoi(taskId)
	if err != nil {
		response.Error(ctx, http.StatusInternalServerError, "传参类型错误")
		return
	}

    // 获取任务信息
	taskInfo, res := common.GetTaskInfo(Id)
	if res != nil {
		response.Error(ctx, http.StatusInternalServerError, "获取任务失败")
		return
	}

    // 校验状态
	if taskInfo.State != 2 {
		response.Error(ctx, http.StatusInternalServerError, "导出任务执行失败")
		return
	}

    // 校验文件
	file, msg := os.Open(taskInfo.FilePath)
	if msg != nil {
		response.Error(ctx, http.StatusInternalServerError, "文件不存在或文件已损坏")
		return
	}

	defer file.Close()
	fileName := path.Base(taskInfo.FilePath)
	ctx.Header("Content-Type", "application/octet-stream")
	ctx.Header("Content-Disposition", "attachment; filename="+fileName)
	ctx.Header("Content-Transfer-Encoding", "binary")
	ctx.File(taskInfo.FilePath)
	return
}

结束语

以上就是gin+Excelize的简单使用，稍作修改即可使用，如有错误请指正，祝大家事事順遂，生活幸福愉快！！！