eta_chart_lib/services/excel/lucky_sheet.go

package excel

import (
	"encoding/json"
	"eta/eta_chart_lib/models/request"
	"eta/eta_chart_lib/utils"
	"fmt"
	"reflect"
	"sort"
	"strconv"
)

type LuckySheetDataBak struct {
	CalcChain []interface{}        `json:"calcChain" description:"公式链"`
	CellData  []LuckySheetCellData `json:"celldata" description:"单元格数据"`
	ChWidth   int64                `json:"ch_width" description:"工作表区域的宽度"`
	Config    struct {
		BorderInfo []struct {
			BorderType string `json:"borderType" description:""`
			Color      string `json:"color" description:""`
			Range      []struct {
				Column []int64 `json:"column" description:""`
				Row    []int64 `json:"row" description:""`
			} `json:"range" description:""`
			RangeType string `json:"rangeType" description:""`
			Style     string `json:"style" description:""`
			Value     struct {
				B struct {
					Color string `json:"color" description:""`
					Style string `json:"style" description:""`
				} `json:"b" description:""`
				ColIndex int64 `json:"col_index" description:""`
				L        struct {
					Color string `json:"color" description:""`
					Style string `json:"style" description:""`
				} `json:"l" description:""`
				R struct {
					Color string `json:"color" description:""`
					Style string `json:"style" description:""`
				} `json:"r" description:""`
				RowIndex int64 `json:"row_index" description:""`
				T        struct {
					Color string `json:"color" description:""`
					Style string `json:"style" description:""`
				} `json:"t" description:""`
			} `json:"value" description:"" description:""`
		} `json:"borderInfo" description:""`
		Colhidden    struct{}           `json:"colhidden" description:""`
		Columnlen    map[string]float64 `json:"columnlen" description:""`
		CustomHeight struct {
			Zero int64 `json:"0"`
		} `json:"customHeight" description:""`
		CustomWidth struct {
			Two int64 `json:"2" description:""`
		} `json:"customWidth" description:""`
		Merge  struct{}           `json:"merge" description:""`
		Rowlen map[string]float64 `json:"rowlen" description:""`
	} `json:"config" description:""`
	Data [][]struct {
		Ct struct {
			Fa string `json:"fa"`
			T  string `json:"t"`
		} `json:"ct"`
		M string      `json:"m"`
		V interface{} `json:"v"`
	} `json:"data" description:""`
	DataVerification              struct{}      `json:"dataVerification" description:""`
	Filter                        interface{}   `json:"filter" description:""`
	FilterSelect                  interface{}   `json:"filter_select" description:""`
	Hyperlink                     struct{}      `json:"hyperlink" description:""`
	Images                        struct{}      `json:"images" description:""`
	Index                         string        `json:"index" description:""`
	JfgirdSelectSave              []interface{} `json:"jfgird_select_save" description:""`
	LuckysheetAlternateformatSave []interface{} `json:"luckysheet_alternateformat_save" description:""`
	LuckysheetConditionformatSave []interface{} `json:"luckysheet_conditionformat_save" description:""`
	LuckysheetSelectSave          []struct {
		Column      []int64 `json:"column" description:""`
		ColumnFocus int64   `json:"column_focus" description:""`
		Height      int64   `json:"height" description:""`
		HeightMove  int64   `json:"height_move" description:""`
		Left        int64   `json:"left" description:""`
		LeftMove    int64   `json:"left_move" description:""`
		Row         []int64 `json:"row" description:""`
		RowFocus    int64   `json:"row_focus" description:""`
		Top         int64   `json:"top" description:""`
		TopMove     int64   `json:"top_move" description:""`
		Width       int64   `json:"width" description:""`
		WidthMove   int64   `json:"width_move" description:""`
	} `json:"luckysheet_select_save" description:"" description:""`
	LuckysheetSelectionRange []struct {
		Column []int64 `json:"column" description:""`
		Row    []int64 `json:"row" description:""`
	} `json:"luckysheet_selection_range" description:""`
	RhHeight          float64 `json:"rh_height" description:""`
	ScrollLeft        float64 `json:"scrollLeft" description:""`
	ScrollTop         float64 `json:"scrollTop" description:""`
	Status            int64   `json:"status" description:""`
	Visibledatacolumn []int64 `json:"visibledatacolumn" description:""`
	Visibledatarow    []int64 `json:"visibledatarow" description:""`
	ZoomRatio         float64 `json:"zoomRatio" description:"sheet缩放比例"`
}

// LuckySheetData sheet表格数据
type LuckySheetData struct {
	CellData []LuckySheetCellData `json:"celldata" description:"单元格数据"`
	ChWidth  int64                `json:"ch_width" description:"工作表区域的宽度"`
	Config   LuckySheetDataConfig `json:"config" description:""`
	//Index             int                  `json:"index" description:"工作表索引"`
	RhHeight          float64     `json:"rh_height" description:"工作表区域的高度"`
	ScrollLeft        float64     `json:"scrollLeft" description:"左右滚动条位置"`
	ScrollTop         float64     `json:"scrollTop" description:"上下滚动条位置"`
	Status            interface{} `json:"status" description:"激活状态"`
	VisibleDataColumn []int64     `json:"visibledatacolumn" description:"所有列的位置信息，递增的列位置数据，初始化无需设置"`
	VisibleDataRow    []int64     `json:"visibledatarow" description:"所有行的位置信息，递增的行位置数据，初始化无需设置"`
	ZoomRatio         float64     `json:"zoomRatio" description:"sheet缩放比例"`
}

// LuckySheetDataConfig sheet表单的配置
type LuckySheetDataConfig struct {
	BorderInfo []LuckySheetDataConfigBorderInfo `json:"borderInfo" description:"边框"`
	Colhidden  map[string]int64                 `json:"colhidden" description:"隐藏列，示例值：\"colhidden\":{\"30\":0,\"31\":0}"`
	Columnlen  map[string]float64               `json:"columnlen" description:"每个单元格的列宽"`
	//CustomHeight struct {
	//	Zero int64 `json:"0"`
	//} `json:"customHeight" description:""`
	//CustomWidth struct {
	//	Two int64 `json:"2" description:""`
	//} `json:"customWidth" description:""`
	Merge  map[string]LuckySheetDataConfigMerge `json:"merge" description:"合并单元格"`
	Rowlen map[string]float64                   `json:"rowlen" description:"每个单元格的行高"`
}

// LuckySheetDataConfigMerge 合并单元格设置
type LuckySheetDataConfigMerge struct {
	Row    int `json:"r" description:"行数"`
	Column int `json:"c" description:"列数"`
	Rs     int `json:"rs" description:"合并的行数"`
	Cs     int `json:"cs" description:"合并的列数"`
}

// LuckySheetDataConfigBorderInfo 单元格边框信息
type LuckySheetDataConfigBorderInfo struct {
	BorderType string `json:"borderType" description:"边框类型 border-left | border-right | border-top | border-bottom | border-all | border-outside | border-inside | border-horizontal | border-vertical | border-none"`
	Color      string `json:"color" description:"边框颜色 color: 16进制颜色值"`
	Range      []struct {
		Column []int64 `json:"column" description:"行"`
		Row    []int64 `json:"row" description:"列"`
	} `json:"range" description:"选区范围 range: 行列信息数组"`
	RangeType string                                  `json:"rangeType" description:"选区 rangeType: range | cell "`
	Style     string                                  `json:"style" description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ，和aspose.cells的getLineStyle()的值对应的话，需要自己做个转换，参考 aspose.cells"`
	Value     LuckySheetDataConfigBorderInfoCellValue `json:"value" description:"" description:"范围类型分单个单元格的数据"`
}

// LuckySheetDataConfigBorderInfoCellValue 单元格边框信息（范围类型为：单个单元格）
type LuckySheetDataConfigBorderInfoCellValue struct {
	B        LuckySheetDataConfigBorderInfoCell `json:"b" description:"下边框"`
	L        LuckySheetDataConfigBorderInfoCell `json:"l" description:"左边框"`
	R        LuckySheetDataConfigBorderInfoCell `json:"r" description:"右边框"`
	T        LuckySheetDataConfigBorderInfoCell `json:"t" description:"上边框"`
	ColIndex int64                              `json:"col_index" description:"第几行"`
	RowIndex int64                              `json:"row_index" description:"第几列"`
}

// LuckySheetDataConfigBorderInfoCell 单元格边框信息（cell类型）
type LuckySheetDataConfigBorderInfoCell struct {
	Color string      `json:"color" description:"边框颜色 color: 16进制颜色值"`
	Style int         `description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ，和aspose.cells的getLineStyle()的值对应的话，需要自己做个转换，参考 aspose.cells"`
	Sl    interface{} `json:"style" description:"边框粗细 style: 1 Thin | 2 Hair | 3 Dotted | 4 Dashed | 5 DashDot | 6 DashDotDot | 7 Double | 8 Medium | 9 MediumDashed | 10 MediumDashDot | 11 MediumDashDotDot | 12 SlantedDashDot | 13 Thick ，和aspose.cells的getLineStyle()的值对应的话，需要自己做个转换，参考 aspose.cells"`
}

// LuckySheetCellData 单个单元格数据
type LuckySheetCellData struct {
	Col   int64               `json:"c" description:"列"`
	Row   int64               `json:"r" description:"行"`
	Value LuckySheetDataValue `json:"v" description:"单元格内值的数据"`
}

// LuckySheetDataValue 单元格内值的数据
type LuckySheetDataValue struct {
	CellType       LuckySheetDataCellType `json:"ct" description:"单元格值格式：文本、时间等	"`
	Value          interface{}            `json:"v" description:"原始值"`
	Monitor        string                 `json:"m" description:"显示值"`
	Background     string                 `json:"bg" description:"背景色,实例值：#fff000"`
	FontFamily     int                    `description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书	"`
	FF             interface{}            `json:"ff" description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书	"`
	FontColor      string                 `json:"fc" description:"字体颜色,示例值：#fff000" `
	Bold           int                    `json:"bl" description:"粗体，0 常规 、 1加粗	"`
	Italic         int                    `json:"it" description:"斜体，0 常规 、 1 斜体"`
	Fontsize       int                    `description:"字体大小，14"`
	CancelLine     int                    ` description:"删除线,	0 常规 、 1 删除线"`
	HorizontalType int                    `description:"水平对齐,	0 居中、1 左、2右"`
	VerticalType   int                    ` description:"垂直对齐,	0 中间、1 上、2下"`
	Fs             interface{}            `json:"fs" description:"字体大小，14"`
	Cl             interface{}            `json:"cl" description:"删除线,	0 常规 、 1 删除线"`
	Ht             interface{}            `json:"ht" description:"水平对齐,	0 居中、1 左、2右"`
	Vt             interface{}            `json:"vt" description:"垂直对齐,	0 中间、1 上、2下"`
	//TextRotate     string                    `json:"tr" description:"竖排文字,	3"`
	//RotateText     string                    `json:"rt" description:"文字旋转角度,	介于0~180之间的整数，包含0和180"`
	TextBeak  int                       `description:"文本换行，	0 截断、1溢出、2 自动换行"`
	Tb        interface{}               `json:"tb" description:"文本换行，	0 截断、1溢出、2 自动换行"`
	Ps        LuckySheetDataCellComment `json:"ps" description:"批注"`
	Function  string                    `json:"f" description:"公式"`
	MergeCell LuckySheetDataConfigMerge `json:"mc" description:"合并单元格信息"`
}

// LuckySheetDataCellType 单元格值格式：文本、时间等
type LuckySheetDataCellType struct {
	Fa string `json:"fa" description:"格式名称，例如：“General”为自动格式"`
	T  string `json:"t" description:"格式类型，例如：“n”为数字类型"`
	S  []struct {
		FontFamily     int         `description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书	"`
		FF             interface{} `json:"ff" description:"字体,0 Times New Roman、 1 Arial、2 Tahoma 、3 Verdana、4 微软雅黑、5 宋体（Song）、6 黑体（ST Heiti）、7 楷体（ST Kaiti）、 8 仿宋（ST FangSong）、9 新宋体（ST Song）、10 华文新魏、11 华文行楷、12 华文隶书	"`
		FontColor      string      `json:"fc" description:"字体颜色,示例值：#fff000" `
		Fontsize       int         `description:"字体大小，14"`
		CancelLine     int         ` description:"删除线,	0 常规 、 1 删除线"`
		HorizontalType int         `description:"水平对齐,	0 居中、1 左、2右"`
		VerticalType   int         `description:"垂直对齐,	0 中间、1 上、2下"`
		Fs             interface{} `json:"fs" description:"字体大小，14"`
		Cl             interface{} `json:"cl" description:"删除线,	0 常规 、 1 删除线"`
		Ht             interface{} `json:"ht" description:"水平对齐,	0 居中、1 左、2右"`
		Vt             interface{} `json:"vt" description:"垂直对齐,	0 中间、1 上、2下"`
		Un             interface{} `json:"un" description:""`
		Bold           interface{} `json:"bl" description:"粗体，0 常规 、 1加粗	"`
		Italic         interface{} `json:"it" description:"斜体，0 常规 、 1 斜体"`
		Value          interface{} `json:"v" description:"原始值"`
	} `json:"s"`
}

// LuckySheetDataCellComment 批注
type LuckySheetDataCellComment struct {
	Left   int    `json:"left" description:"批注框距离左边工作表边缘位置"`
	Top    int    `json:"top" description:"批注框距离上边工作表边缘位置"`
	Width  int    `json:"width" description:"批注框宽度"`
	Height int    `json:"height" description:"批注框高度"`
	Value  string `json:"value" description:"批注内容"`
	IsShow bool   `json:"isshow" description:"是否显示批注"`
}

// GetLuckySheetData 获取LuckySheetData的结构体
func GetLuckySheetData(jsonStr string) (item *LuckySheetData, err error) {
	err = json.Unmarshal([]byte(jsonStr), &item)
	for k, v := range item.CellData {
		value := v.Value
		value.Fontsize = getIntValueByInterface(value.Fs)
		value.CancelLine = getIntValueByInterface(value.Cl)
		value.HorizontalType = getIntValueByInterface(value.Ht)
		value.VerticalType = getIntValueByInterface(value.Vt)
		value.FontFamily = getIntValueByInterface(value.FF)
		value.TextBeak = getIntValueByInterface(value.Tb)

		if len(value.CellType.S) > 0 {
			for kk, vv := range value.CellType.S {
				vv.Fontsize = getIntValueByInterface(vv.Fs)
				vv.CancelLine = getIntValueByInterface(vv.Cl)
				vv.HorizontalType = getIntValueByInterface(vv.Ht)
				vv.VerticalType = getIntValueByInterface(vv.Vt)
				vv.FontFamily = getIntValueByInterface(vv.FF)
				value.CellType.S[kk] = vv
			}
		}

		item.CellData[k].Value = value
	}

	//边框
	if len(item.Config.BorderInfo) > 0 {
		for k, v := range item.Config.BorderInfo {
			v.Value.T.Style = getIntValueByInterface(v.Value.T.Style)
			v.Value.B.Style = getIntValueByInterface(v.Value.B.Style)
			v.Value.L.Style = getIntValueByInterface(v.Value.L.Style)
			v.Value.R.Style = getIntValueByInterface(v.Value.R.Style)
			item.Config.BorderInfo[k] = v
		}
	}
	return
}

// 兼容前端js传递的数据类型
func getIntValueByInterface(valInterface interface{}) (val int) {
	if valInterface == nil {
		return
	}
	switch reflect.TypeOf(valInterface).Kind() {
	case reflect.String:
		tmpValue := reflect.ValueOf(valInterface).String()
		tmpValInt, err := strconv.Atoi(tmpValue)
		if err != nil {
			val = 0
		} else {
			val = tmpValInt
		}
	case reflect.Int, reflect.Int32, reflect.Int64:
		tmpValue := reflect.ValueOf(valInterface).Int()
		val = int(tmpValue)
	}
	return
}

// TableData 表格数据
type TableData struct {
	TableDataList     [][]LuckySheetDataValue
	RowWidthList      []float64
	RowHeightList     []float64
	RemoveTopRow      int              `description:"移除表格上方的行数"`
	RemoveBottomRow   int              `description:"移除表格下方的行数"`
	RemoveLeftColumn  int              `description:"移除表格左侧的列数"`
	RemoveRightColumn int              `description:"移除表格右侧的列数"`
	MergeList         []TableDataMerge `description:"合并数据列"`
}

// TableDataMerge 表格数据合并单元格配置
type TableDataMerge struct {
	StartRowIndex    int `json:"start_row_index" description:"开始的行下标"`
	StartColumnIndex int `json:"start_column" description:"开始的列下标"`
	MergeRowNum      int `json:"merge_row_num" description:"合并的行数"`
	MergeColumnNum   int `json:"merge_column_num" description:"合并的列数"`
}

// GetTableDataByLuckySheetDataStr 通过LuckySheet的string数据获取表格数据
func (item *LuckySheetData) GetTableDataByLuckySheetDataStr() (selfTableData TableData, err error) {
	luckySheetCellDataList := item.CellData
	// 表格数据
	tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
	// 最大行,最大列
	var maxRow, maxCol int64
	for _, v := range luckySheetCellDataList {
		//fmt.Println("row:", v.Row, "=====col:", v.Col)
		if v.Row > maxRow { //最大行
			maxRow = v.Row
		}
		if v.Col > maxCol { //最大列
			maxCol = v.Col
		}
		var tmpRow map[int64]LuckySheetDataValue

		tmpRow, ok := tableDataMap[v.Row]
		if ok {
			tmpRow[v.Col] = v.Value
		} else {
			tmpRow = make(map[int64]LuckySheetDataValue)
			tmpRow[v.Col] = v.Value
		}
		tableDataMap[v.Row] = tmpRow
	}

	tableDataList := make([][]LuckySheetDataValue, 0)
	var i int64

	// 单元格宽度
	configColumnConf := item.Config.Columnlen
	rowWidthMap := make(map[int]float64)
	rowWidthList := make([]float64, 0) //

	// 单元格高度
	configRowConf := item.Config.Rowlen
	rowHeightList := make([]float64, 0) //

	for i = 0; i <= maxRow; i++ {
		//列
		tmpTableColDataList := make([]LuckySheetDataValue, 0)
		// 每个单元格的高度
		tmpHeight, ok := configRowConf[fmt.Sprint(i)]
		if !ok {
			tmpHeight = 0
		}
		rowHeightList = append(rowHeightList, tmpHeight)

		tmpRowData, ok := tableDataMap[i]
		// 如果没有该行数据，那么就默认添加空行数据处理
		if !ok {
			tmpRowData = make(map[int64]LuckySheetDataValue)
		}

		var j int64
		for j = 0; j <= maxCol; j++ {
			tmpTableColData, ok := tmpRowData[j]
			if !ok {
				tmpTableColData = LuckySheetDataValue{}
			}

			//单元格显示的数据处理
			tmpTableColData.Monitor = handleCellVal(tmpTableColData)

			tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)

			// 每个单元格的宽度
			tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
			if !ok {
				tmpWidth = 0
			}
			rowIndex := len(tmpTableColDataList) - 1
			if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
				rowWidthList = append(rowWidthList, tmpWidth)
			}
			rowWidthMap[rowIndex] = tmpWidth
		}

		tableDataList = append(tableDataList, tmpTableColDataList)
	}

	// 数据处理，移除上下左右空行空列
	tableDataList, tableRemoveNum, rowHeightList, rowWidthList, tableDataMergeList := handleTableDataList(tableDataList, item.Config.Merge, rowHeightList, rowWidthList)

	// 表格数据
	{
		// 移除空白单元格信息
		selfTableData.RemoveTopRow = tableRemoveNum.RemoveTopRow
		selfTableData.RemoveBottomRow = tableRemoveNum.RemoveBottomRow
		selfTableData.RemoveLeftColumn = tableRemoveNum.RemoveLeftColumn
		selfTableData.RemoveRightColumn = tableRemoveNum.RemoveRightColumn
	}
	selfTableData.TableDataList = tableDataList
	selfTableData.RowWidthList = rowWidthList
	selfTableData.RowHeightList = rowHeightList
	selfTableData.MergeList = tableDataMergeList

	return
}

// TableRemoveNum 上下左右移除的空行空列数量
type TableRemoveNum struct {
	RemoveTopRow      int `description:"移除表格上方的行数"`
	RemoveBottomRow   int `description:"移除表格下方的行数"`
	RemoveLeftColumn  int `description:"移除表格左侧的列数"`
	RemoveRightColumn int `description:"移除表格右侧的列数"`
}

// handleTableDataList 表格数据处理（移除上下左右的空行空列）
func handleTableDataList(tableDataList [][]LuckySheetDataValue, luckySheetDataConfigMergeList map[string]LuckySheetDataConfigMerge, rowHeightList, rowWidthList []float64) ([][]LuckySheetDataValue, TableRemoveNum, []float64, []float64, []TableDataMerge) {

	var removeTopRow, removeBottomRow, removeLeftColumn, removeRightColumn int //上下左右需要移除的空行空列
	tableDataMergeList := make([]TableDataMerge, 0)                            //待合并的单元格信息

	//总共多少行
	lenRow := len(tableDataList)
	//移除上下空行
	deleteRowIndexList := make([]int, 0)
	if lenRow > 0 {
		var flag = false
		// 移除上方空列
		for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
			isDelete := true
			for _, v := range tableDataList[rowIndex] {
				if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				removeTopRow++
				deleteRowIndexList = append(deleteRowIndexList, rowIndex)
			}
		}

		// 移除下方空行
		flag = false
		//尾部
		deleteBottomRowIndexList := make([]int, 0)
		if len(tableDataList) > 1 {
			for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
				isDelete := true
				for _, v := range tableDataList[rowIndex] {
					if v.Monitor != `` {
						isDelete = false
						flag = true
						break
					}
				}
				if flag {
					break
				}
				if isDelete {
					deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
					removeBottomRow++
				}
			}
		}

		if len(deleteBottomRowIndexList) > 0 {
			deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
		}

		lenDeleteRow := len(deleteRowIndexList)
		if lenDeleteRow > 0 {
			for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
				//表格数据
				tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素

				//表格高度
				rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
			}
		}
	}

	//移除左右空列
	deleteColumnIndexList := make([]int, 0)
	if len(tableDataList) > 0 {
		var flag = false
		lenColumn := len(tableDataList[0])
		// 移除左边空列
		for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
			isDelete := true
			for _, v := range tableDataList {
				//如果一列都没有，说明是上面几行是空行，没有数据
				if len(v) <= 0 {
					continue
				}
				if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				removeLeftColumn++
				deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
			}
		}

		// 移除右方空列
		flag = false
		//右边
		deleteTailColumnIndexList := make([]int, 0)
		// 数据要大于1列才会处理
		if lenColumn > 1 {
			for columnIndex := lenColumn - 1; columnIndex >= 0; columnIndex-- {
				isDelete := true
				for _, v := range tableDataList {
					//如果一列都没有，说明是上面几行是空行，没有数据
					if len(v) <= 0 {
						continue
					}
					if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
						isDelete = false
						flag = true
						break
					}
				}
				if flag {
					break
				}
				if isDelete {
					deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
					removeRightColumn++
				}
			}
		}

		if len(deleteTailColumnIndexList) > 0 {
			deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
		}

		lenDeleteColumn := len(deleteColumnIndexList)
		if lenDeleteColumn > 0 {
			for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
				//表格数据
				for k, v := range tableDataList {
					tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
				}

				//表格宽度
				rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
			}
		}
	}

	//处理合并单元格
	for _, v := range luckySheetDataConfigMergeList {
		indexRow := v.Row - removeTopRow
		indexColumn := v.Column - removeLeftColumn

		tableDataMerge := TableDataMerge{
			StartRowIndex:    indexRow,
			StartColumnIndex: indexColumn,
			MergeRowNum:      v.Rs - 1,
			MergeColumnNum:   v.Cs - 1,
		}
		tableDataMergeList = append(tableDataMergeList, tableDataMerge)
	}

	return tableDataList, TableRemoveNum{
		RemoveTopRow:      removeTopRow,
		RemoveBottomRow:   removeBottomRow,
		RemoveLeftColumn:  removeLeftColumn,
		RemoveRightColumn: removeRightColumn,
	}, rowHeightList, rowWidthList, tableDataMergeList
}

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

type CellPosition struct {
	RowIndex    int
	ColumnIndex int
}

// 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 k, v := range deleteRowList {
	//	fmt.Println("k:", k, "v:", v)
	//}
	//return
	//fmt.Println("===============")

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

	return
}

// GetTableDataByLuckySheetDataStrBak 通过LuckySheet的string数据获取表格数据（备份：2022-08-23 10:30:32）
func (item *LuckySheetData) GetTableDataByLuckySheetDataStrBak() (selfTableData TableData, err error) {
	luckySheetCellDataList := item.CellData
	// 表格数据
	tableDataMap := make(map[int64]map[int64]LuckySheetDataValue)
	// 最大行,最大列
	var maxRow, maxCol int64
	for _, v := range luckySheetCellDataList {
		//fmt.Println("row:", v.Row, "=====col:", v.Col)
		if v.Row > maxRow { //最大行
			maxRow = v.Row
		}
		if v.Col > maxCol { //最大列
			maxCol = v.Col
		}
		var tmpRow map[int64]LuckySheetDataValue

		tmpRow, ok := tableDataMap[v.Row]
		if ok {
			tmpRow[v.Col] = v.Value
		} else {
			tmpRow = make(map[int64]LuckySheetDataValue)
			tmpRow[v.Col] = v.Value
		}
		tableDataMap[v.Row] = tmpRow
	}

	tableDataList := make([][]LuckySheetDataValue, 0)
	var i int64

	// 单元格宽度
	configColumnConf := item.Config.Columnlen
	rowWidthMap := make(map[int]float64)
	rowWidthList := make([]float64, 0) //

	// 单元格高度
	configRowConf := item.Config.Rowlen
	rowHeightList := make([]float64, 0) //

	for i = 0; i <= maxRow; i++ {
		//列
		tmpTableColDataList := make([]LuckySheetDataValue, 0)
		// 每个单元格的高度
		tmpHeight, ok := configRowConf[fmt.Sprint(i)]
		if !ok {
			tmpHeight = 0
		}
		rowHeightList = append(rowHeightList, tmpHeight)

		tmpRowData, ok := tableDataMap[i]
		// 如果没有该行数据，那么就默认添加空行数据处理
		if !ok {
			tmpRowData = make(map[int64]LuckySheetDataValue)
		}

		var j int64
		for j = 0; j <= maxCol; j++ {
			tmpTableColData, ok := tmpRowData[j]
			if !ok {
				tmpTableColData = LuckySheetDataValue{}
			}

			//单元格显示的数据处理
			tmpTableColData.Monitor = handleCellVal(tmpTableColData)

			tmpTableColDataList = append(tmpTableColDataList, tmpTableColData)

			// 每个单元格的宽度
			tmpWidth, ok := configColumnConf[fmt.Sprint(j)]
			if !ok {
				tmpWidth = 0
			}
			rowIndex := len(tmpTableColDataList) - 1
			if _, ok2 := rowWidthMap[rowIndex]; !ok2 {
				rowWidthList = append(rowWidthList, tmpWidth)
			}
			rowWidthMap[rowIndex] = tmpWidth
		}

		tableDataList = append(tableDataList, tmpTableColDataList)
	}

	//总共多少行
	lenRow := len(tableDataList)

	//移除上下空行
	deleteRowIndexList := make([]int, 0)
	if lenRow > 0 {
		var flag = false
		// 移除上方空列
		for rowIndex := 0; rowIndex < lenRow; rowIndex++ {
			isDelete := true
			for _, v := range tableDataList[rowIndex] {
				if v.Monitor != `` {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				selfTableData.RemoveTopRow++
				deleteRowIndexList = append(deleteRowIndexList, rowIndex)
			}
		}

		// 移除下方空行
		flag = false
		//尾部
		deleteBottomRowIndexList := make([]int, 0)
		for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
			isDelete := true
			for _, v := range tableDataList[rowIndex] {
				if v.Monitor != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
				selfTableData.RemoveBottomRow++
			}
		}

		if len(deleteBottomRowIndexList) > 0 {
			deleteRowIndexList = append(deleteRowIndexList, utils.RevSlice(deleteBottomRowIndexList)...)
		}

		lenDeleteRow := len(deleteRowIndexList)
		if lenDeleteRow > 0 {
			for rowIndex := lenDeleteRow - 1; rowIndex >= 0; rowIndex-- {
				//表格数据
				tableDataList = append(tableDataList[:deleteRowIndexList[rowIndex]], tableDataList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素

				//表格高度
				rowHeightList = append(rowHeightList[:deleteRowIndexList[rowIndex]], rowHeightList[deleteRowIndexList[rowIndex]+1:]...) // 删除开头N个元素
			}
		}
	}

	//移除前后空列
	deleteColumnIndexList := make([]int, 0)
	if len(tableDataList) > 0 {
		var flag = false
		lenColumn := len(tableDataList[0])
		// 移除前方空列
		for columnIndex := 0; columnIndex < lenColumn; columnIndex++ {
			isDelete := true
			for _, v := range tableDataList {
				//如果一列都没有，说明是上面几行是空行，没有数据
				if len(v) <= 0 {
					continue
				}
				if v[columnIndex].Monitor != `` {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				selfTableData.RemoveLeftColumn++
				deleteColumnIndexList = append(deleteColumnIndexList, columnIndex)
			}
		}

		// 移除后方空列
		flag = false
		//后方
		deleteTailColumnIndexList := make([]int, 0)
		for columnIndex := lenColumn - 1; columnIndex >= 0; columnIndex-- {
			isDelete := true
			for _, v := range tableDataList {
				if v[columnIndex].Monitor != `` || (v[columnIndex].MergeCell.Column != columnIndex && v[columnIndex].MergeCell.Column != 0) {
					isDelete = false
					flag = true
					break
				}
			}
			if flag {
				break
			}
			if isDelete {
				deleteTailColumnIndexList = append(deleteTailColumnIndexList, columnIndex)
				selfTableData.RemoveRightColumn++
			}
		}

		if len(deleteTailColumnIndexList) > 0 {
			deleteColumnIndexList = append(deleteColumnIndexList, utils.RevSlice(deleteTailColumnIndexList)...)
		}

		lenDeleteColumn := len(deleteColumnIndexList)
		if lenDeleteColumn > 0 {
			for columnIndex := lenDeleteColumn - 1; columnIndex >= 0; columnIndex-- {
				//表格数据
				for k, v := range tableDataList {
					tableDataList[k] = append(v[:deleteColumnIndexList[columnIndex]], v[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
				}

				//表格宽度
				rowWidthList = append(rowWidthList[:deleteColumnIndexList[columnIndex]], rowWidthList[deleteColumnIndexList[columnIndex]+1:]...) // 删除开头N个元素
			}
		}
	}

	//处理合并单元格
	tableDataMergeList := make([]TableDataMerge, 0)
	for _, v := range item.Config.Merge {
		indexRow := v.Row - selfTableData.RemoveTopRow
		indexColumn := v.Column - selfTableData.RemoveLeftColumn

		tableDataMerge := TableDataMerge{
			StartRowIndex:    indexRow,
			StartColumnIndex: indexColumn,
			MergeRowNum:      v.Rs - 1,
			MergeColumnNum:   v.Cs - 1,
		}
		tableDataMergeList = append(tableDataMergeList, tableDataMerge)
	}

	// 表格数据
	selfTableData.TableDataList = tableDataList
	selfTableData.RowWidthList = rowWidthList
	selfTableData.RowHeightList = rowHeightList
	selfTableData.MergeList = tableDataMergeList

	return
}

// GetTableDataByCustomData 通过自定义表格数据获取表格数据
func GetTableDataByCustomData(excelType int, data request.TableDataReq) (selfTableData TableData, err error) {
	tableDataList := make([][]LuckySheetDataValue, 0)
	mergeList := make([]TableDataMerge, 0)

	// 指标数
	lenEdb := len(data.Data)
	if lenEdb <= 0 {
		return
	}

	// 日期数
	lenCol := len(data.Data[0].Data)
	if lenCol <= 0 {
		return
	}
	// 指标列：1；指标行：2
	if excelType == 1 {
		mergeList = append(mergeList, TableDataMerge{
			StartRowIndex:    0,
			StartColumnIndex: 0,
			MergeRowNum:      1,
			MergeColumnNum:   0,
		})
		// 第一行
		{
			firstCol := make([]LuckySheetDataValue, 0)
			firstCol = append(firstCol, LuckySheetDataValue{
				Value:      "日期",
				Monitor:    "日期",
				Background: "#505b78",
				FontColor:  "#FFFFFF",
				MergeCell: LuckySheetDataConfigMerge{
					Row:    0, //行数
					Column: 0, //列数
					Rs:     2, //合并的行数
					Cs:     1, //合并的列数
				},
			})

			for _, v := range data.Data {
				edbName := v.EdbName
				if v.EdbAliasName != `` {
					edbName = v.EdbAliasName
				}
				firstCol = append(firstCol, LuckySheetDataValue{
					Value:      edbName,
					Monitor:    edbName,
					Background: "#505b78",
					FontColor:  "#FFFFFF",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, firstCol)
		}

		// 第二行
		{
			secondCol := make([]LuckySheetDataValue, 0)
			secondCol = append(secondCol, LuckySheetDataValue{})

			for _, v := range data.Data {
				name := v.Unit + " / " + v.Frequency
				secondCol = append(secondCol, LuckySheetDataValue{
					Value:      name,
					Monitor:    name,
					Background: "#505b78",
					FontColor:  "#FFFFFF",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, secondCol)
		}

		// 开始数据了
		{
			for i := 0; i < lenCol; i++ {
				dataCol := make([]LuckySheetDataValue, 0)

				// 日期
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:     data.Data[0].Data[i].DataTime,
					Monitor:   data.Data[0].Data[i].DataTime,
					MergeCell: LuckySheetDataConfigMerge{},
				})

				// 数据值
				for _, v := range data.Data {
					background := ``
					if v.Data[i].DataType == 5 {
						background = "#ffefdd"
					}
					dataCol = append(dataCol, LuckySheetDataValue{
						Value:      v.Data[i].Value,
						Monitor:    v.Data[i].ShowValue,
						MergeCell:  LuckySheetDataConfigMerge{},
						Background: background,
					})
				}

				tableDataList = append(tableDataList, dataCol)
			}
		}

		// 开始文本行了
		{
			for _, textColList := range data.TextRowData {
				dataCol := make([]LuckySheetDataValue, 0)
				for _, v := range textColList {
					dataCol = append(dataCol, LuckySheetDataValue{
						Value:     v.Value,
						Monitor:   v.ShowValue,
						MergeCell: LuckySheetDataConfigMerge{},
					})
				}
				tableDataList = append(tableDataList, dataCol)
			}
		}
	} else {
		// 指标行

		mergeList = append(mergeList, TableDataMerge{
			StartRowIndex:    0,
			StartColumnIndex: 0,
			MergeRowNum:      0,
			MergeColumnNum:   1,
		})

		// 第一行
		{
			firstCol := make([]LuckySheetDataValue, 0)
			firstCol = append(firstCol, LuckySheetDataValue{
				Value:      "日期",
				Monitor:    "日期",
				Background: "#505b78",
				FontColor:  "#FFFFFF",
				MergeCell: LuckySheetDataConfigMerge{
					Row:    0, //行数
					Column: 0, //列数
					Rs:     1, //合并的行数
					Cs:     2, //合并的列数
				},
			})
			firstCol = append(firstCol, LuckySheetDataValue{})

			// 日期列
			for _, v := range data.Data[0].Data {
				firstCol = append(firstCol, LuckySheetDataValue{
					Value:      v.DataTime,
					Monitor:    v.DataTime,
					Background: "#efeef1",
					FontColor:  "#666666",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			// 文本列
			for _, textColList := range data.TextRowData {
				firstCol = append(firstCol, LuckySheetDataValue{
					Value:      textColList[0].Value,
					Monitor:    textColList[0].ShowValue,
					Background: "#efeef1",
					FontColor:  "#666666",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, firstCol)
		}

		// 日期列+文本列+两列表头（日期这个文案表头）
		//colLen := lenCol+2+len(data.TextRowData)
		for i := 0; i < lenEdb; i++ {
			dataCol := make([]LuckySheetDataValue, 0)

			// 指标信息
			tmpEdbInfo := data.Data[i]

			// 指标名称
			{
				edbName := tmpEdbInfo.EdbName
				if tmpEdbInfo.EdbAliasName != `` {
					edbName = tmpEdbInfo.EdbAliasName
				}
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:      edbName,
					Monitor:    edbName,
					Background: "#505b78",
					FontColor:  "#FFFFFF",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			// 指标单位、频度
			{
				name := tmpEdbInfo.Unit + " / " + tmpEdbInfo.Frequency
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:      name,
					Monitor:    name,
					Background: "#505b78",
					FontColor:  "#FFFFFF",
					MergeCell:  LuckySheetDataConfigMerge{},
				})
			}

			// 指标数据列
			for _, tmpData := range tmpEdbInfo.Data {
				background := ``
				if tmpData.DataType == 5 {
					background = "#ffefdd"
				}
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:      tmpData.Value,
					Monitor:    tmpData.ShowValue,
					MergeCell:  LuckySheetDataConfigMerge{},
					Background: background,
				})
			}

			// 文本列
			for _, textColList := range data.TextRowData {
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:     textColList[i+1].Value,
					Monitor:   textColList[i+1].ShowValue,
					MergeCell: LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, dataCol)
		}

	}

	selfTableData.MergeList = mergeList
	selfTableData.TableDataList = tableDataList

	return
}

// GetTableDataByMixedTableData 通过混合表格数据获取表格数据
func GetTableDataByMixedTableData(config [][]request.MixedTableCellDataReq) (selfTableData TableData, err error) {
	tableDataList := make([][]LuckySheetDataValue, 0)
	mergeList := make([]TableDataMerge, 0)

	// 开始文本行了
	{
		for _, row := range config {
			dataCol := make([]LuckySheetDataValue, 0)
			for _, cell := range row {
				tmp := LuckySheetDataValue{
					Value:     cell.Value,
					Monitor:   cell.ShowValue,
					MergeCell: LuckySheetDataConfigMerge{},
				}
				if cell.ShowStyle != "" {
					tmp.Monitor = cell.ShowFormatValue
				}
				dataCol = append(dataCol, tmp)
			}
			tableDataList = append(tableDataList, dataCol)
		}
	}

	selfTableData.MergeList = mergeList
	selfTableData.TableDataList = tableDataList

	return
}