eta_api/services/excel/excel_to_lucky_sheet.go

package excel

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

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

type SimpleLuckySheetData struct {
	Name  string `json:"name" description:"工作表名称"`
	Index int    `json:"index" description:"工作表索引"`
	CellData  []SimpleLuckySheetCellData `json:"celldata" description:"单元格数据"`
	Config    SimpleLuckySheetDataConfig `json:"config" description:""`
	CalcChain []CalcChain                `json:"calcChain" 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":普通计算
	Times int `json:"times"`
}

type SimpleLuckySheetDataConfig struct {
	BorderInfo []LuckySheetDataConfigBorderInfo `json:"borderInfo" description:"边框"`
	Colhidden  map[string]int64                 `json:"colhidden" description:"隐藏列，示例值：\"colhidden\":{\"30\":0,\"31\":0}"`
	Merge map[string]LuckySheetDataConfigMerge `json:"merge" description:"合并单元格"`
	Columnlen map[string]float64 `json:"columnlen" description:"每个单元格的列宽"`
}

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

type SimpleLuckySheetDataValue struct {
	CellType LuckySheetDataCellType `json:"ct" description:"单元格值格式：文本、时间等	"`
	Value    interface{}            `json:"v" description:"原始值"`
	Function string `json:"f" 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,
		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
	}


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

	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 {
					}
					if maxColumnIndex < cIndex {
						maxColumnIndex = cIndex
					}
					cellName, tmpErr := excelize.CoordinatesToCellName(cIndex+1, rIndex+1)
					if tmpErr != nil {
						err = tmpErr
						return
					}
					cellFormula, tmpErr := f.GetCellFormula(sheetName, cellName)
					if tmpErr != nil {
						err = tmpErr
						return
					}


					var colCellIntfac interface{}
					cellTypeT := "g"
					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,
						})
					}

					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))

	sheetData.Config.Columnlen = colWidthMap

	sheetData.CellData = cellData
	sheetData.CalcChain = calcChainList

	return
}

func emuToPx(num float64) float64 {
	return num * 15
}