excel

README

excel

Excel is a library to assist in manipulating Excel file.

Powered by github.com/qax-os/excelize

Get excel

go get github.com/cuishu/excel

How to use

The first line of the excel file must be the same with the tag of Go struct

For example

We have a file named a.xlsx

Sheet1

id	name
1	Smith

type Human {
    ID   int    `xlsx:"id"`
    Name string `xlsx:"name"`
}

Read Sheet

var humans []Human
// Read from file
Sheet{Filename: "a.xlsx", Sheet: "Sheet1"}.Scan(&humans)
// Read from reader
NewSheetFromReader(reader, "Sheet1").Scan(&humans)

for _, human := range humans {
  fmt.Println(human.Name)
  ...
}

Go Slice to excel file

var humans []Human
humans = append(Human{ID: 1, Name: "Smith"})
humans = append(Human{ID: 2, Name: "Jack"})
humans = append(Human{ID: 3, Name: "James"})
    
buff, err := (&Sheet{Sheet: "Sheet1"}).Export(&users)

ioutil.WriteFile("a.xlsx", buff.Bytes(), 0644)

Supported data types

int int8 int16 int32 int64
uint uint8 uint16 uint32 uint64
float32 float64
string
bool
time.Time

Read write the whole file

If the file has more than one sheet, we should use a struct receive them.

Example

type Human struct {
	ID   int    `xlsx:"id"`
	Name string `xlsx:"name"`
}

type Animal Human

type Example struct {
	Humans  []Human  `xlsx:"humans"`
	Animals []Animal `xlsx:"animals"`
}

var example Example

// Read
(Excel{Filename: "b.xlsx"}).Scan(&example)
// Read from io.Reader
NewExcelFromReader(reader).Scan(&example)

// To bytes buffer
Excel{}.Export(&example)

Offset

If the header is not in the first row, then you should use offset, which defaults to 0

e := Sheet{Filename: "a.xlsx", Sheet: "Sheet3"}
var ss []TestObject
e.Offset(1).Scan(&ss)
...

Advance

You can use custom types to implement MarshalXLSX and UnmarshalXLSX to implement type convert.

For example

const (
	Male = 1
	Female = 2
)

type Sex int

func (sex Sex) MarshalXLSX() ([]byte, error) {
	if sex == Male {
		return []byte("Male"), nil
	} else if sex == Female {
		return []byte("Female"), nil
	}
	return nil, errors.New("invalid value")
}

func (sex *Sex) UnmarshalXLSX(data []byte) error {
	s := string(data)
	if s == "Male" {
		*sex = Male
		return nil
	}
	if s == "Female" {
		*sex = Female
		return nil
	}
	return errors.New("invalid value")
}

Documentation

Index

• type Cell
• type Excel
  • func NewExcelFromReader(r io.Reader) *Excel
  • func (e Excel) Export(v interface{}) (*bytes.Buffer, error)
  • func (e *Excel) Offset(n int) *Excel
  • func (e Excel) Scan(v interface{}) error
• type HyperLink
• type LinkType
• type PicFormat
• type Picture
  • func NewPicture(path string, format *PicFormat) Picture
  • func NewPictureFromBytes(file []byte, format *PicFormat) (Picture, error)
  • func (pic Picture) Buffer() *bytes.Buffer
• type Schema
• type Sheet
  • func NewSheetFromReader(r io.Reader, sheet string) *Sheet
  • func (s *Sheet) Export(v interface{}) (*bytes.Buffer, error)
  • func (s *Sheet) Filter(schema Schema) *Sheet
  • func (s Sheet) Offset(n int) Sheet
  • func (s Sheet) Scan(v interface{}) error

Types

type Cell

type Cell struct {
	Value     string
	HyperLink HyperLink
	Style     *excelize.Style
}

type Excel

type Excel struct {
	Filename string
	// contains filtered or unexported fields
}

func NewExcelFromReader

func NewExcelFromReader(r io.Reader) *Excel

func (Excel) Export

func (e Excel) Export(v interface{}) (*bytes.Buffer, error)

func (*Excel) Offset

func (e *Excel) Offset(n int) *Excel

func (Excel) Scan

func (e Excel) Scan(v interface{}) error

type HyperLink

type HyperLink struct {
	Link string
	Type LinkType
}

type LinkType

type LinkType string

const (
	External LinkType = "External"
	Location LinkType = "Location"
)

type PicFormat

type PicFormat excelize.GraphicOptions

type Picture

type Picture struct {
	Name   string
	File   []byte
	Format *PicFormat
	// contains filtered or unexported fields
}

func NewPicture

func NewPicture(path string, format *PicFormat) Picture

func NewPictureFromBytes

func NewPictureFromBytes(file []byte, format *PicFormat) (Picture, error)

func (Picture) Buffer

func (pic Picture) Buffer() *bytes.Buffer

type Schema

type Schema map[string]bool

type Sheet

type Sheet struct {
	Filename string
	Sheet    string
	// contains filtered or unexported fields
}

func NewSheetFromReader

func NewSheetFromReader(r io.Reader, sheet string) *Sheet

func (*Sheet) Export

func (s *Sheet) Export(v interface{}) (*bytes.Buffer, error)

func (*Sheet) Filter

func (s *Sheet) Filter(schema Schema) *Sheet

func (Sheet) Offset

func (s Sheet) Offset(n int) Sheet

func (Sheet) Scan

func (s Sheet) Scan(v interface{}) error