Master Excel Manipulation in Go with tealeg: From Basics to Reflection

Excel Concepts

An Excel file can contain multiple sheets, each sheet being a table. Each row in the table is called a Row. Each cell within a row is called a Cell.

Using tealeg to Operate Excel

Install tealeg

go get github.com/tealeg/xlsx

Create a New Spreadsheet with tealeg

tealeg provides a simple API: create a Sheet, add Rows, fill Cells, and finally save to disk.

func TestTealeg(t *testing.T) {
    excel := xlsx.NewFile()
    // New sheet
    sheet, err := excel.AddSheet("Sheet1")
    if err != nil { t.Fatal(err) }
    // Header row
    headerRow := sheet.AddRow()
    headerRow.SetHeightCM(0.5)
    headerRow.AddCell().Value = "姓名"
    headerRow.AddCell().Value = "年龄"
    // Data rows
    valList := [][]string{{"张三", "13"}, {"李四", "14"}, {"王五", "15"}}
    for _, line := range valList {
        row := sheet.AddRow()
        row.SetHeightCM(0.5)
        for _, v := range line { row.AddCell().Value = v }
    }
    // Save to file
    if err := excel.Save("username.xlsx"); err != nil { t.Fatal(err) }
}

Running this test creates a username.xlsx file with the same structure described in the Excel concepts.

Go Reflection: Converting Structs to Excel

Tag each struct field with an excel tag.

Use reflection to read these tags as column headers.

Reflect over struct values to build a map[string]string where the key is the tag and the value is the field value.

If a slice or array of structs is provided, convert each element to such a map and collect them into a slice of maps, then write each map as a row.

Get Struct Tag List

func getStructTagList(v interface{}, tag string) []string {
    var resList []string
    if v == nil { return resList }
    var item interface{}
    switch reflect.TypeOf(v).Kind() {
    case reflect.Slice, reflect.Array:
        values := reflect.ValueOf(v)
        if values.Len() == 0 { return resList }
        item = values.Index(0).Interface()
    case reflect.Struct:
        item = reflect.ValueOf(v).Interface()
    default:
        panic(fmt.Sprintf("type %v not support", reflect.TypeOf(v).Kind()))
    }
    t := reflect.TypeOf(item)
    for i := 0; i < t.NumField(); i++ {
        resList = append(resList, t.Field(i).Tag.Get(tag))
    }
    return resList
}

Convert Struct Values to map[excelTag]strucVal

func getTagValMap(v interface{}, tag string) map[string]string {
    resMap := make(map[string]string)
    if v == nil { return resMap }
    t := reflect.TypeOf(v)
    for i := 0; i < t.NumField(); i++ {
        field := t.Field(i)
        tagValue := field.Tag.Get(tag)
        val := reflect.ValueOf(v).FieldByName(field.Name)
        resMap[tagValue] = fmt.Sprintf("%v", val.Interface())
    }
    return resMap
}

Convert Slice/Array/Struct to []map[excelTag]strucVal

func struct2MapTagList(v interface{}, tag string) []map[string]string {
    var resList []map[string]string
    switch reflect.TypeOf(v).Kind() {
    case reflect.Slice, reflect.Array:
        values := reflect.ValueOf(v)
        for i := 0; i < values.Len(); i++ {
            resList = append(resList, getTagValMap(values.Index(i).Interface(), tag))
        }
    case reflect.Struct:
        resList = append(resList, getTagValMap(v, tag))
    default:
        panic(fmt.Sprintf("type %v not support", reflect.TypeOf(v).Kind()))
    }
    return resList
}

Write []map[excelTag]strucVal to Excel with tealeg

func Struct2Xlsx(v interface{}) (*xlsx.File, error) {
    var tag = "excel"
    tagList := getStructTagList(v, tag)
    mapTagList := struct2MapTagList(v, tag)
    excelFile := xlsx.NewFile()
    sheet, err := excelFile.AddSheet("Sheet1")
    if err != nil { return nil, err }
    // Header row
    headerRow := sheet.AddRow()
    for _, tagVal := range tagList {
        headerRow.SetHeightCM(0.5)
        headerRow.AddCell().Value = tagVal
    }
    // Data rows
    for _, rowMap := range mapTagList {
        row := sheet.AddRow()
        for _, tagVal := range tagList {
            row.SetHeightCM(0.5)
            row.AddCell().Value = rowMap[tagVal]
        }
    }
    return excelFile, nil
}

Test Case

type Username struct {
    Name string `excel:"姓名"`
    Age  int    `excel:"年龄"`
}

func TestStruct2Excel(t *testing.T) {
    data := []Username{{Name: "张三", Age: 13}, {Name: "李四", Age: 14}, {Name: "王五", Age: 15}}
    excel, err := Struct2Xlsx(data)
    if err != nil { t.Fatal(err) }
    if err := excel.Save("username.xlsx"); err != nil { t.Fatal(err) }
}

Running the test generates username.xlsx in the project directory, containing the struct data converted into an Excel sheet.