1.func()
import(
"falcon/middleware/contrib/cache"
"fmt"
"reflect"
"github.com/360EntSecGroup-Skylar/excelize"
)
func WriteXlsx(sheet string, records interface{}) *excelize.File {
xlsx := excelize.NewFile() // new file
index := xlsx.NewSheet(sheet) // new sheet
xlsx.SetActiveSheet(index) // set active (default) sheet
firstCharacter := 65 // start from 'A' line
t := reflect.TypeOf(records)
// log.Info(t)
if t.Kind() != reflect.Slice {
return xlsx
}
s := reflect.ValueOf(records)
// log.Info(s)
for i := 0; i < s.Len(); i++ {
elem := s.Index(i).Interface()
elemType := reflect.TypeOf(elem)
elemValue := reflect.ValueOf(elem)
// log.Info(elemValue)
for j := 0; j < elemType.NumField(); j++ {
field := elemType.Field(j)
//获取atruct的tag
tag := field.Tag.Get("xlsx")
// log.Info(tag)
name := tag
column := string(firstCharacter + j)
if tag == "" {
// log.Info(tag)
continue
}
// 把读到的tag写入表头
if i == 0 {
xlsx.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+1), name)
}
// 写入struct值
if j == 8{
//需要转一下格式
tmp:=elemValue.FieldByName("Duration").String()
duration,_:=strconv.Atoi(tmp)
log.Info(duration)
xlsx.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+2), duration)
}else{
xlsx.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+2), elemValue.Field(j).Interface())
}
}
}
return xlsx
}
tips:
func (f *File) SetCellValue(sheet, axis string, value interface{})
第二个参数---单元格,fmt.Sprintf("%s%d", column, i+2),
从 firstCharacter := 65 // start from ‘A’ line 开始,“A2”-“A3”…
2.结构体
type Runtimedevice struct {
Runtimedevice string `gorm:"column:device_id" xlsx:"devid" json:"devid"`
Model string `gorm:"column:model" xlsx:"model"`
Mac string `gorm:"column:mac" xlsx:"mac"`
Sn string `gorm:"column:sn" xlsx:"sn"`
Appkey string `gorm:"column:appkey" xlsx:"appkey"`
Appversion string `gorm:"column:appversion" xlsx:"appversion"`
Startmillis string `gorm:"column:startmillis" xlsx:"startmillis"`
Endmillis string `gorm:"column:endmillis" xlsx:"endmillis"`
Duration string `gorm:"column:duration" xlsx:"duration(ms)"`
}
注意tag,既是你的表头字段
3.调用
f := WriteXlsx("Sheet1", Results)
// 根据指定路径保存文件
if err := f.SaveAs(date + "_ums.xlsx"); err != nil {
println(err.Error())
}