go-excel: github.com/szyhf/go-excel Index | Examples | Files | Directories

package excel

import "github.com/szyhf/go-excel"

Package excel provide a simple and light reader to read `*.xlsx` as a relate-db-like table. See `ReadMe.md` or `Examples` for more usage.

Index

Examples

Package Files

common.go connect.go const.go errors.go read.go scan.go schema.go title.go types.go utils.go xml_row.go xml_shared_strings.go xml_workbook.go

Variables

var (
    // ErrConnectNotOpened means can not open connect to excel.
    ErrConnectNotOpened = errors.New("connect should opened")
    // ErrWorkbookRelsNotExist means can not found the workbook rels of excel.
    ErrWorkbookRelsNotExist = errors.New("parse xlsx file failed: xl/_rels/workbook.xml.rels not exist")
    // ErrWorkbookNotExist means can not found the workbook of excel.
    ErrWorkbookNotExist = errors.New("parse xlsx file failed: xl/workbook.xml not exist")
    // ErrSharedStringsNotExist means can not found the shared of excel.
    ErrSharedStringsNotExist = errors.New("parse xlsx file failed: xl/sharedStringPaths.xml not exist")
    // ErrInvalidConatiner means can not using the container.
    ErrInvalidConatiner = errors.New("container should be ptr to slice")
    // ErrEmptyRow means the row is empty.
    ErrEmptyRow = errors.New("empty row")
    // ErrNoRow means there is no row.
    ErrNoRow = errors.New("no row")
    // ErrScanNil means scan nil.
    ErrScanNil = errors.New("scan(nil)")
)

func UnmarshalXLSX Uses

func UnmarshalXLSX(filePath string, container interface{}) error

UnmarshalXLSX unmarshal a sheet of XLSX file into a slice container. The sheet name will be inferred from element of container If container implement the function of GetXLSXSheetName()string, the return string will used. Oterwise will use the reflect struct name.

Code:

var stdList []*Standard
err := excel.UnmarshalXLSX(filePath, &stdList)
if err != nil {
    fmt.Println(err)
    return
}
if !reflect.DeepEqual(stdList, expectStandardPtrList) {
    fmt.Printf("unexprect std list: %s", convert.MustJsonPrettyString(stdList))
}
fmt.Println(convert.MustJsonString(stdList))

Output:

[{"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""},{"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""},{"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""},{"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}]

Code:

var stdList []Standard
err := excel.UnmarshalXLSX(filePath, &stdList)
if err != nil {
    fmt.Println(err.Error())
    return
}
if !reflect.DeepEqual(stdList, expectStandardList) {
    fmt.Printf("unexprect std list: %s", convert.MustJsonPrettyString(stdList))
}

fmt.Print(convert.MustJsonString(stdList))

Output:

[{"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""},{"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""},{"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""},{"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}]

type Config Uses

type Config struct {
    // sheet: if sheet is string, will use sheet as sheet name.
    //        if sheet is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
    //        if sheet is a object implements `GetXLSXSheetName()string`, the return value will be used.
    //        otherwise, will use sheet as struct and reflect for it's name.
    // 		  if sheet is a slice, the type of element will be used to infer like before.
    Sheet interface{}
    // Use the index row as title, every row before title-row will be ignore, default is 0.
    TitleRowIndex int
    // Skip n row after title, default is 0 (not skip), empty row is not counted.
    Skip int
    // Auto prefix to sheet name.
    Prefix string
    // Auto suffix to sheet name.
    Suffix string
}

Config of connecter

type Connecter Uses

type Connecter interface {
    // Open a file of excel
    Open(filePath string) error
    // Open a binary of excel
    OpenBinary(xlsxData []byte) error

    // Close file reader
    Close() error

    // Get all sheets name
    GetSheetNames() []string

    // Generate an new reader of a sheet
    // sheetNamer: if sheetNamer is string, will use sheet as sheet name.
    //             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
    //             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
    // 	           if sheetNamer is a slice, the type of element will be used to infer like before.
    //             otherwise, will use sheetNamer as struct and reflect for it's name.
    NewReader(sheetNamer interface{}) (Reader, error)
    // Generate an new reader of a sheet
    // sheetNamer: if sheetNamer is string, will use sheet as sheet name.
    //             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
    //             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
    //             otherwise, will use sheetNamer as struct and reflect for it's name.
    // 			   if sheetNamer is a slice, the type of element will be used to infer like before.
    MustReader(sheetNamer interface{}) Reader

    NewReaderByConfig(config *Config) (Reader, error)
    MustReaderByConfig(config *Config) Reader
}

An Connecter of excel file

func NewConnecter Uses

func NewConnecter() Connecter

NewConnecter make a new connecter to connect to a exist xlsx file.

type Reader Uses

type Reader interface {
    // Get all titles sorted
    GetTitles() []string
    // Read current row into a object
    Read(v interface{}) error
    // Read all rows
    // container: container should be ptr to slice or array.
    ReadAll(container interface{}) error
    // Read next rows
    Next() bool
    // Close the reader
    Close() error
}

Reader to read excel

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

var stdList [][]string
err = rd.ReadAll(&stdList)
if err != nil {
    fmt.Println(err)
    return
}

if !reflect.DeepEqual(expectStandardSliceList, stdList) {
    fmt.Printf("unexpect stdlist: \n%s", convert.MustJsonPrettyString(stdList))
}

fmt.Println(convert.MustJsonString(stdList))

Output:

[["1","Andy","1","1|2","{\"Foo\":\"Andy\"}"],["2","Leo","2","2|3|4","{\"Foo\":\"Leo\"}"],["3","Ben","3","3|4|5|6","{\"Foo\":\"Ben\"}"],["4","Ming","4","1","{\"Foo\":\"Ming\"}"]]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

var stdMapList []map[string]string
err = rd.ReadAll(&stdMapList)
if err != nil {
    fmt.Println(err)
    return
}

if !reflect.DeepEqual(expectStandardMapList, stdMapList) {
    fmt.Printf("unexpect stdlist: \n%s", convert.MustJsonPrettyString(stdMapList))
}

fmt.Println(convert.MustJsonString(stdMapList))

Output:

[{"AgeOf":"1","ID":"1","NameOf":"Andy","Slice":"1|2","UnmarshalString":"{\"Foo\":\"Andy\"}"},{"AgeOf":"2","ID":"2","NameOf":"Leo","Slice":"2|3|4","UnmarshalString":"{\"Foo\":\"Leo\"}"},{"AgeOf":"3","ID":"3","NameOf":"Ben","Slice":"3|4|5|6","UnmarshalString":"{\"Foo\":\"Ben\"}"},{"AgeOf":"4","ID":"4","NameOf":"Ming","Slice":"1","UnmarshalString":"{\"Foo\":\"Ming\"}"}]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

// will fill with default value which cell can not unmarshal to int
// int is just example, can be other type
var stdMapList []map[string]int
err = rd.ReadAll(&stdMapList)
if err != nil {
    fmt.Println(err)
    return
}

fmt.Println(convert.MustJsonString(stdMapList))

Output:

[{"AgeOf":1,"ID":1,"NameOf":0,"Slice":0,"UnmarshalString":0},{"AgeOf":2,"ID":2,"NameOf":0,"Slice":0,"UnmarshalString":0},{"AgeOf":3,"ID":3,"NameOf":0,"Slice":0,"UnmarshalString":0},{"AgeOf":4,"ID":4,"NameOf":0,"Slice":1,"UnmarshalString":0}]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

var stdList []*Standard
// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdList)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

err = rd.ReadAll(&stdList)
if err != nil {
    fmt.Println(err)
    return
}
if !reflect.DeepEqual(expectStandardPtrList, stdList) {
    fmt.Printf("unexpect stdlist: \n%s", convert.MustJsonPrettyString(stdList))
}

fmt.Println(convert.MustJsonString(stdList))

Output:

[{"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""},{"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""},{"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""},{"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

var stdList []Standard
// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdList)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

err = rd.ReadAll(&stdList)
if err != nil {
    fmt.Println(err)
    return
}
if !reflect.DeepEqual(expectStandardList, stdList) {
    fmt.Printf("unexpect stdlist: \n%s", convert.MustJsonPrettyString(stdList))
}

fmt.Println(convert.MustJsonString(stdList))

Output:

[{"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""},{"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""},{"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""},{"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}]

Code:

xlsxData, err := ioutil.ReadFile(filePath)
if err != nil {
    fmt.Println(err)
    return
}

conn := excel.NewConnecter()
err = conn.OpenBinary(xlsxData)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

var stdList []*Standard
// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdList)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

err = rd.ReadAll(&stdList)
if err != nil {
    fmt.Println(err)
    return
}
if !reflect.DeepEqual(expectStandardPtrList, stdList) {
    fmt.Printf("unexpect stdlist: \n%s", convert.MustJsonPrettyString(stdList))
}

fmt.Println(convert.MustJsonString(stdList))

Output:

[{"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""},{"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""},{"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""},{"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

idx := 0
for rd.Next() {
    var m map[string]string
    if err := rd.Read(&m); err != nil {
        fmt.Println(err)
        return
    }

    expectStdMap := expectStandardMapList[idx]
    if !reflect.DeepEqual(m, expectStdMap) {
        fmt.Printf("unexpect std at %d = \n%s", idx, convert.MustJsonPrettyString(expectStdMap))
    }

    fmt.Printf("%d => %s\n", idx, convert.MustJsonString(m))
    idx++
}

Output:

0 => {"AgeOf":"1","ID":"1","NameOf":"Andy","Slice":"1|2","UnmarshalString":"{\"Foo\":\"Andy\"}"}
1 => {"AgeOf":"2","ID":"2","NameOf":"Leo","Slice":"2|3|4","UnmarshalString":"{\"Foo\":\"Leo\"}"}
2 => {"AgeOf":"3","ID":"3","NameOf":"Ben","Slice":"3|4|5|6","UnmarshalString":"{\"Foo\":\"Ben\"}"}
3 => {"AgeOf":"4","ID":"4","NameOf":"Ming","Slice":"1","UnmarshalString":"{\"Foo\":\"Ming\"}"}

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

idx := 0
for rd.Next() {
    var l []string
    if err := rd.Read(&l); err != nil {
        fmt.Println(err)
        return
    }

    expectStdList := expectStandardSliceList[idx]
    if !reflect.DeepEqual(l, expectStdList) {
        fmt.Printf("unexpect std at %d %s = \n%s", idx, convert.MustJsonPrettyString(l), convert.MustJsonPrettyString(expectStdList))
    }

    fmt.Printf("%d => %s\n", idx, convert.MustJsonString(l))
    idx++
}

Output:

0 => ["1","Andy","1","1|2","{\"Foo\":\"Andy\"}"]
1 => ["2","Leo","2","2|3|4","{\"Foo\":\"Leo\"}"]
2 => ["3","Ben","3","3|4|5|6","{\"Foo\":\"Ben\"}"]
3 => ["4","Ming","4","1","{\"Foo\":\"Ming\"}"]

Code:

conn := excel.NewConnecter()
err := conn.Open(filePath)
if err != nil {
    fmt.Println(err)
    return
}
defer conn.Close()

// Generate an new reader of a sheet
// sheetNamer: if sheetNamer is string, will use sheet as sheet name.
//             if sheetNamer is int, will i'th sheet in the workbook, be careful the hidden sheet is counted. i ∈ [1,+inf]
//             if sheetNamer is a object implements `GetXLSXSheetName()string`, the return value will be used.
//             otherwise, will use sheetNamer as struct and reflect for it's name.
// 			   if sheetNamer is a slice, the type of element will be used to infer like before.
rd, err := conn.NewReader(stdSheetName)
if err != nil {
    fmt.Println(err)
    return
}
defer rd.Close()

idx := 0
for rd.Next() {
    var s Standard
    if err := rd.Read(&s); err != nil {
        fmt.Println(err)
        return
    }
    expectStd := expectStandardList[idx]
    if !reflect.DeepEqual(s, expectStd) {
        fmt.Printf("unexpect std at %d = \n%s", idx, convert.MustJsonPrettyString(expectStd))
    }
    fmt.Printf("%d => %s\n", idx, convert.MustJsonString(s))
    idx++
}

Output:

0 => {"ID":1,"Name":"Andy","NamePtr":"Andy","Age":1,"Slice":[1,2],"Temp":{"Foo":"Andy"},"WantIgnored":""}
1 => {"ID":2,"Name":"Leo","NamePtr":"Leo","Age":2,"Slice":[2,3,4],"Temp":{"Foo":"Leo"},"WantIgnored":""}
2 => {"ID":3,"Name":"Ben","NamePtr":"Ben","Age":3,"Slice":[3,4,5,6],"Temp":{"Foo":"Ben"},"WantIgnored":""}
3 => {"ID":4,"Name":"Ming","NamePtr":"Ming","Age":4,"Slice":[1],"Temp":{"Foo":"Ming"},"WantIgnored":""}

Directories

PathSynopsis
internal/twenty_six

Package excel imports 11 packages (graph). Updated 2020-01-05. Refresh now. Tools for package owners.