123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649 |
- package excel
- import (
- "encoding/json"
- "errors"
- "eta/eta_api/models/data_manage/excel"
- "eta/eta_api/services/data"
- excelServices "eta/eta_api/services/excel"
- "eta/eta_api/utils"
- "fmt"
- "github.com/araddon/dateparse"
- "github.com/shopspring/decimal"
- "github.com/tealeg/xlsx"
- "github.com/xuri/excelize/v2"
- "strings"
- )
- func GetCustomAnalysisExcelData(excelInfo *excel.ExcelInfo) (luckySheet excelServices.LuckySheet, err error, errMsg string) {
-
- sheetList, err := excel.GetAllSheetList(excelInfo.ExcelInfoId)
- if err != nil {
- errMsg = "保存失败"
- err = errors.New("查找当前excel的sheet列表失败,Err:" + err.Error())
- return
- }
- currSheetMap := make(map[string]string)
- for _, sheet := range sheetList {
- currSheetMap[sheet.SheetName] = sheet.SheetName
- }
- sheetCellDataMapList := make(map[int][]excelServices.LuckySheetCellData)
-
- {
- dataList, tmpErr := excel.GetAllSheetDataListByExcelInfoId(excelInfo.ExcelInfoId)
- if tmpErr != nil {
- err = tmpErr
- return
- }
- for _, cellData := range dataList {
- sheetDataList, ok := sheetCellDataMapList[cellData.ExcelSheetId]
- if !ok {
- sheetDataList = make([]excelServices.LuckySheetCellData, 0)
- }
- tmpSheetDataList := make([]excelServices.LuckySheetCellData, 0)
- err = json.Unmarshal([]byte(cellData.Data), &tmpSheetDataList)
- if err != nil {
- err = errors.New(fmt.Sprintf("解析data的配置失败,sheetId:%d,Err:%s", cellData.ExcelDataId, err.Error()))
- return
- }
- sheetCellDataMapList[cellData.ExcelSheetId] = append(sheetDataList, tmpSheetDataList...)
- }
- }
-
- luckySheet = excelServices.LuckySheet{
- SheetList: make([]excelServices.LuckySheetData, 0),
- }
- for _, sheet := range sheetList {
- var luckySheetDataConfig excelServices.LuckySheetDataConfig
- err = json.Unmarshal([]byte(sheet.Config), &luckySheetDataConfig)
- if err != nil {
- err = errors.New(fmt.Sprintf("解析sheet的配置失败,sheetId:%d,Err:%s", sheet.ExcelSheetId, err.Error()))
- return
- }
- tmpLuckySheetDataInfo := excelServices.LuckySheetData{
- Name: sheet.SheetName,
- Index: sheet.Sort,
- CellData: sheetCellDataMapList[sheet.ExcelSheetId],
- Config: luckySheetDataConfig,
- }
- luckySheet.SheetList = append(luckySheet.SheetList, tmpLuckySheetDataInfo)
- }
- return
- }
- func GenerateExcelCustomAnalysisExcel(excelInfo *excel.ExcelInfo) (downloadFilePath string, err error, errMsg string) {
- luckySheet, err, errMsg := GetCustomAnalysisExcelData(excelInfo)
- if err != nil {
- return
- }
- downloadFilePath, err = luckySheet.ToExcel(false)
- return
- }
- type dataStruct struct {
- Value float64
- Ok bool
- }
- func HandleEdbSequenceVal(dateSequenceVal, dataSequenceVal []string) (newDateList []string, newDataList []float64, err error, errMsg string) {
- newDateList = make([]string, 0)
- newDataList = make([]float64, 0)
- dataList := make([]dataStruct, 0)
- {
- for _, v := range dataSequenceVal {
-
- if v == `` {
-
- dataList = append(dataList, dataStruct{
- Value: 0,
- Ok: false,
- })
- continue
- }
- v = strings.Replace(v, ",", "", -1)
- v = strings.Replace(v, ",", "", -1)
-
- v = strings.Replace(v, " ", "", -1)
- v = strings.TrimSpace(v)
- var tmpVal float64
- if strings.Contains(v, "%") {
-
- isPercentage, percentageValue := utils.IsPercentage(v)
- if !isPercentage {
- dataList = append(dataList, dataStruct{
- Value: 0,
- Ok: false,
- })
- continue
- }
- tmpValDec, tmpErr := decimal.NewFromString(percentageValue)
- if tmpErr != nil {
- dataList = append(dataList, dataStruct{
- Value: 0,
- Ok: false,
- })
- continue
- }
- tmpVal, _ = tmpValDec.Div(decimal.NewFromFloat(100)).Float64()
- } else {
- tmpValDec, tmpErr := decimal.NewFromString(v)
- if tmpErr != nil {
- dataList = append(dataList, dataStruct{
- Value: 0,
- Ok: false,
- })
- continue
- }
- tmpVal, _ = tmpValDec.Float64()
- }
- dataList = append(dataList, dataStruct{
- Value: tmpVal,
- Ok: true,
- })
- }
- }
-
- dateList := make([]string, 0)
- {
- for _, v := range dateSequenceVal {
-
- if v == `` {
-
- dateList = append(dateList, "")
- continue
- }
-
- v = strings.Replace(v, " ", "", -1)
- v = strings.TrimSpace(v)
- t1, tmpErr := dateparse.ParseAny(v)
- if tmpErr != nil {
- dateList = append(dateList, "")
- continue
- }
- dateList = append(dateList, t1.Format(utils.FormatDate))
- }
- }
- lenData := len(dataList)
- lenDate := len(dateList)
-
- num := lenDate
- if num > lenData {
- num = lenData
- }
- for i := 0; i < num; i++ {
- date := dateList[i]
- tmpData := dataList[i]
-
- if !tmpData.Ok || date == `` {
- continue
- }
- newDateList = append(newDateList, date)
- newDataList = append(newDataList, tmpData.Value)
- }
- return
- }
- type ResetCustomAnalysisData struct {
- EdbInfoId int
- DateList []string
- DataList []float64
- }
- func Refresh(excelInfo *excel.ExcelInfo, lang string) (err error, errMsg string, isSendEmail bool) {
- isSendEmail = true
- list, err := excel.GetAllExcelEdbMappingItemByExcelInfoId(excelInfo.ExcelInfoId)
- if err != nil {
- errMsg = "获取失败"
- err = errors.New("查找所有的mapping失败" + err.Error())
- return
- }
-
- if len(list) <= 0 {
- return
- }
- for k, v := range list {
- var tmpCalculateFormula excel.CalculateFormula
- err = json.Unmarshal([]byte(v.CalculateFormula), &tmpCalculateFormula)
- if err != nil {
- errMsg = "获取失败"
- err = errors.New(fmt.Sprintf("指标id:%d,公式转换失败,Err:%s", v.EdbInfoId, err.Error()))
- return
- }
- v.DateSequenceStr = tmpCalculateFormula.DateSequenceStr
- v.DataSequenceStr = tmpCalculateFormula.DataSequenceStr
- list[k] = v
- }
- luckySheet, err, errMsg := GetCustomAnalysisExcelData(excelInfo)
- if err != nil {
- err = errors.New(fmt.Sprintf("获取自定义分析Excel数据失败,Err:%s", err.Error()))
- return
- }
-
- xlsxFile, err := luckySheet.GetExcelData(false)
- if err != nil {
- err = errors.New(fmt.Sprintf("获取excel表格数据,Err:%s", err.Error()))
- return
- }
-
- edbInfoIdList := make([]int, 0)
- for _, v := range list {
- edbInfoIdList = append(edbInfoIdList, v.EdbInfoId)
-
- relDateList, relDataList, tmpErr, tmpErrMsg := getDateAndDataList(v, xlsxFile)
- if tmpErr != nil {
- err = errors.New(fmt.Sprintf("《%s》获取对应的日期和数据列表,Err:%s", v.EdbName, tmpErr.Error()))
- errMsg = fmt.Sprintf("《%s》%s", v.EdbName, tmpErrMsg)
- return
- }
- if len(relDateList) <= 0 {
- errMsg = fmt.Sprintf("《%s》的日期序列为空", v.EdbName)
- err = errors.New(errMsg)
- return
- }
- if len(relDataList) <= 0 {
- errMsg = fmt.Sprintf("《%s》的数据序列为空", v.EdbName)
- err = errors.New(errMsg)
- return
- }
-
- req2 := &ResetCustomAnalysisData{
- EdbInfoId: v.EdbInfoId,
- DateList: relDateList,
- DataList: relDataList,
- }
-
- reqJson, tmpErr := json.Marshal(req2)
- if tmpErr != nil {
- err = errors.New(fmt.Sprintf("结构体转对象失败,Err:%s", tmpErr.Error()))
- return
- }
- respItem, tmpErr := data.ResetCustomAnalysisData(string(reqJson), lang)
- if tmpErr != nil {
- err = errors.New(fmt.Sprintf("调用指标库去更新,Err:%s", tmpErr.Error()))
- return
- }
- if respItem.Ret != 200 {
- errMsg = respItem.Msg
- err = errors.New(respItem.ErrMsg)
- return
- }
- }
- if len(edbInfoIdList) > 0 {
- err, _ = data.EdbInfoRefreshAllFromBaseV3(edbInfoIdList, false, true, true)
- }
-
- return
- }
- func getDateAndDataList(excelEdbMappingItem *excel.ExcelEdbMappingItem, xlsxFile *xlsx.File) (newDateList []string, newDataList []float64, err error, errMsg string) {
- var dateList, dataList []string
-
- {
- sheetName, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr, tmpErrMsg := GetSheetStr(excelEdbMappingItem.DateSequenceStr)
- if tmpErr != nil {
- errMsg = tmpErrMsg
- err = tmpErr
- return
- }
-
- sheetInfo, ok := xlsxFile.Sheet[sheetName]
- if !ok {
- errMsg = "找不到" + sheetName
- err = errors.New(errMsg)
- return
- }
- startNum = startNum - 1
- endNum = endNum - 1
-
- if isRow {
-
-
-
- var startColumn, endColumn int
- if isAll {
-
- endColumn = len(sheetInfo.Cols) - 1
- } else {
- tmpStartColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + startColumnName
- err = errors.New(errMsg)
- return
- }
- tmpEndColumn, tmpErr := excelize.ColumnNameToNumber(endColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + endColumnName
- err = errors.New(errMsg)
- return
- }
- startColumn = tmpStartColumn - 1
- endColumn = tmpEndColumn - 1
- }
-
- maxCol := len(sheetInfo.Cols)
- if endColumn > maxCol {
- endColumn = maxCol - 1
- }
-
- dateList = make([]string, endColumn-startColumn+1)
- i := 0
- for currColumn := startColumn; currColumn <= endColumn; currColumn++ {
- currCell := sheetInfo.Cell(startNum, currColumn)
- if currCell == nil {
- errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, startNum)
- err = errors.New(errMsg)
- return
- }
-
- dateList[i] = currCell.Value
- i++
- }
- } else if isColumn {
- if isAll {
-
- endNum = len(sheetInfo.Rows) - 1
- }
- startColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + startColumnName
- err = errors.New(errMsg)
- return
- }
- startColumn = startColumn - 1
-
- maxRow := len(sheetInfo.Rows)
- if endNum > maxRow {
- endNum = maxRow - 1
- }
-
- dateList = make([]string, endNum-startNum+1)
- i := 0
- for currRow := startNum; currRow <= endNum; currRow++ {
- currCell := sheetInfo.Cell(currRow, startColumn)
- if currCell == nil {
- errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, startNum)
- err = errors.New(errMsg)
- return
- }
-
- dateList[i] = currCell.Value
- i++
- }
- }
- }
-
- {
- sheetName, startColumnName, endColumnName, startNum, endNum, isAll, isRow, isColumn, tmpErr, tmpErrMsg := GetSheetStr(excelEdbMappingItem.DataSequenceStr)
- if tmpErr != nil {
- errMsg = tmpErrMsg
- err = tmpErr
- return
- }
-
- sheetInfo, ok := xlsxFile.Sheet[sheetName]
- if !ok {
- errMsg = "找不到" + sheetName
- err = errors.New(errMsg)
- return
- }
- startNum = startNum - 1
- endNum = endNum - 1
-
- if isRow {
-
- var startColumn, endColumn int
- if isAll {
-
- endColumn = len(sheetInfo.Cols) - 1
- } else {
- tmpStartColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + startColumnName
- err = errors.New(errMsg)
- return
- }
- tmpEndColumn, tmpErr := excelize.ColumnNameToNumber(endColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + endColumnName
- err = errors.New(errMsg)
- return
- }
- startColumn = tmpStartColumn - 1
- endColumn = tmpEndColumn - 1
- }
-
- maxCol := len(sheetInfo.Cols)
- if endColumn > maxCol {
- endColumn = maxCol - 1
- }
-
- dataList = make([]string, endColumn-startColumn+1)
- i := 0
- for currColumn := startColumn; currColumn <= endColumn; currColumn++ {
- currCell := sheetInfo.Cell(startNum, currColumn)
- if currCell == nil {
- errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, startNum)
- err = errors.New(errMsg)
- return
- }
-
- dataList[i] = currCell.Value
- i++
- }
- } else if isColumn {
- if isAll {
-
- endNum = len(sheetInfo.Rows) - 1
- }
- startColumn, tmpErr := excelize.ColumnNameToNumber(startColumnName)
- if tmpErr != nil {
- errMsg = "列名异常:" + startColumnName
- err = errors.New(errMsg)
- return
- }
- startColumn = startColumn - 1
-
- maxRow := len(sheetInfo.Rows)
- if endNum > maxRow {
- endNum = maxRow - 1
- }
-
- dataList = make([]string, endNum-startNum+1)
- i := 0
- for currRow := startNum; currRow <= endNum; currRow++ {
- currCell := sheetInfo.Cell(currRow, startColumn)
- if currCell == nil {
- errMsg = fmt.Sprintf("第%d列,第%d行数据异常", startColumn, startNum)
- err = errors.New(errMsg)
- return
- }
-
- dataList[i] = currCell.Value
- i++
- }
- }
- }
-
-
-
- newDateList, newDataList, err, errMsg = HandleEdbSequenceVal(dateList, dataList)
- return
- }
- func GetSheetStr(sequenceStr string) (sheetName, startColumnName, endColumnName string, startNum, endNum int, isAll, isRow, isColumn bool, err error, errMsg string) {
-
- tmpList := strings.Split(sequenceStr, "!")
- if len(tmpList) != 2 {
- errMsg = "错误的公式,查找sheet异常:" + sequenceStr
- err = errors.New(errMsg)
- return
- }
- sheetName = tmpList[0]
-
- tmpList = strings.Split(tmpList[1], ":")
- if len(tmpList) != 2 {
- errMsg = "错误的公式,查找开始/结束单元格异常:" + sequenceStr
- err = errors.New(errMsg)
- return
- }
- startList := strings.Split(tmpList[0], "$")
- endList := strings.Split(tmpList[1], "$")
- lenList := len(startList)
- if lenList != len(endList) {
- errMsg = "错误的公式,开始与结束单元格异常:" + sequenceStr
- err = errors.New(errMsg)
- return
- }
- if lenList != 3 && lenList != 2 {
- errMsg = "错误的公式:" + sequenceStr
- err = errors.New(errMsg)
- return
- }
- startColumnName = startList[1]
- endColumnName = endList[1]
-
- if lenList == 2 {
- isAll = true
- startDeci, tmpErr1 := decimal.NewFromString(startList[1])
- endDeci, tmpErr2 := decimal.NewFromString(endList[1])
- if tmpErr1 == nil && tmpErr2 == nil {
- isRow = true
- startNum = int(startDeci.IntPart())
- endNum = int(endDeci.IntPart())
- startColumnName = ``
- endColumnName = ``
- return
- }
- if tmpErr1 == nil || tmpErr2 == nil {
- err = errors.New("错误的公式2:" + sequenceStr)
- return
- }
-
- isColumn = true
- return
- }
-
- startDeci, tmpErr1 := decimal.NewFromString(startList[2])
- endDeci, tmpErr2 := decimal.NewFromString(endList[2])
- if tmpErr1 != nil && errors.Is(tmpErr1, tmpErr2) {
- err = errors.New("错误的公式3:" + sequenceStr)
- return
- }
- startNum = int(startDeci.IntPart())
- endNum = int(endDeci.IntPart())
- if startColumnName != endColumnName && startNum != endNum {
- errMsg = `选区不允许跨行或者跨列`
- err = errors.New(errMsg)
- }
- if startColumnName == endColumnName {
- isColumn = true
- } else {
- isRow = true
- }
- return
- }
|