jyps/project_chuan/xlsx.go

package main

import (
	"fmt"
	"github.com/xuri/excelize/v2"
	util "jygit.jydev.jianyu360.cn/data_processing/common_utils"
	"jygit.jydev.jianyu360.cn/data_processing/common_utils/mongodb"
	"log"
)

func dealXlsxData() {
	//url := "http://172.17.4.184:19908"
	////url := "http://127.0.0.1:19908"
	//username := "jybid"
	//password := "Top2023_JEB01i@31"
	////index := "bidding" //索引名称
	//// 创建 Elasticsearch 客户端
	//client, err := elastic.NewClient(
	//	elastic.SetURL(url),
	//	elastic.SetBasicAuth(username, password),
	//	elastic.SetSniff(false),
	//)
	//if err != nil {
	//	log.Fatalf("创建 Elasticsearch 客户端失败：%s", err)
	//}

	// 2. 读取 Excel
	f, err := excelize.OpenFile("投资入库项目_202504.xlsx")
	if err != nil {
		log.Fatal("❌ 无法打开 Excel 文件:", err)
	}
	defer func() {
		f.Save()
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	// 3. 读取 B 列（项目名称）
	rows, err := f.GetRows("Sheet1") // 替换为正确的 Sheet 名称
	if err != nil {
		log.Fatal("❌ 无法读取 Excel:", err)
	}

	log.Println("开始处理数据--------")
	for i := 1; i < len(rows); i++ {
		p1_project_code := rows[i][0]
		p1_project_name := rows[i][1]
		p1_project_owner := rows[i][2]
		p1_project_owner_code := rows[i][3]
		p1_project_owner_area := rows[i][4]
		p1_project_total := rows[i][5]
		//
		log.Println("项目名称--------", p1_project_name)
		insert := map[string]interface{}{
			"p1_project_code":       p1_project_code,
			"p1_project_name":       p1_project_name,
			"p1_project_owner":      p1_project_owner,
			"p1_project_owner_code": p1_project_owner_code,
			"p1_project_owner_area": p1_project_owner_area,
			"p1_project_total":      p1_project_total,
		}
		start := 8
		row := rows[i]
		p1_biddings := make([]map[string]interface{}, 0)
		p1_bidding_ids := make([]string, 0)
		for i := start; i+3 < len(row); i += 4 {
			amount := row[i]
			title := row[i+1]
			href := row[i+2]
			score := row[i+3]
			bid := GetIdByURL(href)
			p1_bidding_ids = append(p1_bidding_ids, bid)
			bidding, _ := MgoB.FindById("bidding", bid, nil)
			data := map[string]interface{}{
				"bidding_id": bid,
				"href":       href,
				"title":      title,
				"socre":      score,
				"amount":     amount,
				"toptype":    (*bidding)["toptype"],
				"subtype":    (*bidding)["subtype"],
			}
			//log.Println("aa", insert, amount, title, href, score)
			p1_biddings = append(p1_biddings, data)
		}

		insert["biddings"] = p1_biddings
		p1_bidding_ids = removeDuplicates(p1_bidding_ids)
		p1_projects := make([]map[string]interface{}, 0)
		for _, vv := range p1_bidding_ids {
			where2 := map[string]interface{}{
				"ids": vv,
			}
			projectset, _ := MgoP.FindOne("projectset_20230904", where2)
			if projectset != nil && len(*projectset) > 0 {
				data := map[string]interface{}{
					"project_id":  mongodb.BsonIdToSId((*projectset)["_id"]),
					"projectname": util.ObjToString((*projectset)["projectname"]),
					"bidamount":   (*projectset)["bidamount"],
					"sortprice":   (*projectset)["sortprice"],
					"area":        (*projectset)["area"],
					"city":        (*projectset)["city"],
					"district":    (*projectset)["district"],
				}
				p1_projects = append(p1_projects, data)
			}
		}
		uniqueProjects := make([]map[string]interface{}, 0)
		seen := make(map[string]bool)

		for _, project := range p1_projects {
			pid := project["project_id"].(string)
			if !seen[pid] {
				seen[pid] = true
				uniqueProjects = append(uniqueProjects, project)
			}
		}

		if len(uniqueProjects) > 0 {
			insert["projects"] = uniqueProjects
		}

		MgoP.InsertOrUpdate("qfw", "wcc_dealXlsxData", insert)
	}

	log.Println("数据处理完毕--------")
}

// exportTestData 导出拓扑统计局 样例数据
func exportTestData() {
	sess := MgoP.GetMgoConn()
	defer MgoP.DestoryMongoConn(sess)

	queryMgo := sess.DB("qfw").C("wcc_dealXlsxData").Find(nil).Select(nil).Iter()
	count := 0

	total_datas := make([][]interface{}, 0)
	for tmp := make(map[string]interface{}); queryMgo.Next(tmp); count++ {
		if count%1000 == 0 {
			log.Println("current", count)
		}
		//
		projectname := util.ObjToString(tmp["p1_project_name"])
		where := map[string]interface{}{
			"projectname": projectname,
		}
		pro_project, _ := MgoP.FindOne("projectset_proposed", where)
		ppid := ""
		if pro_project != nil {
			ppid = mongodb.BsonIdToSId((*pro_project)["_id"])
		}
		//1.拟建原始数据
		v1 := []interface{}{
			ppid,
			tmp["p1_project_code"],
			tmp["p1_project_name"],
			tmp["p1_project_name"],
			tmp["p1_project_owner"],
			tmp["p1_project_owner_code"],
		}

		//2.在建项目数据
		if projects, ok := tmp["projects"].([]interface{}); ok {
			log.Println(projects)
		}

		total_datas = append(total_datas, v1)
	}

}