前言
新项目需要具有据数据导出功能,整体功能设计为异步导出,包括添加导出任务并执行导出(本次设计为一步执行,也可以分两步执行), 任务中心,文件下载功能。
系统环境
- 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的简单使用,稍作修改即可使用,如有错误请指正,祝大家事事順遂,生活幸福愉快!!!