【Golang】xlsx.row to struct slice

1
import (
2
  "errors"
3
  "reflect"
4
  "strconv"
5
  "strings"
6
  "time"
7

8
  "github.com/gogf/gf/v2/os/gtime"
9
  "github.com/tealeg/xlsx"
10
)
11

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

17
func ParseXlsxStruct(rows []*xlsx.Row, data any) error {
18
  rdata := reflect.ValueOf(data)
19
  if rdata.Kind() != reflect.Ptr {
20
    return errors.New("data must be &[]*struct/&[]struct")
21
  }
22
  rdata = rdata.Elem()
23
  if rdata.Type().Kind() != reflect.Slice {
24
    return errors.New("data must be &[]*struct/&[]struct")
25
  }
26
  dataElemType := rdata.Type().Elem()
27
  var isPrt bool
28
  if dataElemType.Kind() == reflect.Ptr {
29
    dataElemType = dataElemType.Elem()
30
    isPrt = true
31
  }
32
  if dataElemType.Kind() != reflect.Struct {
33
    return errors.New("data must be &[]*struct/&[]struct")
34
  }
35

36
  dataElemTypeOptions := make(map[int]*FieldTagOption)
37
  for i := 0; i < dataElemType.NumField(); i++ {
38
    dataElemTypeOptions[i] = &FieldTagOption{}
39
    for _, tag := range strings.Split(dataElemType.Field(i).Tag.Get("xlsx"), ";") {
40
      s := strings.SplitN(tag, ":", 2)
41
      switch s[0] {
42
      case "timeformat":
43
        dataElemTypeOptions[i].timeformat = s[1]
44
      case "boolmap":
45
        for _, v := range strings.Split(s[1], ",") {
46
          if dataElemTypeOptions[i].boolMap == nil {
47
            dataElemTypeOptions[i].boolMap = make(map[string]bool)
48
          }
49
          b := strings.Split(v, "-")
50
          if len(b) != 2 {
51
            return errors.New("boolmap tag value must be like true-false,true-true,false-false,false-false")
52
          }
53
          bb, err := strconv.ParseBool(b[1])
54
          if err != nil {
55
            return errors.New("boolmap tag value must be like true-false,true-true,false-false,false-false")
56
          }
57
          dataElemTypeOptions[i].boolMap[b[0]] = bb
58
        }
59
      }
60
    }
61
  }
62

63
  tmp := reflect.MakeSlice(reflect.SliceOf(rdata.Type().Elem()), 0, len(rows))
64
  for _, r := range rows {
65
    var elemPtr, elemVal reflect.Value
66
    if isPrt {
67
      elemPtr = reflect.New(dataElemType)
68
      elemVal = elemPtr.Elem()
69
    } else {
70
      elemVal = reflect.New(dataElemType).Elem()
71
    }
72
    for i, v := range r.Cells {
73
      if i >= dataElemType.NumField() {
74
        break
75
      }
76
      f := elemVal.Field(i)
77
      if !f.CanSet() {
78
        continue
79
      }
80

81
      txt, _ := v.FormattedValue()
82
      switch f.Kind() {
83
      case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
84
        if n, err := strconv.ParseInt(txt, 10, 64); err == nil {
85
          f.SetInt(n)
86
        }
87
      case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
88
        if n, err := strconv.ParseUint(txt, 10, 64); err == nil {
89
          f.SetUint(n)
90
        }
91
      case reflect.Float32, reflect.Float64:
92
        if f64, err := strconv.ParseFloat(txt, 64); err == nil {
93
          f.SetFloat(f64)
94
        }
95
      case reflect.Bool:
96
        if dataElemTypeOptions[i].boolMap != nil {
97
          f.SetBool(dataElemTypeOptions[i].boolMap[txt])
98
        } else {
99
          if b, err := strconv.ParseBool(txt); err == nil {
100
            f.SetBool(b)
101
          }
102
        }
103
      case reflect.String:
104
        f.SetString(txt)
105
      default:
106
        switch f.Interface().(type) {
107
        case *gtime.Time:
108
          t := gtime.NewFromStr(txt)
109
          if dataElemTypeOptions[i].timeformat != "" {
110
            t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
111
          }
112
          f.Set(reflect.ValueOf(t))
113
        case time.Time:
114
          t := gtime.NewFromStr(txt)
115
          if dataElemTypeOptions[i].timeformat != "" {
116
            t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
117
          }
118
          f.Set(reflect.ValueOf(t.Time))
119
        case *time.Time:
120
          t := gtime.NewFromStr(txt)
121
          if dataElemTypeOptions[i].timeformat != "" {
122
            t = gtime.NewFromStrLayout(txt, dataElemTypeOptions[i].timeformat)
123
          }
124
          f.Set(reflect.ValueOf(&t.Time))
125
        }
126
      }
127
    }
128
    if isPrt {
129
      tmp = reflect.Append(tmp, elemPtr)
130
    } else {
131
      tmp = reflect.Append(tmp, elemVal)
132
    }
133
  }
134
  rdata.Set(tmp)
135
  return nil
136
}

1
import (
2
  "errors"
3
  "reflect"
4
  "testing"
5
  "time"
6

7
  "github.com/gogf/gf/v2/os/gtime"
8
  "github.com/tealeg/xlsx"
9
)
10

11
func newRow(sheet *xlsx.Sheet, vals ...string) *xlsx.Row {
12
  r := sheet.AddRow()
13
  for _, v := range vals {
14
    cell := r.AddCell()
15
    cell.SetString(v)
16
  }
17
  return r
18
}
19

20
type Person struct {
21
  Name  string
22
  Age   int
23
  GTime *gtime.Time `xlsx:"timeformat:20060102"`
24
  Time  time.Time
25
  PTime *time.Time
26
  Bool  bool `xlsx:"boolmap:是-true,否-false"`
27
}
28

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

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

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

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

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

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

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

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

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

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

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