Golang 10w+excel大数据量使用管道缓冲并发导入

前序

1. 使用策略模式适应不同的环境、灵活性和可维护性等
2. 引入缓冲层导入比不是缓冲层导入快一倍+ 10w数据3s
3. 引入这个文件开箱即用
4. 使用go-zero下的threading控制并发和安全并发

代码如下

import.go

package excel

import (
	"fmt"
	"sync"

	"github.com/gin-gonic/gin"
	"github.com/google/uuid"
	"github.com/xuri/excelize/v2"
	"github.com/zeromicro/go-zero/core/threading"
)

type ExcelImportImpl interface {
	ColumnsUnique() []int
	ColumnsFilter(row []string) bool
	AddData(row [][]string) (uniqNum uint32, err error)
}

type ExcelImportBuilder struct {
	file       *excelize.File
	ProgressTx string // 进度条
	ctx        *gin.Context
	Total      uint32
	Success    uint32
	Fail       uint32
	Unique     uint32
}

func NewExcelImportBuilder(ctx *gin.Context, f *excelize.File) (*ExcelImportBuilder, error) {
	return &ExcelImportBuilder{
		file:       f,
		ProgressTx: uuid.New().String(),
		ctx:        ctx,
	}, nil
}

// start 从第几行开始读
func (builder *ExcelImportBuilder) Save(start int, impl ExcelImportImpl) error {
	rows, err := builder.file.Rows(builder.file.GetSheetName(builder.file.GetActiveSheetIndex()))
	if err != nil {
		return err
	}

	// 1000份数据写入管道
	dataChan := make(chan [][]string, 100)
	threading.GoSafe(func() {
		defer close(dataChan)
		defer rows.Close()
		columnsUnique := impl.ColumnsUnique()
		num := 1000
		results, count, i := make([][]string, 0, num), 0, 0
		dataAll := make(map[int]map[string]struct{}, len(columnsUnique))
		for rows.Next() {
			i++
			if start >= i {
				continue
			}
			builder.Total++
			row, err := rows.Columns()
			if err != nil {
				break
			}

			// 长度判断和过滤
			if len(row) <= 0 || impl.ColumnsFilter(row) {
				builder.Fail++
				continue
			}

			// 去重某些列
			var uniqueResult bool
			for i := 0; i < len(columnsUnique); i++ {
				item := columnsUnique[i]
				key := row[item]
				seen := dataAll[item]
				if seen == nil {
					seen = make(map[string]struct{})
				}
				// 判断是否存在
				_, ok := seen[key]
				if ok {
					uniqueResult = true
					continue
				}

				seen[key] = struct{}{}
				dataAll[item] = seen
			}
			if uniqueResult {
				builder.Unique++
				continue
			}

			// 加入队列切片
			count++
			results = append(results, row)

			// 重置计算器和切片
			if count-num == 0 {
				dataChan <- results
				count = 0
				results = make([][]string, 0, num)
			}
		}
		if count > 0 {
			dataChan <- results
		}
	})

	// 插入数据
	wg := sync.WaitGroup{}
	task := threading.NewTaskRunner(100)
	for rows := range dataChan {
		rows := rows
		// 并发插入
		wg.Add(1)
		task.Schedule(func() {
			defer wg.Done()
			// 插入数据
			fmt.Println(rows)
			uniqNum, err := impl.AddData(rows)
			if err != nil {
				builder.Fail++
			} else {
				builder.Success += uint32(len(rows)) - uniqNum
			}
			builder.Unique += uniqNum
		})
	}
	wg.Wait()

	return nil
}

service.go

type whiteImprot struct {
	c           *gin.Context
	creatorName string
	creatorId   int32
}

func (w *whiteImprot) ColumnsUnique() []int {
	return []int{
		0,
	}
}
func (w *whiteImprot) ColumnsFilter(row []string) bool {
	return false
}
func (w *whiteImprot) AddData(row [][]string) (uniqNum uint32, err error) {
	names := lo.Map[[]string, string](row, func(item []string, index int) string {
		return item[0]
	})
	return model.NewWhiteListModel(w.c).CreateUniqWhite(names, w.creatorName, w.creatorId)
}

// 引入缓冲层导入比不是缓冲层导入快一倍+ 10w数据3s
func ImportWhite(c *gin.Context, f *excelize.File) (*response.ImportWhiteResp, error) {
	var (
		response    = response.ImportWhiteResp{}
		creatorName = c.GetString("user_name")
		creatorId   = int32(c.GetInt("user_id"))
	)

	whiteImprot := &whiteImprot{
		c:           c,
		creatorName: creatorName,
		creatorId:   creatorId,
	}
	builder, err := excel.NewExcelImportBuilder(c, f)
	if err != nil {
		return nil, err
	}

	err = builder.Save(1, whiteImprot)
	if err != nil {
		return nil, err
	}
	response.FailTotal = builder.Fail + builder.Unique
	response.SuccessTotal = builder.Success

	return &response, nil
}

导出你看另一篇文档

https://blog.csdn.net/qq_39272466/article/details/132203333?spm=1001.2014.3001.5501