excel

package module
v0.4.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 22, 2023 License: MIT Imports: 7 Imported by: 0

README

excel

GoDoc Go Report Card License

Marshal and Unmarshal Excel file with the help of excelize.

Installation

go get github.com/go-mods/excel

Usage

Consider the following structs

type Employee struct {
    // use field name as default column name
    ID int
    // map the column 'firstName'
    First string `excel:"firstName,default:anonymous"`
    // column can also be used to set the column name
    FirstPtr *string `excel:"column:firstName,export:false" excel-out:"-"`
    // map the column 'lastName'
    Last string `excel:"lastName,default:anonymous"`
    // 'column' can be omitted when only mapping to column name
    // this is equal to 'column(email)'
    Email string `excel:"email"`
    // map the column 'contactNumber'
    ContactNumber string `excel:"contactNumber"`
    // map the column 'age'
    Age int `excel:"age"`
    // map the column 'dob'
    BirthDate time.Time `excel:"dob,format:d/m/Y,default:"`
    // map the column 'salary'
    Salary int `excel:"salary"`
    // Slice of staff ID's
    // split will split the string into slice using `|` separator
    Staff []int `excel:"staff,split:|" excel-out:"split:;"`
    // Slice of pointer of staff ID's
    StaffPtr []*int `excel:"staff,split:|,export:false" excel-out:"-"`
    // 'FullName' column contains a json string
    EncodedName EncodedName `excel:"encodedName,encoding:json"`
    // 'FullName' column contains a json string
    EncodedNamePtr *EncodedName `excel:"encodedName,encoding:json,export:false" excel-out:"-"`
    // use '-' to ignore.
    Ignored string `excel:"-"`
}

type EncodedName struct {
	FirstName string `json:"first_name"`
	LastName  string `json:"last_name"`
	FullName  string `json:"full_name"`
}
Unmarshal Excel file to struct
package main

import (
	"github.com/go-mods/excel"
	"github.com/xuri/excelize/v2"
)

func main() {
    // Open the employees test file
    file, _ := excelize.OpenFile(employeesTestFile)
    defer func() { _ = file.Close() }()
    
    // Employees container
    var employees []*Employee

    // Configure what to read in the Excel file
    excel, _ := excel.NewReader(file)
    excel.SetSheetName(employeesSheet)
    excel.SetAxis(employeesAxis)

	// Unmarshal employees
    err := excel.Unmarshal(&employees)
    if err != nil {
        t.Error(err)
        return
    }
}
Marshal Excel file from struct
package main

import (
	"github.com/go-mods/excel"
	"github.com/xuri/excelize/v2"
)

func main() {
    // Create a new Excel file
    file := excelize.NewFile()
    file.SetSheetName(file.GetSheetName(file.GetActiveSheetIndex()), employeesSheet)
    defer func() { _ = file.Close() }()

    // Employees container
    var employees []*Employee
    employee1 := &Employee{ID: 1, First: "First", Last: "last", Email: "test@test.com", BirthDate: time.Now()}
    employee2 := &Employee{ID: 2, First: "Second", Last: "last", Salary: 100, EncodedName: EncodedName{FirstName: "Second", LastName: "last", FullName: "Second last"}}
    employee3 := &Employee{ID: 3, BirthDate: time.Now(), Age: 32, Staff: []int{1, 2}}
    employees = append(employees, employee1, employee2, employee3)

    // Configure how to write in the Excel file
    excel, _ := excel.NewWriter(file)
    excel.SetSheetName(employeesSheet)
    excel.SetAxis(employeesAxis)
    
    // Unmarshal employees
    err := excel.Marshal(&employees)
        if err != nil {
        t.Error(err)
        return
    }
    
    // Save file
    _ = file.SaveAs(employeesExportFile)
}

Customizable Converters

type DateTime struct {
    time.Time
}

func (date *DateTime) Marshall() (interface{}, error) {
    return date.Time.Format("20060201"), nil
}

func (date *DateTime) Unmarshall(s string) (err error) {
    date.Time, err = time.Parse("20060201", s)
    return err
}

type User struct {
    Id       int       `excel:"Id"`
    Name     string    `excel:"Name"`
    Created  DateTime  `excel:"Created"`
    Modified *DateTime `excel:"Modified"`
}

Tags

This is the list of tags that can be used. excel-in and excel-out always have precedence on excel

Tags description excel excel-in excel-out
column Field name in the title row.
by default the field name will be used
in and out can be differents		 X X X
default Default value to use when none is defined in the cell. X X
format Format to apply X X X
encoding Encode or decode to the specified format
only json encoding is supported at the moment		 X X X
split Define the split separator to use for array or slice field. X X X
required Will return ann error if the column is not present X X
- Do not map the field to a column X X X

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrFileIsNil      = errors.New("excel: the file is nil")
	ErrSheetNotValid  = errors.New("excel: the sheet name is not valid")
	ErrAxisNotValid   = errors.New("excel: the axis is not valid")
	ErrConfigNotValid = errors.New("excel: the configuration is not valid")

	ErrMapKeyNotString   = errors.New("excel: the map key must be a string")
	ErrNoReaderFound     = errors.New("excel: unable to create an appropriate reader")
	ErrNoWriterFound     = errors.New("excel: unable to create an appropriate writer")
	ErrContainerNotSlice = errors.New("excel: the Container must be a slice")
	ErrContainerNotMap   = errors.New("excel: the Container must be a map")

	ErrContainerInvalid = errors.New("excel: the Container must be a slice or a pointer")
	ErrColumnRequired   = errors.New("excel: required colum")

	ErrNotImplemented = errors.New("excel: not implemented")
)

Functions

This section is empty.

Types

type Axis 

type Axis struct {
	Axis string
	Col  int
	Row  int
}

Axis represent the coordinates in the Excel file where data will read or write

type Container added in v0.4.0 

type Container struct {
	Value   reflect.Value
	Type    reflect.Type
	Pointer bool
}

Container is a struct that contains the Value and type of the container it is used to create the appropriate reader or writer

type Excel 

type Excel struct {
	File   *excelize.File
	Reader *Reader
	Writer *Writer

	Struct *Struct
}

func NewReader 

func NewReader(file *excelize.File) (*Excel, error)

NewReader creates a new Excel reader

func NewWriter 

func NewWriter(file *excelize.File) (*Excel, error)

NewWriter create the configuration used by the writer

func (*Excel) GetSheetIndex 

func (e *Excel) GetSheetIndex() int

GetSheetIndex gets the sheet index used by the reader or writer

func (*Excel) GetSheetName 

func (e *Excel) GetSheetName() string

GetSheetName gets the sheet name used by the reader or writer

func (*Excel) Marshal 

func (e *Excel) Marshal(container any, tags ...map[string]*Tags) error

Marshal writes the container into the Excel file

func (*Excel) SetAxis 

func (e *Excel) SetAxis(axis string)

SetAxis sets the axis to be used by the reader or writer

func (*Excel) SetAxisCoordinates 

func (e *Excel) SetAxisCoordinates(col int, row int)

SetAxisCoordinates sets the axis coordinates to be used by the reader or writer

func (*Excel) SetSheetIndex 

func (e *Excel) SetSheetIndex(index int)

SetSheetIndex sets the sheet index to be used by the reader or writer

func (*Excel) SetSheetName 

func (e *Excel) SetSheetName(sheet string)

SetSheetName sets the sheet name to be used by the reader or writer

func (*Excel) Unmarshal 

func (e *Excel) Unmarshal(container any, tags ...map[string]*Tags) error

Unmarshal reads the Excel file and unmarshals it into the container

type Field added in v0.4.0 

type Field struct {
	Name  string
	Index int
	Type  reflect.Type

	MainTags  *Tags // mainTags used by default
	ReadTags  *Tags // mainTags for reading
	WriteTags *Tags // mainTags for writing
}

Field is a struct used to store the information of a field of a struct

func (*Field) GetReadColumnName added in v0.4.0 

func (f *Field) GetReadColumnName() string

GetReadColumnName returns the column name to read from the excel file

func (*Field) GetReadDefault added in v0.4.0 

func (f *Field) GetReadDefault() interface{}

GetReadDefault returns the default value to use if the cell is empty

func (*Field) GetReadEncoding added in v0.4.0 

func (f *Field) GetReadEncoding() string

GetReadEncoding returns the encoding to use when reading the cell

func (*Field) GetReadFormat added in v0.4.0 

func (f *Field) GetReadFormat() string

GetReadFormat returns the format to use when reading the cell

func (*Field) GetReadIgnore added in v0.4.0 

func (f *Field) GetReadIgnore() bool

GetReadIgnore returns whether the field should be ignored when reading the cell

func (*Field) GetReadRequired added in v0.4.0 

func (f *Field) GetReadRequired() bool

GetReadRequired returns whether the field is required when reading the cell

func (*Field) GetReadSplit added in v0.4.0 

func (f *Field) GetReadSplit() string

GetReadSplit returns the split to use when reading the cell

func (*Field) GetWriteColumnName added in v0.4.0 

func (f *Field) GetWriteColumnName() string

GetWriteColumnName returns the column name to write to the excel file

func (*Field) GetWriteDefault added in v0.4.0 

func (f *Field) GetWriteDefault() interface{}

GetWriteDefault returns the default value to use if the cell is empty

func (*Field) GetWriteEncoding added in v0.4.0 

func (f *Field) GetWriteEncoding() string

GetWriteEncoding returns the encoding to use when writing the cell

func (*Field) GetWriteFormat added in v0.4.0 

func (f *Field) GetWriteFormat() string

GetWriteFormat returns the format to use when writing the cell

func (*Field) GetWriteIgnore added in v0.4.0 

func (f *Field) GetWriteIgnore() bool

GetWriteIgnore returns whether the field should be ignored when writing the cell

func (*Field) GetWriteRequired added in v0.4.0 

func (f *Field) GetWriteRequired() bool

GetWriteRequired returns whether the field is required when writing the cell

func (*Field) GetWriteSplit added in v0.4.0 

func (f *Field) GetWriteSplit() string

GetWriteSplit returns the split to use when writing the cell

type Fields added in v0.4.0 

type Fields []*Field

Fields is a list of Field

func (*Fields) Count added in v0.4.0 

func (f *Fields) Count() int

Count returns the number of fields

func (*Fields) CountReadIgnored added in v0.4.0 

func (f *Fields) CountReadIgnored() int

CountReadIgnored returns the number of ignored fields

func (*Fields) CountWriteIgnored added in v0.4.0 

func (f *Fields) CountWriteIgnored() int

CountWriteIgnored returns the number of ignored fields

type IReadTags added in v0.4.0 

type IReadTags interface {
	GetReadTags() map[string]*Tags
}

The IReadTags interface can be used as a replacement of the mainTags parameters when importing an Excel file.

GetTagsIn is used when importing an Excel file and will be used if ITags is not implemented.

type IReader added in v0.4.0 

type IReader interface {
	Unmarshall() error
	SetColumnsTags(tags map[string]*Tags)
}

IReader interface All readers must implement this interface

type ITags added in v0.4.0 

type ITags interface {
	GetTags() map[string]*Tags
}

The ITags interface can be used as a replacement of the mainTags parameters. The GetTags method must return a map of the mainTags. The key of the map is the name of the field and the value is a Tags structure.

Example: 

type MyStruct struct {
	Column1 string `excel:"column=MyColumn1"`
	Column2 string `excel:"column=MyColumn2;required"`
	Column3 string `excel:"column=MyColumn3;default=Hello World"`
}

func (s *MyStruct) GetTags() map[string]excel.MainTags {
	return map[string]excel.MainTags{
		"Column1": excel.MainTags{column: "MyColumn1"},
		"Column2": excel.MainTags{column: "MyColumn2", Required: true},
		"Column3": excel.MainTags{column: "MyColumn3", Default: "Hello World"},
	}
}

In this example: the Column1 field will be mapped to the "MyColumn1" column of the Excel file. The Column2 field will be mapped to the "MyColumn2" column of the Excel file and it will be required. The Column3 field will be mapped to the "MyColumn3" column of the Excel file and it will have a default value of "Hello World".

type IWriteTags added in v0.4.0 

type IWriteTags interface {
	GetWriteTags() map[string]*Tags
}

The IWriteTags interface can be used as a replacement of the mainTags parameters when exporting an Excel file.

GetTagsOut is used when exporting an Excel file and will be used if ITags is not implemented.

type IWriter added in v0.4.0 

type IWriter interface {
	Marshall(data any) error
	SetColumnsTags(tags map[string]*Tags)
}

IWriter interface All writers must implement this interface

type MapWriter added in v0.4.0 

type MapWriter struct {
	Writer *Writer
	// contains filtered or unexported fields
}

func (*MapWriter) Marshall added in v0.4.0 

func (w *MapWriter) Marshall(data any) error

func (*MapWriter) SetColumnsTags added in v0.4.0 

func (w *MapWriter) SetColumnsTags(_ map[string]*Tags)

type Marshaller 

type Marshaller interface {
	Marshall() (interface{}, error)
}

Marshaller can be implemented by any Value that has a Marshal method This converter is used to convert the Value to the desired representation

type Reader 

type Reader struct {
	Sheet Sheet
	Axis  Axis
	// contains filtered or unexported fields
}

Reader is the Excel reader

type Sheet 

type Sheet struct {
	Name  string
	Index int
}

Sheet represent the sheet in the Excel file where data will read or write

type SliceReader added in v0.4.0 

type SliceReader struct {
	Reader *Reader
	// contains filtered or unexported fields
}

func (*SliceReader) SetColumnsTags added in v0.4.0 

func (r *SliceReader) SetColumnsTags(_ map[string]*Tags)

func (*SliceReader) Unmarshall added in v0.4.0 

func (r *SliceReader) Unmarshall() error

type SliceWriter added in v0.4.0 

type SliceWriter struct {
	Writer *Writer
	// contains filtered or unexported fields
}

func (*SliceWriter) Marshall added in v0.4.0 

func (w *SliceWriter) Marshall(data any) error

func (*SliceWriter) SetColumnsTags added in v0.4.0 

func (w *SliceWriter) SetColumnsTags(_ map[string]*Tags)

type Struct added in v0.4.0 

type Struct struct {
	Type   reflect.Type
	Fields Fields
}

Struct is a struct used to store information about a struct

func (*Struct) GetField added in v0.4.0 

func (s *Struct) GetField(index int) *Field

GetField returns the field from the index

type StructReader added in v0.4.0 

type StructReader struct {
	Reader *Reader
	Struct *Struct
	// contains filtered or unexported fields
}

StructReader is the Excel reader for a struct It implements the IReader interface

func (*StructReader) SetColumnsTags added in v0.4.0 

func (r *StructReader) SetColumnsTags(tags map[string]*Tags)

func (*StructReader) Unmarshall added in v0.4.0 

func (r *StructReader) Unmarshall() error

Unmarshall reads the excel file and fill the container

type StructWriter added in v0.4.0 

type StructWriter struct {
	Writer *Writer
	Struct *Struct
	// contains filtered or unexported fields
}

StructWriter is the Excel writer for a struct It implements the IWriter interface

func (*StructWriter) Marshall added in v0.4.0 

func (w *StructWriter) Marshall(data any) error

Marshall writes the Excel file from the container

func (*StructWriter) SetColumnsTags added in v0.4.0 

func (w *StructWriter) SetColumnsTags(tags map[string]*Tags)

type Tags added in v0.4.0 

type Tags struct {
	Column   string
	Default  interface{}
	Format   string
	Encoding string
	Split    string
	Required bool
	Ignore   bool
	// contains filtered or unexported fields
}

Tags is used to store the mainTags parameters of a field.

The mainTags parameters are defined in the struct definition and are prefixed by "excel" and are used to configure the import and export of an Excel file.

Example: 

type MyStruct struct {
	Column1 string `excel:"column=MyColumn1"`
	Column2 string `excel:"column=MyColumn2;required"`
	Column3 string `excel:"column=MyColumn3;default=Hello World"`
}

In this example: the Column1 field will be mapped to the "MyColumn1" column of the Excel file. The Column2 field will be mapped to the "MyColumn2" column of the Excel file and it will be required. The Column3 field will be mapped to the "MyColumn3" column of the Excel file and it will have a default value of "Hello World".

type Unmarshaller 

type Unmarshaller interface {
	Unmarshall(s string) error
}

Unmarshaller can be implemented by any Value that has an Unmarshall method This converter is used to convert the Value to the desired representation

type Writer 

type Writer struct {
	Sheet Sheet
	Axis  Axis
	// contains filtered or unexported fields
}

Writer is the Excel writer

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.