使用Go语言的gorm框架查询数据库并分页导出到Excel实例

文章目录

基本配置
- 配置文件管理
- 命令行工具: Cobra
- - 快速入门
  - 基本用法
生成mock数据
- SQL准备
- gorm自动生成结构体代码
- 生成mock数据
查询数据
导出Excel
- 使用 excelize
- 实现思路
- 完整代码参考
入口文件
效果演示
- 分页导出多个Excel文件
- 合并为一个完整的Excel文件
完整代码

基本配置

配置文件管理

添加依赖 go get github.com/spf13/viper，支持 JSON, TOML, YAML, HCL 等格式的配置文件。

在项目根目录下面新建 conf 目录，然后新建 application.yml 文件,此文件需要忽略版本控制。每次修改后，记得同步修改 conf/application.yml.demo 文件，让别人也知道你添加或修改了哪些内容。

server:port: 8080
datasource:driverName: mysqlhost: "127.0.0.1"port: "3306"database: go-demo-2025username: rootpassword: "123456"charset: utf8loc: Asia/Shanghai

配置初始化: common/initialization.go

// 配置初始化
func InitConfig() {workDir, _ := os.Getwd()               //获取目录对应的路径viper.SetConfigName("application")     //配置文件名viper.SetConfigType("yml")             //配置文件类型（后缀名）viper.AddConfigPath(workDir + "/conf") //执行go run对应的路径配置fmt.Println(workDir)err := viper.ReadInConfig()if err != nil {panic(err)}
}

数据库配置: 使用 gorm 初始化数据库配置,参考 common/database.go 文件

var DB *gorm.DB// https://gorm.io/zh_CN/docs/index.html
func InitDB() *gorm.DB {//从配置文件中读取数据库配置信息host := viper.GetString("datasource.host")port := viper.Get("datasource.port")database := viper.GetString("datasource.database")username := viper.GetString("datasource.username")password := viper.GetString("datasource.password")charset := viper.GetString("datasource.charset")loc := viper.GetString("datasource.loc")args := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s&parseTime=true&loc=%s",username,password,host,port,database,charset,url.QueryEscape(loc))//fmt.Println(args)db, err := gorm.Open(mysql.Open(args), &gorm.Config{Logger: logger.Default.LogMode(logger.Info), //配置日志级别，打印出所有的sql})if err != nil {fmt.Println(err)panic("failed to connect database, err: " + err.Error())}DB = dbreturn db
}

命令行工具: Cobra

Cobra是Go的CLI框架。它包含一个用于创建强大的现代CLI应用程序的库和一个用于快速生成基于Cobra的应用程序和命令文件的工具。

简单理解, 类似于 thinkphp 封装的 php think xxx 的命令行工具.

Cobra 官网: https://cobra.dev

快速入门

安装: go get github.com/spf13/cobra
入口文件: command.go
核心文件: cmd/cobra.go

基本用法

测试Demo: command/testCmd.go

执行: go run command.go testCmd --paramA 100 --paramB 200 hello your name

输出:

--- test 运行 ---
参数个数: 3
100
200
0=>hello
1=>your
2=>name

更多参考: https://www.cnblogs.com/niuben/p/13886555.html

生成mock数据

SQL准备

CREATE TABLE `user` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',`user_id` bigint(20) unsigned NOT NULL COMMENT '用户编号',`name` varchar(255) NOT NULL DEFAULT '' COMMENT '用户姓名',`age` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '用户年龄',`address` varchar(255) NOT NULL DEFAULT '' COMMENT '地址',`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`),UNIQUE KEY `key_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

gorm自动生成结构体代码

需要引入gorm.io/gen扩展，参考代码：gorm_generate_db_struct.go

package mainimport ("gorm.io/driver/mysql""gorm.io/gen""gorm.io/gorm""strings"
)func main() {// 初始化配置common.InitConfig()// 连接数据库db := common.InitDB()// 生成实例g := gen.NewGenerator(gen.Config{// 相对执行`go run`时的路径, 会自动创建目录OutPath: "old_crm_models/query",// WithDefaultQuery 生成默认查询结构体(作为全局变量使用), 即`Q`结构体和其字段(各表模型)// WithoutContext 生成没有context调用限制的代码供查询// WithQueryInterface 生成interface形式的查询代码(可导出), 如`Where()`方法返回的就是一个可导出的接口类型Mode: gen.WithDefaultQuery | gen.WithQueryInterface,// 表字段可为 null 值时, 对应结体字段使用指针类型//FieldNullable: true, // generate pointer when field is nullable// 表字段默认值与模型结构体字段零值不一致的字段, 在插入数据时需要赋值该字段值为零值的, 结构体字段须是指针类型才能成功, 即`FieldCoverable:true`配置下生成的结构体字段.// 因为在插入时遇到字段为零值的会被GORM赋予默认值. 如字段`age`表默认值为10, 即使你显式设置为0最后也会被GORM设为10提交.// 如果该字段没有上面提到的插入时赋零值的特殊需要, 则字段为非指针类型使用起来会比较方便.FieldCoverable: false, // generate pointer when field has default value, to fix problem zero value cannot be assign: https://gorm.io/docs/create.html#Default-Values// 模型结构体字段的数字类型的符号表示是否与表字段的一致, `false`指示都用有符号类型FieldSignable: false, // detect integer field's unsigned type, adjust generated data type// 生成 gorm 标签的字段索引属性FieldWithIndexTag: false, // generate with gorm index tag// 生成 gorm 标签的字段类型属性FieldWithTypeTag: true, // generate with gorm column type tag})// 设置目标 dbg.UseDB(db)// 自定义字段的数据类型// 统一数字类型为int64,兼容protobufdataMap := map[string]func(detailType string) (dataType string){"tinyint":   func(detailType string) (dataType string) { return "int64" },"smallint":  func(detailType string) (dataType string) { return "int64" },"mediumint": func(detailType string) (dataType string) { return "int64" },"bigint":    func(detailType string) (dataType string) { return "int64" },"int":       func(detailType string) (dataType string) { return "int64" },}// 要先于`ApplyBasic`执行g.WithDataTypeMap(dataMap)// 自定义模型结体字段的标签// 将特定字段名的 json 标签加上`string`属性,即 MarshalJSON 时该字段由数字类型转成字符串类型jsonField := gen.FieldJSONTagWithNS(func(columnName string) (tagContent string) {//toStringField := `balance, `toStringField := ``if strings.Contains(toStringField, columnName) {return columnName + ",string"}return columnName})// 将非默认字段名的字段定义为自动时间戳和软删除字段;// 自动时间戳默认字段名为:`updated_at`、`created_at, 表字段数据类型为: INT 或 DATETIME// 软删除默认字段名为:`deleted_at`, 表字段数据类型为: DATETIME//autoUpdateTimeField := gen.FieldGORMTag("update_time", "column:update_time;type:int unsigned;autoUpdateTime")//autoCreateTimeField := gen.FieldGORMTag("create_time", "column:create_time;type:int unsigned;autoCreateTime")// 模型自定义选项组//fieldOpts := []gen.ModelOpt{jsonField, autoCreateTimeField, autoUpdateTimeField}fieldOpts := []gen.ModelOpt{jsonField}// 创建模型的结构体,生成文件在 model 目录; 先创建的结果会被后面创建的覆盖// 创建全部模型文件, 并覆盖前面创建的同名模型allModel := g.GenerateAllTable(fieldOpts...)// 创建模型的方法,生成文件在 query 目录; 先创建结果不会被后创建的覆盖g.ApplyBasic(allModel...)g.Execute()
}

参考： https://segmentfault.com/a/1190000042502370

生成的结构体代码如下：

type User struct {ID         int64     `gorm:"column:id;type:int(11) unsigned;primaryKey;autoIncrement:true;comment:ID" json:"id"`                  // IDUserID     int64     `gorm:"column:user_id;type:bigint(20) unsigned;not null;comment:用户编号" json:"user_id"`                        // 用户编号Name       string    `gorm:"column:name;type:varchar(255);not null;comment:用户姓名" json:"name"`                                     // 用户姓名Age        int64     `gorm:"column:age;type:tinyint(4) unsigned;not null;comment:用户年龄" json:"age"`                                // 用户年龄Address    string    `gorm:"column:address;type:varchar(255);not null;comment:地址" json:"address"`                                 // 地址CreateTime time.Time `gorm:"column:create_time;type:datetime;not null;default:CURRENT_TIMESTAMP;comment:添加时间" json:"create_time"` // 添加时间UpdateTime time.Time `gorm:"column:update_time;type:datetime;not null;default:CURRENT_TIMESTAMP;comment:更新时间" json:"update_time"` // 更新时间
}

生成mock数据

代码路径：service/users/userService.go

// 批量添加mock数据
func (ctx *UserService) BatchCreateMockData() {for i := 1; i <= 82; i++ { //循环操作82次var users []*model.Userfor j := 1; j <= 100; j++ { //一次添加100条数据userid := "1" + fmt.Sprintf("%03d", i) + fmt.Sprintf("%03d", j)useridInt, _ := strconv.Atoi(userid)users = append(users, &model.User{UserID:  int64(useridInt),Name:    funcUtils.GenerateRandomChineseName(),Age:     int64(common.GenerateRandomNumber()),Address: funcUtils.GenerateRandomChinaAddress(),})}err := ctx.GormDB.Create(users).Errorif err != nil {fmt.Println(fmt.Sprintf("第 %d 页添加失败,错误原因:%s", i, err))} else {fmt.Println(fmt.Sprintf("第 %d 页添加成功", i))}}
}

备注：以上代码中关于测试数据生成的工具：

中国地址生成器 https://github.com/GoFinalPack/chinese-address-generator
随机生成中国人姓名 https://www.jianshu.com/p/bab0994647b3

在这里插入图片描述
生成的测试数据效果如下：

查询数据

gorm 查询方法文档: https://gorm.io/zh_CN/docs/query.html

定义查询总数和查询列表的方法，代码路径：service/users/userService.go

// 查询总数
func (ctx *UserService) GetUserCount() int64 {var count int64err := ctx.GormDB.Model(&model.User{}).Where("1=1").Count(&count).Errorif err != nil {fmt.Println(fmt.Sprintf("查询总数错误:%s", err))return 0}//fmt.Println(fmt.Sprintf("总条数:%d", count))return count
}func (ctx *UserService) GetUserList(page int, pageSize int) []model.User {var dataList []model.Useroffset := (page - 1) * pageSize //偏移量err2 := ctx.GormDB.Select("*").Where("1=1").Order("id asc").Limit(pageSize).Offset(offset).Find(&dataList).Errorif err2 != nil {fmt.Println(fmt.Sprintf("查询列表错误:%s", err2))return nil}return dataList
}

导出Excel

使用 excelize

使用率高的几个扩展

excelize (github.com/xuri/excelize/v2)
excelize (github.com/360EntSecGroup-Skylar/excelize)
xlsx (github.com/tealeg/xlsx/v3)
xxhash (github.com/OneOfOne/xxhash)

相关 Excel 开源类库性能对比: https://xuri.me/excelize/zh-hans/performance.html

此处以 github.com/xuri/excelize/v2 为例演示常用的Excel操作方法. 官方中文文档: https://xuri.me/excelize/zh-hans/

安装excelize go get github.com/xuri/excelize/v2

导出 Excel 文档参考代码:

var filePath stringfunc init() {filePath = fmt.Sprintf("files/%s", time.Now().Format("2006/01/02/"))
}// WriteExcel 导出 Excel 文档
// data: 要导出的数据
// return: 文件名, error
func WriteExcel(data [][]string, relativePath string, fileName string) (string, error) {//创建存放目录relativeFilePath := filePath + relativePath + "/"_, err := os.ReadDir(relativeFilePath)if err != nil {// 不存在就创建err = os.MkdirAll(relativeFilePath, fs.ModePerm)if err != nil {fmt.Println(err)}}//创建表格file := excelize.NewFile()sheetName := "Sheet1"index, _ := file.NewSheet(sheetName)for i, row := range data {for j, val := range row {// 列和行 数字索引转excel坐标索引cellName, _ := excelize.CoordinatesToCellName(j+1, i+1)//fmt.Println("cellName:", cellName)// 写入sheetfile.SetCellValue(sheetName, cellName, val)}}file.SetActiveSheet(index)//导出文件filePathName := relativeFilePath + fileName + "_" + common.GetMicroTimestamp() + ".xlsx"err = file.SaveAs(filePathName)if err != nil {return "", err}return filePathName, nil
}

实现思路

导出数据部分，考虑到数据量可能较大，如果一次性查询全量数据，可能造成内存或CPU爆满，因此不建议一次性全部导出，而是采用分页导出到多个文件，然后再将多个文件合并为一个Excel表格文件。

这里需要注意一个细节，就是正常导出的表格数据一般都是按照id（或者添加时间）倒序排列，最新的在前面。但是由于使用了分页导出，如果我们采用 order by id desc limit xxx offset xxx 有可能在分页查询的过程中产生新的数据，那么分页的偏移量（offset）可能导致出现重复数据，就是第一页的某一条数据有可能在第二页重复出现（应该很好理解吧？）。所以查询数据的时候需要 order by id asc 按照 id 从小到大的顺序导出数据就可以避免这个问题。

分页导出后，需要对整体顺序再次反转，最后合并的表格数据才能是按照由新到旧的顺序的结果。

// 二维数组/切片 反转
func ReverseTwoDimSlice(slice [][]string) {// 按照子切片的第0个元素进行倒序排列sort.Slice(slice, func(i, j int) bool {return slice[i][0] > slice[j][0] // 返回true表示i在j之前})
}

导出的表头，可以考虑使用上面 gorm 生成的 struct 部分，通过反射可以获取，核心代码如下：

func GetStructTag(data any) []string {t := reflect.TypeOf(data)var result []stringfor i := 0; i < t.NumField(); i++ {field := t.Field(i)jsonTag := field.Tag.Get("json")//fmt.Printf("Field: %s, Tag: %s\n", field.Name, jsonTag)result = append(result, jsonTag)}return result
}

上面代码传入的 data参数为 model 结构体的 tag 的 json 部分：
在这里插入图片描述
如果需要指定导出的表头字段，可以如下定义：

dataKeySlice = [][]string{{"id", "ID"},{"user_id", "用户ID"},{"name", "用户姓名"},{"age", "年龄"},{"address", "地址"},{"create_time", "添加时间"},{"update_time", "修改时间"},
}
dataKeys, dataKeysTitle := excelUtils.GetDataKeyAndTitle(dataKeySlice)// 根据自定义的二维切片,封装导出的表头字段的key和title
func GetDataKeyAndTitle(dataKeySlice [][]string) ([]string, []string) {//自定义导出的字段var dataKeys []stringvar dataKeysTitle []stringif len(dataKeySlice) > 0 { //如果定义了key对应的字段值for _, v := range dataKeySlice {if len(v[1]) > 0 {dataKeysTitle = append(dataKeysTitle, v[1])} else if len(v[0]) > 0 {dataKeysTitle = append(dataKeysTitle, v[0])}if len(v[0]) > 0 {dataKeys = append(dataKeys, v[0])}}}return dataKeys, dataKeysTitle
}

导出多个Excel文件后，再对它们进行合并为一个Excel文件：

// 合并一个目录下的所有Excel文件
func MergeExcel(dirPath string, outputFileName string, isDeleteOriginFiles bool) string {dir, err := ioutil.ReadDir(dirPath)if err != nil {fmt.Printf("open dir failed: %s\n", err.Error())}//设置路径，文件夹放在main的同级目录下PathSeperator := string(os.PathSeparator)outputdir := dirPath + "/../" + outputFileName//合并后的文件var new_file *xlsx.Filevar new_sheet *xlsx.Sheetnew_file = xlsx.NewFile()var new_err errornew_sheet, new_err = new_file.AddSheet("Sheet1")for _, fi := range dir {//fmt.Printf("open success: %s\n", Pthdir + PthSep+fi.Name())if new_err != nil {fmt.Printf(new_err.Error())}//读取文件xlFile, err := xlsx.OpenFile(dirPath + PathSeperator + fi.Name())if err != nil {fmt.Printf("open failed: %s\n", err)}for _, sheet := range xlFile.Sheets {//fmt.Printf("Sheet Name: %s\n", sheet.Name)num := 0for _, row := range sheet.Rows {num++//跳过前5行，将后面的行写入新的文件//if(num > 5){new_row := new_sheet.AddRow()//new_row.SetHeightCM(1)for _, cell := range row.Cells {text := cell.String()//fmt.Printf("%s\n", text)new_cell := new_row.AddCell()new_cell.Value = text}//}}}}//写入文件new_err = new_file.Save(outputdir)if new_err != nil {fmt.Printf(new_err.Error())}//是否删除原文件if isDeleteOriginFiles {os.RemoveAll(dirPath)}outputFilePath, _ := filepath.Abs(outputdir)return outputFilePath
}

完整代码参考

代码路径： service/users/userDataExportService.go

package usersimport ("fmt""go-demo-2025/common""go-demo-2025/utils/excelUtils""go-demo-2025/utils/funcUtils""math""os""path/filepath""strconv"
)// 通过反射直接获取结构体中的所有数据字段,并转换为map,再根据key的顺序逐一映射到新的切片
func (ctx *UserService) ExportUserList() {requestKey := common.GetYmdHis() + "_" + common.RandomString(10)count := ctx.GetUserCount()count = 1300 //调试数据//分页查询列表pageSize := 1000                                           //每页查询多少条pageCount := math.Ceil(float64(count) / float64(pageSize)) //总页数//fmt.Println("总页数:", pageCount)//自定义导出的字段var dataKeySlice [][]string//如果需要导出数据表的所有字段,则注释下面的二维切片dataKeySlice = [][]string{{"id", "ID"},{"user_id", "用户ID"},{"name", "用户姓名"},{"age", "年龄"},{"address", "地址"},{"create_time", "添加时间"},{"update_time", "修改时间"},}dataKeys, dataKeysTitle := excelUtils.GetDataKeyAndTitle(dataKeySlice)for page := 1; page <= int(pageCount); page++ {dataList := ctx.GetUserList(page, pageSize)var excelData [][]stringfor key, item := range dataList {//如果没有定义指定要导出的字段,则获取数据表的所有字段if len(dataKeys) == 0 { //dataKeys切片(model结构体的tag的json部分)dataKeys = funcUtils.GetStructTag(item)}//结构体转为mapitemMap, _ := funcUtils.StructToMap(item, "json")//fmt.Println(itemMap)//os.Exit(1)//按照顺序将map中的数据填充到key的切片中var itemSlice []string//第一列使用key,下一步排序用itemSlice = append(itemSlice, fmt.Sprintf("%03d", key)) //key前面补两个0,要不然反转的时候会按照字符串顺序排序,导致"2>10".这样改后就是"10>02")for _, keys := range dataKeys {                         //按照dataKeys设定的字段,逐一插入到切片中itemSlice = append(itemSlice, itemMap[keys])}excelData = append(excelData, itemSlice)}funcUtils.ReverseTwoDimSlice(excelData)                     //倒序排列,必须保证第0个元素是 key 值excelData = funcUtils.DeleteTwoDimSliceFirstChar(excelData) //删除第0个元素(key值)pageDiff := int(pageCount) - page + 1//导出Excel文件_, err1 := excelUtils.WriteExcel(excelData, requestKey, fmt.Sprintf("用户数据导出_page_%s", fmt.Sprintf("%04d", pageDiff)))if err1 != nil {fmt.Println("Write excel error: ", err1)os.Exit(1)}//fmt.Println("Write excel success, file name is: ", fileName)}//导出表头var excelDataTitle [][]stringif len(dataKeysTitle) == 0 {dataKeysTitle = dataKeys}excelDataTitle = append(excelDataTitle, dataKeysTitle)fileName, _ := excelUtils.WriteExcel(excelDataTitle, requestKey, "用户数据导出_page_0000")//合并多个文件为一个absPath, _ := filepath.Abs(fileName) // 获取文件的绝对路径dirPath := filepath.Dir(absPath)     //获取文件所在目录的绝对路径//fmt.Println(dirPath)outputFileName := fmt.Sprintf("用户数据导出_%v.xlsx", requestKey)outputFilePath := excelUtils.MergeExcel(dirPath, outputFileName, true)fmt.Println("最终导出的文件:", outputFilePath)
}

入口文件

新增命令行脚本： command/userCmd.go

package commandimport ("fmt""github.com/spf13/cobra""go-demo-2025/service/users""os""time"
)// go run command.go userCmd --operate exportData
func init() {RootCmd.AddCommand(userCmd)
}var userCmd = &cobra.Command{Use:   "userCmd",Short: "关于用户相关的命令行操作",Long:  ``,Run: func(cmd *cobra.Command, args []string) {fmt.Println("--- userCmd 运行 ---")userCmdRun(args)},
}func userCmdRun(args []string) {if operate == "" {fmt.Println("缺少参数operate")os.Exit(1)}if operate == "createMockData" { //生成mock数据fmt.Println("执行createMockData...")service := users.NewUserService()service.BatchCreateMockData()} else if operate == "exportData" { //导出用户数据fmt.Println("执行exportData...")service := users.NewUserService()startTime := time.Now()service.ExportUserList()endTime := time.Now()timeCost := endTime.Sub(startTime)fmt.Println("总耗时: ", timeCost)} else {fmt.Println("暂未定义此operate的业务逻辑")os.Exit(1)}
}

效果演示

分页导出多个Excel文件

合并多个文件前的效果演示，在合并多个Excel部分的代码暂时终止一下，看看效果。
在这里插入图片描述

在项目根目录下执行命令 go run command.go userCmd --operate exportData
在这里插入图片描述
生成的文件在：项目根目录下面的 /files/年/月/日/xxx 下面：

其中 用户数据导出_page_0000_xxx 是表头数据：

在这里插入图片描述

合并为一个完整的Excel文件

接下来，去掉刚才的 os.Exit(1) 再试一次直接导出一个完整的Excel文件，还是执行命令 go run command.go userCmd --operate exportData

在这里插入图片描述
这次直接合并为多个文件，并且删除之前的多个小文件。

完整代码

源代码：https://gitee.com/rxbook/go-demo-2025

下载后，解压到自定义目录，配置好 Go 环境，创建数据库go-demo-2025，导入 data/go-demo-2025.sql 的SQL语句，复制 conf/application.yml.demo 为 conf/application.yml 修改对应的数据为你自己的数据库连接信息。