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 条评论
登录 后参与评论

相关文章

来自专栏电光石火

Markdown 语法手册 (完整整理版)

1. 斜体和粗体 代码: *斜体*或_斜体_ **粗体** ***加粗斜体*** ~~删除线~~ 显示效果: ...

91110
来自专栏程序员宝库

Javascript 将 HTML 页面生成 PDF 并下载

最近碰到个需求,需要把当前页面生成 pdf,并下载。弄了几天,自己整理整理,记录下来,我觉得应该会有人需要 :)

1483
来自专栏MasiMaro 的技术博文

菜单的使用

2)弹出式菜单:一般在顶级菜单上都有很多菜单项,单击这些菜单项时会弹出一个下拉式的菜单项,我们点击的这个菜单称为弹出式菜单

1174
来自专栏互联网杂技

Javascript 将 HTML 页面生成 PDF 并下载

最近碰到个需求,需要把当前页面生成 pdf,并下载。弄了几天,自己整理整理,记录下来,我觉得应该会有人需要 :)

2642
来自专栏Windows Community

Windows Community Toolkit 3.0 - InfiniteCanvas

InfiniteCanvas 是一个 Canvas 控件,它支持无限画布的滚动,支持 Ink,文本,格式文本,画布缩放操作,撤销重做操作,导入和导出数据。

773
来自专栏GIS讲堂

Arcgis for js加载天地图

天地图的切片地图可以通过esri.layers.TiledMapServiceLayer来加载,在此将之进行了一定的封装,如下:

5542
来自专栏守候书阁

[边学边练]用简单实例学习React

学习之路不可停止,最近在玩 React。也动手尝试写了一个实例。借此整理总结下初步学习 React 的一些基础知识。因为这几天比较忙,没那么多时间,所以实例和文...

1506
来自专栏Web项目聚集地

Javascript将HTML转成PDF并下载「支持多页」

由于html2canvas只能将它能处理的生成canvas image,因此渲染出来的结果并不是100%与原来一致。但它不需要服务器参与,整个图片都由客户端浏览...

3452
来自专栏云端架构

【云端架构】前端js键盘绑定事件

在Web开发中,有时候我们可能会把页面中的某些按钮绑定到键盘的输入事件中。以下代码能方便实现我们需要的功能。

37910
来自专栏向治洪

React Native之ViewPagerAndroid 组件

概述 今天我们来讲解一下关于 ViewPager 的使用,它是一个允许子视图左右滚动翻页的容器。我们知道在Android开发中系统有ViewPager这个组件,...

2108

扫码关注云+社区

领取腾讯云代金券