xls

package module
v1.0.0 Latest Latest
Warning

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

 Go to latest Published: Jan 31, 2019 License: Apache-2.0 Imports: 14 Imported by: 0

README

xls

GoDoc

Pure Golang xls library writen by Rongshu Tech(chinese). Add Formula & Format support by [chen.s.g] (http://www.imohe.com)

Thanks for contributions from Tamás Gulácsi, sergeilem.

English User please mailto Liu Ming

This is a xls library writen in pure Golang. Almostly it is translated from the libxls library in c.

The master brunch has just the reading function without the format.

new_formater branch is for better format for date and number ,but just under test, you can try it in development environment. If you have some problem about the output format, tell me the problem, I will try to fix it.

Basic Usage

  • Use Open function for open file

  • Use OpenWithCloser function for open file and use the return value closer for close file

  • Use OpenReader function for open xls from a reader, you should close related file in your own code

  • Follow the example in GODOC

Documentation

Overview

xls package use to parse the 97 -2004 microsoft xls file(".xls" suffix, NOT ".xlsx" suffix )

there are some example in godoc, please follow them.

Index

Examples

Constants

View Source 
const MS_BIFF_CRYPTO_NONE = 0

Encryption type

View Source 
const MS_BIFF_CRYPTO_RC4 = 2
View Source 
const MS_BIFF_CRYPTO_XOR = 1
View Source 
const REKEY_BLOCK = 0x400

Size of stream blocks when using RC4 encryption

View Source 
const TYPE_BOOL = 5
View Source 
const TYPE_CURRENCY = 11
View Source 
const TYPE_DATETIME = 9
View Source 
const TYPE_ERROR = 8
View Source 
const TYPE_FORMULA = 3
View Source 
const TYPE_INLINE = 7
View Source 
const TYPE_NULL = 6
View Source 
const TYPE_NUMERIC = 4
View Source 
const TYPE_PERCENTAGE = 10
View Source 
const TYPE_STRING = 2
View Source 
const TYPE_STRING2 = 1

Data types

View Source 
const XLS_BIFF7 = 0x0500
View Source 
const XLS_BIFF8 = 0x0600

ParseXL definitions

View Source 
const XLS_Type_ARRAY = 0x0221
View Source 
const XLS_Type_BLANK = 0x0201
View Source 
const XLS_Type_BOF = 0x0809
View Source 
const XLS_Type_BOOLERR = 0x0205
View Source 
const XLS_Type_BOTTOMMARGIN = 0x0029
View Source 
const XLS_Type_CODEPAGE = 0x0042
View Source 
const XLS_Type_COLINFO = 0x007d
View Source 
const XLS_Type_CONTINUE = 0x003c
View Source 
const XLS_Type_DATAVALIDATION = 0x01be
View Source 
const XLS_Type_DATAVALIDATIONS = 0x01b2
View Source 
const XLS_Type_DATEMODE = 0x0022
View Source 
const XLS_Type_DBCELL = 0x00d7
View Source 
const XLS_Type_DEFAULTROWHEIGHT = 0x0225
View Source 
const XLS_Type_DEFCOLWIDTH = 0x0055
View Source 
const XLS_Type_DEFINEDNAME = 0x0018
View Source 
const XLS_Type_DIMENSION = 0x0200
View Source 
const XLS_Type_EOF = 0x000a
View Source 
const XLS_Type_EXTERNALBOOK = 0x01ae
View Source 
const XLS_Type_EXTERNNAME = 0x0023
View Source 
const XLS_Type_EXTERNSHEET = 0x0017
View Source 
const XLS_Type_EXTSST = 0x00ff
View Source 
const XLS_Type_FILEPASS = 0x002f
View Source 
const XLS_Type_FONT = 0x0031
View Source 
const XLS_Type_FOOTER = 0x0015
View Source 
const XLS_Type_FORMAT = 0x041e
View Source 
const XLS_Type_FORMULA = 0x0006

record identifiers

View Source 
const XLS_Type_HCENTER = 0x0083
View Source 
const XLS_Type_HEADER = 0x0014
View Source 
const XLS_Type_HORIZONTALPAGEBREAKS = 0x001b
View Source 
const XLS_Type_HYPERLINK = 0x01b8
View Source 
const XLS_Type_IMDATA = 0x007f
View Source 
const XLS_Type_INDEX = 0x020b
View Source 
const XLS_Type_LABEL = 0x0204
View Source 
const XLS_Type_LABELSST = 0x00fd
View Source 
const XLS_Type_LEFTMARGIN = 0x0026
View Source 
const XLS_Type_MERGEDCELLS = 0x00e5
View Source 
const XLS_Type_MSODRAWING = 0x00ec
View Source 
const XLS_Type_MSODRAWINGGROUP = 0x00eb
View Source 
const XLS_Type_MULBLANK = 0x00be
View Source 
const XLS_Type_MULRK = 0x00bd
View Source 
const XLS_Type_NOTE = 0x001c
View Source 
const XLS_Type_NUMBER = 0x0203
View Source 
const XLS_Type_OBJ = 0x005d
View Source 
const XLS_Type_OBJECTPROTECT = 0x0063
View Source 
const XLS_Type_PAGELAYOUTVIEW = 0x088b
View Source 
const XLS_Type_PAGESETUP = 0x00a1
View Source 
const XLS_Type_PALETTE = 0x0092
View Source 
const XLS_Type_PANE = 0x0041
View Source 
const XLS_Type_PASSWORD = 0x0013
View Source 
const XLS_Type_PRINTGRIDLINES = 0x002b
View Source 
const XLS_Type_PROTECT = 0x0012
View Source 
const XLS_Type_RANGEPROTECTION = 0x0868
View Source 
const XLS_Type_RIGHTMARGIN = 0x0027
View Source 
const XLS_Type_RK = 0x027e
View Source 
const XLS_Type_ROW = 0x0208
View Source 
const XLS_Type_SCENPROTECT = 0x00dd
View Source 
const XLS_Type_SCL = 0x00a0
View Source 
const XLS_Type_SELECTION = 0x001d
View Source 
const XLS_Type_SHAREDFMLA = 0x04bc
View Source 
const XLS_Type_SHEET = 0x0085
View Source 
const XLS_Type_SHEETLAYOUT = 0x0862
View Source 
const XLS_Type_SHEETPR = 0x0081
View Source 
const XLS_Type_SHEETPROTECTION = 0x0867
View Source 
const XLS_Type_SST = 0x00fc
View Source 
const XLS_Type_STRING = 0x0207
View Source 
const XLS_Type_STYLE = 0x0293
View Source 
const XLS_Type_TOPMARGIN = 0x0028
View Source 
const XLS_Type_TXO = 0x01b6
View Source 
const XLS_Type_UNKNOWN = 0xffff
View Source 
const XLS_Type_VCENTER = 0x0084
View Source 
const XLS_Type_VERTICALPAGEBREAKS = 0x001a
View Source 
const XLS_Type_WINDOW2 = 0x023e
View Source 
const XLS_Type_XF = 0x00e0
View Source 
const XLS_Type_XFEXT = 0x087d
View Source 
const XLS_WorkbookGlobals = 0x0005
View Source 
const XLS_Worksheet = 0x0010

Variables

View Source 
var ErrIsInt = errors.New("is int")

Functions

func ByteToUint16 added in v1.0.0 

func ByteToUint16(b []byte) uint16

ByteToUint16 Read 16-bit unsigned integer

func ByteToUint32 added in v1.0.0 

func ByteToUint32(b []byte) uint32

ByteToUint32 Read 32-bit unsigned integer

Types

type BlankCol 

type BlankCol struct {
	Col
	Xf uint16
}

func (*BlankCol) Debug added in v1.0.0 

func (c *BlankCol) Debug(wb *WorkBook)

func (*BlankCol) String 

func (c *BlankCol) String(wb *WorkBook) []string

type CellRange 

type CellRange struct {
	FirstRowB uint16
	LastRowB  uint16
	FristColB uint16
	LastColB  uint16
}

range type of multi cells in multi rows

func (*CellRange) FirstCol 

func (c *CellRange) FirstCol() uint16

func (*CellRange) FirstRow 

func (c *CellRange) FirstRow() uint16

func (*CellRange) LastCol 

func (c *CellRange) LastCol() uint16

func (*CellRange) LastRow 

func (c *CellRange) LastRow() uint16

type Col 

type Col struct {
	RowB      uint16
	FirstColB uint16
}

func (*Col) Debug added in v1.0.0 

func (c *Col) Debug(wb *WorkBook)

func (*Col) FirstCol 

func (c *Col) FirstCol() uint16

func (*Col) LastCol 

func (c *Col) LastCol() uint16

func (*Col) Row 

func (c *Col) Row() uint16

func (*Col) String 

func (c *Col) String(wb *WorkBook) []string

type Coler 

type Coler interface {
	Row() uint16
}

type ExtSheetInfo added in v1.0.0 

type ExtSheetInfo struct {
	ExternalBookIndex uint16
	FirstSheetIndex   uint16
	LastSheetIndex    uint16
}

ExtSheetInfo external sheet references provided for named cells

type Font 

type Font struct {
	Info *FontInfo
	Name string
}

type FontInfo 

type FontInfo struct {
	Height     uint16
	Flag       uint16
	Color      uint16
	Bold       uint16
	Escapement uint16
	Underline  byte
	Family     byte
	Charset    byte
	Notused    byte
	NameB      byte
}

type Format 

type Format struct {
	Head struct {
		Index uint16
		Size  uint16
	}
	Raw []string
	// contains filtered or unexported fields
}

Format value interface

func (*Format) Prepare added in v1.0.0 

func (f *Format) Prepare()

Prepare format meta data

func (*Format) String added in v1.0.0 

func (f *Format) String(v float64) string

String format content to spec string see http://www.openoffice.org/sc/excelfileformat.pdf Page #174

type FormulaCol 

type FormulaCol struct {
	Code   uint16
	Btl    uint16
	Btc    uint16
	Bts    []byte
	Header *FormulaColHeader
	// contains filtered or unexported fields
}

func (*FormulaCol) Debug added in v1.0.0 

func (c *FormulaCol) Debug(wb *WorkBook)

func (*FormulaCol) FirstCol added in v1.0.0 

func (c *FormulaCol) FirstCol() uint16

func (*FormulaCol) LastCol added in v1.0.0 

func (c *FormulaCol) LastCol() uint16

func (*FormulaCol) Row added in v1.0.0 

func (c *FormulaCol) Row() uint16

func (*FormulaCol) String 

func (c *FormulaCol) String(wb *WorkBook) []string

type FormulaColHeader added in v1.0.0 

type FormulaColHeader struct {
	Col
	IndexXf uint16
	Result  [8]byte
	Flags   uint16
	// contains filtered or unexported fields
}

func (*FormulaColHeader) IsPart added in v1.0.0 

func (f *FormulaColHeader) IsPart() bool

IsPart part of shared formula check WARNING: We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true the formula data may be ordinary formula data, therefore we need to check explicitly for the tExp token (0x01)

func (*FormulaColHeader) Value added in v1.0.0 

func (f *FormulaColHeader) Value() float64

Value formula header value 

type HyperLink struct {
	CellRange
	Description      string
	TextMark         string
	TargetFrame      string
	Url              string
	ShortedFilePath  string
	ExtendedFilePath string
	IsUrl            bool
}

hyperlink type's content

func (*HyperLink) Debug added in v1.0.0 

func (h *HyperLink) Debug(wb *WorkBook)

func (*HyperLink) String 

func (h *HyperLink) String(wb *WorkBook) []string

get the hyperlink string, use the public variable Url to get the original Url

type LabelsstCol 

type LabelsstCol struct {
	Col
	Xf  uint16
	Sst uint32
}

func (*LabelsstCol) Debug added in v1.0.0 

func (c *LabelsstCol) Debug(wb *WorkBook)

func (*LabelsstCol) String 

func (c *LabelsstCol) String(wb *WorkBook) []string

type MulBlankCol 

type MulBlankCol struct {
	Col
	Xfs      []uint16
	LastColB uint16
}

func (*MulBlankCol) Debug added in v1.0.0 

func (c *MulBlankCol) Debug(wb *WorkBook)

func (*MulBlankCol) LastCol 

func (c *MulBlankCol) LastCol() uint16

func (*MulBlankCol) String 

func (c *MulBlankCol) String(wb *WorkBook) []string

type MulrkCol 

type MulrkCol struct {
	Col
	Xfrks    []XfRk
	LastColB uint16
}

func (*MulrkCol) Debug added in v1.0.0 

func (c *MulrkCol) Debug(wb *WorkBook)

func (*MulrkCol) LastCol 

func (c *MulrkCol) LastCol() uint16

func (*MulrkCol) String 

func (c *MulrkCol) String(wb *WorkBook) []string

type NumberCol 

type NumberCol struct {
	Col
	Index uint16
	Float float64
}

func (*NumberCol) Debug added in v1.0.0 

func (c *NumberCol) Debug(wb *WorkBook)

func (*NumberCol) String 

func (c *NumberCol) String(wb *WorkBook) []string

type RK 

type RK uint32

func (RK) Debug added in v1.0.0 

func (rk RK) Debug(wb *WorkBook)

func (RK) String 

func (rk RK) String(wb *WorkBook) string

type Ranger 

type Ranger interface {
	FirstRow() uint16
	LastRow() uint16
}

range type of multi rows

type RkCol 

type RkCol struct {
	Col
	Xfrk XfRk
}

func (*RkCol) Debug added in v1.0.0 

func (c *RkCol) Debug(wb *WorkBook)

func (*RkCol) String 

func (c *RkCol) String(wb *WorkBook) []string

type Row 

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

Row the data of one row

func (*Row) Col 

func (r *Row) Col(i int) string

Col Get the Nth Col from the Row, if has not, return nil. Suggest use Has function to test it.

func (*Row) FirstCol 

func (r *Row) FirstCol() int

FirstCol Get the number of First Col of the Row.

func (*Row) LastCol 

func (r *Row) LastCol() int

LastCol Get the number of Last Col of the Row.

type SstInfo 

type SstInfo struct {
	Total uint32
	Count uint32
}

type WorkBook 

type WorkBook struct {
	Debug    bool
	Is5ver   bool
	Type     uint16
	Codepage uint16
	Xfs      []XF
	Fonts    []Font
	Formats  map[uint16]*Format

	Author string
	// contains filtered or unexported fields
}

WorkBook excel work book

func Open 

func Open(file string, charset string) (*WorkBook, error)

Open one xls file

Example 
if xlFile, err := Open("Table.xls", "utf-8"); err == nil {
	fmt.Println(xlFile.Author)
}

Output:

func OpenReader 

func OpenReader(reader io.ReadSeeker, charset string) (wb *WorkBook, err error)

OpenReader open xls file from reader

func OpenWithBuffer added in v1.0.0 

func OpenWithBuffer(file string, charset string) (*WorkBook, error)

OpenWithBuffer open one xls file with memory buffer

func OpenWithCloser added in v1.0.0 

func OpenWithCloser(file string, charset string) (*WorkBook, io.Closer, error)

OpenWithCloser open one xls file and return the closer

func (*WorkBook) Format added in v1.0.0 

func (w *WorkBook) Format(xf uint16, v float64) (string, bool)

Format format value to string

func (*WorkBook) GetSheet 

func (w *WorkBook) GetSheet(num int) *WorkSheet

GetSheet get one sheet by its number

Example

Output: read the content of first two cols in each row 

if xlFile, err := Open("Table.xls", "utf-8"); err == nil {
	if sheet1 := xlFile.GetSheet(0); sheet1 != nil {
		fmt.Print("Total Lines ", sheet1.MaxRow, sheet1.Name)
		col1 := sheet1.Row(0).Col(0)
		col2 := sheet1.Row(0).Col(0)
		for i := 0; i <= (int(sheet1.MaxRow)); i++ {
			row1 := sheet1.Row(i)
			col1 = row1.Col(0)
			col2 = row1.Col(1)
			fmt.Print("\n", col1, ",", col2)
		}
	}
}

Output:

func (*WorkBook) NumSheets 

func (w *WorkBook) NumSheets() int

NumSheets Get the number of all sheets, look into example

func (*WorkBook) ReadAllCells 

func (w *WorkBook) ReadAllCells(max int) (res [][]string)

ReadAllCells helper function to read all cells from file Notice: the max value is the limit of the max capacity of lines. Warning: the helper function will need big memory if file is large.

func (*WorkBook) SetDebug added in v1.0.0 

func (w *WorkBook) SetDebug(debug bool)

SetDebug set debug flag

type WorkSheet 

type WorkSheet struct {
	Name string

	//NOTICE: this is the max row number of the sheet, so it should be count -1
	MaxRow uint16
	// contains filtered or unexported fields
}

WorkSheet in one WorkBook

func (*WorkSheet) Row 

func (w *WorkSheet) Row(i int) *Row

type XF added in v1.0.0 

type XF interface {
	FormatNo() uint16
}

type Xf5 

type Xf5 struct {
	Font      uint16
	Format    uint16
	Type      uint16
	Align     uint16
	Color     uint16
	Fill      uint16
	Border    uint16
	Linestyle uint16
}

func (*Xf5) FormatNo added in v1.0.0 

func (x *Xf5) FormatNo() uint16

type Xf8 

type Xf8 struct {
	Font        uint16
	Format      uint16
	Type        uint16
	Align       byte
	Rotation    byte
	Ident       byte
	Usedattr    byte
	Linestyle   uint32
	Linecolor   uint32
	Groundcolor uint16
}

func (*Xf8) FormatNo added in v1.0.0 

func (x *Xf8) FormatNo() uint16

type XfRk 

type XfRk struct {
	Index uint16
	Rk    RK
}

func (*XfRk) Debug added in v1.0.0 

func (xf *XfRk) Debug(wb *WorkBook)

func (*XfRk) String 

func (xf *XfRk) String(wb *WorkBook) string

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.