jyps/xlsx/xlsx_test.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"
	"strings"
	"testing"
)

func TestA(T *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27017",
		Size:        10,
		DbName:      "wcc",
		//UserName:    "SJZY_RWbid_ES",
		//Password:    "SJZY@B4i4D5e6S",
		//Direct:      true,
	}
	Mgo.InitPool()

	f, err := excelize.OpenFile("./拼数据.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}
	cells, _ := f.GetCols("Sheet2")

	for i := 1; i < len(rows); i++ {
		log.Print(rows[i][3])
		if rows[i][2] == "省级" {
			for k, v := range cells[0] {
				if k == 0 || v == "" {
					continue
				}

				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][3] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}

		if rows[i][2] == "市级" {
			for k, v := range cells[1] {
				if k == 0 || v == "" {
					continue
				}
				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["city"] = rows[i][4]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][4] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}

		if rows[i][2] == "县级" {
			for k, v := range cells[2] {
				if k == 0 || v == "" {
					continue
				}
				insert := make(map[string]interface{})
				insert["top"] = rows[i][0]
				insert["area"] = rows[i][3]
				insert["city"] = rows[i][4]
				insert["district"] = rows[i][5]
				insert["level"] = rows[i][2]
				insert["sub"] = v
				insert["name"] = rows[i][4] + rows[i][5] + v
				Mgo.InsertOrUpdate("wcc", "wcc_0313", insert)
			}
		}
	}

	fmt.Println("over")
}

func TestB(T *testing.T) {
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27083",
		Size:        10,
		DbName:      "qfw",
		UserName:    "SJZY_RWbid_ES",
		Password:    "SJZY@B4i4D5e6S",
		Direct:      true,
	}
	Mgo.InitPool()

	f, err := excelize.OpenFile("./fanglei.11111.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("Sheet1")
	if err != nil {
		fmt.Println(err)
		return
	}
	for i := 1; i < len(rows); i++ {
		id := rows[i][0]
		re, _ := Mgo.FindById("bidding", id, nil)
		Mgo.SaveByOriID("wcc_0327_bidding", re)
	}

	log.Println("over")

}

// TestAAA 处理专项债券数据
func TestAAA(t *testing.T) {
	//87 竞品
	Mgo := &mongodb.MongodbSim{
		//MongodbAddr: "172.17.189.140:27080",
		MongodbAddr: "127.0.0.1:27081",
		Size:        10,
		DbName:      "py_theme",
		UserName:    "",
		Password:    "",
		Direct:      true,
	}
	Mgo.InitPool()
	sess := Mgo.GetMgoConn()
	defer Mgo.DestoryMongoConn(sess)

	f, err := excelize.OpenFile("./河南2024专项债.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		f.Save()
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	rows, err := f.GetRows("项目列表")
	if err != nil {
		fmt.Println(err)
		return
	}

	keys := []string{"projectName", "totalInvestment", "regionName", "cityName", "countyName", "capital", "applyDebt", "portfolioFinancing", "specialDebtAsCapital",
		"expectedReturn", "projectCost", "projectTypeName3", "projectSubject", "startDate", "endDate", "operationStartDate",
		"operationEndDate", "sourceIncome", "constructionContent", "remarks", "portfolioFinancingSource", "costIncomePercent", "coverageMultiple", "implementingAgency",
		"accountingFirm", "lawFirm", "createTime", "updateTime"}
	titles := []string{"项目名称", "总投资（万元）", "省份", "地市", "区县", "不含专项债资本金（万元）", "申请专项债总额（万元）", "其他债务融资（万元）", "专项债作资本金（万元）", "预期收入（万元）", "成本",
		"项目领域", "项目业主", "建设期开始", "建设期结束", "运营期开始", "运营期结束", "收入来源", "建设内容", "特殊情况备注", "其他债务融资来源", "成本/收入（%）", "覆盖倍数", "主管部门", "会计所", "律所", "入库时间", "更新时间"}

	keys2 := []string{"bondName", "bondShortName", "bondNo", "regionName", "bondType1Name", "bondType2Name", "projectType1Name", "totalAmount", "issueDate", "issuePlaceName", "issueTerm", "issueInterestRate", "issuePhase", "payInterestMethodName",
		"newBondAmount", "counterBondAmount", "refinancingBondAmount", "redemptionMethod", "valueDate", "expiryDate", "payInterestDate", "latelyPayInterestDate", "lastPayInterest", "cumulativePayInterest"}

	titles2 := []string{"项目名称", "债券名称", "债券简称", "债券编码", "省份", "债券性质", "债券类型", "官方项目类型", "发行金额（万元）", "发行日期", "发行场所", "发行期限(年)", "发行利率(%)", "发行期数", "付息方式", "新增债券（亿元）", "置换债券（亿元）", "再融资债券（亿元）", "赎回方式",
		"起息日", "到息日", "付息日", "最近付息日", "上期已付息（亿元）", "累计付息（亿元）"}
	line := 0
	line++
	f.NewSheet("项目详情")
	f.NewSheet("债券详情")
	bondLine := 0
	//设置第一行title
	_ = f.SetSheetRow("项目详情", fmt.Sprintf("%s%d", "A", line), &titles)
	_ = f.SetSheetRow("债券详情", fmt.Sprintf("%s%d", "A", line), &titles2)
	//
	for i := 1; i < len(rows); i++ {
		projectName := rows[i][0]
		//projectName := "新建南通至宁波高速铁路（海盐段）"
		log.Println(projectName)
		line++
		where := map[string]interface{}{
			"project.jcxx.projectName": projectName,
		}
		detail, _ := Mgo.FindOne("specialbond_detail", where)
		if len(*detail) == 0 {
			continue
		}
		insert := make([]interface{}, 0)
		if project, ok := (*detail)["project"].(map[string]interface{}); ok {
			if jcxx, ok := project["jcxx"].(map[string]interface{}); ok {
				for _, v := range keys {
					insert = append(insert, jcxx[v])
				}
			}
		}

		err := f.SetSheetRow("项目详情", fmt.Sprintf("%s%d", "A", line), &insert)
		if err != nil {
			log.Println(err)
			return
		}

		// 债券基本信息
		if bonds, ok := (*detail)["bond"].([]interface{}); ok {
			for _, bond := range bonds {
				if bondData, ok := bond.(map[string]interface{}); ok {
					if jcxx, ok := bondData["jbxx"].(map[string]interface{}); ok {
						if strings.Contains(util.ObjToString(jcxx["bondName"]), "2024") {
							bondInsert := make([]interface{}, 0)
							bondInsert = append(bondInsert, projectName)
							for _, v := range keys2 {
								bondInsert = append(bondInsert, jcxx[v])
							}
							bondLine++
							f.SetSheetRow("债券详情", fmt.Sprintf("%s%d", "A", line), &bondInsert)
						}
					}
				}
			}
		}
	}

	//query := sess.DB("py_theme").C("specialbond_detail").Find(nil).Select(nil).Iter()
	//count := 0
	//for tmp := make(map[string]interface{}); query.Next(tmp); count++ {
	//	if count%10000 == 0 {
	//		log.Println("current:", count)
	//	}
	//
	//	id := mongodb.BsonIdToSId(tmp["_id"])
	//	log.Println(id)
	//}

}