excel

package module
v0.6.0 Latest Latest
Warning

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

 Go to latest Published: Nov 16, 2023 License: MIT Imports: 8 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")
	ErrAxisNotValid   = errors.New("excel: the axis is not valid")
	ErrConfigNotValid = errors.New("excel: the configuration is not valid")

	ErrSheetNotValid  = errors.New("excel: the sheet name is not valid")
	ErrSheetNotFound  = errors.New("excel: the sheet is not found")
	ErrSheetNameEmpty = errors.New("excel: the sheet name is empty")
	ErrSheetIndex     = errors.New("excel: the sheet index is not valid")

	ErrTableNameEmpty = errors.New("excel: the table name is empty")
	ErrTableRange     = errors.New("excel: the table range 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) AddTable added in v0.6.0 

func (e *Excel) AddTable(table *Table) error

AddTable adds a table to the Excel file

func (*Excel) DeleteTable added in v0.6.0 

func (e *Excel) DeleteTable(name string) error

DeleteTable deletes the table in the Excel file

func (*Excel) DeleteTableContent added in v0.6.0 

func (e *Excel) DeleteTableContent(name string) error

DeleteTableContent deletes the content of the table in the Excel file

func (*Excel) GetActiveSheet added in v0.6.0 

func (e *Excel) GetActiveSheet() *Sheet

GetActiveSheet returns the active sheet

func (*Excel) GetSheet added in v0.6.0 

func (e *Excel) GetSheet(name string) *Sheet

GetSheet returns the sheet object

func (*Excel) GetSheetFromIndex added in v0.6.0 

func (e *Excel) GetSheetFromIndex(index int) *Sheet

GetSheetFromIndex returns the sheet object

func (*Excel) GetTable added in v0.6.0 

func (e *Excel) GetTable(name string) (*Table, error)

GetTable returns the table in the Excel file

func (*Excel) GetTableSheet added in v0.6.0 

func (e *Excel) GetTableSheet(name string) (*Sheet, error)

GetTableSheet returns the sheet where the table is located

func (*Excel) GetTables added in v0.6.0 

func (e *Excel) GetTables() ([]Table, error)

GetTables returns the tables in the Excel file

func (*Excel) Marshal 

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

Marshal writes the container into the Excel file

func (*Excel) ResizeTable added in v0.6.0 

func (e *Excel) ResizeTable(table *Table, newRange string) error

ResizeTable resize the table by changing the range

func (*Excel) SetActiveSheet added in v0.6.0 

func (e *Excel) SetActiveSheet(sheet *Sheet)

SetActiveSheet sets the active sheet

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) SetSheet added in v0.6.0 

func (e *Excel) SetSheet(sheet *Sheet)

SetSheet sets the sheet to be used by the reader or writer

func (*Excel) SetSheetFromIndex added in v0.6.0 

func (e *Excel) SetSheetFromIndex(index int)

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

func (*Excel) SetSheetFromName added in v0.6.0 

func (e *Excel) SetSheetFromName(name string)

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

func (*Excel) Sheet added in v0.6.0 

func (e *Excel) Sheet() *Sheet

Sheet returns the sheet object 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() (*ReaderResult, 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) (*WriterResult, 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) (*WriterResult, 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 Range added in v0.6.0 

type Range struct {
	// StartColumn is the start column of the range
	StartColumn int
	// StartRow is the start row of the range
	StartRow int
	// StartName is the start name of the range
	StartName string
	// EndColumn is the end column of the range
	EndColumn int
	// EndRow is the end row of the range
	EndRow int
	// EndName is the end name of the range
	EndName string
}

Range represent the range in the Excel file where data will read or write

func MinRange added in v0.6.0 

func MinRange(startName string) (*Range, error)

MinRange returns the minimum range

func ToRange added in v0.6.0 

func ToRange(ref string) (*Range, error)

ToRange converts a string to a Range

func (*Range) AddColumns added in v0.6.0 

func (r *Range) AddColumns(columns int) error

AddColumns adds columns to the range

func (*Range) AddRows added in v0.6.0 

func (r *Range) AddRows(rows int) error

AddRows adds rows to the range

func (*Range) ColumnAsRange added in v0.6.0 

func (r *Range) ColumnAsRange(column int) (*Range, error)

ColumnAsRange returns the range of the column

func (*Range) Columns added in v0.6.0 

func (r *Range) Columns() int

Columns returns the number of columns in the range

func (*Range) FirstColumnAsRange added in v0.6.0 

func (r *Range) FirstColumnAsRange() (*Range, error)

FirstColumnAsRange returns the range of the first column

func (*Range) FirstRowAsRange added in v0.6.0 

func (r *Range) FirstRowAsRange() (*Range, error)

FirstRowAsRange returns the range of the first row

func (*Range) LastColumnAsRange added in v0.6.0 

func (r *Range) LastColumnAsRange() (*Range, error)

LastColumnAsRange returns the range of the last column

func (*Range) LastRowAsRange added in v0.6.0 

func (r *Range) LastRowAsRange() (*Range, error)

LastRowAsRange returns the range of the last row

func (*Range) RemoveColumns added in v0.6.0 

func (r *Range) RemoveColumns(columns int) error

RemoveColumns removes columns from the range

func (*Range) RemoveRows added in v0.6.0 

func (r *Range) RemoveRows(rows int) error

RemoveRows removes rows from the range

func (*Range) RowAsRange added in v0.6.0 

func (r *Range) RowAsRange(row int) (*Range, error)

RowAsRange returns the range of the row

func (*Range) Rows added in v0.6.0 

func (r *Range) Rows() int

Rows returns the number of rows in the range

func (*Range) SetColumns added in v0.6.0 

func (r *Range) SetColumns(columns int) error

SetColumns sets the number of columns in the range

func (*Range) SetRows added in v0.6.0 

func (r *Range) SetRows(rows int) error

SetRows sets the number of rows in the range

func (*Range) ToRef added in v0.6.0 

func (r *Range) ToRef() string

ToRef converts a Range to a string

func (*Range) UpdateNames added in v0.6.0 

func (r *Range) UpdateNames() error

UpdateNames updates the name of the range

type Reader 

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

Reader is the Excel reader

type ReaderResult added in v0.5.0 

type ReaderResult struct {
	Rows    int
	Columns int
}

ReaderResult is a struct that contains the result of the reader

type Sheet 

type Sheet struct {
	Name  string
	Index int
	// contains filtered or unexported fields
}

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

func (*Sheet) GetComment added in v0.6.0 

func (s *Sheet) GetComment(cell string) *excelize.Comment

GetComment returns the comment of the cell

func (*Sheet) IsValid added in v0.6.0 

func (s *Sheet) IsValid() bool

IsValid returns true if the sheet is valid

func (*Sheet) IsValidError added in v0.6.0 

func (s *Sheet) IsValidError() error

IsValidError returns an error if the sheet is not valid

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() (*ReaderResult, 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) (*WriterResult, 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() (*ReaderResult, 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) (*WriterResult, 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 Table added in v0.6.0 

type Table struct {
	Sheet *Sheet
	*excelize.Table
}

Table represent the table in the Excel file

func (*Table) Delete added in v0.6.0 

func (t *Table) Delete() error

Delete the table

func (*Table) DeleteContent added in v0.6.0 

func (t *Table) DeleteContent() error

DeleteContent deletes the content of the table

func (*Table) GetColumn added in v0.6.0 

func (t *Table) GetColumn(title string) (int, error)

GetColumn returns the column index of the title

func (*Table) GetColumnAt added in v0.6.0 

func (t *Table) GetColumnAt(index int) (string, error)

GetColumnAt returns the column name at the desired index

func (*Table) GetDataRange added in v0.6.0 

func (t *Table) GetDataRange() (*Range, error)

GetDataRange returns the range of the data of the table

func (*Table) GetHeaderRange added in v0.6.0 

func (t *Table) GetHeaderRange() (*Range, error)

GetHeaderRange returns the range of the header of the table

func (*Table) GetRange added in v0.6.0 

func (t *Table) GetRange() (*Range, error)

GetRange returns the range of the table

func (*Table) IsValid added in v0.6.0 

func (t *Table) IsValid() bool

IsValid returns true if the table is valid

func (*Table) IsValidError added in v0.6.0 

func (t *Table) IsValidError() error

IsValidError returns an error if the table is not valid

func (*Table) Resize added in v0.6.0 

func (t *Table) Resize(newRange string) error

Resize the table by changing the range

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
	Result *WriterResult
	// contains filtered or unexported fields
}

Writer is the Excel writer

type WriterResult added in v0.5.0 

type WriterResult struct {
	Rows    int
	Columns int
}

WriterResult is a struct that contains the result of the 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.