Go实战全家桶之84: goExcel 通用导出框架

   本文展示了一个基于Go语言的Excel导出功能实现。代码包含两个主要部分：
1) 测试用例展示了如何查询计件工资数据并导出Excel文件；
2) GoexcelFrame结构体封装了Excel操作的核心功能，包括数据准备、表头设置、数据类型处理等。
   实现支持多种数据类型(整数、小数、日期等)的导出，并提供了文件名生成、文件保存等功能。通过PutInt、PutDecimal等方法可以灵活设置列格式，最终生成的Excel文件包含格式化后的数据和表头。
   该模块通过结构化的方式简化了Excel导出流程，适用于各种业务数据的报表导出场景。

测试用例：

func (self *TestDbSuite) QueryPage(ym int) *pagemodel.PageResult[*dbentity.HrmsPieceWage] {

    var dao = dbentity.NewDaoHrmsPieceWage()
    //dao.SetQueryZero(true)
    dao.DbEq("ym", ym)
    dao.DbGt("item_id", 0)
    dao.DbGt("emp_id", 0)
    dao.OrderByDesc("ym")
    dao.OrderByDesc("dept_id")
    dao.OrderByDesc("emp_id")

    var ret = dao.QueryModelAll()
    if ret.ExistRecord() {
       for i := range ret.Data {
          ret.Data[i].InitEmptyName()
       }
    }
    return ret
}

func (self *TestDbSuite) Test006_HrmsPieceWage() {

    var ret = self.QueryPage(202507)
    var excel = goexcel.FindBeanExcelFrame()
    if ret.ExistRecord() {
       excel.Prepare(dbentity.NewHrmsPieceWage(), ret.MapDefault().Data)
       excel.PutInt("ym", "计件月度")
       excel.PutKey("department_name", "部门")
       excel.PutKey("emp_no", "工号")
       excel.PutKey("emp_name", "姓名")
       excel.PutKey("order_no", "单号")
       excel.PutKey("item_no", "款号")
       excel.PutDeciaml("order_qty", "订单数量")
       excel.PutDeciaml("price", "单价")
       excel.PutDate("work_at", "生产日期")
       excel.PutDeciaml("product_qty", "生产数量")
       excel.PutDeciaml("payment", "工资")
       excel.PutDate("created_at", "建档日期")

       excel.ExportFile()
    }

}

再封装

func (self *TestDbSuite) Test007_HrmsPieceWageEmp() {

    var ret = uidto.FindBeanPayRequest().ExportFileEmp(0)
    golog.Info(ret)

}
func (self *TestDbSuite) Test008_HrmsPieceWage() {

    var ret = uidto.FindBeanPayRequest().ExportFileOrder(0)
    golog.Info(ret)

}
func (self *TestDbSuite) Test009_HrmsPieceWage() {

    var ret = uidto.FindBeanPayRequest().ExportFile(0)
    golog.Info(ret)

}

实现

package goexcel

import (
    "fmt"
    "gitee.com/gowebframe/webframe/goconfig/base/basedata"
    "gitee.com/gowebframe/webframe/goconfig/base/basedto"
    "gitee.com/gowebframe/webframe/goconfig/base/jsonutils"
    "gitee.com/gowebframe/webframe/goconfig/ichublog/golog"
    "gitee.com/leijmdas/lo"
    "github.com/duke-git/lancet/random"
    "github.com/gogf/gf/v2/util/gconv"
    "github.com/xuri/excelize/v2"
    "goapi/domain/ui/uibase"
    "goapi/goutil/goconst"
    "goapi/goutil/godto/exceldto"
    "goapi/goutil/goutil"
    "gorm.io/gorm/schema"
    "strings"
    "time"
)

type GoexcelFrame struct {
    basedto.BaseEntity
    uibase.UiqueryBase
    Headers    []*exceldto.ExcelHeader

    titles     []string
    keyNames   []string
    key2Titles map[string]string
    rows       []map[string]any

    fileName  string
    sheetName string

    randFileName bool
    ReportType   string

    startSeq  int
    *basedata.DataPath
}

func NewGoexcelFrame() *GoexcelFrame {
    return &GoexcelFrame{
       randFileName: false,
       ReportType:   goconst.REPORT_DIMENSION_TYPE_ITEM,
       DataPath:     basedata.FindBeanDataPath(),
       Headers:      make([]*exceldto.ExcelHeader, 0),
       sheetName:    "Sheet1",
       fileName:     "go.xlsx",
       titles:       []string{"ID", "NAME", "PRICE"},
       keyNames:     []string{"ID", "Name", "Price"},
       rows: []map[string]any{
          {"ID": 1, "Name": "苹果", "Price": 8},
          {"ID": 2, "Name": "雪梨", "Price": 3},
          {"ID": 3, "Name": "香蕉", "Price": 5},
       }}
}
func (self *GoexcelFrame) clear() {
    self.titles = []string{}
    self.keyNames = []string{}
    self.rows = []map[string]any{}
    self.key2Titles = map[string]string{}
}
func (self *GoexcelFrame) Prepare(table schema.Tabler, list []*map[string]any) error {
    self.fileName = table.TableName() + ".xlsx"
    return self.SaveDbPrepare(list)
}
func (self *GoexcelFrame) SaveDbPrepare(list []*map[string]any) error {
    self.clear()
    for i := range list {
       self.rows = append(self.rows, jsonutils.Stru2Map(list[i]))
    }
    if len(self.rows) > 0 {

       for k, _ := range self.rows[0] {
          self.titles = append(self.titles, k)
          self.keyNames = append(self.keyNames, k)
          self.key2Titles[k] = k
       }

    }
    return nil
}
func (self *GoexcelFrame) PutKey(key, name string, dataType ...int) *exceldto.ExcelHeader {
    self.startSeq++
    self.key2Titles[key] = name
    var header = &exceldto.ExcelHeader{}
    header.Key = key
    header.Sequence = self.startSeq
    header.Title = name
    if len(dataType) > 0 {
       header.PropDbType = dataType[0]
    }
    self.Headers = append(self.Headers, header)
    return header
}

func (self *GoexcelFrame) PutInt(key, name string) *exceldto.ExcelHeader {

    return self.PutKey(key, name).PutInt()
}
func (self *GoexcelFrame) PutDeciaml(key, name string) *exceldto.ExcelHeader {

    return self.PutKey(key, name).PutDecimal()
}
func (self *GoexcelFrame) PutDate(key, name string) *exceldto.ExcelHeader {

    return self.PutKey(key, name).PutDate()
}
func (self *GoexcelFrame) PutDatetime(key, name string) *exceldto.ExcelHeader {

    return self.PutKey(key, name).PutDatetime()
}
func (self *GoexcelFrame) ExportFile() error {

    self.titles = []string{}
    for _, v := range self.key2Titles {
       self.titles = append(self.titles, v)
    }
    self.TransBody()

    var f = self.NewFile()
    self.WriteHead(f)
    self.WriteBody(f)
    return self.SaveFile(f)
}
func (self *GoexcelFrame) TransBody() error {
    golog.Info("TransBody...")
    var headers = self.FilterHeaders()
    for i := range self.rows {
       for col := range headers {
          var header = headers[col]
          var v = self.rows[i][header.Key]
          if header.IfDbDate() {
             self.rows[i][header.Key] = goutil.Utc2Beijing(v.(string)).Format(time.DateOnly)
          } else if header.IfDecimal() {
             self.rows[i][header.Key] = gconv.Float64(v)
          } else if header.IfInt() {
             self.rows[i][header.Key] = gconv.Int64(v)
          }
       }
    }
    self.FillData(self.rows)
    return nil
}
func (self *GoexcelFrame) WriteHead(f *excelize.File) {
    // 设置表头
    for col := range self.Headers {
       cellName, _ := excelize.CoordinatesToCellName(col+1, 1, false)

       f.SetCellValue(self.sheetName, cellName, self.Headers[col].Title)
    }
}
func (self *GoexcelFrame) NewFile() *excelize.File {
    f := excelize.NewFile()
    return f
}
func (self *GoexcelFrame) WriteBody(f *excelize.File) {
    // 写入数据
    for i := range self.rows {
       for col := range self.Headers {
          cellName, _ := excelize.CoordinatesToCellName(col+1, i+2, false)
          var key = self.Headers[col]
          f.SetCellValue(self.sheetName, cellName, self.rows[i][key.Key])
       }
    }

}
func (self *GoexcelFrame) FullFilepath() string {
    var paths = []any{
       self.DataOutPath,
       strings.ReplaceAll(self.fileName, ".xlsx", "_"),
       self.ReportType,
       "_",
       random.RandInt(1, 10000),
       ".xlsx",
    }
    return strings.Join(gconv.SliceStr(paths), "")
}
func (self *GoexcelFrame) SaveFile(f *excelize.File) error {
    if err := f.SaveAs(self.FullFilepath()); err != nil {
       fmt.Println(err)
       return err
    }
    return nil
}

func (self *GoexcelFrame) Titles() []string {
    return self.titles
}

func (self *GoexcelFrame) SetTitles(titles []string) {
    self.titles = titles
}

func (self *GoexcelFrame) KeyNames() []string {
    return self.keyNames
}

func (self *GoexcelFrame) SetKeyNames(keyNames []string) {
    self.keyNames = keyNames
}

func (self *GoexcelFrame) Rows() []map[string]any {
    return self.rows
}

func (self *GoexcelFrame) SetRows(rows []map[string]any) {
    self.rows = rows
}

func (self *GoexcelFrame) FileName() string {
    return self.fileName
}

func (self *GoexcelFrame) SetFileName(fileName string) *GoexcelFrame {
    self.fileName = fileName
    return self
}

func (self *GoexcelFrame) SheetName() string {
    return self.sheetName
}

func (self *GoexcelFrame) SetSheetName(sheetName string) {
    self.sheetName = sheetName
}
func (self *GoexcelFrame) FilterHeaders() []*exceldto.ExcelHeader {
    return lo.Filter(self.Headers, func(item *exceldto.ExcelHeader, index int) bool {
       return item.IfExport()
    })
}

package exceldto

import (
    "gitee.com/gowebframe/webframe/goconfig/base/basedto"
    "goapi/goutil/goconst"
)

type ExcelHeader struct {
    basedto.BaseEntity `json:"-"`
    Key                string
    Title              string
    PropDbType         int
    width              int
    Sequence           int
}

func NewExcelHeader() *ExcelHeader {
    return &ExcelHeader{
       PropDbType: goconst.GODB_TYPE_STRING,
    }
}
func (self *ExcelHeader) IfDbDate() bool {
    return self.PropDbType == goconst.GODB_TYPE_DATE
}
func (self *ExcelHeader) IfDbDatetime() bool {
    return self.PropDbType == goconst.GODB_TYPE_DATETIME
}

func (self *ExcelHeader) IfInt() bool {
    return self.PropDbType == goconst.GODB_TYPE_INT
}
func (self *ExcelHeader) IfDecimal() bool {
    return self.PropDbType == goconst.GODB_TYPE_DECIMAL
}
func (self *ExcelHeader) IfString() bool {
    return self.PropDbType == goconst.GODB_TYPE_STRING
}

func (self *ExcelHeader) IfExport() bool {
    return self.Key != self.Title
}

func (self *ExcelHeader) PutInt() *ExcelHeader {
    self.PropDbType = goconst.GODB_TYPE_INT
    return self
}

func (self *ExcelHeader) PutDecimal() *ExcelHeader {
    self.PropDbType = goconst.GODB_TYPE_DECIMAL
    return self
}
func (self *ExcelHeader) PutDate() *ExcelHeader {
    self.PropDbType = goconst.GODB_TYPE_DATE
    return self

}
func (self *ExcelHeader) PutDatetime() *ExcelHeader {
    self.PropDbType = goconst.GODB_TYPE_DATETIME
    return self

}

func (self *ExcelHeader) Width() int {
    return self.width
}

func (self *ExcelHeader) SetWidth(width int) {
    self.width = width
}

func (self *PayRequest) ExportFile(ym int) *page.PageResult {

    var ret = self.QueryModelMax(ym)
    var excel = goexcel.FindBeanExcelFrame()
    excel.ReportType = goconst.REPORT_DIMENSION_TYPE_ITEM
    
    if ret.ExistRecord() {
       excel.Prepare(dbentity.NewHrmsPieceWage(), ret.MapDefault().Data)
       excel.PutInt("ym", "计件月度").SetWidth(100)
       excel.PutKey("department_name", "部门")
       excel.PutKey("emp_no", "工号")
       excel.PutKey("emp_name", "姓名")
       excel.PutKey("order_no", "单号")
       excel.PutKey("item_no", "款号")
       excel.PutDeciaml("order_qty", "订单数量")
       excel.PutDeciaml("price", "单价（元）")
       excel.PutDate("work_at", "生产日期")
       excel.PutDeciaml("product_qty", "生产数量")
       excel.PutDeciaml("payment", "工资（元）")
       // excel.PutDate("created_at", "建档日期")
       ret.Hosturl = excel.FullFilepath()
       var err = excel.ExportFile()
       if err != nil {
          ret.CodeMsg(500, err.Error())
          return ret.PageResult
       }
    }
    ret.PageResult.Data = nil
    return ret.PageResult

｝

func (self *PayRequest) QueryModelMax(ym int) *pagemodel.PageResult[*dbentity.HrmsPieceWage] {
    if ym == 0 {
       ym = gconv.Int(time.Now().Format("200601"))
    }
    var dao = dbentity.NewDaoHrmsPieceWage()
    dao.DbEq("ym", ym)
    dao.DbGt("item_id", 0)
    dao.DbGt("emp_id", 0)
    dao.OrderByDesc("ym")
    dao.OrderByDesc("dept_id")
    dao.OrderByDesc("emp_id")
    var ret = dao.QueryModelMax()
    if ret.ExistRecord() {
       for i := range ret.Data {
          ret.Data[i].InitEmptyName()
       }
    }
    return ret
}

func (self *TestDbSuite) Test007_HrmsPieceWageEmp() {

    var ret = uidto.FindBeanPayRequest().ExportFileEmp(0)
    golog.Info(ret)

}

func (self *PayRequest) ExportFileEmp(ym int) *page.PageResult {

    var ret = self.QueryModelMax(ym)
    self.sum2Emp(ret)
    var excel = goexcel.FindBeanExcelFrame()
    excel.ReportType = goconst.REPORT_DIMENSION_TYPE_EMP
    if ret.ExistRecord() {
       excel.Prepare(dbentity.NewHrmsPieceWage(), ret.MapDefault().Data)
       excel.PutInt("ym", "计件月度")
       excel.PutKey("department_name", "部门")
       excel.PutKey("emp_no", "工号")
       excel.PutKey("emp_name", "姓名")
       excel.PutDeciaml("order_qty", "订单数量")
       excel.PutDeciaml("product_qty", "生产数量")
       excel.PutDeciaml("payment", "工资（元）")
       ret.Hosturl = excel.FullFilepath()
       var err = excel.ExportFile()
       if err != nil {
          ret.CodeMsg(500, err.Error())
          return ret.PageResult
       }
    }
    ret.PageResult.Data = nil
    return ret.PageResult

func (self *PayRequest) sum2Emp(ret *pagemodel.PageResult[*dbentity.HrmsPieceWage]) map[string]*dbentity.HrmsPieceWage {
    var sum = make(map[string]*dbentity.HrmsPieceWage, 0)
    if ret.ExistRecord() {
       for i := 0; i < len(ret.Data); i++ {
          var data = ret.Data[i]
          if k, exist := sum[data.EmpKey()]; exist {
             k.Sum(data)
          } else {
             sum[data.EmpKey()] = data
          }
       }
    }
    ret.Data = make([]*dbentity.HrmsPieceWage, 0)
    for _, v := range sum {
       ret.Data = append(ret.Data, v)
    }
    return sum
}