Go处理Excel

摘要

Go处理Excel,csv的几种包的使用方法

tealeg/xlsx

https://github.com/tealeg/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
package main

import (
"fmt"

"github.com/tealeg/xlsx"
)

func main() {
excelFileName := "test.xlsx"
xlFile, err := xlsx.OpenFile(excelFileName)
if err != nil {
fmt.Printf("open failed: %s\n", err)
}
for _, sheet := range xlFile.Sheets {
fmt.Printf("Sheet Name: %s\n", sheet.Name)
for _, row := range sheet.Rows {
for _, cell := range row.Cells {
text := cell.String()
fmt.Printf("%s\n", text)
}
}
}
}

创建

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
39
40
41
42
43
44
45
46
package main

import (
"fmt"

"github.com/tealeg/xlsx"
)

func main() {
var file *xlsx.File
var sheet *xlsx.Sheet
var row, row1, row2 *xlsx.Row
var cell *xlsx.Cell
var err error

file = xlsx.NewFile()
sheet, err = file.AddSheet("Sheet1")
if err != nil {
fmt.Printf(err.Error())
}
row = sheet.AddRow()
row.SetHeightCM(1)
cell = row.AddCell()
cell.Value = "姓名"
cell = row.AddCell()
cell.Value = "年龄"

row1 = sheet.AddRow()
row1.SetHeightCM(1)
cell = row1.AddCell()
cell.Value = "狗子"
cell = row1.AddCell()
cell.Value = "18"

row2 = sheet.AddRow()
row2.SetHeightCM(1)
cell = row2.AddCell()
cell.Value = "蛋子"
cell = row2.AddCell()
cell.Value = "28"

err = file.Save("test_write.xlsx")
if err != nil {
fmt.Printf(err.Error())
}
}

修改

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
package main

import (
"github.com/tealeg/xlsx"
)

func main() {
excelFileName := "test.xlsx"
xlFile, err := xlsx.OpenFile(excelFileName)
if err != nil {
panic(err)
}
first := xlFile.Sheets[0]
row := first.AddRow()
row.SetHeightCM(1)
cell := row.AddCell()
cell.Value = "铁锤"
cell = row.AddCell()
cell.Value = "99"

err = xlFile.Save(excelFileName)
if err != nil {
panic(err)
}
}

Luxurioust/excelize

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
package main

import (
"fmt"
"github.com/xuri/excelize"
)

func nxlsx (excelFileName string) (all_rows []RowType) {
xlsx, err := excelize.OpenFile(excelFileName)
if err != nil {
fmt.Println(err)
}
cell := xlsx.GetCellValue("Sheet1", "B2")
fmt.Println(cell)

rows := xlsx.GetRows("Sheet")
for index, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
if index == 0 {continue}
all_rows = append(all_rows, RowType{
row[0],row[1],
row[2],row[3],row[4],
row[5], row[6],row[7], 0,
})
}
return all_rows
}


func main() {
var excelFileName string = "DXM质量部(BPIT).xlsx"
all_data := nxlsx(excelFileName)
fmt.Println("all_data: ", len(all_data))
}

创建

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

import (
"fmt"

"github.com/xuri/excelize"
)

func main() {
xlsx := excelize.NewFile()

index := xlsx.NewSheet("Sheet1")
xlsx.SetCellValue("Sheet1", "A1", "姓名")
xlsx.SetCellValue("Sheet1", "B1", "年龄")
xlsx.SetCellValue("Sheet1", "A2", "狗子")
xlsx.SetCellValue("Sheet1", "B2", "18")
// Set active sheet of the workbook.
xlsx.SetActiveSheet(index)
// Save xlsx file by the given path.
err := xlsx.SaveAs("test_write.xlsx")
if err != nil {
fmt.Println(err)
}
}

插入图表

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
package main

import (
"fmt"

"github.com/xuri/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}
xlsx := excelize.NewFile()
for k, v := range categories {
xlsx.SetCellValue("Sheet1", k, v)
}
for k, v := range values {
xlsx.SetCellValue("Sheet1", k, v)
}
xlsx.AddChart("Sheet1", "E1", `{"type":"bar3D","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 Line Chart"}}`)
// Save xlsx file by the given path.
err := xlsx.SaveAs("test_write.xlsx")
if err != nil {
fmt.Println(err)
}
}

插入图片

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/xuri/excelize"
)

func main() {
xlsx, err := excelize.OpenFile("test.xlsx")
if err != nil {
fmt.Println(err)
return
}
// Insert a picture.
err = xlsx.AddPicture("Sheet1", "A2", "image1.png", "")
if err != nil {
fmt.Println(err)
}
// Insert a picture to worksheet with scaling.
err = xlsx.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 = xlsx.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 = xlsx.Save()
if err != nil {
fmt.Println(err)
}
}

修改

zl

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
39
func XLSX(excelFileName string) [2048]RowType {
xlFile, err := xlsx.OpenFile(excelFileName)
var all_rows [2048]RowType

if err != nil {
fmt.Printf("open failed: %s\n", err)
}
//key := []string{}
for _, sheet := range xlFile.Sheets {
//fmt.Printf("Sheet Name: %s\n", sheet.Name)
fmt.Println(sheet.MaxRow)
fmt.Println(reflect.TypeOf(sheet.MaxRow))
for i := 0; i < sheet.MaxRow; i++ {
if i == 0 {continue}
key := sheet.Row(i).Cells
all_rows[i] = RowType{
key[0].String(),key[1].String(),
key[2].String(),key[3].String(),
key[4].String(),key[5].String(),
key[6].String(),key[7].String(), 0}
fmt.Println(all_rows)
fmt.Println(i)
}
//os.Exit(5)
//for rowindex, row := range sheet.Rows {
// //fmt.Println(r)
// if rowindex == 0 {
// continue
// }
// for _, cell := range row.Cells {
// key = append(key,cell.String())
// //text := cell.String()
// //fmt.Printf("%s\n", text)
// //os.Exit(1)
// }
}
fmt.Println(all_rows[0].HOST)
return all_rows
}

生成csv

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
package main

import (
"encoding/csv"
"os"
)

func main() {
f, err := os.Create("test.csv")//创建文件
if err != nil {
panic(err)
}
defer f.Close()

f.WriteString("\xEF\xBB\xBF") // 写入UTF-8 BOM

w := csv.NewWriter(f)//创建一个新的写入文件流
data := [][]string{
{"1", "中国", "23"},
{"2", "美国", "23"},
{"3", "bb", "23"},
{"4", "bb", "23"},
{"5", "bb", "23"},
}
w.WriteAll(data)//写入数据
w.Flush()
}