CODEWITHSUNDEEP
excelgo
Abanoub Istfanous
Abanoub Istfanous

Reputation: 966

golang convert excel sheet to a struct

I tried to use https://github.com/tealeg/xlsx and https://github.com/360EntSecGroup-Skylar/excelize I success to read from excel file

package main

import (
    "fmt"

    "github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
    f, err := excelize.OpenFile("Book1.xlsx")
    if err != nil {
        fmt.Println(err)
        return
    }
    // Get value from cell by given worksheet name and axis.
    cell, err := f.GetCellValue("Sheet1", "B2")
    if err != nil {
        fmt.Println(err)
        return
    }
    fmt.Println(cell)
    // Get all the rows in the Sheet1.
    rows, err := f.GetRows("Sheet1")
    for _, row := range rows {
        for _, colCell := range row {
            fmt.Print(colCell, "\t")
        }
        fmt.Println()
    }
}

put I want to convert data from excel to Array of defined struct

example

type InvoiceSheet struct {
    amount string
    item   string
   
}

var invoices []InvoiceSheet

err := excel.READMETHOD('path of file', &invoices)

then I can iterate invoices

Upvotes: 0

Views: 6743

Answers (2)

proximab
proximab

Reputation: 2473

I don't think any directly serialization is supported (not familiar with the package) so then why you wouldn't traverse your spreadsheet and initialize struct by struct and append it to a slice.

If your schema is known in advance you could define a map with your categories and use them as a reference point e.g. 

columns:= map[string]string{"Id" : "A1", "Item" : "B1", "Amount" : "C1"}

Start a loop within you will parse next rows to extract your struct values.

func (m *MergeCell) GetCellValue() string

Do it until its something to read, maybe until there is Id a not empty string. I've just came up with a possible way to implement it nicer.

Instead, map[string]string use some map[string]Cell.

That Cell would look like that:

type struct Cell {
    column : string
    row : int
}
func (c Cell) str() {
    return fmt.Sprintf("%s%d", c.column, c.row)
}

It appears they have some column iterator implemented that can help you as well.

Here is example that basically solves your task (I omitted error handling to make it shorter).

    const sheet2 = "SheetName"

    f, _ := OpenFile(filepath.Join("test", "Book1.xlsx"))

    rows, _ := f.Rows(sheet2)

    var collectedRows [][]string
    for rows.Next() {
        columns, _:= rows.Columns()
        // here init your struct and append it to slice as described above
    }

More examples with rows you'll find in tests files e.g. here.

Now it's your turn. Good luck!

Upvotes: 1

Cknu
Cknu

Reputation: 186

I'm not interiorized with that module, but looking at the source code I see there's a "Columns()" function you can use to get the values you need.

// Columns return the current row's column values.
func (rows *Rows) Columns() ([]string, error)

https://github.com/360EntSecGroup-Skylar/excelize/blob/master/rows.go

Upvotes: 1

Related Questions