案例分析:将数据库中的数据存入表格中
// HeaderColumn 表头字段定义
type HeaderColumn struct {
Field string // 字段,数据映射到的数据字段名
Title string // 标题,表格中的列名称
}
type Memory struct {
Sn string `gorm:"column:sn;type:varchar(50);NOT NULL" json:"sn"`
Frequency string `gorm:"column:frequency;type:varchar(50);NOT NULL" json:"frequency"`
PartNumber string `gorm:"column:part_number;type:varchar(50);NOT NULL" json:"part_number"`
Vendor string `gorm:"column:vendor;type:varchar(50);NOT NULL" json:"vendor"`
Number int `json:"number"`
}
func GetMemory() []Memory {
var memorys []Memory
err := orm.MuopEloquent.Debug().Model(&models.FittingMemory{}).Select("sn,frequency,part_number,vendor,count(sn) as number").Where("del_flag = 0").Group("frequency").Group("part_number").Group("vendor").Find(&memorys).Error
if err != nil{
fmt.Println("查询内存信息有误")
}
return memorys
}
func Export(c *gin.Context) {
file := xlsx.NewFile() // NewWriter 创建一个Excel写操作实例
sheet, err := file.AddSheet("memory") //表实例
if err != nil {
fmt.Printf(err.Error())
}
stus := GetMemory() //add data
headers := []*HeaderColumn{
{Field: "sn", Title: "SN"},
{Field: "frequency", Title: "内存主频"},
{Field: "part_number", Title: "内存型号"},
{Field: "vendor", Title: "内存品牌"},
{Field: "number", Title: "内存数量"},
}
style := map[string]float64{
"sn": 2.0,
"frequency": 2.0,
"part_number": 2.0,
"vendor": 2.0,
"number": 2.0,
}
sheet, _ = SetHeader(sheet, headers, style)
for _,stu := range stus{
data := make(map[string]string)
data["sn"] = stu.Sn
data["frequency"] = stu.Frequency
data["part_number"] = stu.PartNumber
data["vendor"] = stu.Vendor
data["number"] = strconv.Itoa(stu.Number)
row := sheet.AddRow()
row.SetHeightCM(0.8)
for _,field := range headers{
row.AddCell().Value = data[field.Field]
}
}
outFile := "D:\\test\\memory.xlsx"
err = file.Save(outFile)
if err != nil {
fmt.Printf(err.Error())
}
fmt.Println("\n\nexport success")
}
// SetHeader 写模式下,设置字段表头和字段顺序
// 参数 header 为表头和字段映射关系,如:HeaderColumn{Field:"title", Title: "标题", Requre: true}
// 参数 width 为表头每列的宽度,单位 CM:map[string]float64{"title": 0.8}
func SetHeader(sheet *xlsx.Sheet, header []*HeaderColumn, width map[string]float64) (*xlsx.Sheet, error) {
if len(header) == 0 {
return nil, errors.New("Excel.SetHeader 错误: 表头不能为空")
}
// 表头样式
style := xlsx.NewStyle()
font := xlsx.DefaultFont()
font.Bold = true
alignment := xlsx.DefaultAlignment()
alignment.Vertical = "center"
style.Font = *font
style.Alignment = *alignment
style.ApplyFont = true
style.ApplyAlignment = true
// 设置表头字段
row := sheet.AddRow()
row.SetHeightCM(1.0)
row_w := make([]string, 0)
for _, column := range header {
row_w = append(row_w, column.Field)
cell := row.AddCell()
cell.Value = column.Title
cell.SetStyle(style) //设置单元样式
}
// 表格列,宽度
if len(row_w) > 0 {
for k, v := range row_w {
if width[v] > 0.0 {
sheet.SetColWidth(k, k, width[v]*10)
}
}
}
return sheet, nil
}