xlsx

package module
v3.2.4 Latest Latest
Warning

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

 Go to latest Published: Nov 3, 2021 License: BSD-3-Clause Imports: 25 Imported by: 0

README 

* XLSX

[[https://travis-ci.org/tealeg/xlsx][https://img.shields.io/travis/tealeg/xlsx/master.svg?style=flat-square]]
[[https://codecov.io/gh/tealeg/xlsx][https://codecov.io/gh/tealeg/xlsx/branch/master/graph/badge.svg]]
[[https://godoc.org/github.com/tealeg/xlsx][https://godoc.org/github.com/tealeg/xlsx?status.svg]]
[[https://github.com/tealeg/xlsx#license][https://img.shields.io/badge/license-bsd-orange.svg]]

** Introduction
xlsx is a library to simplify reading and writing the XML format used
by recent version of Microsoft Excel in Go programs.

** Different versions of this project

*** Prior to v1.0.0

You don't want these versions ;-)

It's hard to remember exactly, but work on this library started within
a month of the first public announcement of Go, now more than a decade
ago.  It was essentially a quick hack to get data out of XLSX files at
my workplace.  Nobody but me relied on it, so it was fine to use this
brand new language for this task. Somewhat later I decided to share
the code, and I know it was well established as an open-source project
by the time I left that job in late 2011.

Although I did do some "release" tags, versioning in Go in the early
days relied on tagging your code with the name of the Go release
(i.e. go1.2) and then `go get` would fetch that tag, if it existed,
and if not, it'd grab the master branch.

*** Version 1.x.x

Version 1.0.0 was tagged in 2017 to support vendoring tools.

As of October 8th, 2019, I've branched off v1.x.x maintenance work
from master.  The master branch now tracks v2.x.x.

If you have existing code, can live with the issues in the 1.x.x
codebase, and don't want to update your code to use a later version,
then you can stick to these releases.  I mostly won't be touching this
code, but if something really important comes up, let me know.

*** Version 2.x.x

Version 2.0.0 introduced breaking changes in the API.

The scope of these changes included the way `Col` elements and
`DataValidation` works, as these aspects have been built around
incorrect models of the underlying XLSX format.

See the [[milestone][https://github.com/tealeg/xlsx/milestone/5]] for details.

Version 2.0.1 was tagged purely because 2.0.0 wasn't handled correctly
with regards to how go modules work. It isn't possible to use 2.0.0
from a Go Modules based project.

*** Version 3.x.x 
Version 3.0.0 introduces some more breaking changes in the API.  All
methods that can return an `xlsx.File` struct now accept zero, one or
many `xlsx.FileOption` functions as their final arguments.  These can
be used to modify the behaviour of the resultant struct - in
particular they replace the `...WithRowLimit` variants of those
methods with the result of calling `xlsx.RowLimit` and they add the
ability to define a custom backing store for the spreadsheet data to
be held in whilst processing.

StreamFileBuilder has been dropped from this version of the library as it has become difficult to maintain. 

** Full API docs
The full API docs can be viewed using go's built in documentation
tool, or online at [[http://godoc.org/github.com/tealeg/xlsx][godoc.org]].

** Contributing

We're extremely happy to review pull requests.  Please be patient, maintaining XLSX doesn't pay anyone's salary (to my knowledge).

If you'd like to propose a change please ensure the following:

- All existing tests are passing.
- There are tests in the test suite that cover the changes you're making.
- You have added documentation strings (in English) to (at least) the public functions you've added or modified.
- Your use of, or creation of, XML is compliant with [[http://www.ecma-international.org/publications/standards/Ecma-376.htm][part 1 of the 4th edition of the ECMA-376 Standard for Office Open XML]].

Eat a peach - Geoff

Documentation

Index

Examples

Constants

View Source 
const (
	DataValidationTypeCustom
	DataValidationTypeDate
	DataValidationTypeDecimal

	DataValidationTypeTextLeng
	DataValidationTypeTime
	// DataValidationTypeWhole Integer
	DataValidationTypeWhole
)

Data validation types

View Source 
const (
	DataValidationOperatorBetween
	DataValidationOperatorEqual
	DataValidationOperatorGreaterThan
	DataValidationOperatorGreaterThanOrEqual
	DataValidationOperatorLessThan
	DataValidationOperatorLessThanOrEqual
	DataValidationOperatorNotBetween
	DataValidationOperatorNotEqual
)

Data validation operators

View Source 
const (
	MJD_0      float64 = 2400000.5
	MJD_JD2000 float64 = 51544.5
)
View Source 
const (
	TRUE  = 0x01
	FALSE = 0x00
	US    = 0x1f // Unit Separator
	RS    = 0x1e // Record Separator
	GS    = 0x1d // Group Separator
)
View Source 
const (
	// RichTextFontFamilyUnspecified indicates that the font family was not specified
	RichTextFontFamilyUnspecified   RichTextFontFamily = -1
	RichTextFontFamilyNotApplicable RichTextFontFamily = 0
	RichTextFontFamilyRoman         RichTextFontFamily = 1
	RichTextFontFamilySwiss         RichTextFontFamily = 2
	RichTextFontFamilyModern        RichTextFontFamily = 3
	RichTextFontFamilyScript        RichTextFontFamily = 4
	RichTextFontFamilyDecorative    RichTextFontFamily = 5

	// RichTextCharsetUnspecified indicates that the font charset was not specified
	RichTextCharsetUnspecified RichTextCharset = -1
	RichTextCharsetANSI        RichTextCharset = 0
	RichTextCharsetDefault     RichTextCharset = 1
	RichTextCharsetSymbol      RichTextCharset = 2
	RichTextCharsetMac         RichTextCharset = 77
	RichTextCharsetShiftJIS    RichTextCharset = 128
	RichTextCharsetHangul      RichTextCharset = 129
	RichTextCharsetJohab       RichTextCharset = 130
	RichTextCharsetGB2312      RichTextCharset = 134
	RichTextCharsetBIG5        RichTextCharset = 136
	RichTextCharsetGreek       RichTextCharset = 161
	RichTextCharsetTurkish     RichTextCharset = 162
	RichTextCharsetVietnamese  RichTextCharset = 163
	RichTextCharsetHebrew      RichTextCharset = 177
	RichTextCharsetArabic      RichTextCharset = 178
	RichTextCharsetBaltic      RichTextCharset = 186
	RichTextCharsetRussian     RichTextCharset = 204
	RichTextCharsetThai        RichTextCharset = 222
	RichTextCharsetEastEurope  RichTextCharset = 238
	RichTextCharsetOEM         RichTextCharset = 255

	RichTextVertAlignSuperscript RichTextVertAlign = "superscript"
	RichTextVertAlignSubscript   RichTextVertAlign = "subscript"

	RichTextUnderlineSingle RichTextUnderline = "single"
	RichTextUnderlineDouble RichTextUnderline = "double"
)
View Source 
const (
	Helvetica     = "Helvetica"
	Baskerville   = "Baskerville Old Face"
	TimesNewRoman = "Times New Roman"
	Bodoni        = "Bodoni MT"
	GillSans      = "Gill Sans MT"
	Courier       = "Courier"
)

Several popular font names that can be used to create fonts

View Source 
const (
	RGB_Light_Green = "FFC6EFCE"
	RGB_Dark_Green  = "FF006100"
	RGB_Light_Red   = "FFFFC7CE"
	RGB_Dark_Red    = "FF9C0006"
	RGB_White       = "00000000"
	RGB_Black       = "FFFFFFFF"
)
View Source 
const ColWidth = 9.5

Default column width in excel

View Source 
const Excel2006MaxRowCount = 1048576
View Source 
const Excel2006MaxRowIndex = Excel2006MaxRowCount - 1
View Source 
const NoRowLimit int = -1
View Source 
const (
	Solid_Cell_Fill = "solid"
)
View Source 
const TEMPLATE_DOCPROPS_APP = `` /* 308-byte string literal not displayed */
View Source 
const TEMPLATE_DOCPROPS_CORE = `` /* 364-byte string literal not displayed */
View Source 
const TEMPLATE_XL_THEME_THEME = `` /* 10940-byte string literal not displayed */
View Source 
const TEMPLATE__RELS_DOT_RELS = `` /* 580-byte string literal not displayed */

Variables

View Source 
var (
	DefaultDateFormat     = builtInNumFmt[14]
	DefaultDateTimeFormat = builtInNumFmt[22]

	DefaultDateOptions = DateTimeOptions{
		Location:        timeLocationUTC,
		ExcelTimeFormat: DefaultDateFormat,
	}

	DefaultDateTimeOptions = DateTimeOptions{
		Location:        timeLocationUTC,
		ExcelTimeFormat: DefaultDateTimeFormat,
	}
)
View Source 
var HSLModel = color.ModelFunc(hslModel)

HSLModel converts any color.Color to a HSL color.

Functions

func ColIndexToLetters 

func ColIndexToLetters(n int) string

ColIndexToLetters is used to convert a zero based, numeric column indentifier into a character code.

func ColLettersToIndex 

func ColLettersToIndex(letters string) int

ColLettersToIndex is used to convert a character based column reference to a zero based numeric column identifier.

func FileToSlice 

func FileToSlice(path string, options ...FileOption) ([][][]string, error)

A convenient wrapper around File.ToSlice, FileToSlice will return the raw data contained in an Excel XLSX file as three dimensional slice. The first index represents the sheet number, the second the row number, and the third the cell number.

For example: 

var mySlice [][][]string
var value string
mySlice = xlsx.FileToSlice("myXLSX.xlsx")
value = mySlice[0][0][0]

Here, value would be set to the raw value of the cell A1 in the first sheet in the XLSX file.

func FileToSliceUnmerged 

func FileToSliceUnmerged(path string, options ...FileOption) ([][][]string, error)

FileToSliceUnmerged is a wrapper around File.ToSliceUnmerged. It returns the raw data contained in an Excel XLSX file as three dimensional slice. Merged cells will be unmerged. Covered cells become the values of theirs origins.

func GetCellIDStringFromCoords 

func GetCellIDStringFromCoords(x, y int) string

GetCellIDStringFromCoords returns the Excel format cell name that represents a pair of zero based cartesian coordinates.

func GetCellIDStringFromCoordsWithFixed 

func GetCellIDStringFromCoordsWithFixed(x, y int, xFixed, yFixed bool) string

GetCellIDStringFromCoordsWithFixed returns the Excel format cell name that represents a pair of zero based cartesian coordinates. It can specify either value as fixed.

func GetCoordsFromCellIDString 

func GetCoordsFromCellIDString(cellIDString string) (x, y int, err error)

GetCoordsFromCellIDString returns the zero based cartesian coordinates from a cell name in Excel format, e.g. the cellIDString "A1" returns 0, 0 and the "B3" return 1, 2.

func HSLToRGB 

func HSLToRGB(h, s, l float64) (r, g, b uint8)

HSLToRGB converts an HSL triple to a RGB triple.

Ported from http://goo.gl/Vg1h9

func MakeDefaultContentTypes 

func MakeDefaultContentTypes() (types xlsxTypes)

func NewDataValidation 

func NewDataValidation(startRow, startCol, endRow, endCol int, allowBlank bool) *xlsxDataValidation

NewDataValidation return data validation struct

func RGBToHSL 

func RGBToHSL(r, g, b uint8) (h, s, l float64)

RGBToHSL converts an RGB triple to a HSL triple.

Ported from http://goo.gl/Vg1h9

func RowIndexToString 

func RowIndexToString(rowRef int) string

RowIndexToString is used to convert a zero based, numeric row indentifier into its string representation.

func SetDefaultFont 

func SetDefaultFont(size float64, name string)

func SkipEmptyCells 

func SkipEmptyCells(flags *cellVisitorFlags)

SkipEmptyCells can be passed as an option to Row.ForEachCell in order to make it skip over empty cells in the sheet.

func SkipEmptyRows 

func SkipEmptyRows(flags *rowVisitorFlags)

SkipEmptyRows can be passed to the Sheet.ForEachRow function to cause it to skip over empty Rows.

func TimeFromExcelTime 

func TimeFromExcelTime(excelTime float64, date1904 bool) time.Time

Convert an excelTime representation (stored as a floating point number) to a time.Time.

func TimeToExcelTime 

func TimeToExcelTime(t time.Time, date1904 bool) float64

TimeToExcelTime will convert a time.Time into Excel's float representation, in either 1900 or 1904 mode. If you don't know which to use, set date1904 to false. TODO should this should handle Julian dates?

func TimeToUTCTime 

func TimeToUTCTime(t time.Time) time.Time

func UseDiskVCellStore 

func UseDiskVCellStore(f *File)

UseDiskVCellStore is a FileOption that makes all Sheet instances for a File use DiskV as their backing store. You can use this option when handling very large Sheets that would otherwise riquire allocating vast amounts of memory.

func UseMemoryCellStore 

func UseMemoryCellStore(f *File)

UseMemoryCellStore is a FileOption that makes all Sheet instances for a File use memory as their backing store. This is the default backing store. You can use this option when you are comfortable keeping the contents of each Sheet in memory. This is faster than using a disk backed store, but can easily use a large amount of memory and, if you exhaust the available system memory, it'll actualy be slower than using a disk backed store (e.g. DiskV).

Types

type Alignment 

type Alignment struct {
	Horizontal   string
	Indent       int
	ShrinkToFit  bool
	TextRotation int
	Vertical     string
	WrapText     bool
}

func DefaultAlignment 

func DefaultAlignment() *Alignment

type AutoFilter 

type AutoFilter struct {
	TopLeftCell     string
	BottomRightCell string
}

type Border 

type Border struct {
	Left        string
	LeftColor   string
	Right       string
	RightColor  string
	Top         string
	TopColor    string
	Bottom      string
	BottomColor string
}

Border is a high level structure intended to provide user access to the contents of Border Style within an Sheet.

func DefaultBorder 

func DefaultBorder() *Border

func NewBorder 

func NewBorder(left, right, top, bottom string) *Border

type Cell 

type Cell struct {
	Row      *Row
	Value    string
	RichText []RichTextRun

	NumFmt string

	Hidden bool
	HMerge int
	VMerge int

	DataValidation *xlsxDataValidation
	Hyperlink      Hyperlink
	// contains filtered or unexported fields
}

Cell is a high level structure intended to provide user access to the contents of Cell within an xlsx.Row.

func (*Cell) Bool 

func (c *Cell) Bool() bool

Bool returns a boolean from a cell's value. TODO: Determine if the current return value is appropriate for types other than CellTypeBool.

func (*Cell) Float 

func (c *Cell) Float() (float64, error)

Float returns the value of cell as a number.

func (*Cell) FormattedValue 

func (c *Cell) FormattedValue() (string, error)

FormattedValue returns a value, and possibly an error condition from a Cell. If it is possible to apply a format to the cell value, it will do so, if not then an error will be returned, along with the raw value of the Cell.

func (*Cell) Formula 

func (c *Cell) Formula() string

Formula returns the formula string for the cell.

func (*Cell) GeneralNumeric 

func (c *Cell) GeneralNumeric() (string, error)

GeneralNumeric returns the value of the cell as a string. It is formatted very closely to the the XLSX spec for how to display values when the storage type is Number and the format type is General. It is not 100% identical to the spec but is as close as you can get using the built in Go formatting tools.

func (*Cell) GeneralNumericWithoutScientific 

func (c *Cell) GeneralNumericWithoutScientific() (string, error)

GeneralNumericWithoutScientific returns numbers that are always formatted as numbers, but it does not follow the rules for when XLSX should switch to scientific notation, since sometimes scientific notation is not desired, even if that is how the document is supposed to be formatted.

func (*Cell) GetNumberFormat 

func (c *Cell) GetNumberFormat() string

GetNumberFormat returns the number format string for a cell.

func (*Cell) GetStyle 

func (c *Cell) GetStyle() *Style

GetStyle returns the Style associated with a Cell

func (*Cell) GetTime 

func (c *Cell) GetTime(date1904 bool) (t time.Time, err error)

GetTime returns the value of a Cell as a time.Time

func (*Cell) Int 

func (c *Cell) Int() (int, error)

Int returns the value of cell as integer. Has max 53 bits of precision See: float64(int64(math.MaxInt))

func (*Cell) Int64 

func (c *Cell) Int64() (int64, error)

Int64 returns the value of cell as 64-bit integer.

func (*Cell) IsTime 

func (c *Cell) IsTime() bool

IsTime returns true if the cell stores a time value.

func (Cell) MarshalBinary 

func (c Cell) MarshalBinary() ([]byte, error)

Return a representation of the Cell as a slice of bytes

func (*Cell) Merge 

func (c *Cell) Merge(hcells, vcells int)

Merge with other cells, horizontally and/or vertically.

func (*Cell) SetBool 

func (c *Cell) SetBool(b bool)

SetBool sets a cell's value to a boolean.

func (*Cell) SetDataValidation 

func (c *Cell) SetDataValidation(dd *xlsxDataValidation)

SetDataValidation set data validation

func (*Cell) SetDate 

func (c *Cell) SetDate(t time.Time)

SetDate sets the value of a cell to a float.

func (*Cell) SetDateTime 

func (c *Cell) SetDateTime(t time.Time)

func (*Cell) SetDateTimeWithFormat 

func (c *Cell) SetDateTimeWithFormat(n float64, format string)

func (*Cell) SetDateWithOptions 

func (c *Cell) SetDateWithOptions(t time.Time, options DateTimeOptions)

SetDateWithOptions allows for more granular control when exporting dates and times

func (*Cell) SetFloat 

func (c *Cell) SetFloat(n float64)

SetFloat sets the value of a cell to a float.

func (*Cell) SetFloatWithFormat 

func (c *Cell) SetFloatWithFormat(n float64, format string)

SetFloatWithFormat sets the value of a cell to a float and applies formatting to the cell.

func (*Cell) SetFormat 

func (c *Cell) SetFormat(format string)

SetCellFormat set cell value format

func (*Cell) SetFormula 

func (c *Cell) SetFormula(formula string)

SetFormula sets the format string for a cell. 

func (c *Cell) SetHyperlink(hyperlink string, displayText string, tooltip string)

SetHyperlink sets this cell to contain the given hyperlink, displayText and tooltip. If the displayText or tooltip are an empty string, they will not be set. The hyperlink provided must be a valid URL starting with http:// or https:// or excel will not recognize it as an external link.

func (*Cell) SetInt 

func (c *Cell) SetInt(n int)

SetInt sets a cell's value to an integer.

func (*Cell) SetInt64 

func (c *Cell) SetInt64(n int64)

SetInt64 sets a cell's value to a 64-bit integer.

func (*Cell) SetNumeric 

func (c *Cell) SetNumeric(s string)

SetNumeric sets a cell's value to a number

func (*Cell) SetRichText 

func (c *Cell) SetRichText(r []RichTextRun)

SetRichText sets the value of a cell to a set of the rich text.

func (*Cell) SetString 

func (c *Cell) SetString(s string)

SetString sets the value of a cell to a string.

func (*Cell) SetStringFormula 

func (c *Cell) SetStringFormula(formula string)

func (*Cell) SetStyle 

func (c *Cell) SetStyle(style *Style)

SetStyle sets the style of a cell.

func (*Cell) SetValue 

func (c *Cell) SetValue(n interface{})

SetInt sets a cell's value to an integer.

func (*Cell) String 

func (c *Cell) String() string

String returns the value of a Cell as a string. If you'd like to see errors returned from formatting then please use Cell.FormattedValue() instead.

func (*Cell) Type 

func (c *Cell) Type() CellType

Type returns the CellType of a cell. See CellType constants for more details.

func (*Cell) UnmarshalBinary 

func (c *Cell) UnmarshalBinary(data []byte) error

Read a slice of bytes, produced by MarshalBinary, into a Cell

type CellInterface 

type CellInterface interface {
	String() string
	FormattedValue() string
}

CellInterface defines the public API of the Cell.

type CellStore 

type CellStore interface {
	ReadRow(key string) (*Row, error)
	WriteRow(r *Row) error
	MoveRow(r *Row, newIndex int) error
	RemoveRow(key string) error
	Close() error
}

CellStore provides an interface for interacting with backend cell storage. For example, this allows us, as required, to persist cells to some store instead of holding them in memory. This tactic allows us a degree of control around the characteristics of our programs when handling large spreadsheets - we can choose to run more slowly, but without exhausting system memory.

If you wish to implement a custom CellStore you must not only support this interface, but also a CellStoreConstructor and a FileOption that set's the File's cellStoreConstructor to the right constructor.

func NewDiskVCellStore 

func NewDiskVCellStore() (CellStore, error)

NewDiskVCellStore is a CellStoreConstructor than returns a CellStore in terms of DiskV.

func NewMemoryCellStore 

func NewMemoryCellStore() (CellStore, error)

NewMemoryCellStore returns a pointer to a newly allocated MemoryCellStore

type CellStoreConstructor 

type CellStoreConstructor func() (CellStore, error)

CellStoreConstructor defines the signature of a function that will be used to return a new instance of the CellStore implmentation, you must pass this into

type CellType 

type CellType int

CellType is an int type for storing metadata about the data type in the cell. 

const (
	CellTypeString CellType = iota
	// CellTypeStringFormula is a specific format for formulas that return string values. Formulas that return numbers
	// and booleans are stored as those types.
	CellTypeStringFormula
	CellTypeNumeric
	CellTypeBool
	// CellTypeInline is not respected on save, all inline string cells will be saved as SharedStrings
	// when saving to an XLSX file. This the same behavior as that found in Excel.
	CellTypeInline
	CellTypeError
	// d (Date): Cell contains a date in the ISO 8601 format.
	// That is the only mention of this format in the XLSX spec.
	// Date seems to be unused by the current version of Excel, it stores dates as Numeric cells with a date format string.
	// For now these cells will have their value output directly. It is unclear if the value is supposed to be parsed
	// into a number and then formatted using the formatting or not.
	CellTypeDate
)

These are the cell types from the ST_CellType spec

func (CellType) Ptr 

func (ct CellType) Ptr() *CellType

type CellVisitorFunc 

type CellVisitorFunc func(c *Cell) error

CellVisitorFunc defines the signature of a function that will be called when visiting a Cell using CellStore.ForEachInRow.

type CellVisitorOption 

type CellVisitorOption func(flags *cellVisitorFlags)

CellVisitorOption describes a function that can set values in a cellVisitorFlags struct to affect the way ForEachCell operates

type Col 

type Col struct {
	Min          int
	Max          int
	Hidden       *bool
	Width        *float64
	Collapsed    *bool
	OutlineLevel *uint8
	BestFit      *bool
	CustomWidth  *bool
	Phonetic     *bool
	// contains filtered or unexported fields
}

func NewColForRange 

func NewColForRange(min, max int) *Col

NewColForRange return a pointer to a new Col, which will apply to columns in the range min to max (inclusive). Note, in order for this Col to do anything useful you must set some of its parameters and then apply it to a Sheet by calling sheet.SetColParameters.

func (*Col) GetStyle 

func (c *Col) GetStyle() *Style

GetStyle returns the Style associated with a Col

func (*Col) SetOutlineLevel 

func (c *Col) SetOutlineLevel(outlineLevel uint8)

func (*Col) SetStyle 

func (c *Col) SetStyle(style *Style)

SetStyle sets the style of a Col

func (*Col) SetType 

func (c *Col) SetType(cellType CellType)

SetType will set the format string of a column based on the type that you want to set it to. This function does not really make a lot of sense.

func (*Col) SetWidth 

func (c *Col) SetWidth(width float64)

SetWidth sets the width of columns that have this Col applied to them. The width is expressed as the number of characters of the maximum digit width of the numbers 0-9 as rendered in the normal style's font.

type ColStore 

type ColStore struct {
	Root *ColStoreNode
	Len  int
}

ColStore is the working store of Col definitions, it will simplify all Cols added to it, to ensure there ar no overlapping definitions.

func (*ColStore) Add 

func (cs *ColStore) Add(col *Col) *ColStoreNode

Add a Col to the ColStore. If it overwrites all, or part of some existing Col's range of columns the that Col will be adjusted and/or split to make room for the new Col.

func (*ColStore) FindColByIndex 

func (cs *ColStore) FindColByIndex(index int) *Col

func (*ColStore) ForEach 

func (cs *ColStore) ForEach(fn func(idx int, col *Col))

ForEach calls the function fn for each Col defined in the ColStore.

type ColStoreNode 

type ColStoreNode struct {
	Col  *Col
	Prev *ColStoreNode
	Next *ColStoreNode
}

type DataValidationErrorStyle 

type DataValidationErrorStyle int
const (
	StyleStop DataValidationErrorStyle
	StyleWarning
	StyleInformation
)

Data validation error styles

type DataValidationOperator 

type DataValidationOperator int

DataValidationOperator operator enum

type DataValidationType 

type DataValidationType int

type DateTimeOptions 

type DateTimeOptions struct {
	// Location allows calculating times in other timezones/locations
	Location *time.Location
	// ExcelTimeFormat is the string you want excel to use to format the datetime
	ExcelTimeFormat string
}

DateTimeOptions are additional options for exporting times

type DiskVCellStore 

type DiskVCellStore struct {
	// contains filtered or unexported fields
}

DiskVCellStore is an implementation of the CellStore interface, backed by DiskV

func (*DiskVCellStore) Close 

func (cs *DiskVCellStore) Close() error

Close will remove the persisant storage for a given Sheet completely.

func (*DiskVCellStore) MoveRow 

func (cs *DiskVCellStore) MoveRow(r *Row, index int) error

MoveRow moves a Row from one position in a Sheet (index) to another within the persistant store.

func (*DiskVCellStore) ReadRow 

func (cs *DiskVCellStore) ReadRow(key string) (*Row, error)

ReadRow reads a row from the persistant store, identified by key, into memory and returns it.

func (*DiskVCellStore) RemoveRow 

func (cs *DiskVCellStore) RemoveRow(key string) error

RemoveRow removes a Row from the Sheet's representation in the persistant store.

func (*DiskVCellStore) WriteRow 

func (cs *DiskVCellStore) WriteRow(r *Row) error

WriteRow writes a Row to persistant storage.

type File 

type File struct {
	Date1904 bool

	Sheets []*Sheet
	Sheet  map[string]*Sheet

	DefinedNames []*xlsxDefinedName
	// contains filtered or unexported fields
}

File is a high level structure providing a slice of Sheet structs to the user.

func NewFile 

func NewFile(options ...FileOption) *File

NewFile creates a new File struct. You may pass it zero, one or many FileOption functions that affect the behaviour of the file.

func OpenBinary 

func OpenBinary(bs []byte, options ...FileOption) (*File, error)

OpenBinary() take bytes of an XLSX file and returns a populated xlsx.File struct for it.

func OpenFile 

func OpenFile(fileName string, options ...FileOption) (file *File, err error)

OpenFile will take the name of an XLSX file and returns a populated xlsx.File struct for it. You may pass it zero, one or many FileOption functions that affect the behaviour of the file.

func OpenReaderAt 

func OpenReaderAt(r io.ReaderAt, size int64, options ...FileOption) (*File, error)

OpenReaderAt() take io.ReaderAt of an XLSX file and returns a populated xlsx.File struct for it.

func ReadZip 

func ReadZip(f *zip.ReadCloser, options ...FileOption) (*File, error)

ReadZip() takes a pointer to a zip.ReadCloser and returns a xlsx.File struct populated with its contents. In most cases ReadZip is not used directly, but is called internally by OpenFile.

func ReadZipReader 

func ReadZipReader(r *zip.Reader, options ...FileOption) (*File, error)

ReadZipReader() can be used to read an XLSX in memory without touching the filesystem.

func (*File) AddSheet 

func (f *File) AddSheet(sheetName string) (*Sheet, error)

AddSheet Add a new Sheet, with the provided name, to a File. The minimum sheet name length is 1 character. If the sheet name length is less an error is thrown. The maximum sheet name length is 31 characters. If the sheet name length is exceeded an error is thrown. These special characters are also not allowed: : \ / ? * [ ]

func (*File) AddSheetWithCellStore 

func (f *File) AddSheetWithCellStore(sheetName string, constructor CellStoreConstructor) (*Sheet, error)

func (*File) AppendSheet 

func (f *File) AppendSheet(sheet Sheet, sheetName string) (*Sheet, error)

Appends an existing Sheet, with the provided name, to a File

func (*File) MakeStreamParts 

func (f *File) MakeStreamParts() (map[string]string, error)

MakeStreamParts constructs a map of file name to XML content representing the file in terms of the structure of an XLSX file.

func (*File) MarshallParts 

func (f *File) MarshallParts(zipWriter *zip.Writer) error

MarshallParts constructs a map of file name to XML content representing the file in terms of the structure of an XLSX file.

func (*File) Save 

func (f *File) Save(path string) (err error)

Save the File to an xlsx file at the provided path.

func (*File) ToSlice 

func (f *File) ToSlice() (output [][][]string, err error)

Return the raw data contained in the File as three dimensional slice. The first index represents the sheet number, the second the row number, and the third the cell number.

For example: 

var mySlice [][][]string
var value string
mySlice = xlsx.FileToSlice("myXLSX.xlsx")
value = mySlice[0][0][0]

Here, value would be set to the raw value of the cell A1 in the first sheet in the XLSX file.

func (*File) ToSliceUnmerged 

func (f *File) ToSliceUnmerged() (output [][][]string, err error)

ToSliceUnmerged returns the raw data contained in the File as three dimensional slice (s. method ToSlice). A covered cell become the value of its origin cell. Example: table where A1:A2 merged. | 01.01.2011 | Bread | 20 | | | Fish | 70 | This sheet will be converted to the slice: [ [01.01.2011 Bread 20] 

[01.01.2011 Fish 70] ]

func (*File) Write 

func (f *File) Write(writer io.Writer) error

Write the File to io.Writer as xlsx

type FileOption 

type FileOption func(f *File)

func RowLimit 

func RowLimit(n int) FileOption

RowLimit will limit the rows handled in any given sheet to the first n, where n is the number of rows.

type Fill 

type Fill struct {
	PatternType string
	BgColor     string
	FgColor     string
}

Fill is a high level structure intended to provide user access to the contents of background and foreground color index within an Sheet.

func DefaultFill 

func DefaultFill() *Fill

func NewFill 

func NewFill(patternType, fgColor, bgColor string) *Fill

type Font 

type Font struct {
	Size      float64
	Name      string
	Family    int
	Charset   int
	Color     string
	Bold      bool
	Italic    bool
	Underline bool
	Strike    bool
}

func DefaultFont 

func DefaultFont() *Font

func NewFont 

func NewFont(size float64, name string) *Font

type HSL 

type HSL struct {
	H, S, L float64
}

HSL represents a cylindrical coordinate of points in an RGB color model.

Values are in the range 0 to 1.

func (HSL) RGBA 

func (c HSL) RGBA() (uint32, uint32, uint32, uint32)

RGBA returns the alpha-premultiplied red, green, blue and alpha values for the HSL. 

type Hyperlink struct {
	DisplayString string
	Link          string
	Tooltip       string
}

type MemoryCellStore 

type MemoryCellStore struct {
	// contains filtered or unexported fields
}

MemoryCellStore is the default CellStore - it holds all rows and cells in system memory. This is fast, right up until you run out of memory ;-)

func (*MemoryCellStore) Close 

func (mcs *MemoryCellStore) Close() error

Close is nullOp for the MemoryCellStore, but we have to comply with the interface.

func (*MemoryCellStore) MoveRow 

func (mcs *MemoryCellStore) MoveRow(r *Row, index int) error

MoveRow moves the persisted Row's position in the sheet.

func (*MemoryCellStore) ReadRow 

func (mcs *MemoryCellStore) ReadRow(key string) (*Row, error)

ReadRow returns a Row identfied by the given key.

func (*MemoryCellStore) RemoveRow 

func (mcs *MemoryCellStore) RemoveRow(key string) error

RemoveRow removes a row from the sheet, it doesn't specifically move any following rows, leaving this decision to the user.

func (*MemoryCellStore) WriteRow 

func (mcs *MemoryCellStore) WriteRow(r *Row) error

WriteRow pushes the Row to the MemoryCellStore.

type Pane 

type Pane struct {
	XSplit      float64
	YSplit      float64
	TopLeftCell string
	ActivePane  string
	State       string // Either "split" or "frozen"
}

type RefTable 

type RefTable struct {
	// contains filtered or unexported fields
}

func MakeSharedStringRefTable 

func MakeSharedStringRefTable(source *xlsxSST) *RefTable

MakeSharedStringRefTable takes an xlsxSST struct and converts it's contents to an slice of strings used to refer to string values by numeric index - this is the model used within XLSX worksheet (a numeric reference is stored to a shared cell value).

func NewSharedStringRefTable 

func NewSharedStringRefTable() *RefTable

NewSharedStringRefTable creates a new, empty RefTable.

func (*RefTable) AddRichText 

func (rt *RefTable) AddRichText(r []RichTextRun) int

AddRichText adds a set of rich text to the reference table and return it's numeric index. If a set of rich text already exists then it simply returns the existing index.

func (*RefTable) AddString 

func (rt *RefTable) AddString(str string) int

AddString adds a string to the reference table and return it's numeric index. If the string already exists then it simply returns the existing index.

func (*RefTable) Length 

func (rt *RefTable) Length() int

func (*RefTable) ResolveSharedString 

func (rt *RefTable) ResolveSharedString(index int) (plainText string, richText []RichTextRun)

ResolveSharedString looks up a string value or the rich text by numeric index from a provided reference table (just a slice of strings in the correct order). If the rich text was found, non-empty slice will be returned in richText. This function only exists to provide clarity of purpose via it's name.

type Relation 

type Relation struct {
	Type       RelationshipType
	Target     string
	TargetMode RelationshipTargetMode
}

type RelationshipTargetMode 

type RelationshipTargetMode string
const (
	RelationshipTargetModeExternal RelationshipTargetMode = "External"
)

type RelationshipType 

type RelationshipType string
const (
	RelationshipTypeHyperlink RelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink"
)

type RichTextCharset 

type RichTextCharset int

type RichTextColor 

type RichTextColor struct {
	// contains filtered or unexported fields
}

RichTextColor is the color of the RichTextRun.

func NewRichTextColorFromARGB 

func NewRichTextColorFromARGB(alpha, red, green, blue int) *RichTextColor

NewRichTextColorFromARGB creates a new RichTextColor from ARGB component values. Each component must have a value in range of 0 to 255.

func NewRichTextColorFromThemeColor 

func NewRichTextColorFromThemeColor(themeColor int) *RichTextColor

NewRichTextColorFromThemeColor creates a new RichTextColor from the theme color. The argument `themeColor` is a zero-based index of the theme color.

type RichTextFont 

type RichTextFont struct {
	// Name is the font name. If Name is empty, Size, Family and Charset will be ignored.
	Name string
	// Size is the font size.
	Size float64
	// Family is a value of the font family. Use one of the RichTextFontFamily constants.
	Family RichTextFontFamily
	// Charset is a value of the charset of the font. Use one of the RichTextCharset constants.
	Charset RichTextCharset
	// Color is the text color.
	Color *RichTextColor
	// Bold specifies the bold face font style.
	Bold bool
	// Italic specifies the italic font style.
	Italic bool
	// Strike specifies a strikethrough line.
	Strike bool
	// VertAlign specifies the vertical position of the text. Use one of the RichTextVertAlign constants, or empty.
	VertAlign RichTextVertAlign
	// Underline specifies the underline style. Use one of the RichTextUnderline constants, or empty.
	Underline RichTextUnderline
}

RichTextFont is the font spec of the RichTextRun.

type RichTextFontFamily 

type RichTextFontFamily int

type RichTextRun 

type RichTextRun struct {
	Font *RichTextFont
	Text string
}

RichTextRun is a run of the decorated text.

func (*RichTextRun) Equals 

func (rt *RichTextRun) Equals(other *RichTextRun) bool

type RichTextUnderline 

type RichTextUnderline string

type RichTextVertAlign 

type RichTextVertAlign string

type Row 

type Row struct {
	Hidden bool   // Hidden determines whether this Row is hidden or not.
	Sheet  *Sheet // Sheet is a reference back to the Sheet that this Row is within.
	// contains filtered or unexported fields
}

Row represents a single Row in the current Sheet.

func (*Row) AddCell 

func (r *Row) AddCell() *Cell

AddCell adds a new Cell to the Row

func (*Row) ForEachCell 

func (r *Row) ForEachCell(cvf CellVisitorFunc, option ...CellVisitorOption) error

ForEachCell will call the provided CellVisitorFunc for each currently defined cell in the Row. Optionally you may pass one or more CellVisitorOption to affect how ForEachCell operates. For example you may wish to pass SkipEmptyCells to only visit cells which are populated.

func (*Row) GetCell 

func (r *Row) GetCell(colIdx int) *Cell

GetCell returns the Cell at a given column index, creating it if it doesn't exist.

func (*Row) GetHeight 

func (r *Row) GetHeight() float64

GetHeight returns the height of the Row in PostScript points.

func (*Row) GetOutlineLevel 

func (r *Row) GetOutlineLevel() uint8

GetOutlineLevel returns the outline level of the Row.

func (*Row) ReadStruct 

func (r *Row) ReadStruct(ptr interface{}) error

ReadStruct reads a struct from r to ptr. Accepts a ptr to struct. This code expects a tag xlsx:"N", where N is the index of the cell to be used. Basic types like int,string,float64 and bool are supported

Example 
//example type
type structTest struct {
	IntVal     int     `xlsx:"0"`
	StringVal  string  `xlsx:"1"`
	FloatVal   float64 `xlsx:"2"`
	IgnoredVal int     `xlsx:"-"`
	BoolVal    bool    `xlsx:"4"`
}
structVal := structTest{
	IntVal:     16,
	StringVal:  "heyheyhey :)!",
	FloatVal:   3.14159216,
	IgnoredVal: 7,
	BoolVal:    true,
}
//create a new xlsx file and write a struct
//in a new row
f := NewFile()
sheet, _ := f.AddSheet("TestRead")
row := sheet.AddRow()
row.WriteStruct(&structVal, -1)

//read the struct from the same row
readStruct := &structTest{}
err := row.ReadStruct(readStruct)
if err != nil {
	panic(err)
}
fmt.Println(readStruct)

Output:

func (*Row) SetHeight 

func (r *Row) SetHeight(ht float64)

SetHeight sets the height of the Row in PostScript points

func (*Row) SetHeightCM 

func (r *Row) SetHeightCM(ht float64)

SetHeightCM sets the height of the Row in centimetres, inherently converting it to PostScript points.

func (*Row) SetOutlineLevel 

func (r *Row) SetOutlineLevel(outlineLevel uint8)

SetOutlineLevel sets the outline level of the Row (used for collapsing rows)

func (*Row) WriteSlice 

func (r *Row) WriteSlice(e interface{}, cols int) int

Writes an array to row r. Accepts a pointer to array type 'e', and writes the number of columns to write, 'cols'. If 'cols' is < 0, the entire array will be written if possible. Returns -1 if the 'e' doesn't point to an array, otherwise the number of columns written.

func (*Row) WriteStruct 

func (r *Row) WriteStruct(e interface{}, cols int) int

Writes a struct to row r. Accepts a pointer to struct type 'e', and the number of columns to write, `cols`. If 'cols' is < 0, the entire struct will be written if possible. Returns -1 if the 'e' doesn't point to a struct, otherwise the number of columns written

type RowNotFoundError 

type RowNotFoundError struct {
	// contains filtered or unexported fields
}

RowNotFoundError is an Error that should be returned by a RowStore implementation if a call to ReadRow is made with a key that doesn't correspond to any persisted Row.

func NewRowNotFoundError 

func NewRowNotFoundError(key, reason string) *RowNotFoundError

NewRowNotFoundError creates a new RowNotFoundError, capturing the Row key and the reason this key could not be found.

func (RowNotFoundError) Error 

func (cnfe RowNotFoundError) Error() string

Error returns a human-readable description of the failure to find a Row. It makes RowNotFoundError comply with the Error interface.

type RowVisitor 

type RowVisitor func(r *Row) error

A RowVisitor function should be provided by the user when calling Sheet.ForEachRow, it will be called once for every Row visited.

type RowVisitorOption 

type RowVisitorOption func(flags *rowVisitorFlags)

RowVisitorOption defines the call signature of functions that can be passed as options to the Sheet.ForEachRow function to affect its behaviour.

type Sheet 

type Sheet struct {
	Name            string
	File            *File
	Cols            *ColStore
	MaxRow          int
	MaxCol          int
	Hidden          bool
	Selected        bool
	SheetViews      []SheetView
	SheetFormat     SheetFormat
	AutoFilter      *AutoFilter
	Relations       []Relation
	DataValidations []*xlsxDataValidation
	// contains filtered or unexported fields
}

Sheet is a high level structure intended to provide user access to the contents of a particular sheet within an XLSX file.

func NewSheet 

func NewSheet(name string) (*Sheet, error)

NewSheet constructs a Sheet with the default CellStore and returns a pointer to it.

func NewSheetWithCellStore 

func NewSheetWithCellStore(name string, constructor CellStoreConstructor) (*Sheet, error)

NewSheetWithCellStore constructs a Sheet, backed by a CellStore, for which you must provide the constructor function.

func (*Sheet) AddDataValidation 

func (s *Sheet) AddDataValidation(dv *xlsxDataValidation)

Add a DataValidation to a range of cells

func (*Sheet) AddRow 

func (s *Sheet) AddRow() *Row

Add a new Row to a Sheet

func (*Sheet) AddRowAtIndex 

func (s *Sheet) AddRowAtIndex(index int) (*Row, error)

Add a new Row to a Sheet at a specific index

func (*Sheet) Cell 

func (s *Sheet) Cell(row, col int) (*Cell, error)

Get a Cell by passing it's cartesian coordinates (zero based) as row and column integer indexes.

For example: 

cell := sheet.Cell(0,0)

... would set the variable "cell" to contain a Cell struct containing the data from the field "A1" on the spreadsheet.

func (*Sheet) Close 

func (s *Sheet) Close()

Remove Sheet's dependant resources - if you are done with operations on a sheet this should be called to clear down the Sheet's persistent cache. Typically this happens *after* you've saved your changes.

func (*Sheet) Col 

func (s *Sheet) Col(idx int) *Col

Return the Col that applies to this Column index, or return nil if no such Col exists

func (*Sheet) ForEachRow 

func (s *Sheet) ForEachRow(rv RowVisitor, options ...RowVisitorOption) error

func (*Sheet) MarshalSheet 

func (s *Sheet) MarshalSheet(w io.Writer, refTable *RefTable, styles *xlsxStyleSheet, relations *xlsxWorksheetRels) error

func (*Sheet) RemoveRowAtIndex 

func (s *Sheet) RemoveRowAtIndex(index int) error

Removes a row at a specific index

func (*Sheet) Row 

func (s *Sheet) Row(idx int) (*Row, error)

Make sure we always have as many Rows as we do cells.

func (*Sheet) SetColParameters 

func (s *Sheet) SetColParameters(col *Col)

Set the parameters of a column. Parameters are passed as a pointer to a Col structure which you much construct yourself.

func (*Sheet) SetColWidth 

func (s *Sheet) SetColWidth(min, max int, width float64)

Set the width of a range of columns.

func (*Sheet) SetOutlineLevel 

func (s *Sheet) SetOutlineLevel(minCol, maxCol int, outlineLevel uint8)

Set the outline level for a range of columns.

func (*Sheet) SetType 

func (s *Sheet) SetType(minCol, maxCol int, cellType CellType)

Set the type for a range of columns.

type SheetFormat 

type SheetFormat struct {
	DefaultColWidth  float64
	DefaultRowHeight float64
	OutlineLevelCol  uint8
	OutlineLevelRow  uint8
}

type SheetView 

type SheetView struct {
	Pane *Pane
}

type Style 

type Style struct {
	Border          Border
	Fill            Fill
	Font            Font
	ApplyBorder     bool
	ApplyFill       bool
	ApplyFont       bool
	ApplyAlignment  bool
	Alignment       Alignment
	NamedStyleIndex *int
}

Style is a high level structure intended to provide user access to the contents of Style within an XLSX file.

func NewStyle 

func NewStyle() *Style

Return a new Style structure initialised with the default values.

type WorkBookRels 

type WorkBookRels map[string]string

func (*WorkBookRels) MakeXLSXWorkbookRels 

func (w *WorkBookRels) MakeXLSXWorkbookRels() xlsxWorkbookRels

type XLSXReaderError 

type XLSXReaderError struct {
	Err string
}

XLSXReaderError is the standard error type for otherwise undefined errors in the XSLX reading process.

func (*XLSXReaderError) Error 

func (e *XLSXReaderError) Error() string

Error returns a string value from an XLSXReaderError struct in order that it might comply with the builtin.error interface.

type XLSXUnmarshaler 

type XLSXUnmarshaler interface {
	Unmarshal(*Row) error
}

XLSXUnmarshaler is the interface implemented for types that can unmarshal a Row as a representation of themselves.

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.