原创

golang中操作excel

生命不止,继续 go go go !!!

##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即内容。

####读取xlsx

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

读取结果:

Sheet Name: Sheet1

姓名

年龄

狗子

18

蛋子

28

####创建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()
	}
}

输出:

18

姓名 年龄

狗子 18

蛋子 28

大便 99

####创建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)
	}
}

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Go实战--golang中使用RethinkDB(gorethink/gorethink.v3)

    生命不止,继续go go go !!! 关于golang中操作数据库,曾经介绍了不少: Go实战–go语言操作sqlite数据库(The way to go) ...

    程序员的酒和故事
  • 通过httprouter和redis框架搭建restful api服务

    HttpRouter is a lightweight high performance HTTP request router (also called mu...

    程序员的酒和故事
  • 创业公司如何画饼???

    何为画饼 “画饼充饥”画个饼来解除饥饿。比喻用空想来安慰自己。特别是用来欺骗别人。 出处:选举莫取有名,如画地做饼,不可啖也。 ? 画一张虚无缥缈的饼 大多数...

    程序员的酒和故事
  • Go 语言读写 Excel 文档

    Excelize 是 Go 语言编写的一个用来操作 Office Excel 文档类库,基于 ECMA-376 Office OpenXML 标准。可以使用它来...

    xuri
  • Golang是如何操作excel的?

    为了方便开源库的快速上手,我们先来了解 excel 中的几个关键术语,如下图所示,①为sheet,也就是表格中的页签;②为row,代表 excel 中的一行;③...

    平也
  • GraphQL学习第四篇 -在Koa中使用GraphQL

    越陌度阡
  • go Http Post 发送文件流

    水滴石穿。这里把Go Http Post 参数的函数也贴了处理主要对比两者不同之处。

    地球流浪猫
  • celery时差问题解决方法

    请记得点赞和分享这篇文章让更多的人看到它!另外,记得关注我的简书号马哥学Python,这样你就不会错过任何有价值的文章!

    马哥Python
  • Web安全学习笔记(十):PHP基础(下) 之 连接数据库

    把这个总结完,对于学习的基础也算完事了,剩下的我就要将实战中遇到的多多分享了,也希望多结识一些志同道合,聊得来的朋友。

    7089bAt@PowerLi
  • Web前端面试宝典(最新)

    html语义化让页面的内容结构化,结构更清晰,便于对浏览器、搜索引擎解析;即使在没有样式CSS情况下也以一种文档格式显示,并且是容易阅读的;

    半指温柔乐

扫码关注云+社区

领取腾讯云代金券