123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379 |
- 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"`
- Func []interface{} `json:"func"`
- Color string `json:"color"`
-
-
-
- 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)
- 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
- }
|