gopkg/excel/read.go

// Package excel...
//
// Description : excel...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 2021-11-19 3:59 下午
package excel

import (
	"github.com/xuri/excelize/v2"
)

// NewRead 获取读取实例
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:00 下午 2021/11/19
func NewRead(fullFilePath string, filePassword string) (*Read, error) {
	var err error
	read := &Read{}
	if read.fileHandler, err = excelize.OpenFile(fullFilePath, excelize.Options{Password: filePassword}); nil != err {
		return nil, err
	}
	return read, nil
}

// Read ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:03 下午 2021/11/19
type Read struct {
	fileHandler *excelize.File
}

// GetAllSheetList ...
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:06 下午 2021/11/19
func (r *Read) GetAllSheetList() []string {
	return r.fileHandler.GetSheetList()
}

// GetAllData 读取全部数据
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:07 下午 2021/11/19
func (r *Read) GetAllData() (map[string][][]string, error) {
	var (
		sheetList []string
		result    map[string][][]string
		err       error
	)
	sheetList = r.GetAllSheetList()
	result = make(map[string][][]string)
	for _, sheetName := range sheetList {
		if result[sheetName], err = r.fileHandler.GetRows(sheetName); nil != err {
			return nil, err
		}
	}
	return result, nil
}

// GetAllDataToMap 读取全部数据,并返回map结构
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:20 下午 2021/11/19
func (r *Read) GetAllDataToMap(fieldList []string) (map[string][]map[string]string, error) {
	var (
		allData      map[string][][]string
		err          error
		formatResult map[string][]map[string]string
	)
	formatResult = make(map[string][]map[string]string)
	if allData, err = r.GetAllData(); nil != err {
		return nil, err
	}
	for sheetName, sheetData := range allData {
		formatResult[sheetName] = make([]map[string]string, 0)
		for _, lineData := range sheetData {
			tmpResult := make(map[string]string)
			for idx, colData := range lineData {
				if idx >= len(fieldList) {
					// 指定的字段列表较短, 自动剔除后面的字段
					break
				}
				tmpResult[fieldList[idx]] = colData
			}
			// 字段列表较长, 单元格不足, 自动补齐空字符串
			for i := len(lineData); i < len(fieldList); i++ {
				tmpResult[fieldList[i]] = ""
			}
			formatResult[sheetName] = append(formatResult[sheetName], tmpResult)
		}
	}
	// 格式化数据
	return formatResult, nil
}

// ExtractAssignCol 抽取指定的列，并返回list数据, 列的计数从 0 开始
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:53 下午 2021/11/19
func (r *Read) ExtractAssignCol(colIndexList []int) (map[string][][]string, error) {
	var (
		allData      map[string][][]string
		err          error
		formatResult map[string][][]string
	)
	if allData, err = r.GetAllData(); nil != err {
		return nil, err
	}
	formatResult = make(map[string][][]string)
	for sheetName, sheetData := range allData {
		formatResult[sheetName] = make([][]string, 0)
		for _, lineData := range sheetData {
			tmpData := make([]string, 0)
			for _, colIdx := range colIndexList {
				if len(lineData) <= colIdx {
					// 索引越界,默认空值
					tmpData = append(tmpData, "")
					continue
				}
				tmpData = append(tmpData, lineData[colIdx])
			}
			formatResult[sheetName] = append(formatResult[sheetName], tmpData)
		}
	}
	return formatResult, nil
}

// ExtractAssignColToMap 抽取指定的列，并返回map数据
//
// Author : go_developer@163.com<白茶清欢>
//
// Date : 4:53 下午 2021/11/19
func (r *Read) ExtractAssignColToMap(ruleTable map[int]string) (map[string][]map[string]string, error) {
	var (
		allData      map[string][][]string
		err          error
		formatResult map[string][]map[string]string
	)
	formatResult = make(map[string][]map[string]string)
	if allData, err = r.GetAllData(); nil != err {
		return nil, err
	}
	for sheetName, sheetData := range allData {
		formatResult[sheetName] = make([]map[string]string, 0)
		for _, lineData := range sheetData {
			tmpResult := make(map[string]string)
			for idx, colData := range lineData {
				colName, exist := ruleTable[idx]
				if !exist {
					// 当前字段不需要提取
					continue
				}
				tmpResult[colName] = colData
			}
			for _, colName := range ruleTable {
				if _, exist := tmpResult[colName]; !exist {
					tmpResult[colName] = ""
				}
			}
			formatResult[sheetName] = append(formatResult[sheetName], tmpResult)
		}
	}
	// 格式化数据
	return formatResult, nil
}