hongtao3_watch/services/index.go

package services

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	"hongze/hongtao3_watch/utils"
	"strings"
	"time"
)

const (
	FormatTime    = "15:04:05" //时间格式
	FormatTimeStr = "15:04"    //时间格式
)

//var checkLock sync.RWMutex

// ReadExcel 读取Excel
func ReadExcelV2(filePath string) {
	time.Sleep(5 * time.Second)
	excelInfo, err := excelize.OpenFile(filePath)
	if err != nil {
		fmt.Println("OpenFile excel err:" + err.Error())
		return
	}
	fmt.Println("开始读取EXCEL了：", filePath)

	defer func() {
		excelInfo.Close()
	}()

	sheetList := excelInfo.GetSheetList()

	indexNameList := make([]string, 0)
	thirdIndexIdList := make([]string, 0)
	frequencyList := make([]string, 0)
	unitList := make([]string, 0)
	//indexIdList := make([]int, 0)
	dataMap := make(map[string]map[string]string, 0)
	for _, sheet := range sheetList {
		//fmt.Println(sheet)
		sheetThirdIndexIdList := make([]string, 0)
		rows, err := excelInfo.GetRows(sheet)
		if err != nil {
			fmt.Println("err:", err)
			continue
		}

		// excel异常
		if len(rows) <= 13 {
			fmt.Println("错误的excel表")
			continue
		}
		indexLen := len(rows[0])
		//fmt.Println(indexLen)

		// 没有指标
		if indexLen <= 1 {
			fmt.Println("没有指标")
			continue
		}
		for k, row := range rows {
			//fmt.Println(row)
			// 指标信息处理
			{
				// 指标名称
				if k == 0 {
					for colIndex, col := range row {
						if colIndex == 0 {
							continue
						}
						indexNameList = append(indexNameList, col)
					}
				}

				//第三方指标id
				if k == 1 {
					for colIndex, col := range row {
						if colIndex == 0 {
							continue
						}
						thirdIndexIdList = append(thirdIndexIdList, col)
						sheetThirdIndexIdList = append(sheetThirdIndexIdList, col)
					}
				}
				//频度
				if k == 8 {
					for colIndex, col := range row {
						if colIndex == 0 {
							continue
						}
						if strings.Contains(col, "日度") {
							frequencyList = append(frequencyList, "日度")
						} else if strings.Contains(col, "周度") {
							frequencyList = append(frequencyList, "周度")
						} else if strings.Contains(col, "旬度") {
							frequencyList = append(frequencyList, "旬度")
						} else if strings.Contains(col, "月度") {
							frequencyList = append(frequencyList, "月度")
						} else if strings.Contains(col, "季度") {
							frequencyList = append(frequencyList, "季度")
						} else if strings.Contains(col, "年度") {
							frequencyList = append(frequencyList, "年度")
						} else {
							frequencyList = append(frequencyList, col)
						}
					}
				}
				//单位
				if k == 10 {
					for colIndex, col := range row {
						if colIndex == 0 {
							continue
						}
						unitList = append(unitList, col)
					}
				}
			}
			if k <= 12 {
				continue
			}

			// 数据处理
			var currDate string
			for colIndex, col := range row {
				// 数据日期
				if colIndex == 0 {
					currDate = col
					continue
				}
				// 没有数据的情况
				if col == `` {
					continue
				}

				// 校验数据长度
				lenSheetThirdIndexIdList := len(sheetThirdIndexIdList)
				if lenSheetThirdIndexIdList != colIndex {
					fmt.Println("长度不一致,sheetThirdIndexIdList:", lenSheetThirdIndexIdList, ";colIndex:", colIndex)
					continue
				}

				thirdIndexId := sheetThirdIndexIdList[colIndex-1]
				if _, ok := dataMap[thirdIndexId]; !ok {
					tmpDataMap := make(map[string]string)
					dataMap[thirdIndexId] = tmpDataMap
				}
				dataMap[thirdIndexId][currDate] = col
			}

		}
	}

	// 调用指标库公共服务处理该数据
	HandleExcelDataByEdbLib(dataMap, indexNameList, thirdIndexIdList, frequencyList, unitList, filePath)
}

// ReadExcel 读取Excel
func ReadExcel(filePath string) {
	var err error
	defer func() {
		if err != nil {
			tips := fmt.Sprintf("ReadExcel, err: %s", err.Error())
			fmt.Println(tips)
		}
	}()

	time.Sleep(5 * time.Second)
	excelInfo, e := excelize.OpenFile(filePath)
	if e != nil {
		err = fmt.Errorf("OpenFile err: %s", e.Error())
		return
	}
	defer func() {
		_ = excelInfo.Close()
	}()
	fmt.Println("读取excel：", filePath)

	indexNameList := make([]string, 0)
	thirdIndexIdList := make([]string, 0)
	frequencyList := make([]string, 0)
	unitList := make([]string, 0)
	dataMap := make(map[string]map[string]string, 0)

	type IndexItem struct {
		IndexName string
		IndexCode string
		Frequency string
		Unit      string
		DataList  map[string]string
	}
	indexes := make([]*IndexItem, 0)

	startName := "数据项名称"
	sheetList := excelInfo.GetSheetList()
	for _, sheet := range sheetList {
		rows, e := excelInfo.GetRows(sheet)
		if e != nil {
			fmt.Println("GetRows: ", e.Error())
			continue
		}
		if len(rows) <= 13 {
			fmt.Println("错误的excel表")
			continue
		}

		colIndexMap := make(map[int]*IndexItem) // 列对应的指标信息
		colTitle := make(map[int]bool)          // 列对应的是否为表头
		colDateCols := make(map[int][]int)      // 日期列对应的指标列集合
		colYes := make(map[int]bool)            // 列是否对应指标列

		// 遍历行
		for rk, row := range rows {

			// 遍历列
			lastDate := "" // 上一个日期
			for ck, col := range row {
				col = strings.TrimSpace(col)

				// 指标名称行
				if rk == 0 {
					// 读取到"数据项名称"或空时跳过
					if col == startName {
						colTitle[ck] = true
						colDateCols[ck] = make([]int, 0)
						continue
					}
					if col == "" {
						continue
					}
					colYes[ck] = true
					if colIndexMap[ck] == nil {
						colIndexMap[ck] = new(IndexItem)
					}
					colIndexMap[ck].IndexName = col
					colIndexMap[ck].DataList = make(map[string]string, 0)
				}

				// 指标ID
				if rk == 1 && colYes[ck] {
					colIndexMap[ck].IndexCode = col
				}

				// 频度
				if rk == 8 && colYes[ck] {
					if strings.Contains(col, "日度") {
						colIndexMap[ck].Frequency = "日度"
					} else if strings.Contains(col, "周度") {
						colIndexMap[ck].Frequency = "周度"
					} else if strings.Contains(col, "旬度") {
						colIndexMap[ck].Frequency = "旬度"
					} else if strings.Contains(col, "月度") {
						colIndexMap[ck].Frequency = "月度"
					} else if strings.Contains(col, "季度") {
						colIndexMap[ck].Frequency = "季度"
					} else if strings.Contains(col, "年度") {
						colIndexMap[ck].Frequency = "年度"
					} else {
						colIndexMap[ck].Frequency = col
					}
				}

				// 单位
				if rk == 10 && colYes[ck] {
					colIndexMap[ck].Unit = col
				}

				if rk <= 12 {
					continue
				}

				// 标题列取日期
				if colTitle[ck] {
					if col == "" {
						continue
					}
					_, e = time.Parse(utils.FormatDate, col)
					if e != nil {
						continue
					}
					lastDate = col
				}
				// 指标列-即值列
				if colYes[ck] {
					if col == "" || col == "--" || col == "#N/A" {
						continue
					}
					// 取上一个日期列的日期
					if lastDate != "" {
						colIndexMap[ck].DataList[lastDate] = col
					}
				}
			}
		}

		// 每一页读取后赋值给indexes
		for _, v := range colIndexMap {
			indexes = append(indexes, v)
		}
	}

	// 转为指定格式返回
	if len(indexes) == 0 {
		return
	}
	for _, v := range indexes {
		indexNameList = append(indexNameList, v.IndexName)
		thirdIndexIdList = append(thirdIndexIdList, v.IndexCode)
		frequencyList = append(frequencyList, v.Frequency)
		unitList = append(unitList, v.Unit)
		dataMap[v.IndexCode] = v.DataList
	}

	// 调用指标库公共服务处理该数据
	HandleExcelDataByEdbLib(dataMap, indexNameList, thirdIndexIdList, frequencyList, unitList, filePath)
}