golang使用xlsx操纵excel浅析

欢迎直接访问我的博客：http://www.yqun.xyz:1313

Introduction

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

Installation

使用go get

go get github.com/tealeg/xlsx

# 需要注意的是，这里安装依赖于GOPATH这个环境变量。

直接git clone手动安装

git clone https://github.com/tealeg/xlsx.git

# 然后把xlsx放到$GOPATH/src/github.com/目录下就ok了

我在使用go gget安装的时候失败了，错误信息如下：

package golang.org/x/xerrors: unrecognized import path “golang.org/x/xerrors” (https fetch: Get https://golang.org/x/xerrors?go-get=1: dial tcp 216.239.37.1:443: connectex: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)

原因就是没有golang.org/x/xerrors没有安装

然后我使用go get安装xerrors也失败了，我就直接git手动安装了。

git clone https://github.com/golang/xerrors.git

# 然后将xerrors放到$GOPATH/src/golang.org/x/目录下就ok了。

---

How to use

xlsx.File

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(bs []byte, options …FileOption) (*File, error)

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

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(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(f *zip.ReadCloser, options …FileOption) (*File, error)

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

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

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

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: : \ / ? * [ ]

AddSheet将具有提供的名称的新工作表添加到文件。 工作表名称的最小长度为1个字符。 如果工作表名称长度短，则会引发错误。 工作表名称的最大长度为31个字符。 如果超过工作表名称的长度，则会引发错误。 也不允许使用这些特殊字符：：\ /？ * []

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

使用用户提供的Cell的构造器

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

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

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

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

根据XLSX文件的结构，构造一个文件名到表示该文件的XML内容的映射。

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

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

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 (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 (f *File) Write(writer io.Writer) (err error)

Write the File to io.Writer as xlsx

FileOption

type FileOption func(f *File)

xlsx.Sheet

Defination

type Sheet struct {
   
    Name            string
    File            *File
    Cols            *ColStore
    MaxRow          int
    MaxCol          int
    Hidden          bool
    Selected        bool
    SheetViews      []SheetView
    SheetFormat     SheetFormat
    AutoFilter      *AutoFilter
    Relatio