excel

README

Excel

Microsoft Excel .xlsx read/write for golang

Basic Usage

Installation

To get the package, execute:

go get github.com/leaker/excel

To import this package, add the following line to your code:

import "github.com/leaker/excel"

Create spreadsheet

Here is example usage that will create xlsx file.

package main

import (
	"fmt"
	"github.com/leaker/excel"
	"time"
)

func main() {
	f := excel.NewFile()

	sheet := f.NewSheet("Sheet2")
	sheet.SetCellValue(excel.ColumnNumber("A"), 1, "Name")
	sheet.SetCellValue(excel.ColumnNumber("A"), 2, "Jason")
	sheet.SetCellValue(excel.ColumnNumber("B"), 1, "Score")
	sheet.SetCellValue(excel.ColumnNumber("B"), 2, 100)
	// date value 
	sheet.SetCellValue(3, 1, "Date")
	sheet.Cell(3, 2).SetDateValue(time.Date(1980, 9, 8, 0, 0, 0, 0, time.Local))
	// time value
	sheet.AxisCell("D1").SetStringValue("LastTime")
	sheet.AxisCell("D2").
		SetTimeValue(time.Now()).
		SetNumberFormat("yyyy-mm-dd hh:mm:ss")

	if err := f.SaveFile("Document1.xlsx"); err != nil {
		fmt.Println(err)
	}
}

Reading spreadsheet

The following constitutes the bare to read a spreadsheet document.

package main

import (
	"fmt"
	"github.com/leaker/excel"
)

func main() {
	f, err := excel.OpenFile("Document1.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}

	sheet := f.OpenSheet("Sheet2")
	A1 := sheet.GetCellString(1, 1)
	fmt.Println(A1)

	cell := sheet.AxisCell("B2")
	fmt.Println(cell.GetIntValue())
}

TODO:

• Basic File Format
• File: NewFile, OpenFile, SaveFile, Write, Sheets
• Sheet:
  • NewSheet, OpenSheet
  • SetCellValue, GetCellString, GetCellInt, Cell, AxisCell
  • ...
• Cell:
  • Row, Col
  • SetValue, SetIntValue, SetFloatValue, SetFloatValuePrec, SetStringValue, SetBoolValue, SetDefaultValue, SetTimeValue, SetDateValue, SetDurationValue
  • GetIntValue, GetStringValue
  • SetNumberFormat
  • ...

Documentation

Index

• Constants
• func CellNameToCoordinates(cellName string) (col int, row int)
• func ColumnName(columnNumber int) (columnName string)
• func ColumnNumber(columnName string) (columnNumber int)
• func CoordinatesToCellName(col, row int) string
• func TimeToExcelTime(t time.Time) float64
• type Axis
  • func (a Axis) C() (col, row int)
• type Cell
• type File
  • func NewFile() File
  • func OpenFile(name string) (File, error)
• type Sheet

Constants

const (
	VariantTypeVariant = "variant"
	VariantTypeVTLPSTR = "lpstr"
)

VariantTypes

Functions

func CellNameToCoordinates

func CellNameToCoordinates(cellName string) (col int, row int)

CellNameToCoordinates convert cell name to [col, row] coordinates

Example:

xlsx.CellNameToCoordinates("A1") // returns 1, 1
xlsx.CellNameToCoordinates("B5") // returns 2, 5

func ColumnName

func ColumnName(columnNumber int) (columnName string)

ColumnName convert the column number to column name

Example:

xlsx.ColumnName(51) // returns "AY"

func ColumnNumber

func ColumnNumber(columnName string) (columnNumber int)

ColumnNumber convert the column name to column number

Example:

xlsx.ColumnNumber("AY") // returns 51

func CoordinatesToCellName

func CoordinatesToCellName(col, row int) string

CoordinatesToCellName convert [col, row] coordinates to cell name

Example:

xlsx.CoordinatesToCellName(1, 1) // returns "A1"

func TimeToExcelTime

func TimeToExcelTime(t time.Time) float64

TimeToExcelTime convert time.Time to excel time format

Types

type Axis

type Axis string

func (Axis) C

func (a Axis) C() (col, row int)

type Cell

type Cell interface {
	// Row cell row number
	Row() int

	// Col cell col number
	Col() int

	// SetValue provides to set the value of a cell
	// Allow Types:
	//     int
	//     int8
	//     int16
	//     int32
	//     int64
	//     uint
	//     uint8
	//     uint16
	//     uint32
	//     uint64
	//     float32
	//     float64
	//     string
	//     []byte
	//     time.Duration
	//     time.Time
	//     bool
	//     nil
	//
	// Example:
	//     cell.SetValue(100)
	//     cell.SetValue("Hello")
	//     cell.SetValue(3.14)
	SetValue(value interface{}) Cell

	// SetIntValue set cell for int type
	SetIntValue(value int) Cell

	// GetIntValue get cell value with int type
	GetIntValue() int

	// SetFloatValue set cell for float64 type
	SetFloatValue(value float64) Cell

	// SetFloatValuePrec set cell for float64 type with pres
	SetFloatValuePrec(value float64, prec int, bitSize int) Cell

	// GetStringValue get cell value with string type
	GetStringValue() string

	// SetStringValue set cell value for string type
	SetStringValue(value string) Cell

	// SetBoolValue set cell value for bool type
	SetBoolValue(value bool) Cell

	// SetDefaultValue set cell value without any type
	SetDefaultValue(value string) Cell

	// SetTimeValue set cell value for time.Time type
	SetTimeValue(value time.Time) Cell

	// SetDateValue set cell value for time.Time type with date format
	SetDateValue(value time.Time) Cell

	// SetDurationValue set cell value for time.Duration type
	SetDurationValue(value time.Duration) Cell

	// SetNumberFormat set cell number format with format code
	// https://docs.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat?view=openxml-2.8.1
	SetNumberFormat(formatCode string) Cell
}

Cell cell operator

type File

type File interface {
	// SaveFile save xlsx file
	SaveFile(name string) error

	// Write save to steam
	Write(w io.Writer) error

	// OpenSheet open a exist Sheet by name
	//
	// Example:
	//
	//     sheet := file.OpenSheet("Sheet1")
	//
	// return nil if sheet not exist
	OpenSheet(name string) Sheet

	// NewSheet create a new Sheet with sheet name
	// Example:
	//
	//     sheet := file.NewSheet("Sheet2")
	NewSheet(name string) Sheet

	// Sheets return all sheet for operator
	Sheets() []Sheet
}

File define for operation xlsx file

func NewFile

func NewFile() File

NewFile create a default xlsx File with default template

func OpenFile

func OpenFile(name string) (File, error)

OpenFile open a xlsx for operator

type Sheet

type Sheet interface {
	// SetCellValue set cell value
	//
	// Example:
	//     sheet.SetCellValue(1, 1, "val") // A1 => "val"
	//     sheet.SetCellValue(2, 3, 98.01) // B3 => 98.01
	//     sheet.SetCellValue(3, 1, 1000) // C1 => 1000
	//     sheet.SetCellValue(4, 4, time.Now()) // D4 => "2021-03-11 05:19:16.483"
	SetCellValue(col, row int, value interface{}) Cell

	// GetCellString get cell value of string
	//
	// Example:
	//     sheet.GetCellString(1, 1) // A1 => "val"
	GetCellString(col, row int) string

	// GetCellInt get cell value of string
	//
	// Example:
	//     sheet.GetCellInt(3, 1) // C1 => 1000
	GetCellInt(col, row int) int

	// Cell get cell by cell col and row
	Cell(col, row int) Cell

	// AxisCell get cell by cell name
	AxisCell(axis Axis) Cell
}

Sheet sheet operator