package excel import ( "encoding/json" "eta/eta_chart_lib/models" "eta/eta_chart_lib/models/request" "eta/eta_chart_lib/utils" "fmt" "reflect" "sort" "strconv" "strings" "github.com/shopspring/decimal" ) 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:"instance" 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:"instance" 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) for rowIndex := lenRow - 1; rowIndex > removeTopRow; 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列不处理 for columnIndex := lenColumn - 1; columnIndex > removeLeftColumn; 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, lang string) (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) dateStr := "日期" switch lang { case utils.EnLangVersion: dateStr = "Date" } firstCol = append(firstCol, LuckySheetDataValue{ Value: dateStr, Monitor: dateStr, MergeCell: LuckySheetDataConfigMerge{ Row: 0, //行数 Column: 0, //列数 Rs: 2, //合并的行数 Cs: 1, //合并的列数 }, }) for _, v := range data.Data { edbName := v.EdbName if v.EdbAliasName != `` { edbName = v.EdbAliasName } else { switch lang { case utils.EnLangVersion: edbName = v.EdbNameEn } } firstCol = append(firstCol, LuckySheetDataValue{ Value: edbName, Monitor: edbName, MergeCell: LuckySheetDataConfigMerge{}, }) } tableDataList = append(tableDataList, firstCol) } // 第二行 { secondCol := make([]LuckySheetDataValue, 0) secondCol = append(secondCol, LuckySheetDataValue{}) for _, v := range data.Data { var name string switch lang { case utils.EnLangVersion: name = v.UnitEn + " / " + utils.GetFrequencyEn(v.Frequency) default: name = v.Unit + " / " + v.Frequency } secondCol = append(secondCol, LuckySheetDataValue{ Value: name, Monitor: name, 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" } tmpCol := LuckySheetDataValue{ Value: v.Data[i].Value, Monitor: v.Data[i].ShowValue, MergeCell: LuckySheetDataConfigMerge{}, Background: background, } if tmpCol.Monitor == `` { tmpCol.Monitor = "-" } dataCol = append(dataCol, tmpCol) } tableDataList = append(tableDataList, dataCol) } } // 开始文本行了 { for _, textColList := range data.TextRowData { dataCol := make([]LuckySheetDataValue, 0) for _, v := range textColList { tmpCol := LuckySheetDataValue{ Value: v.Value, Monitor: v.ShowValue, MergeCell: LuckySheetDataConfigMerge{}, } tmpCol.Value = v.Value if tmpCol.Monitor == "" { tmpCol.Monitor = "-" } dataCol = append(dataCol, tmpCol) } tableDataList = append(tableDataList, dataCol) } } } else { // 指标行 mergeList = append(mergeList, TableDataMerge{ StartRowIndex: 0, StartColumnIndex: 0, MergeRowNum: 0, MergeColumnNum: 1, }) // 第一行 { firstCol := make([]LuckySheetDataValue, 0) dateStr := "日期" switch lang { case utils.EnLangVersion: dateStr = "Date" } firstCol = append(firstCol, LuckySheetDataValue{ Value: dateStr, Monitor: dateStr, 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, MergeCell: LuckySheetDataConfigMerge{}, }) } // 文本列 for _, textColList := range data.TextRowData { tmpCol := LuckySheetDataValue{ Value: textColList[0].Value, Monitor: textColList[0].ShowValue, MergeCell: LuckySheetDataConfigMerge{}, } if tmpCol.Monitor == `` { tmpCol.Monitor = "-" } firstCol = append(firstCol, tmpCol) } 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 } else { switch lang { case utils.EnLangVersion: edbName = tmpEdbInfo.EdbNameEn } } dataCol = append(dataCol, LuckySheetDataValue{ Value: edbName, Monitor: edbName, MergeCell: LuckySheetDataConfigMerge{}, }) } // 指标单位、频度 { var name string switch lang { case utils.EnLangVersion: name = tmpEdbInfo.UnitEn + " / " + utils.GetFrequencyEn(tmpEdbInfo.Frequency) default: name = tmpEdbInfo.Unit + " / " + tmpEdbInfo.Frequency } dataCol = append(dataCol, LuckySheetDataValue{ Value: name, Monitor: name, MergeCell: LuckySheetDataConfigMerge{}, }) } // 指标数据列 for _, tmpData := range tmpEdbInfo.Data { background := `` if tmpData.DataType == 5 { background = "#ffefdd" } tmpCol := LuckySheetDataValue{ Value: tmpData.Value, Monitor: tmpData.ShowValue, MergeCell: LuckySheetDataConfigMerge{}, Background: background, } if tmpCol.Monitor == `` { tmpCol.Monitor = "-" } dataCol = append(dataCol, tmpCol) } // 文本列 for _, textColList := range data.TextRowData { tmpCol := LuckySheetDataValue{ Value: textColList[i+1].Value, Monitor: textColList[i+1].ShowValue, MergeCell: LuckySheetDataConfigMerge{}, } if tmpCol.Monitor == `` { tmpCol.Monitor = "-" } dataCol = append(dataCol, tmpCol) } tableDataList = append(tableDataList, dataCol) } } selfTableData.MergeList = mergeList selfTableData.TableDataList = tableDataList return } // GetTableDataByMixedTableData 通过混合表格数据获取表格数据 func GetTableDataByMixedTableData(config [][]request.MixedTableCellDataReq, hideMerged bool, excelInfoId int) (selfTableData TableData, err error) { tableDataList := make([][]LuckySheetDataValue, 0) mergeList := make([]TableDataMerge, 0) // 获取管理规则 excelRuleMappingList, err := models.GetExcelRuleMappingByExcelInfoId(excelInfoId) if err != nil { return } excelRuleMap := make(map[int]*models.ExcelInfoRuleMappingView) for _, v := range excelRuleMappingList { excelRuleMap[v.ExcelInfoRuleMappingId] = v } ruleScopeMap := generateRuleScopeIndexMap(excelRuleMappingList) // 开始文本行了 { for i, row := range config { dataCol := make([]LuckySheetDataValue, 0) for j, cell := range row { tmp := LuckySheetDataValue{ Value: cell.Value, Monitor: cell.ShowValue, MergeCell: LuckySheetDataConfigMerge{}, } if cell.DataType != request.CustomTextDT && tmp.Monitor == `` { tmp.Monitor = "-" cell.ShowFormatValue = "-" } // 前端需要隐藏被合并的单元格, 混合表格/平衡表通过这个字段判断, 不通过HandleTableCell方法隐藏 if cell.MerData != nil { if hideMerged && cell.MerData.Type == "merged" { continue } tmp.MergeCell.Rs = cell.MerData.Mer.Rowspan tmp.MergeCell.Cs = cell.MerData.Mer.Colspan tmp.MergeCell.Row = cell.MerData.Mer.Row tmp.MergeCell.Column = cell.MerData.Mer.Col } if cell.ShowStyle != "" { var styleConfig request.MixCellShowStyle if err := json.Unmarshal([]byte(cell.ShowStyle), &styleConfig); err != nil { utils.FileLog.Info("表格样式showStyle解析失败", err.Error()) } if styleConfig.BackgroundColor != "" { tmp.Background = styleConfig.BackgroundColor } if styleConfig.Color != "" { tmp.FontColor = styleConfig.Color } switch styleConfig.Align { case "center": tmp.HorizontalType = 0 tmp.Ht = 0 case "left": tmp.HorizontalType = 1 tmp.Ht = 1 case "right": tmp.HorizontalType = 2 tmp.Ht = 2 } tmp.Monitor = cell.ShowFormatValue // 如果没有showValue, 则使用value if cell.ShowValue == "" { _, err := strconv.ParseFloat(cell.Value, 64) if err == nil { cell.ShowValue = cell.Value } } _, err := strconv.ParseFloat(cell.ShowValue, 64) if err == nil { hasPercent := false if styleConfig.Nt == "percent" { hasPercent = true } // 修复历史数据 if styleConfig.Pn != 0 { if styleConfig.Decimal == nil { styleConfig.Decimal = new(int) } *styleConfig.Decimal += styleConfig.Pn if *styleConfig.Decimal < 0 { *styleConfig.Decimal = 0 } } if styleConfig.Decimal != nil { tmp.Monitor = utils.RoundNumber(cell.ShowValue, *styleConfig.Decimal, hasPercent) } else { if hasPercent { numDecimal, _ := decimal.NewFromString(cell.ShowValue) tmpStr := numDecimal.Mul(decimal.NewFromInt(100)).String() tmp.Monitor = tmpStr + "%" } else { tmp.Monitor = cell.ShowValue } } // switch styleConfig.Last { // case "nt": // // 先进行数字的百分比计算，然后保留小数点位数 // percent := tmpShowValue * 100 // if styleConfig.Decimal == nil { // continue // } // factor := math.Pow(10, float64(*styleConfig.Decimal)) // rounded := math.Round(percent*factor) / factor // tmp.Monitor = fmt.Sprintf("%g%%", rounded) // case "decimal": // // 先保留小数点位数，再进行百分比计算 // if styleConfig.Decimal == nil { // continue // } // factor := math.Pow(10, float64(*styleConfig.Decimal)) // rounded := math.Round(tmpShowValue*factor) / factor // if styleConfig.Nt == "percent" { // percent := rounded * 100 // var precisionStr string // if *styleConfig.Decimal > 2 { // precision := *styleConfig.Decimal - 2 // precisionStr = strconv.FormatFloat(rounded, 'f', precision, 64) // } else { // precisionStr = strconv.FormatFloat(math.Round(percent), 'f', -1, 64) // } // tmp.Monitor = fmt.Sprintf("%s%%", precisionStr) // } else { // tmp.Monitor = fmt.Sprintf("%g", rounded) // } // } } else { if cell.DataType == request.CustomTextDT { tmp.Monitor = cell.Value } } } if ruleIds, ok := ruleScopeMap[i][j]; ok { for _, ruleId := range ruleIds { if checkCellRule(excelRuleMap[ruleId], cell.ShowValue, config) { tmp.Background = strings.TrimSpace(excelRuleMap[ruleId].BackgroundColor) tmp.FontColor = strings.TrimSpace(excelRuleMap[ruleId].FontColor) } } } dataCol = append(dataCol, tmp) } tableDataList = append(tableDataList, dataCol) } } selfTableData.MergeList = mergeList selfTableData.TableDataList = tableDataList return }