1
import (
2
  "errors"
3
  "github.com/tealeg/xlsx"
4
  "io"
5
  "net/http"
6
  "reflect"
7
)
8
// ParseXlsxToStruct
9
//
10
//   @Description: 解析xlsx到struct中
11
//   @param data  xlsx数据
12
//   @param out  目标struct
13
//   @param sheet 默认第一个sheet
14
//   @return error
15
//    ParseXlsxToStruct(data,&out)
16
func ParseXlsxToStruct(data []byte, out any, sheet ...string) error {
17
  value := reflect.ValueOf(out)
18
  if value.Type().Kind() != reflect.Ptr {
19
    return errors.New("必须以指针的方式传入")
20
  }
21
  value = value.Elem()
22
  if value.Type().Kind() != reflect.Slice {
23
    return errors.New("内部必须为struct切片")
24
  }
25
  if value.Len() != 0 {
26
    return errors.New("必须是空切片")
27
  }
28

29
  xfile, err := xlsx.OpenBinary(data)
30
  if err != nil {
31
    return err
32
  }
33
  if len(xfile.Sheets) == 0 {
34
    return errors.New("xlsx中没有sheet")
35
  }
36
  var sheets []*xlsx.Sheet
37
  if len(sheet) > 0 {
38
    sheets = make([]*xlsx.Sheet, 0, len(sheet))
39
    for _, v := range sheet {
40
      if s, ok := xfile.Sheet[v]; ok {
41
        sheets = append(sheets, s)
42
      }
43
    }
44
  } else {
45
    sheets = append(sheets, xfile.Sheets[0])
46
  }
47

48
  title := GetUniqueSheetsTitle(sheets)
49
  var tmp = make(map[string][]string, len(title))
50
  for _, s := range sheets {
51
    if len(s.Rows) == 0 {
52
      continue
53
    }
54
    title := GetSheetTitle(s)
55
    for i, row := range s.Rows {
56
      if i == 0 {
57
        continue
58
      }
59
      var dataMap = make(map[string]string)
60
      for j, cell := range row.Cells {
61
        if title[j] == "" {
62
          continue
63
        }
64
        dataMap[title[j]] = cell.String()
65
      }
66
      for _, t := range title {
67
        if t == "" {
68
          continue
69
        }
70
        tmp[t] = append(tmp[t], dataMap[t])
71
      }
72
    }
73
  }
74
  MapToStructByTag(tmp, out, "xlsx")
75
  return nil
76
}
77

78
func MapToStructByTag(data map[string][]string, in interface{}, tagKey string) {
79
  // 获取传入结构体切片的反射类型和值
80
  inValue := reflect.ValueOf(in).Elem()
81
  inType := inValue.Type().Elem()
82

83
  var l int
84
  for _, v := range data {
85
    if l < len(v) {
86
      l = len(v)
87
    }
88
  }
89
  //fmt.Println(inType)
90
  news := reflect.MakeSlice(inValue.Type(), l, l)
91
  defer func() {
92
    inValue.Set(news)
93
  }()
94

95
  for i := 0; i < news.Len(); i++ {
96
    structValue := news.Index(i)
97
    structType := inType
98
    // 获取结构体指针所指向的类型
99
    var prt bool
100
    var structValuePtr reflect.Value
101
    if structValue.Kind() == reflect.Ptr {
102
      structType = structType.Elem()
103
      structValuePtr = reflect.New(structType)
104
      structValue = structValuePtr.Elem()
105
      prt = true
106
    }
107

108
    // 遍历结构体字段
109
    for j := 0; j < structType.NumField(); j++ {
110
      fieldValue := structValue.Field(j)
111
      fieldType := structType.Field(j)
112
      // 检查字段的 tag 是否与 key 匹配
113
      tag := fieldType.Tag.Get(tagKey)
114
      if values, ok := data[tag]; ok {
115
        if i >= len(values) {
116
          continue
117
        }
118
        // 检查字段是否可以被设置
119
        if fieldValue.CanSet() {
120
          // 根据字段类型进行适当的转换并设置值
121
          switch fieldType.Type.Kind() {
122
          case reflect.String:
123
            structValue.Field(j).SetString(values[i])
124
          }
125
        }
126
      }
127

128
    }
129
    if prt {
130
      news.Index(i).Set(structValuePtr)
131
    }
132
  }
133
}
134

135
func ParseXlsxToStructByUrl(url string, out any, sheet ...string) error {
136
  resp, err := http.Get(url)
137
  if err != nil {
138
    return err
139
  }
140
  defer resp.Body.Close()
141
  data, _ := io.ReadAll(resp.Body)
142
  return ParseXlsxToStruct(data, out, sheet...)
143
}
144

145
func ParseStructToXlsx(a any, fn func(tag string, val any, cell *xlsx.Cell) error) (*xlsx.File, error) {
146
  val := reflect.ValueOf(a)
147
  if val.Type().Kind() != reflect.Slice {
148
    return nil, errors.New("必须传入[]*struct")
149
  }
150
  xFile := xlsx.NewFile()
151
  sheet, _ := xFile.AddSheet("A1")
152
  title := sheet.AddRow()
153
  for i := 0; i < val.Len(); i++ {
154
    elemVal := val.Index(i)
155
    for elemVal.Kind() == reflect.Ptr {
156
      elemVal = elemVal.Elem()
157
    }
158
    if elemVal.Kind() != reflect.Struct {
159
      return nil, errors.New("必须传入[]*struct")
160
    }
161
    row := sheet.AddRow()
162
    for j := 0; j < elemVal.NumField(); j++ {
163
      cell := row.AddCell()
164
      tag, ok := elemVal.Type().Field(j).Tag.Lookup("xlsx")
165
      if !ok {
166
        continue
167
      }
168
      if i == 0 {
169
        title.AddCell().SetString(tag)
170
      }
171
      v := elemVal.Field(j).Interface()
172
      if err := fn(tag, v, cell); err != nil {
173
        return nil, err
174
      }
175
    }
176
  }
177
  return xFile, nil
178
}
179

180
func GetUniqueSheetsTitle(ss []*xlsx.Sheet) []string {
181
  var tmp = make(map[string]struct{})
182
  var out []string
183
  for _, s := range ss {
184
    title := GetSheetTitle(s)
185
    for _, t := range title {
186
      if _, ok := tmp[t]; !ok && t != "" {
187
        out = append(out, t)
188
      }
189
    }
190
  }
191
  return out
192
}
193

194
func GetSheetTitle(s *xlsx.Sheet) []string {
195
  if len(s.Rows) == 0 {
196
    return nil
197
  }
198
  if row := s.Rows[0]; row != nil {
199
    var title = make([]string, len(row.Cells))
200
    for i, cell := range row.Cells {
201
      title[i] = cell.String()
202
    }
203
    return title
204
  }
205
  return nil
206
}