eta_api/services/excel/excel_to_lucky_sheet.go

package excel

import (
	"encoding/json"
	"eta/eta_api/utils"
	"fmt"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
)

// ConvToLuckySheet 普通的excel转luckySheet数据
func ConvToLuckySheet(filePath string) (err error) {
	defer func() {
		if err != nil {
			fmt.Println(err)
		}
	}()
	f, err := excelize.OpenFile(filePath)
	if err != nil {
		fmt.Println(err)
		return
	}
	sheetDataList := make([]SimpleLuckySheetData, 0)
	// 获取所有sheet
	sheetList := f.GetSheetList()
	for sheetIndex, sheetName := range sheetList {
		sheetData, tmpErr := getLuckySheetData(f, sheetIndex, sheetName)
		if tmpErr != nil {
			err = tmpErr
			return
		}
		sheetDataList = append(sheetDataList, sheetData)
	}
	byteS, err := json.Marshal(sheetDataList)
	if err != nil {
		return
	}
	utils.FileLog.Info(string(byteS))
	return
}

// SimpleLuckySheetData sheet表格数据
type SimpleLuckySheetData struct {
	Name  string `json:"name" description:"工作表名称"`
	Index int    `json:"index" description:"工作表索引"`
	//Row      int                        `json:"row" description:"行数"`
	//Column   int                        `json:"column" description:"列数"`
	CellData  []SimpleLuckySheetCellData `json:"celldata" description:"单元格数据"`
	Config    SimpleLuckySheetDataConfig `json:"config" description:""`
	CalcChain []CalcChain                `json:"calcChain" description:"公式链"`
	//Status            int64   `json:"status" description:"激活状态"`
}

type CalcChain struct {
	Col   int64         `json:"c"`     //列数
	Row   int64         `json:"r"`     //行数
	Index int           `json:"index"` //工作表id
	Func  []interface{} `json:"func"`  //公式信息，包含公式计算结果和公式字符串
	Color string        `json:"color"` //"w"：采用深度优先算法 "b":普通计算
	//Parent  interface{}   `json:"parent"`
	//Chidren struct {
	//} `json:"chidren"`
	Times int `json:"times"`
}

// SimpleLuckySheetDataConfig sheet表单的配置
type SimpleLuckySheetDataConfig struct {
	BorderInfo []LuckySheetDataConfigBorderInfo `json:"borderInfo" description:"边框"`
	Colhidden  map[string]int64                 `json:"colhidden" description:"隐藏列，示例值：\"colhidden\":{\"30\":0,\"31\":0}"`
	//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:"每个单元格的行高"`
	Columnlen map[string]float64 `json:"columnlen" description:"每个单元格的列宽"`
}

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

// SimpleLuckySheetDataValue 单元格内值的数据
type SimpleLuckySheetDataValue struct {
	CellType LuckySheetDataCellType `json:"ct" description:"单元格值格式：文本、时间等	"`
	Value    interface{}            `json:"v" description:"原始值"`
	Monitor  string                 `json:"m" description:"显示值"`
	//Fontsize       int                    `description:"字体大小，14"`
	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:"合并单元格信息"`
}

func getLuckySheetData(f *excelize.File, sheetIndex int, sheetName string) (sheetData SimpleLuckySheetData, err error) {
	cellData := make([]SimpleLuckySheetCellData, 0)         // excel数据
	mergeData := make(map[string]LuckySheetDataConfigMerge) //合并单元格数据
	calcChainList := make([]CalcChain, 0)                   //公式链信息

	sheetData = SimpleLuckySheetData{
		Name:  sheetName,
		Index: sheetIndex,
		//Row:      0,
		//Column:   0,
		CellData: cellData,
		Config:   SimpleLuckySheetDataConfig{},
	}
	rows, tmpErr := f.GetRows(sheetName)
	if tmpErr != nil {
		err = tmpErr
		return
	}
	//sheetData.Row = len(rows)
	//sheetData.Column = len(Column)

	// 最大单元格数
	maxColumnIndex := 0
	mergeCellList, err := f.GetMergeCells(sheetName)
	if err != nil {
		return
	}

	for _, v := range mergeCellList {
		// 左上角单元格位置
		cStartIndex, rStartIndex, tmpErr := excelize.CellNameToCoordinates(v.GetStartAxis())
		if tmpErr != nil {
			err = tmpErr
			return
		}
		// 右下角单元格位置
		cEndIndex, rEndIndex, tmpErr := excelize.CellNameToCoordinates(v.GetEndAxis())
		if tmpErr != nil {
			err = tmpErr
			return
		}
		//fmt.Println(v.GetEndAxis())
		tmpLuckySheetDataConfigMerge := LuckySheetDataConfigMerge{
			Row:    rStartIndex - 1,
			Column: cStartIndex - 1,
			Rs:     rEndIndex - rStartIndex + 1,
			Cs:     cEndIndex - cStartIndex + 1,
		}
		mergeData[fmt.Sprint(rStartIndex-1, "_", cStartIndex-1)] = tmpLuckySheetDataConfigMerge
	}
	sheetData.Config.Merge = mergeData

	colWidthMap := make(map[string]float64)
	for rIndex, row := range rows {
		for cIndex, colCell := range row {
			if rIndex == 0 {
				//colName, tmpErr := excelize.ColumnNumberToName(cIndex + 1)
				//if tmpErr != nil {
				//	err = tmpErr
				//	return
				//}
				//colWidth, tmpErr := f.GetColWidth(sheetName, colName)
				//if tmpErr != nil {
				//	err = tmpErr
				//	return
				//}
				//colWidthMap[fmt.Sprint(cIndex)] = emuToPx(colWidth)
			}
			if maxColumnIndex < cIndex {
				maxColumnIndex = cIndex
			}
			cellName, tmpErr := excelize.CoordinatesToCellName(cIndex+1, rIndex+1)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			cellType, tmpErr := f.GetCellType(sheetName, cellName)
			if tmpErr != nil {
				err = tmpErr
				return
			}
			cellFormula, tmpErr := f.GetCellFormula(sheetName, cellName)
			if tmpErr != nil {
				err = tmpErr
				return
			}

			fmt.Println(cellName, ": ", "类型：", cellType, ",公式：", cellFormula, ",值", colCell, "\t")

			var colCellIntfac interface{}
			cellTypeT := "g"
			//colCell = utils.Tof
			tmpDec, tmpErr := decimal.NewFromString(colCell)
			if tmpErr != nil {
				colCellIntfac = colCell
			} else {
				colCellIntfac, _ = tmpDec.Float64()
				cellTypeT = "n"
			}

			if cellFormula != `` {
				cellFormula = `=` + cellFormula

				calcChainList = append(calcChainList, CalcChain{
					Col:   int64(cIndex),
					Row:   int64(rIndex),
					Index: sheetIndex,
					Func:  []interface{}{true, colCell, cellFormula},
					Color: "w",
					Times: 0,
				})
			}

			cellData = append(cellData, SimpleLuckySheetCellData{
				Col: int64(cIndex),
				Row: int64(rIndex),
				Value: SimpleLuckySheetDataValue{
					CellType: LuckySheetDataCellType{
						Fa: "General",
						T:  cellTypeT,
						//S:  nil,
					},
					Value:   colCellIntfac,
					Monitor: colCell,
					//Background:     "",
					//FontFamily:     0,
					//FontColor:      "",
					Function: cellFormula,
					//MergeCell: LuckySheetDataConfigMerge{},
				},
			})
		}
	}

	sheetData.Config.Columnlen = colWidthMap

	sheetData.CellData = cellData
	sheetData.CalcChain = calcChainList
	//sheetData.Column = maxColumnIndex + 1

	return
}

// emuToPx 计量单位emu转px
func emuToPx(num float64) float64 {
	return num * 15
}