eta_api/services/excel/lucky_sheet_table.go

package excel

import (
	"eta/eta_api/models/data_manage/excel"
	"eta/eta_api/utils"
	"fmt"
	"sort"
	"strconv"
	"strings"
	"time"
)

// HandleTableCell 前端d毛需要我根据合并单元格处理掉多余的单元格
func HandleTableCell(oldTableData TableData) (newTableData TableData) {
	newTableData = oldTableData

	mergeList := oldTableData.MergeList
	lenMergeList := len(mergeList)
	if lenMergeList <= 0 {
		return
	}

	deleteRowMap := make(map[int]map[int]int)
	deleteRowList := make([]int, 0)
	deleteColumnMap := make(map[int][]int, 0)
	for i := lenMergeList - 1; i >= 0; i-- {
		tmpMerge := mergeList[i]

		//
		for rowIndex := tmpMerge.MergeRowNum; rowIndex >= 0; rowIndex-- {
			tmpColumnMap := make(map[int]int)
			if columnMap, ok := deleteRowMap[tmpMerge.StartRowIndex+rowIndex]; ok {
				tmpColumnMap = columnMap
			} else {
				deleteRowList = append(deleteRowList, tmpMerge.StartRowIndex+rowIndex)
			}

			deleteColumnList := make([]int, 0)
			if columnList, ok := deleteColumnMap[tmpMerge.StartRowIndex+rowIndex]; ok {
				deleteColumnList = columnList
			}

			for columnIndex := tmpMerge.MergeColumnNum; columnIndex >= 0; columnIndex-- {
				if rowIndex == 0 && columnIndex == 0 {
					continue
				}
				tmpColumnMap[tmpMerge.StartColumnIndex+columnIndex] = tmpMerge.StartColumnIndex + columnIndex
				deleteColumnList = append(deleteColumnList, tmpMerge.StartColumnIndex+columnIndex)
			}

			// 待删除的行
			deleteRowMap[tmpMerge.StartRowIndex+rowIndex] = tmpColumnMap
			// 该行待删除的列
			deleteColumnMap[tmpMerge.StartRowIndex+rowIndex] = deleteColumnList
		}
	}

	sort.Ints(deleteRowList)

	for i := len(deleteRowList) - 1; i >= 0; i-- {
		rowIndex := deleteRowList[i]
		deleteColumnList := deleteColumnMap[rowIndex]
		sort.Ints(deleteColumnList)
		for i := len(deleteColumnList) - 1; i >= 0; i-- {
			columnIndex := deleteColumnList[i]

			// 最后一行合并单元格时，就不再次移除合并的单元格，避免数组越界
			if rowIndex >= len(newTableData.TableDataList) {
				continue
			}
			tmpColumnDataList := newTableData.TableDataList[rowIndex]

			// 最后一列合并单元格时，就不再次移除合并的单元格，避免数组越界
			if columnIndex >= len(tmpColumnDataList) {
				continue
			}
			newTableData.TableDataList[rowIndex] = append(tmpColumnDataList[:columnIndex], tmpColumnDataList[columnIndex+1:]...) // 删除开头N个元素
			//fmt.Println("row:", rowIndex, "===column:", columnIndex)
		}
	}

	// 数据值处理
	for rowIndex, colList := range newTableData.TableDataList {
		for colIndex, v := range colList {
			v.Monitor = handleCellVal(v)
			colList[colIndex] = v
		}
		newTableData.TableDataList[rowIndex] = colList
	}
	return
}

// handleCellVal 处理单元格数据
func handleCellVal(tmpTableColData LuckySheetDataValue) (valueStr string) {
	valueStr = tmpTableColData.Monitor
	if valueStr == `` {
		//valueStr = fmt.Sprint(cellInfo.Value)
		if valueStr == `` && tmpTableColData.CellType.S != nil {
			//不是设置在单元格上面，而是设置在文本上
			for _, cellS := range tmpTableColData.CellType.S {
				valueStr += fmt.Sprint(cellS.Value)
			}
		}
	}
	return
}

// HandleRuleToTableCell 根据管理规则渲染单元格数据
func HandleRuleToTableCell(excelInfoId int, oldTableData TableData) (newTableData TableData, err error) {
	newTableData = oldTableData
	excelRuleMappingList, err := excel.GetExcelRuleMappingByExcelInfoId(excelInfoId)
	if err != nil {
		return
	}
	if len(excelRuleMappingList) == 0 {
		return
	}
	tableDataList := oldTableData.TableDataList
	excelRuleMap := make(map[int]*excel.ExcelInfoRuleMappingView)
	for _, v := range excelRuleMappingList {
		excelRuleMap[v.ExcelInfoRuleMappingId] = v
	}
	ruleScopeMap := generateRuleScopeIndexMap(excelRuleMappingList)
	for row, scopeValues := range ruleScopeMap {
		for col, ruleId := range scopeValues {
			if v, ok := excelRuleMap[ruleId]; ok {
				if len(tableDataList) > row && len(tableDataList[row]) > col {
					// 符合管理规则要求，则进行字体和背景颜色的渲染
					if checkCellRule(v, tableDataList[row][col].Monitor, tableDataList) {
						tableDataList[row][col].Background = v.BackgroundColor
						tableDataList[row][col].FontColor = v.FontColor
					}
				} else {
					continue
				}
			} else {
				continue
			}
		}
	}
	return
}

func getCellValueByType(value string, valueType int, tableDataList [][]LuckySheetDataValue) (float64, bool) {
	if valueType == 2 {
		coords := strings.Split(value, ",")
		var coordIntArr []int
		for _, v := range coords {
			t, _ := strconv.Atoi(v)
			coordIntArr = append(coordIntArr, t)
		}
		if len(coordIntArr) == 2 {
			x, y := coordIntArr[0]-1, coordIntArr[1]-1
			conditionValue, err := strconv.ParseFloat(tableDataList[y][x].Monitor, 64)
			if err != nil {
				return 0, false
			}
			return conditionValue, true
		}
	} else {
		conditionValue, err := strconv.ParseFloat(value, 64)
		if err != nil {
			return 0, false
		}
		return conditionValue, true
	}
	return 0, false
}

func checkCellRule(ruleInfo *excel.ExcelInfoRuleMappingView, value string, tableDataList [][]LuckySheetDataValue) bool {
	var tableValue float64
	var tableTime time.Time
	var err error

	if ruleInfo.RuleType == 5 {
		tableTime, err = time.ParseInLocation(utils.FormatDate, value, time.Local)
		if err != nil {
			return false
		}
	} else {
		tableValue, err = strconv.ParseFloat(value, 64)
		if err != nil {
			return false
		}
	}
	switch ruleInfo.RuleType {
	// 大于
	case 1:
		conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
		if !ok {
			return false
		}
		if tableValue > conditionValue {
			return true
		}
	// 小于
	case 2:
		conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
		if !ok {
			return false
		}
		if tableValue < conditionValue {
			return true
		}
	// 介于
	case 3:
		leftcondValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
		if !ok {
			return false
		}
		rightcondValue, ok := getCellValueByType(ruleInfo.RightValueBack, ruleInfo.RightValueType, tableDataList)
		if !ok {
			return false
		}
		if leftcondValue <= tableValue && tableValue <= rightcondValue {
			return true
		}
	// 等于
	case 4:
		conditionValue, ok := getCellValueByType(ruleInfo.LeftValueBack, ruleInfo.LeftValueType, tableDataList)
		if !ok {
			return false
		}
		if tableValue == conditionValue {
			return true
		}
	// 发生日期
	case 5:
		// 发生日期
		var dateStart, dataEnd time.Time
		switch ruleInfo.LeftValueBack {
		// 今天
		case "today":
			// 获得今天的零点零分零秒
			dateStart = utils.Today()
			if tableTime == dateStart {
				return true
			}
		// 明天
		case "tomorrow":
			dateStart = utils.Tomorrow()
			if tableTime == dateStart {
				return true
			}
		// 最近7天
		case "last7days":
			dateStart, dataEnd = utils.Last7Days()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 上周
		case "lastweek":
			dateStart, dataEnd = utils.LastWeek()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 本周
		case "thisweek":
			dateStart, dataEnd = utils.ThisWeek()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 下周
		case "nextweek":
			dateStart, dataEnd = utils.NextWeek()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 上月
		case "lastmonth":
			dateStart, dataEnd = utils.LastMonth()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 本月
		case "thismonth":
			dateStart, dataEnd = utils.ThisMonth()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		// 下月
		case "nextmonth":
			dateStart, dataEnd = utils.NextMonth()
			if tableTime.After(dateStart) && tableTime.Before(dataEnd) {
				return true
			}
		default:
			return false
		}

	}
	return false
}

func generateRuleScopeIndexMap(items []*excel.ExcelInfoRuleMappingView) (ruleScopeMap map[int]map[int]int) {
	ruleScopeMap = make(map[int]map[int]int)
	for _, item := range items {
		coords := strings.Split(item.ScopeCoord, ",")
		var coordIntArr []int
		for _, v := range coords {
			t, _ := strconv.Atoi(v)
			coordIntArr = append(coordIntArr, t)
		}

		if len(coords) == 4 {
			xmin, ymin, xmax, ymax := coordIntArr[0]-1, coordIntArr[1]-1, coordIntArr[2]-1, coordIntArr[3]-1
			for i := ymin; i <= ymax; i++ {
				for j := xmin; j <= xmax; j++ {
					if _, ok := ruleScopeMap[i]; !ok {
						ruleScopeMap[i] = make(map[int]int)
					}
					ruleScopeMap[i][j] = item.ExcelInfoRuleMappingId
				}
			}
		}
		if len(coords) == 2 {
			x, y := coordIntArr[0]-1, coordIntArr[1]-1
			if _, ok := ruleScopeMap[y]; !ok {
				ruleScopeMap[y] = make(map[int]int)
			}
			ruleScopeMap[y][x] = item.ExcelInfoRuleMappingId
		}
	}
	return
}