package excel import ( "encoding/json" "errors" "fmt" "github.com/tealeg/xlsx" "github.com/xuri/excelize/v2" "hongze/hongze_ETA_mobile_api/utils" "os" "reflect" "strconv" "strings" "time" ) 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 int64 `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 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) 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 } 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 >= 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) 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 } func (item *LuckySheetData) ToExcel() (downloadFilePath string, err error) { tableData, err := item.GetTableDataByLuckySheetDataStr() //dir, err := os.Executable() //exPath := filepath.Dir(dir) //downloadFilePath := exPath + "/" + time.Now().Format(utils.FormatDateTimeUnSpace) + ".xlsx" downloadFilePath, err = getDownloadPath() if err != nil { return } xlsxFile := xlsx.NewFile() if err != nil { return } style := xlsx.NewStyle() alignment := xlsx.Alignment{ Horizontal: "center", Vertical: "center", WrapText: true, } style.Alignment = alignment style.ApplyAlignment = true sheet, err := xlsxFile.AddSheet("sheet1") if err != nil { return } for k, v := range tableData.RowWidthList { err = sheet.SetColWidth(k, k, v/10) if err != nil { return } } for index, v := range tableData.TableDataList { tmpRow := sheet.AddRow() //设置单元格高度 if tableData.RowHeightList[index] > 0 { tmpRow.SetHeight(tableData.RowHeightList[index] / 2) } for _, cellInfo := range v { tmpStyle := new(xlsx.Style) //xlsx.Style{ // Fill: xlsx.Fill{}, // ApplyBorder: false, // ApplyFill: false, // ApplyFont: false, // NamedStyleIndex: nil, //} //fill := *NewFill("solid", "FF000000", "00FF0000") defaultFill := xlsx.DefaultFill() if cellInfo.Background != `` { defaultFill.PatternType = "solid" backgroundColor := cellInfo.Background // 这么做是为了避免传入的是RGB的格式（rgb(255, 255, 255)） backgroundColor = getColor(backgroundColor) defaultFill.BgColor = strings.TrimPrefix(backgroundColor, "#") defaultFill.FgColor = strings.TrimPrefix(backgroundColor, "#") } tmpStyle.Fill = *defaultFill // 获取表格字体配置 tmpStyle.Font = getExcelFontConf(cellInfo) //获取表格单元格排列配置 tmpStyle.ApplyAlignment = true tmpStyle.Alignment = getExcelAlignmentConf(cellInfo) //边框配置 tmpStyle.Border = xlsx.Border{ Left: "thin", LeftColor: "000000", Right: "thin", RightColor: "000000", Top: "thin", TopColor: "000000", Bottom: "thin", BottomColor: "000000", } //tmpStyle.ApplyAlignment = true //tmpStyle.Alignment.WrapText = true tmpRow := tmpRow.AddCell() tmpRow.SetStyle(tmpStyle) valueStr := cellInfo.Monitor if valueStr == `` { //valueStr = fmt.Sprint(cellInfo.Value) if valueStr == `` && cellInfo.CellType.S != nil { //不是设置在单元格上面，而是设置在文本上 for _, cellS := range cellInfo.CellType.S { valueStr += fmt.Sprint(cellS.Value) } } } tmpRow.SetString(valueStr) //if cellInfo.Function != `` { // tmpRow.SetFormula(cellInfo.Function) //} } } for _, v := range tableData.MergeList { for k, cell := range sheet.Row(v.StartRowIndex).Cells { if v.StartColumnIndex == k { cell.Merge(v.MergeColumnNum, v.MergeRowNum) } } } //return err = xlsxFile.Save(downloadFilePath) if err != nil { return } //randStr := time.Now().Format(utils.FormatDateTimeUnSpace) //downloadFileName := "即将到期客户数据_" + randStr + ".xlsx" //this.Ctx.Output.Download(downLoadnFilePath, downloadFileName) return } // getDownloadPath 获取下载地址 func getDownloadPath() (fpath string, err error) { dateDir := time.Now().Format("20060102") uploadDir := "static/xls/" + dateDir err = os.MkdirAll(uploadDir, 0766) if err != nil { return } randStr := utils.GetRandStringNoSpecialChar(28) fileName := randStr + ".xlsx" fpath = uploadDir + "/" + fileName return } // getExcelFontConf 获取表格字体配置 func getExcelFontConf(cellInfo LuckySheetDataValue) xlsx.Font { fontSize := 10 familyName := `` var isBold, isItalic, isUnderline bool // 字体 { if cellInfo.Bold == 1 { isBold = true } if cellInfo.Italic == 1 { isItalic = true } if cellInfo.CancelLine == 1 { isUnderline = true } //if cellInfo.Fontsize != `` { // convSize, _ := strconv.Atoi(cellInfo.Fontsize) // if convSize > 0 { // fontSize = convSize // } //} if cellInfo.Fontsize > 0 { fontSize = cellInfo.Fontsize } tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily] if !familyNameOk { tmpFamilyName = "宋体" } familyName = tmpFamilyName } return xlsx.Font{ Size: fontSize, Name: familyName, //Family: v2.FontFamily, //Charset: 0, Color: strings.TrimPrefix(cellInfo.FontColor, "#"), Bold: isBold, Italic: isItalic, Underline: isUnderline, } } // getExcelAlignmentConf 获取表格单元格排列配置 func getExcelAlignmentConf(cellInfo LuckySheetDataValue) xlsx.Alignment { //自动换行 isWrapText := false if cellInfo.TextBeak == 2 { isWrapText = true } rotateText := 0 //if cellInfo.RotateText != `` { // convSize, _ := strconv.Atoi(cellInfo.RotateText) // if convSize > 0 { // rotateText = convSize // } //} return xlsx.Alignment{ Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType], //Indent: 0, //ShrinkToFit: false, TextRotation: rotateText, Vertical: LuckyVerticalMap[cellInfo.VerticalType], WrapText: isWrapText, } } // getExcelBorderConf 获取表格边框配置 //func getExcelBorderConf(list []LuckySheetDataConfigBorderInfo) (v xlsx.Border) { // // 字体 // for _, v := range list { // //v.BorderType // //v.Style // // if v.RangeType == "cell" { // // xlsx.Border{ // Left: LuckyBorderMap[v.Value.L.Style], // LeftColor: strings.TrimPrefix(v.Value.L.Color, "#"), // Right: LuckyBorderMap[v.Value.R.Style], // RightColor: strings.TrimPrefix(v.Value.R.Color, "#"), // Top: LuckyBorderMap[v.Value.T.Style], // TopColor: strings.TrimPrefix(v.Value.T.Color, "#"), // Bottom: LuckyBorderMap[v.Value.B.Style], // BottomColor: strings.TrimPrefix(v.Value.B.Color, "#"), // } // } else { // xlsx.Border{ // Left: LuckyBorderMap[v.Style], // LeftColor: strings.TrimPrefix(v.Color, "#"), // Right: LuckyBorderMap[v.Style], // RightColor: strings.TrimPrefix(v.Color, "#"), // Top: LuckyBorderMap[v.Style], // TopColor: strings.TrimPrefix(v.Color, "#"), // Bottom: LuckyBorderMap[v.Style], // BottomColor: strings.TrimPrefix(v.Color, "#"), // } // } // //v.Value.T.Style // // } // // return //} // LuckyFontFamilyMap 字体map var LuckyFontFamilyMap = map[int]string{ 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: "华文隶书", } // LuckyBorderMap 边框map var LuckyBorderMap = map[string]string{ "": "Thin", "1": "Thin", "2": "Hair", "3": "Dotted", "4": "Dashed", "5": "DashDot", //宋体（Song）、 "6": "DashDotDot", // 黑体（ST Heiti） "7": "Double", //楷体（ST Kaiti）, "8": "Medium", //仿宋（ST FangSong）, "9": "MediumDashed", //新宋体（ST Song）, "10": "MediumDashDot", "11": "MediumDashDotDot", "12": "SlantedDashDot", "13": "Thick", } // LuckyHorizontalMap 水平对齐map var LuckyHorizontalMap = map[int]string{ //"": "general", 0: "center", 1: "left", 2: "right", } // LuckyVerticalMap 垂直对齐map var LuckyVerticalMap = map[int]string{ //"": "center", 0: "center", 1: "top", //2: "bottom", } // GetColumnName Return the ColumnName func GetColumnName(rowNumber int, colNumber int) (colName string, err error) { if rowNumber < 1 || colNumber < 1 { err = errors.New("Row and Column Number can not be less than 1") return } dividend := colNumber var modulo int for dividend > 0 { modulo = (dividend - 1) % 26 // Convert int to char colName = string(65+modulo) + colName dividend = (int)((dividend - modulo) / 26) } colName = colName + strconv.Itoa(rowNumber) 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{} } 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 != `` || (v.MergeCell.Row != rowIndex && v.MergeCell.Row != 0) { 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 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 { 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 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) 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 } func (item *LuckySheetData) ToExcel2() (downloadFilePath string, err error) { tableData, err := item.GetTableDataByLuckySheetDataStr() downloadFilePath, err = getDownloadPath() if err != nil { return } f := excelize.NewFile() // Create a new sheet. sheetName := `Sheet1` sheetIndex, _ := f.NewSheet(sheetName) //设置列宽度 for k, v := range tableData.RowWidthList { colName, tmpErr := excelize.ColumnNumberToName(k + 1) if tmpErr != nil { err = errors.New("获取单元格列名失败，ERR：" + tmpErr.Error()) err = tmpErr return } err = f.SetColWidth(sheetName, colName, colName, v/10) if err != nil { err = errors.New("设置列宽度，ERR：" + tmpErr.Error()) return } } // 写入数据 for index, v := range tableData.TableDataList { //设置单元格高度 if tableData.RowHeightList[index] > 0 { tmpErr := f.SetRowHeight(sheetName, index+1, tableData.RowHeightList[index]/2) if tmpErr != nil { err = errors.New("设置单元格高度失败，ERR：" + tmpErr.Error()) return } } for colunmIndex, cellInfo := range v { //f.NewStyle defaultFill := excelize.Fill{ //Type: "solid", Pattern: 1, //Color: []string{cellInfo.Background}, Shading: 0, } if cellInfo.Background != `` { defaultFill.Type = "solid" defaultFill.Color = []string{cellInfo.Background} } cellStyle := &excelize.Style{ Border: []excelize.Border{ {Type: "left", Color: "000000", Style: 1}, {Type: "top", Color: "000000", Style: 1}, {Type: "bottom", Color: "000000", Style: 1}, {Type: "right", Color: "000000", Style: 1}, //{Type: "diagonalDown", Color: "A020F0", Style: 7}, //{Type: "diagonalUp", Color: "A020F0", Style: 8}, }, Fill: defaultFill, Font: getExcelizeFontConf(cellInfo), Alignment: getExcelizeAlignmentConf(cellInfo), //Protection: nil, //NumFmt: 0, //DecimalPlaces: 0, //CustomNumFmt: nil, //Lang: "", //NegRed: false, } cellStyleId, tmpErr := f.NewStyle(cellStyle) if tmpErr != nil { err = errors.New("new Style 失败，ERR：" + tmpErr.Error()) return } columnName, tmpErr := excelize.ColumnNumberToName(colunmIndex + 1) if tmpErr != nil { err = errors.New("获取单元格列名失败，ERR：" + tmpErr.Error()) return } cellName := columnName + strconv.Itoa(index+1) tmpErr = f.SetCellStyle(sheetName, cellName, cellName, cellStyleId) if tmpErr != nil { err = errors.New("设置style失败，ERR：" + tmpErr.Error()) return } valueStr := cellInfo.Monitor if valueStr == `` { //valueStr = fmt.Sprint(cellInfo.Value) if valueStr == `` && cellInfo.CellType.S != nil { //不是设置在单元格上面，而是设置在文本上 for _, cellS := range cellInfo.CellType.S { valueStr += fmt.Sprint(cellS.Value) } } } tmpErr = f.SetCellStr(sheetName, cellName, valueStr) if tmpErr != nil { err = errors.New("设置单元格数据失败，ERR：" + tmpErr.Error()) return } } } for _, v := range tableData.MergeList { //开始列 startColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + 1) if tmpErr != nil { err = tmpErr return } startCellName := startColumnName + strconv.Itoa(v.StartRowIndex+1) //结束列 endColumnName, tmpErr := excelize.ColumnNumberToName(v.StartColumnIndex + v.MergeColumnNum + 1) if tmpErr != nil { err = tmpErr return } endCellName := endColumnName + strconv.Itoa(v.StartRowIndex+v.MergeRowNum+1) err = f.MergeCell(sheetName, startCellName, endCellName) if err != nil { return } } // Set active sheet of the workbook. f.SetActiveSheet(sheetIndex) // Save spreadsheet by the given path. err = f.SaveAs(downloadFilePath) return } // getExcelizeFontConf 获取表格字体配置 func getExcelizeFontConf(cellInfo LuckySheetDataValue) *excelize.Font { fontSize := 10 familyName := `` //single 单线 、double 双线 underlineStyle := `` var isBold, isItalic, isUnderline bool // 字体 { if cellInfo.Bold == 1 { isBold = true } if cellInfo.Italic == 1 { isItalic = true } if cellInfo.CancelLine == 1 { isUnderline = true } //if cellInfo.Fontsize != `` { // convSize, _ := strconv.Atoi(cellInfo.Fontsize) // if convSize > 0 { // fontSize = convSize // } //} if cellInfo.Fontsize > 0 { fontSize = cellInfo.Fontsize } tmpFamilyName, familyNameOk := LuckyFontFamilyMap[cellInfo.FontFamily] if !familyNameOk { tmpFamilyName = "宋体" } familyName = tmpFamilyName } if isUnderline { underlineStyle = "single" } //VertAlign取值： //BASELINE、superscript、subscript return &excelize.Font{ Bold: isBold, Italic: isItalic, Underline: underlineStyle, Family: familyName, Size: float64(fontSize), Strike: isUnderline, Color: cellInfo.FontColor, VertAlign: "", } } // getExcelizeAlignmentConf 获取表格单元格排列配置 func getExcelizeAlignmentConf(cellInfo LuckySheetDataValue) *excelize.Alignment { //自动换行 isWrapText := false if cellInfo.TextBeak == 2 { isWrapText = true } rotateText := 0 //if cellInfo.RotateText != `` { // convSize, _ := strconv.Atoi(cellInfo.RotateText) // if convSize > 0 { // rotateText = convSize // } //} return &excelize.Alignment{ Horizontal: LuckyHorizontalMap[cellInfo.HorizontalType], //Indent: 0, //JustifyLastLine: false, //ReadingOrder: 0, //RelativeIndent: 0, //ShrinkToFit: false, TextRotation: rotateText, Vertical: LuckyVerticalMap[cellInfo.VerticalType], WrapText: isWrapText, } } // getColor 获取hex颜色 func getColor(bgStr string) string { if strings.Contains(bgStr, "(") { arr := strings.Split(bgStr, ",") if len(arr) != 3 { return bgStr } // 第一位 tmpFirstArr := strings.Split(arr[0], "(") arr[0] = tmpFirstArr[len(tmpFirstArr)-1] // 最后一位 tmpLastArr := strings.Split(arr[2], ")") arr[2] = tmpLastArr[0] rgbArr := make([]int64, 0) for _, v := range arr { tmpInt, err := strconv.Atoi(utils.TrimStr(v)) if err != nil { return bgStr } rgbArr = append(rgbArr, int64(tmpInt)) } rgb := RGB{ rgbArr[0], rgbArr[1], rgbArr[2], } bgStr = "" + rgb.Rgb2Hex().Str } return bgStr }