eta_api/services/excel/lucky_sheet.go

package excel

import (
	"encoding/json"
	"errors"
	"eta/eta_api/models/data_manage/excel/request"
	"eta/eta_api/utils"
	"fmt"
	"github.com/tealeg/xlsx"
	"github.com/xuri/excelize/v2"
	"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 {
	Name     string               `json:"name" description:"工作表名称"`
	Index    interface{}          `json:"index" description:"工作表索引"`
	Row      int                  `json:"row" description:"行数"`
	Column   int                  `json:"column" description:"列数"`
	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:"上下滚动条位置"`
	CalcChain         []interface{} `json:"calcChain" description:"公式链"`
	Status            interface{}   `json:"status" description:"激活状态"`
	VisibleDataColumn []int64       `json:"visibledatacolumn" description:"所有列的位置信息，递增的列位置数据，初始化无需设置"`
	VisibleDataRow    []int64       `json:"visibledatarow" description:"所有行的位置信息，递增的行位置数据，初始化无需设置"`
	ZoomRatio         float64       `json:"zoomRatio" description:"sheet缩放比例"`
}

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

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

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

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

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

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

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

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

// ToExcel 通过 TableData生成excel表格
func (tableData TableData) ToExcel() (downloadFilePath string, err error) {
	//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
	}

	// 将单个sheet的数据写入到excel
	err = tableData.WriteExcelSheetData(xlsxFile, "sheet1")
	if err != nil {
		return
	}

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

// WriteExcelSheetData 通过 TableData生成excel表格数据
func (tableData TableData) WriteExcelSheetData(xlsxFile *xlsx.File, sheetName string) (err error) {
	style := xlsx.NewStyle()
	alignment := xlsx.Alignment{
		Horizontal: "center",
		Vertical:   "center",
		WrapText:   true,
	}

	style.Alignment = alignment
	style.ApplyAlignment = true

	sheet, err := xlsxFile.AddSheet(sheetName)
	if err != nil {
		return
	}
	for k, v := range tableData.RowWidthList {
		err = sheet.SetColWidth(k, k, v/10)
		if err != nil {
			return
		}
	}

	// 单元格高度配置列表
	lenRowHeight := len(tableData.RowHeightList)

	for index, v := range tableData.TableDataList {
		tmpRow := sheet.AddRow()

		//设置单元格高度
		if index < lenRowHeight && 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)
			switch cellInfo.CellType.Fa {
			case "General":
				if cellInfo.CellType.S != nil {
					tmpRow.SetString(valueStr)
				} else {
					tmpRow.SetValue(cellInfo.Value)
				}
			case "@":
				tmpRow.SetString(valueStr)
			default:
				tmpRow.SetString(valueStr)
			}
			if cellInfo.Function != `` {
				//xlsxFile.
				//xlsxFile.SetCellFormula
				tmpRow.SetFormula(cellInfo.Function)
			}
			//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
}

// 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)
		// 数据要大于1行才会处理
		if len(tableDataList) > 1 {
			for rowIndex := lenRow - 1; rowIndex >= 0; rowIndex-- {
				isDelete := true
				for _, v := range tableDataList[rowIndex] {
					if v.Monitor != `` {
						isDelete = false
						flag = true
						break
					}
				}
				if flag {
					break
				}
				if isDelete {
					deleteBottomRowIndexList = append(deleteBottomRowIndexList, rowIndex)
					removeBottomRow++
				}
			}
		}

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

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

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

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

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

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

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

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

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

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

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

// ToExcel 通过 luckySheetData生成excel表格
func (item *LuckySheetData) ToExcel() (downloadFilePath string, err error) {
	tableData, err := item.GetTableDataByLuckySheetDataStr()
	if err != nil {
		return
	}
	downloadFilePath, err = tableData.ToExcel()

	return
}

// getDownloadPath 获取下载地址
func getDownloadPath() (fpath string, err error) {
	dateDir := time.Now().Format("20060102")
	uploadDir := "static/xls/" + dateDir
	err = os.MkdirAll(uploadDir, utils.DIR_MOD)
	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, err := f.NewSheet(sheetName)
	if err != nil {
		return
	}

	//设置列宽度
	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
}

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

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

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

			for _, v := range data.Data {
				edbName := v.EdbName
				if v.EdbAliasName != `` {
					edbName = v.EdbAliasName
				}
				firstCol = append(firstCol, LuckySheetDataValue{
					Value:     edbName,
					Monitor:   edbName,
					MergeCell: LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, firstCol)
		}

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

			for _, v := range data.Data {
				name := v.Unit + " / " + v.Frequency
				secondCol = append(secondCol, LuckySheetDataValue{
					Value:     name,
					Monitor:   name,
					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 {
					dataCol = append(dataCol, LuckySheetDataValue{
						Value:     v.Data[i].Value,
						Monitor:   v.Data[i].ShowValue,
						MergeCell: LuckySheetDataConfigMerge{},
					})
				}

				tableDataList = append(tableDataList, dataCol)
			}
		}

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

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

		// 第一行
		{
			firstCol := make([]LuckySheetDataValue, 0)
			firstCol = append(firstCol, LuckySheetDataValue{
				Value:   "日期",
				Monitor: "日期",
				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 {
				firstCol = append(firstCol, LuckySheetDataValue{
					Value:     textColList[0].Value,
					Monitor:   textColList[0].ShowValue,
					MergeCell: LuckySheetDataConfigMerge{},
				})
			}

			tableDataList = append(tableDataList, firstCol)
		}

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

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

			// 指标名称
			{
				edbName := tmpEdbInfo.EdbName
				if tmpEdbInfo.EdbAliasName != `` {
					edbName = tmpEdbInfo.EdbAliasName
				}
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:     edbName,
					Monitor:   edbName,
					MergeCell: LuckySheetDataConfigMerge{},
				})
			}

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

			// 指标数据列
			for _, tmpData := range tmpEdbInfo.Data {
				dataCol = append(dataCol, LuckySheetDataValue{
					Value:     tmpData.Value,
					Monitor:   tmpData.ShowValue,
					MergeCell: LuckySheetDataConfigMerge{},
				})
			}

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

			tableDataList = append(tableDataList, dataCol)
		}

	}

	selfTableData.MergeList = mergeList
	selfTableData.TableDataList = tableDataList

	return
}

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

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

	selfTableData.MergeList = mergeList
	selfTableData.TableDataList = tableDataList

	return
}