【Golang】xlsx.row to struct slice

Author： Rehtt
发布时间：May 13, 2025
50 views
No comments
6871 words
Categories：笔记 Golang Golang笔记

## 0x00

之前业务中解析xlsx表格总是用手动写数组下标的方式，这种方式一来繁琐二来容易出错，特别是表头多的时候排查起来也很麻烦。
所以就自己写一个将xlsx转为struct的模块，感觉用struct比数组更直观

### 代码

```go
import (
	"errors"
	"reflect"
	"strconv"
	"strings"
	"time"

"github.com/gogf/gf/v2/os/gtime"
	"github.com/tealeg/xlsx"
)

type FieldTagOption struct {
	boolMap    map[string]bool // boolmap:是-true,否-false,真-true,假-false
	timeformat string          // timeformat:2006-01-02 15:04:05
}

func ParseXlsxStruct(rows []*xlsx.Row, data any) error {
	rdata := reflect.ValueOf(data)
	if rdata.Kind() != reflect.Ptr {
		return errors.New("data must be &[]*struct/&[]struct")
	}
	rdata = rdata.Elem()
	if rdata.Type().Kind() != reflect.Slice {
		return errors.New("data must be &[]*struct/&[]struct")
	}
	dataElemType := rdata.Type().Elem()
	var isPrt bool
	if dataElemType.Kind() == reflect.Ptr {
		dataElemType = dataElemType.Elem()
		isPrt = true
	}
	if dataElemType.Kind() != reflect.Struct {
		return errors.New("data must be &[]*struct/&[]struct")
	}

dataElemTypeOptions := make(map[int]*FieldTagOption)
	for i := 0; i < dataElemType.NumField(); i++ {
		dataElemTypeOptions[i] = &FieldTagOption{}
		for _, tag := range strings.Split(dataElemType.Field(i).Tag.Get("xlsx"), ";") {
			s := strings.SplitN(tag, ":", 2)
			switch s[0] {
			case "timeformat":
				dataElemTypeOptions[i].timeformat = s[1]
			case "boolmap":
				for _, v := range strings.Split(s[1], ",") {
					if dataElemTypeOptions[i].boolMap == nil {
						dataElemTypeOptions[i].boolMap = make(map[string]bool)
					}
					b := strings.Split(v, "-")
					if len(b) != 2 {
						return errors.New("boolmap tag value must be like true-false,true-true,false-false,false-false")
					}
					bb, err := strconv.ParseBool(b[1])
					if err != nil {
						return errors.New("boolmap tag value must be like true-false,true-true,false-false,false-false")
					}
					dataElemTypeOptions[i].boolMap[b[0]] = bb
				}
			}
		}
	}

tmp := reflect.MakeSlice(reflect.SliceOf(rdata.Type().Elem()), 0, len(rows))
	for _, r := range rows {
		var elemPtr, elemVal reflect.Value
		if isPrt {
			elemPtr = reflect.New(dataElemType)
			elemVal = elemPtr.Elem()
		} else {
			elemVal = reflect.New(dataElemType).Elem()
		}
		for i, v := range r.Cells {
			if i >= dataElemType.NumField() {
				break
			}
			f := elemVal.Field(i)
			if !f.CanSet() {
				continue
			}

txt, _ := v.FormattedValue()
			switch f.Kind() {
			case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
				if n, err := strconv.ParseInt(txt, 10, 64); err == nil {
					f.SetInt(n)
				}
			case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
				if n, err := strconv.ParseUint(txt, 10, 64); err == nil {
					f.SetUint(n)
				}
			case reflect.Float32, reflect.Float64:
				if f64, err := strconv.ParseFloat(txt, 64); err == nil {
					f.SetFloat(f64)
				}
			case reflect.Bool:
				if dataElemTypeOptions[i].boolMap != nil {
					f.SetBool(dataElemTypeOptions[i].boolMap[txt])
				} else {
					if b, err := strconv.ParseBool(txt); err == nil {
						f.SetBool(b)
					}
				}
			case reflect.String:
				f.SetString(txt)
			default:
				switch f.Interface().(type) {
				case *gtime.Time:
					t := gtime.NewFromStr(txt)
					if dataElemTypeOptions[i].timeformat != "" {
						t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
					}
					f.Set(reflect.ValueOf(t))
				case time.Time:
					t := gtime.NewFromStr(txt)
					if dataElemTypeOptions[i].timeformat != "" {
						t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
					}
					f.Set(reflect.ValueOf(t.Time))
				case *time.Time:
					t := gtime.NewFromStr(txt)
					if dataElemTypeOptions[i].timeformat != "" {
						t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
					}
					f.Set(reflect.ValueOf(&t.Time))
				}
			}
		}
		if isPrt {
			tmp = reflect.Append(tmp, elemPtr)
		} else {
			tmp = reflect.Append(tmp, elemVal)
		}
	}
	rdata.Set(tmp)
	return nil
}

```

### 测试用例

```go
import (
	"errors"
	"reflect"
	"testing"
	"time"

"github.com/gogf/gf/v2/os/gtime"
	"github.com/tealeg/xlsx"
)

func newRow(sheet *xlsx.Sheet, vals ...string) *xlsx.Row {
	r := sheet.AddRow()
	for _, v := range vals {
		cell := r.AddCell()
		cell.SetString(v)
	}
	return r
}

type Person struct {
	Name  string
	Age   int
	GTime *gtime.Time `xlsx:"timeformat:20060102"`
	Time  time.Time
	PTime *time.Time
	Bool  bool `xlsx:"boolmap:是-true,否-false"`
}

func TestParseXlsxStruct_Basic(t *testing.T) {
	// 准备 2 行数据
	file := xlsx.NewFile()
	sh, _ := file.AddSheet("Sheet1")
	_ = newRow(sh, "Alice", "30", "20250405", "2025-01-01", "2022-01-01 12:14:15", "是")
	_ = newRow(sh, "Bob", "25", "20250102")

var got []Person
	if err := ParseXlsxStruct(sh.Rows, &got); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}

p := gtime.NewFromStr("2022-01-01 12:14:15")
	want := []Person{
		{Name: "Alice", Age: 30, GTime: gtime.NewFromStrLayout("20250405", "20060102"), Time: gtime.New("2025-01-01").Time, PTime: &p.Time, Bool: true},
		{Name: "Bob", Age: 25, GTime: gtime.NewFromStrLayout("20250102", "20060102")},
	}
	if !reflect.DeepEqual(got, want) {
		t.Fatalf("mismatch\nwant: %+v\ngot : %+v", want, got)
	}
}

func TestParseXlsxStruct_PtrSlice(t *testing.T) {
	file := xlsx.NewFile()
	sh, _ := file.AddSheet("Sheet1")
	_ = newRow(sh, "Cat", "7")

var got []*Person
	if err := ParseXlsxStruct(sh.Rows, &got); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if len(got) != 1 || got[0].Name != "Cat" || got[0].Age != 7 {
		t.Fatalf("unexpected result: %+v", got)
	}
}

func TestParseXlsxStruct_NotPointer(t *testing.T) {
	file := xlsx.NewFile()
	sh, _ := file.AddSheet("Sheet1")
	_ = newRow(sh, "Dog", "3")

var wrong []Person
	// 直接传切片而不是 *切片，应报错
	err := ParseXlsxStruct(sh.Rows, wrong)
	if err == nil {
		t.Fatal("expected error, got nil")
	}
}

func TestParseXlsxStruct_ElemNotStruct(t *testing.T) {
	file := xlsx.NewFile()
	sh, _ := file.AddSheet("Sheet1")
	_ = newRow(sh, "1", "2")

var wrong []int
	err := ParseXlsxStruct(sh.Rows, &wrong)
	if !errors.Is(err, errors.New("slice element must be struct or *struct")) {
		// 只要报错即可；不必精确判定同一个 error 实例
		if err == nil {
			t.Fatal("expected error, got nil")
		}
	}
}

func TestParseXlsxStruct_ExtraColumnsIgnored(t *testing.T) {
	file := xlsx.NewFile()
	sh, _ := file.AddSheet("Sheet1")
	// 第 3 列将被忽略
	_ = newRow(sh, "Eve", "40", "ignored")

var got []Person
	if err := ParseXlsxStruct(sh.Rows, &got); err != nil {
		t.Fatalf("unexpected error: %v", err)
	}
	if got[0].Name != "Eve" || got[0].Age != 40 {
		t.Fatalf("unexpected result: %+v", got[0])
	}
}
```

Last modification：May 13, 2025

如果觉得我的文章对你有用，请随意赞赏

【Golang】xlsx.row to struct slice

Rehtt • 2025 年 05 月 13 日