golang中操作excel(tealeg/xlsx、360EntSecGroup-Skylar/excelize)

原文链接：https://blog.youkuaiyun.com/wangshubo1989/article/details/78181493

xlsx简介

A file with the XLSX file extension is a Microsoft Excel Open XML Format Spreadsheet file. It’s an XML-based spreadsheet file created by Microsoft Excel version 2007 and later.

XLSX files organize data in cells that are stored in worksheets, which are in turn stored in workbooks, which are files that contain multiple worksheets. The cells are positioned by rows and columns and can contain styles, formatting, math functions, and more.

Microsoft Office EXCEL 2007/2010/2013/2016文档的扩展名。xlsx是从Office2007开始使用的，是用新的基于XML的压缩文件格式取代了其目前专有的默认文件格式，在传统的文件名扩展名后面添加了字母x（即：docx取代doc、.xlsx取代xls等等），使其占用空间更小。

tealeg/xlsx

github地址：
https://github.com/tealeg/xlsx

Star: 1954

获取：
go get github.com/tealeg/xlsx

有点excel基础的人，都应该清楚什么是sheet，什么是row，cell即内容。

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)
            }
        }
    }
}

创建xlsx

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())
    }
}

修改xlsx

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或360EntSecGroup-Skylar/excelize## 标题

github地址：
https://github.com/360EntSecGroup-Skylar/excelize

Star: 1476

获取：
go get github.com/xuri/excelize

## 读取xlsx

package main

import (
“fmt”

"github.com/xuri/excelize"

)

func main() {
xlsx, err := excelize.OpenFile(“test.xlsx”)
if err != nil {
fmt.Println(err)
return
}
cell := xlsx.GetCellValue(“Sheet1”, “B2”)
fmt.Println(cell)

rows := xlsx.GetRows("Sheet1")
for _, row := range rows {
    for _, colCell := range row {
        fmt.Print(colCell, "\t")
    }
    fmt.Println()
}

创建xlsx

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)
    }
}
## 插入图表

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)
}
}

插入图片

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)
    }
}