Excelize - 用于读写XLSX文件的Golang库

Golang读写xlsx

Posted by 张嘉伟 on 2019-12-20        本文总阅读量

介绍

Excelize是一个使用纯Go编写的库,它提供一组函数,允许使用者写入和读取XLSX文件。支持读取和写入由Microsoft Excel生成的XLSX文件(2007版本及以后)。支持保存文件而不丢失XLSX的原始图表。此库需要Go版本大于等于1.10版本。

使用方法

安装excelize包

1
go get github.com/360EntSecGroup-Skylar/excelize

创建xlsx文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package main

import (
"fmt"

"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
f := excelize.NewFile()
// Create a new sheet.
index := f.NewSheet("Sheet2")
// Set value of a cell.
f.SetCellValue("Sheet2", "A2", "Hello world.")
f.SetCellValue("Sheet1", "B2", 100)
// Set active sheet of the workbook.
f.SetActiveSheet(index)
// Save xlsx file by the given path.
err := f.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}

读取xlsx文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
package main

import (
"fmt"

"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
f, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Get value from cell by given worksheet name and axis.
cell, err := f.GetCellValue("Sheet1", "B2")
if err != nil {
fmt.Println(err)
return
}
fmt.Println(cell)
// Get all the rows in the Sheet1.
rows, err := f.GetRows("Sheet1")
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
}

添加图表到xlsx文件

结果图:

代码如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package main

import (
"fmt"

"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
categories := map[string]string{"A2": "Small", "A3": "Normal", "A4": "Large", "B1": "Apple", "C1": "Orange", "D1": "Pear"}
values := map[string]int{"B2": 2, "C2": 3, "D2": 3, "B3": 5, "C3": 2, "D3": 4, "B4": 6, "C4": 7, "D4": 8}
f := excelize.NewFile()
for k, v := range categories {
f.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
f.SetCellValue("Sheet1", k, v)
}
err := f.AddChart("Sheet1", "E1", `{"type":"col3DClustered","series":[{"name":"Sheet1!$A$2","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$2:$D$2"},{"name":"Sheet1!$A$3","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$3:$D$3"},{"name":"Sheet1!$A$4","categories":"Sheet1!$B$1:$D$1","values":"Sheet1!$B$4:$D$4"}],"title":{"name":"Fruit 3D Clustered Column Chart"}}`)
if err != nil {
fmt.Println(err)
return
}
// Save xlsx file by the given path.
err = f.SaveAs("./Book1.xlsx")
if err != nil {
fmt.Println(err)
}
}

添加图片到xlsx文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package main

import (
"fmt"
_ "image/gif"
_ "image/jpeg"
_ "image/png"

"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
f, err := excelize.OpenFile("./Book1.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Insert a picture.
err = f.AddPicture("Sheet1", "A2", "./image1.png", "")
if err != nil {
fmt.Println(err)
}
// Insert a picture to worksheet with scaling.
err = f.AddPicture("Sheet1", "D2", "./image2.jpg", `{"x_scale": 0.5, "y_scale": 0.5}`)
if err != nil {
fmt.Println(err)
}
// Insert a picture offset in the cell with printing support.
err = f.AddPicture("Sheet1", "H2", "./image3.gif", `{"x_offset": 15, "y_offset": 10, "print_obj": true, "lock_aspect_ratio": false, "locked": false}`)
if err != nil {
fmt.Println(err)
}
// Save the xlsx file with the origin path.
err = f.Save()
if err != nil {
fmt.Println(err)
}
}

项目地址

GitHub:https://github.com/360EntSecGroup-Skylar/excelize