0x00

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

代码

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
}

测试用例

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])
    }
}

【linux】目录

【linux】目录