go 导出数据库数据到excel

导出excel 代码，可以模仿写

package main

import (
	"encoding/json"
	"fmt"
	"github.com/sirupsen/logrus"
	"github.com/spf13/cobra"
	"github.com/xuri/excelize/v2"
	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"log"
	"time"
)

type Questionnaire struct {
	OrgName    string         `gorm:"column:org_name"`
	Name       string         `gorm:"column:name"`
	Department string         `gorm:"column:department"`
	Position   string         `gorm:"column:position"`
	Email      string         `gorm:"column:email"`
	Tel        string         `gorm:"column:tel"`
	Result     string         `gorm:"column:result"`
	Id         int            `gorm:"column:id;primaryKey"`
	CreatedAt  time.Time      `gorm:"column:created_at;<-:false"`
	UpdatedAt  time.Time      `gorm:"column:updated_at;<-:false"`
	DeletedAt  gorm.DeletedAt `gorm:"column:deleted_at"`
}

type Question struct {
	Name   string   `json:"name"`
	Answer []string `json:"answer"`
}

type QuestionnairePart struct {
	PartName string     `json:"partName"`
	Result   []Question `json:"result"`
}

func (*Questionnaire) TableName() string {
	return "test.questionnaire"
}

var env string

var cmd = &cobra.Command{
	Use:     "exporter-excel",
	Short:   "e",
	Version: "v0.1",
	Run: func(cmd *cobra.Command, args []string) {
		exportData(env)
	},
}

func main() {
	if err := cmd.Execute(); err != nil {
		logrus.Fatal(err)
	}
}

func init() {
	cobra.OnInitialize(initLog)
	cmd.PersistentFlags().StringVar(&env, "env", "dev", "environment")
}

func initLog() {

	logrus.SetFormatter(&logrus.TextFormatter{
		FullTimestamp:   true,
		TimestampFormat: "2006-01-02 15:04:05",
	})
}

func exportData(env string) {
	dsn := ""
	if env == "prod" {
		dsn = "postgresql://root:prod@prod:5432/prod?TimeZone=Asia/Shanghai"
	} else if env == "test" {
		dsn = "postgresql://test:test@test:5432/test?TimeZone=Asia/Shanghai"
	} else {
		dsn = "postgresql://root:dev@dev/dev?TimeZone=Asia/Shanghai"
	}

	var err error
	db, err := gorm.Open(postgres.Open(dsn))
	if err != nil {
		log.Fatalf("init test db failed %v", err)
	}
	var qn []Questionnaire
	db.Order("created_at desc").Find(&qn)
	file := excelize.NewFile()
	//设置表名
	file.SetSheetName("Sheet1", "表1")
	//t := true
	//f := float64(30)
	//file.SetSheetProps("表1", &excelize.SheetPropsOptions{CustomHeight: &t, DefaultRowHeight: &f, FitToPage: &t})
	index, _ := file.NewStyle(&excelize.Style{
		Border: []excelize.Border{
			{Type: "bottom", Color: "#fff600", Style: 1},
		},
		Fill: excelize.Fill{
			Type: "gradient", Color: []string{"#FFFFFF", "#E0EBF5"}, Shading: 0,
		},

		Alignment: &excelize.Alignment{
			Horizontal: "justify",
			Vertical:   "top",
		},
	})
	index2, _ := file.NewStyle(&excelize.Style{
		Border: []excelize.Border{
			{Type: "bottom", Color: "#fff600", Style: 0},
			{Type: "top", Color: "#fff600", Style: 0},
			{Type: "left", Color: "#fff600", Style: 0},
			{Type: "right", Color: "#fff600", Style: 0},
		},
		Fill: excelize.Fill{
			Type: "gradient", Color: []string{"#fff600", "#fff600"}, Shading: 0,
		},

		Alignment: &excelize.Alignment{
			Horizontal: "justify",
			Vertical:   "top",
		},
	})

	index3, _ := file.NewStyle(&excelize.Style{
		Alignment: &excelize.Alignment{
			Horizontal: "justify",
			Vertical:   "top",
		},
	})
	//
	//err = file.SetRowStyle("表1", 1, len(qn), index)
	//if err != nil {
	//	fmt.Println(err)
	//}

	//创建流式写入
	writer, err := file.NewStreamWriter("表1")
	//修改列宽
	writer.SetColWidth(1, 7, 25)
	writer.SetColWidth(8, 35, 50)

	//设置表头
	writer.SetRow("A1", []interface{}{excelize.Cell{StyleID: index, Value: "名称"}, excelize.Cell{StyleID: index, Value: "姓名"}, excelize.Cell{StyleID: index, Value: "部门"}, excelize.Cell{StyleID: index, Value: "职务", excelize.RowOpts{Height: 45, OutlineLevel: 1})
	if err != nil {
		return
	}
	for i := 1; i <= len(qn); i++ {
		//索引转单元格坐标
		cell, _ := excelize.CoordinatesToCellName(1, i+1)
		//添加的数据
		questionnaire := []QuestionnairePart{}
		err = json.Unmarshal([]byte(qn[i-1].Result), &questionnaire)
		if err != nil {
			fmt.Println(err)
			continue
		}
		cellValue := make([]interface{}, 0)

		if qn[i-1].CreatedAt.Format("2006/01/02") == time.Now().AddDate(0, 0, -1).Format("2006/01/02") {
			cellValue = append(cellValue, excelize.Cell{StyleID: index2, Value: qn[i-1].OrgName}, excelize.Cell{StyleID: index2, Value: qn[i-1].Name}, excelize.Cell{StyleID: index2, Value: qn[i-1].Department}, excelize.Cell{StyleID: index2, Value: qn[i-1].Position}, excelize.Cell{StyleID: index2, Value: qn[i-1].Email}, excelize.Cell{StyleID: index2, Value: qn[i-1].Tel})
			cellValue = append(cellValue, excelize.Cell{StyleID: index2, Value: qn[i-1].CreatedAt.Format("2006/01/02")})
			for j := 0; j < len(questionnaire); j++ {
				for k := 0; k < len(questionnaire[j].Result); k++ {
					cellValue = append(cellValue, excelize.Cell{StyleID: index2, Value: questionnaire[j].Result[k].Answer})
				}
			}

		} else {
			cellValue = append(cellValue, excelize.Cell{StyleID: index3, Value: qn[i-1].OrgName}, excelize.Cell{StyleID: index3, Value: qn[i-1].Name}, excelize.Cell{StyleID: index3, Value: qn[i-1].Department}, excelize.Cell{StyleID: index3, Value: qn[i-1].Position}, excelize.Cell{StyleID: index3, Value: qn[i-1].Email}, excelize.Cell{StyleID: index3, Value: qn[i-1].Tel})
			cellValue = append(cellValue, excelize.Cell{StyleID: index3, Value: qn[i-1].CreatedAt.Format("2006/01/02")})
			for j := 0; j < len(questionnaire); j++ {
				for k := 0; k < len(questionnaire[j].Result); k++ {
					cellValue = append(cellValue, excelize.Cell{StyleID: index3, Value: questionnaire[j].Result[k].Answer})
				}
			}
		}

		writer.SetRow(cell, cellValue, excelize.RowOpts{Height: 65, OutlineLevel: 1})

	}
	//结束流式写入
	writer.Flush()

	name := "问卷导出数据" + time.Now().Format("20060102") + ".xlsx"

	file.SaveAs(name)
}