eta_chart_lib/services/excel/lucky_sheet.go

package excel

import (
	"encoding/json"
	"fmt"
	"hongze/hongze_chart_lib/utils"
)

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          int64   `json:"rh_height" description:""`
	ScrollLeft        int64   `json:"scrollLeft" description:""`
	ScrollTop         int64   `json:"scrollTop" description:""`
	Status            int64   `json:"status" description:""`
	Visibledatacolumn []int64 `json:"visibledatacolumn" description:""`
	Visibledatarow    []int64 `json:"visibledatarow" description:""`
	ZoomRatio         int64   `json:"zoomRatio" description:""`
}

// 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          int64   `json:"rh_height" description:"工作表区域的高度"`
	ScrollLeft        int64   `json:"scrollLeft" description:"左右滚动条位置"`
	ScrollTop         int64   `json:"scrollTop" description:"上下滚动条位置"`
	Status            int64   `json:"status" description:"激活状态"`
	VisibleDataColumn []int64 `json:"visibledatacolumn" description:"所有列的位置信息，递增的列位置数据，初始化无需设置"`
	VisibleDataRow    []int64 `json:"visibledatarow" description:"所有行的位置信息，递增的行位置数据，初始化无需设置"`
	ZoomRatio         int64   `json:"zoomRatio" description:"缩放比例"`
}

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

// 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                       `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       string                    `json:"fs" description:"字体大小，14"`
	CancelLine     int                       `json:"cl" description:"删除线,	0 常规 、 1 删除线"`
	HorizontalType string                    `json:"ht" description:"水平对齐,	0 居中、1 左、2右"`
	VerticalType   string                    `json:"vt" description:"垂直对齐,	0 中间、1 上、2下"`
	TextRotate     string                    `json:"tr" description:"竖排文字,	3"`
	RotateText     string                    `json:"rt" description:"文字旋转角度,	介于0~180之间的整数，包含0和180"`
	TextBeak       int                       `json:"tb" description:"文本换行，	0 截断、1溢出、2 自动换行"`
	Ps             LuckySheetDataCellComment `json:"ps" description:"批注"`
	Function       string                    `json:"f" description:"公式"`
	Mc             LuckySheetDataConfigMerge `json:"mc" description:"合并单元格信息"`
}

// LuckySheetDataCellType 单元格值格式：文本、时间等
type LuckySheetDataCellType struct {
	Fa string `json:"fa" description:"格式名称，例如：“General”为自动格式"`
	T  string `json:"t" description:"格式类型，例如：“n”为数字类型"`
	S  []struct {
		FontFamily     string      `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         `json:"fs" description:"字体大小，14"`
		CancelLine     int         `json:"cl" description:"删除线,	0 常规 、 1 删除线"`
		HorizontalType string      `json:"ht" description:"水平对齐,	0 居中、1 左、2右"`
		VerticalType   string      `json:"vt" description:"垂直对齐,	0 中间、1 上、2下"`
		Un             int         `json:"un" description:""`
		Bold           int         `json:"bl" description:"粗体，0 常规 、 1加粗	"`
		Italic         int         `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)
	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 {
			tableDataList = append(tableDataList, tmpTableColDataList)
			continue
		}

		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 != `` {
					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 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 != `` {
					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
}

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