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"
	"sync"
	"time"
)

// 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()
	fmt.Println("读取完成后", time.Now().Format(utils.FormatDateTime))

	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:"单元格内值的数据"`
	Value interface{} `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{},
	}
	fmt.Println("开始读取sheet数据：", time.Now().Format(utils.FormatDateTime))
	rows, tmpErr := f.GetRows(sheetName)
	if tmpErr != nil {
		err = tmpErr
		return
	}
	fmt.Println("读取完sheet数据：", time.Now().Format(utils.FormatDateTime))
	lenRow := len(rows)
	fmt.Println("总共：", lenRow, "条数据")
	if lenRow <= 0 {
		return
	}

	//sheetData.Row = len(rows)
	//sheetData.Column = len(Column)

	// 最大单元格数
	maxColumnIndex := 0

	wg := sync.WaitGroup{}
	wg.Add(1)
	// 协程处理合并单元格
	go func() {
		defer func() {
			wg.Done()
		}()
		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)

	// 每次分割就是5000条
	splitNum := 500
	splitLen := lenRow / splitNum
	residue := lenRow % splitNum
	if residue > 0 {
		splitLen += 1
	}

	for i := 0; i < splitLen; i++ {
		wg.Add(1)

		startRow := i * splitNum
		endRow := (i + 1) * splitNum
		if i == splitLen-1 && residue > 0 {
			endRow = lenRow
		}

		go func(currStartRow, currEndRow int) {
			defer func() {
				wg.Done()
			}()

			for rIndex := currStartRow; rIndex < currEndRow; rIndex++ {
				row := rows[rIndex]
				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,
						})
					}
					//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:"公式"`

					valueMap := make(map[string]interface{})
					valueMap["ct"] = LuckySheetDataCellType{
						Fa: "General",
						T:  cellTypeT,
					}
					valueMap["v"] = colCellIntfac
					if cellFormula != `` {
						valueMap["f"] = cellFormula
					}
					cellData = append(cellData, SimpleLuckySheetCellData{
						Col:   int64(cIndex),
						Row:   int64(rIndex),
						Value: valueMap,
					})
				}
			}
		}(startRow, endRow)
	}
	wg.Wait()

	fmt.Println("解析完sheet数据：", time.Now().Format(utils.FormatDateTime))
	//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,
	//				},
	//				Value:    colCellIntfac,
	//				Monitor:  colCell,
	//				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
}