execl数据的写入读取在业务中常有的事情，当然一般数据量实际并不会很大，但也还是存在该特殊情况写入数据有100w或者更多，具体业务场景不做分析，本文以实现功能为目标会基于go通过协程与流实现生成或读取100w数据量的execl文件。
用例代码地址：gitee.com/dn-jinmin/s…
前言
在项目中execl导入导出的场景较多，因此抽一些时间对该功能进行总结，通过该案例你可以了解到如下内容：
- 协程的运用
- 基于协程的execl读写
- 缓存于流的区别
1. 准备
首先你需要先下载excelize包,建议用v2系列的版本
go get github.com/360EntSecGroup-Skylar/excelize/v2@v2.3.0
数据对象：本文采取的数据对象为user(name,phone,email,age,other)
type User struct {
name string
phone string
email string
age int
other string
}
2. 基础：读写
先简要的提供一下关于go对execl的读写示例
写: 使用
excelize库来写入 Excel 文件。以下是一个简单的示例代码：
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize/v2"
)
type Person struct {
Name string
Phone string
Email string
Age int
Other string
}
func main() {
execlPath := "test.xlsx"
persons := []Person{
{Name: "Tom", Phone: "13888888888", Email: "tom@example.com", Age: 20, Other: "other"},
{Name: "Jerry", Phone: "13999999999", Email: "jerry@example.com", Age: 22, Other: "other"},
}
// 新建一个工作簿（excel 文件）
file := excelize.NewFile()
// 新建一个 Sheet，命名为 Sheet1
sheetName := "Sheet1"
sheet := file.NewSheet(sheetName)
// 写入字段属性行，从 A1 开始
file.SetCellValue(sheetName, "A1", "name")
file.SetCellValue(sheetName, "B1", "phone")
file.SetCellValue(sheetName, "C1", "email")
file.SetCellValue(sheetName, "D1", "age")
file.SetCellValue(sheetName, "E1", "other")
// 写入数据行
rowIndex := 2 // 第二行开始
for _, person := range persons {
file.SetCellValue(sheetName, "A"+fmt.Sprint(rowIndex), person.Name)
file.SetCellValue(sheetName, "B"+fmt.Sprint(rowIndex), person.Phone)
file.SetCellValue(sheetName, "C"+fmt.Sprint(rowIndex), person.Email)
file.SetCellValue(sheetName, "D"+fmt.Sprint(rowIndex), person.Age)
file.SetCellValue(sheetName, "E"+fmt.Sprint(rowIndex), person.Other)
rowIndex++
}
// 设置生成的 Excel 文件的默认 Sheet
file.SetActiveSheet(sheet)
// 保存 Excel 文件
if err := file.SaveAs(execlPath); err != nil {
panic(err)
}
}
示例代码中，通过定义一个 Person 结构体，然后定义一个 person slice 来存储数据，最后通过 excelize 库来读取结构体数据并写入到 Excel 文件。
读：使用
excelize第三方库来读取 excel 文件。以下为一个简单的示例：
package main
import (
"fmt"
"github.com/360EntSecGroup-Skylar/excelize/v2"
)
func main() {
execlPath := "test.xlsx"
// 打开 excel 文件
file, err := excelize.OpenFile(execlPath)
if err != nil {
fmt.Println(err)
return
}
// 读取 Sheet1 中的 A1 单元格
cellA1, err := file.GetCellValue("Sheet1", "A1")
fmt.Println(cellA1, err)
// 读取 Sheet1 中所有单元格
rows, err := file.GetRows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for _, row := range rows {
for _, colCell := range row {
fmt.Print(colCell, "\t")
}
fmt.Println()
}
}
在示例中，我们首先打开一个名为 test.xlsx 的 Excel 文件，然后分别读的 A1 单元格和 Sheet1 中的所有单元格，最后输出到控制台
采用流式写入器：
package main
import (
"fmt"
"strconv"
"github.com/360EntSecGroup-Skylar/excelize/v2"
)
type Person struct {
Name string
Phone string
Email string
Age int
Other string
}
func main() {
execlPath := "test.xlsx"
persons := []Person{
{Name: "Tom", Phone: "138888", Email: "tom@example.com", Age: 20, Other: "other"},
{Name: "Jerry", Phone: "13999999999", Email: "jerry@example.com", Age: 22, Other: "other"},
}
// 创建一个新的空工
file := excelize.NewFile()
// 新建一个 Sheet，命名为 Sheet1
sheetName := "Sheet1"
streamWriter, err := file.NewStreamWriter(sheetName)
if err != nil {
fmt.Println(err)
return
}
// 写入字段属性行，从 A1 开始
if err := streamWriter.SetRow("A1", []interface{}{"name", "phone", "email", "age", "other"}); err != nil {
panic(err)
}
rows := 2
// 写入数据行
for _, person := range persons {
if err := streamWriter.SetRow("A"+strconv.Itoa(rows), []interface{}{person.Name, person.Phone, person.Email, person.Age, person.Other}); err != nil {
panic(err)
}
rows++
}
// 设置生成的 Excel 文件的默认 Sheet
if err := streamWriter.Flush(); err != nil {
panic(err)
}
// 保存 Excel 文件
if err := file.SaveAs(execlPath); err != nil {
panic(err)
}
}
示例代码中，通过采用流式写入器 streamWriter，一次性写入整行数据，可以避免多次调用 SetCellValue 方法造成的性能瓶颈。可以有效提升写入效率，特别是在大量数据写入的场景下.但是要注意flush方法需要写在最后，如果多次调用会丢失之前的数据。
3. 流式 与 缓存区别
在excelize提供了两种方式对execl数据的写入，SetCallValue与streamWriter两种方式。
在上方的代码中，使用f.SetCellValue是将修改后的数据写入到Excel文件的缓存中，并没有直接写入磁盘。 实际上，在使用f.SaveAs方法将Excel文件保存到磁盘之前，所有的修改操作都是在Excel文件的缓存中进行的。当调用f.SaveAs方法时，才会将缓的数据写入到磁盘中。 另外，需要注意的是，当写入大量的数据时，缓存的大小可能会超过系统内存限制，导致程序运行变慢或崩溃。
而流式写入器，则采用批量写入整行数据的方式，可以在性能上进行优化，特别是在需要写入大量数据时，使用流式写入器效率会更高。需要注意的式采取流式写入器flush需至于最后执行，否则会存在数据丢失的问题。
它们之间的区别点：
-
内存占用
- 使用SetCellValue方法时，我们需要将所有要写入的数据先保存到缓存中，然后一次性地将缓存的数据写入excel文件。因此，当写入的数据量较大的时候会占用较多的内存。
- 而使用streamWriter方法时，是将每一行数据依次写入Excel文件，不需要将所有的数据先保存到缓存中，因此占用的内存较少。
-
执行效率
- 由于SetCellValue方法需要将所有的数据保存到缓存中，再将数据一次性写入Excel文件，因此执行效率较低。
- streamWriter方法是将每一行数据一次写入excel文件，因此效率更高，特别是写入大量数据的时候。
-
对excel文件的影响访问
- SetCellValue方法，是直到我们调用SaveAs方法才会将缓存中的数据保存到excel文件中
- streamWriter方法是每次写入数据都会立即反映在excel文件中。
4. 向excel写入100w数据
基于SetCellValue实现
如下是基于SetCellValue实现的例子，在例子中会存在几个问题需根据自己的实际场景修改
需注意：
- 在利用协程的时候注意任务的分配即每个协程处理的数据量
- 需注意利用协程的数量需要可控
- 需注意所有协程全部执行完成
在代码中每个goroutine需要创建大量的数据对象，导致内存占用过多，而go语音的GC不能及时回收这些数据，因此在代码中还做了如下操作以减少内存占用：
- 实现goroutine池控制并发数量，避免同事过多的创建大量数据对象导致内存过多。
- 将大量数据写入excel文件时，避免一次性将所有数据写入内存中，采用分批次写入的方式。
package main
import (
"fmt"
"strconv"
"sync"
"github.com/360EntSecGroup-Skylar/excelize/v2"
"go.uber.org/atomic"
"gitee.com/dn-jinmin/gen-id/generator"
"gitee.com/dn-jinmin/gen-id/utils"
)
type User struct {
name string
phone string
email string
age int
other string
}
type ExcelData struct {
rowIndex *atomic.Int32
users []*User
}
var genid = new(generator.GeneratorData)
const (
batchSize = 10000
goroutine = 100 // 并发goroutine数
total = 1000000
)
func createUser(id int) *User {
return &User{
name: genid.GeneratorName(),
phone: genid.GeneratorPhone(),
email: genid.GeneratorEmail(),
age: utils.RUint(100),
other: "other info for user " + strconv.Itoa(id),
}
}
func writeExcelFile(filePath string) {
f := excelize.NewFile()
index := f.NewSheet("Sheet1")
f.SetCellValue("Sheet1", "A1", "Name")
f.SetCellValue("Sheet1", "B1", "Phone")
f.SetCellValue("Sheet1", "C1", "Email")
f.SetCellValue("Sheet1", "D1", "Age")
f.SetCellValue("Sheet1", "E1", "Other")
var wg sync.WaitGroup
ch := make(chan int, goroutine) // 使用制并发goroutine数
// 创建并发的协程数量
go func() {
for i := 0; i < goroutine; i++ {
ch <- i
}
}()
for i := 0; i < batchSize*goroutine && i < total; i += batchSize {
data := &ExcelData{
rowIndex: atomic.NewInt32(int32(i + 2)), // Excel行号从2开始
users: make([]*User, batchSize),
}
// 创建用户数据
for j := 0; j < batchSize; j++ {
data.users[j] = createUser(i + j)
}
chunkSize := batchSize
chunkSize = minInt(chunkSize, batchSize)
chunks := makeChunks(data.users, chunkSize)
for id, chunk := range chunks {
idx := <-ch // 获取一个goroutine
wg.Add(1)
go func(users []*User) {
defer func() {
wg.Done()
ch <- idx // 归还goroutine
}()
for _, user := range users {
if user == nil {
continue
}
rowIndex := int(data.rowIndex.Load())
// 通过f.SetCellValue方法将数据写入到缓存中
f.SetCellValue("Sheet1", "A"+strconv.Itoa(rowIndex), user.name)
f.SetCellValue("Sheet1", "B"+strconv.Itoa(rowIndex), user.phone)
f.SetCellValue("Sheet1", "C"+strconv.Itoa(rowIndex), user.email)
f.SetCellValue("Sheet1", "D"+strconv.Itoa(rowIndex), user.age)
f.SetCellValue("Sheet1", "E"+strconv.Itoa(rowIndex), user.other)
data.rowIndex.Add(1)
}
}(chunk)
}
wg.Wait()
for _, user := range data.users {
if user != nil {
user = nil
}
}
fmt.Printf("processed [%d - %d] users\n", i+1, i+batchSize)
}
f.SetActiveSheet(index)
if err := f.SaveAs(filePath); err != nil {
fmt.Println(err)
}
}
func makeChunks(users []*User, chunkSize int) [][]*User {
var chunks [][]*User
for i := 0; i < len(users); i += chunkSize {
end := minInt(i+chunkSize, len(users))
chunks = append(chunks, users[i:end])
}
return chunks
}
func minInt(x, y int) int {
if x < y {
return x
}
return y
}
func main() {
writeExcelFile("user.xlsx")
}
在程序中关于中batchSize定义为是每次任务的数据量，goroutine则是处理的任务协程数量。
// 创建并发的协程数量
go func() {
for i := 0; i < goroutine; i++ {
ch <- i
}
}()
创建并发的协程数量，在创建的协程池后则就是具体的任务处理核心代码，根据数据的批次处理量创建对应的用户数量，同事，更好地利用CPU资源，我们可以将数据拆分成为多个小区间进行并发写入。在批次任务执行完成之后对生成的用户数据进行释放，释放的方式是重置为nil这样以便于go的GC能够及时释放内存。
基于streamWriter实现
在技术上的实现于SetCellValue是有区别
package main
import (
"fmt"
"strconv"
"github.com/360EntSecGroup-Skylar/excelize/v2"
"go.uber.org/atomic"
"gitee.com/dn-jinmin/gen-id/generator"
"gitee.com/dn-jinmin/gen-id/utils"
)
type User struct {
name string
phone string
email string
age int
other string
}
type ExcelData struct {
rowIndex *atomic.Int32
}
var genid = new(generator.GeneratorData)
const (
batchSize = 10000
total = 1000000
)
func createUser(id int) *User {
return &User{
name: genid.GeneratorName(),
phone: genid.GeneratorPhone(),
email: genid.GeneratorEmail(),
age: utils.RUint(100),
other: "other info for user " + strconv.Itoa(id),
}
}
func writeExcelFile(filePath string) {
sheet := "Sheet1"
f := excelize.NewFile()
// 创建流式写入器
sw, err := f.NewStreamWriter(sheet)
if err != nil {
fmt.Println(err)
return
}
index := f.NewSheet(sheet)
sw.SetRow("A1", []interface{}{
"Name", "Phone", "Email", "Age", "Other",
})
// 写入数据
for i := 0; i < total; i += batchSize {
data := &ExcelData{
rowIndex: atomic.NewInt32(int32(i + 2)), // Excel行号从2开始
}
// 并发创建用户数据
for j := 0; j < batchSize; j++ {
user := createUser(i + j)
rowIndex := data.rowIndex.Load()
sw.SetRow("A"+strconv.Itoa(int(rowIndex)),
[]interface{}{user.name, user.phone, user.email, user.age, user.other})
data.rowIndex.Add(1)
user = nil
}
fmt.Printf("processed [%d - %d] users\n", i+1, i+batchSize)
}
sw.Flush()
f.SetActiveSheet(index)
if err := f.SaveAs(filePath); err != nil {
fmt.Println(err)
}
}
func main() {
writeExcelFile("user.xlsx")
}
可以看到我并没有利用协程往excel中写数据，而是采取同步的方案去进行写。因为excelize提供的streamWriter是并发不安全的，底层会利用到切片存储数据，在并发的情况下会导致数据异常。
当然如果你的业务中是需要读取mysql的数据导入到excel中，则可以考虑利用一个协程读取mysql的数据再基于通道将数据传递到写excel的协程中。
5. 读取100w数据量的excel文件
读取的方法我们可以利用excelize中提供的Rows()与GetRows()方法进行实现，如下是两者的区别：
- Rows()
使用f.Rows方法可以迭代遍历xlsx表格中的每一行数据。它返回的是一个迭代器对象，需要通过for循环进行遍历。
示例代码:
// 迭代遍历所有行数据
rows, err := f.Rows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for rows.Next() {
row := rows.Columns()
fmt.Println(row)
}
- GetRows()
f.GetRows 使用f.GetRows方法可以一次性读取xlsx文件中的所有行数据，返回的是一个二维数组，其中每个子数组代表一行数据。
示例代码:
// 一次性读取所有行数据
rows, err := f.GetRows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
for _, row := range rows {
fmt.Println(row)
}
因此，f.Rows和f.GetRows方法的使用场景不同，在读取大量数据时，使用f.GetRows会更高效。而在需要逐一遍历处理行数据时，使用f.Rows方法则更加方便。
本案例中对于全部读取可以直接看 “2” 节点的内容，此处直接与协程结合，当读取数据后会基于协程并发处理。
GetRows()方式
package main
import (
"fmt"
"sync"
"github.com/360EntSecGroup-Skylar/excelize/v2"
)
func processRows(rows [][]string, resultChan chan<- string) {
// 处理每一行数据
for _, row := range rows {
// TODO: 执行数据处理操作
// 这里只是简单地将每行数据拼接成一个字符串
resultChan <- fmt.Sprintf("%v\n", row)
}
}
func main() {
// 打开Excel文件
f, err := excelize.OpenFile("test.xlsx")
if err != nil {
fmt.Println(err)
return
}
// 读取所有行数据
rows, err := f.GetRows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
// 创建一个结果通道
resultChan := make(chan string)
// 启动多个goroutine并发处理数据
go func() {
for {
select {
case result := <-resultChan:
// 输出处理结果
fmt.Print(result)
}
}
}()
var wg sync.WaitGroup
for i := 0; i < 10; i++ {
// 每个goroutine处理10万行数据
wg.Add(1)
go func(i int) {
defer wg.Done()
processRows(rows[i*100000:(i+1)*100000], resultChan)
}(i)
}
// 等待所有goroutine完成处理
wg.Wait()
}
说明：
- 使用
excelize包打开Excel文件，并获取工作表名、行数和列数。 - 创建一个有缓冲的通道，用于存储需要处理的行数据。
- 启动多个协程进行并发处理，每个协程从通道中读取一行数据进行处理。
- 将需要处理的行数据发送到通道中。
- 关闭通道，等待所有协程处理完成。
这样可以在保证数据处理效率的同时，充分利用多核CPU的性能。
Rows方式
package main
import (
"fmt"
"sync"
"github.com/360EntSecGroup-Skylar/excelize/v2"
)
func processRows(rows [][]string, resultChan chan<- string) {
// 处理每一行数据
for _, row := range rows {
// TODO: 执行数据处理操作
// 这里只是简单地将每行数据拼接成一个字符串
resultChan <- fmt.Sprintf("%v\n", row)
}
}
func main() {
// 打开Excel文件
f, err := excelize.OpenFile("test.xlsx")
if err != nil {
fmt.Println(err)
return
}
// 读取所有行数据
rows, err := f.Rows("Sheet1")
if err != nil {
fmt.Println(err)
return
}
// 创建一个结果通道
resultChan := make(chan string)
// 启动多个goroutine并发处理数据
go func() {
for {
select {
case result := <-resultChan:
// 输出处理结果
fmt.Print(result)
}
}
}()
// 去掉头表头
rows.Next()
var wg sync.WaitGroup
datas := make([][]string, 0,100000)
for rows.Next() {
data, err := rows.Columns()
if err != nil {
fmt.Println(err)
return
}
datas = append(datas, data)
if len(datas) >= 100000 {
wg.Add(1)
go func(datas [][]string) {
defer wg.Done()
processRows(datas, resultChan)
}(datas)
datas = nil
}
}
// 剩下的处理完成
wg.Add(1)
go func(datas [][]string) {
defer wg.Done()
processRows(datas, resultChan)
}(datas)
// 等待所有goroutine完成处理
wg.Wait()
}
说明：在实现上与GetRows类似只是在写法上需注意，rows是并发不安全的因此不要使rows并发操作。
