Set data with struct tag

[llqgit]

Description
有没有 struct tag 写法，把 []Data 这种直接写入 xlsx（带 title）

文档里没找到有相关写法，框架不支持？
文档看了一遍，根本不知道咋用，难道每一条都需要写

func (f *File) SetCellValue(sheet, axis string, value interface{}) error

都要用 for 循环设置？

我想用

type Data struct {
  Name string `json:"name" xlsx:"name"`
}

// export 导出整个数组
// []Data

不知道是不是真的没有这种写法？
有几个框架都支持了。

[xuri]

You can write an array to row by a given worksheet name with SetSheetRow.

[llqgit]

@xuri 你那个方法不好用，不要数组，要结构体数组。Your func not nice for me, don't need slice, but struct slice.

我最后的方案用反射自己写了一个。My finial solution by use reflect to code myself.

package main

import (
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	"reflect"
	"time"
)

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)

	if t.Kind() != reflect.Slice {
		return xlsx
	}

	s := reflect.ValueOf(records)

	for i := 0; i < s.Len(); i++ {
		elem := s.Index(i).Interface()
		elemType := reflect.TypeOf(elem)
		elemValue := reflect.ValueOf(elem)
		for j := 0; j < elemType.NumField(); j++ {
			field := elemType.Field(j)
			tag := field.Tag.Get("xlsx")
			name := tag
			column := string(firstCharacter + j)
			if tag == "" {
				continue
			}
			// 设置表头
			if i == 0 {
				xlsx.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+1), name)
			}
			// 设置内容
			xlsx.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+2), elemValue.Field(j).Interface())
		}
	}
	return xlsx
}

/*********************** usage ***********************/

type Data struct {
	ID   int64     `json:"id" xlsx:"ID 主键"`
	Name string    `json:"name" xlsx:"名称"`
	Age  string    `json:"age" xlsx:"年龄"`
	Date time.Time `json:"time" xlsx:"日期"`
}

func main() {
	list := []Data{
		{1, "a", "44", time.Now()},
		{2, "a", "45", time.Now()},
		{3, "a", "46", time.Now()},
		{4, "aaa", "47", time.Now()},
	}
	// 创建一个工作表
	f := WriteXlsx("Sheet1", list)
	// 根据指定路径保存文件
	if err := f.SaveAs("Book1.xlsx"); err != nil {
		println(err.Error())
	}
}

导出内容如下：export like this:

[dnabil]

added pointer of slice support from @llqgit answer

package main

import (
	"errors"
	"fmt"
	"reflect"
	"unicode/utf8"

	"github.com/xuri/excelize/v2"
)

//
// Writes a slice/array of structs to an Excel file,
// 
// The function will use the tag "excel" as its "header" (row A)
//
// If excel tag is not set, it will leave an empty column.
//
func StructToExcel(excel *excelize.File, records interface{}) error {
	sheet := excel.GetSheetName(excel.GetActiveSheetIndex())

	firstCharacter := 65 // start from 'A' row

	t := reflect.TypeOf(records)
	if !(t.Kind() == reflect.Slice || t.Kind() == reflect.Array) {
		return errors.New("to write struct to excel, slice/array data type is expected. got: " + t.Kind().String())
	}

	slice := reflect.ValueOf(records)

	if slice.Len() == 0 {
		return errors.New("empty array/slice, nothing is written into the excel")
	}

	for i := 0; i < slice.Len(); i++ {
		elem := slice.Index(i).Interface()
		elemType := reflect.TypeOf(elem)
		elemValue := reflect.ValueOf(elem)

		if elemType.Kind() == reflect.Pointer {
			// Check if the value is not nil
			if elemValue.IsNil() {
				return errors.New("when writing excel, nil pointer type found in slice element")
			}

			elemType = elemValue.Elem().Type()
			elemValue = elemValue.Elem()
		}

		for j := 0; j < elemType.NumField(); j++ {
			column := string(rune(firstCharacter + j))
			tag := elemType.Field(j).Tag.Get("excel")

			if tag == "" {
				continue
			}

			if i == 0 { // write header
				err := excel.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+1), tag)
				if err != nil {
					return err
				}
			}

			dataRowStarts := 2 // B

			err := excel.SetCellValue(sheet, fmt.Sprintf("%s%d", column, i+dataRowStarts), elemValue.Field(j).Interface())
			if err != nil {
				return err
			}
		}
	}

	return nil
}