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) }