jyps/xlsx/main.go

package main

import (
	"context"
	"fmt"
	"github.com/tealeg/xlsx"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
	"log"
	"net/url"
	"os"
	"strings"
)

type WinnerData struct {
	Winner   string `bson:"winner"`
	CreditNo string `bson:"credit_no"`
	Num      int    `bson:"num"`
}

func main() {
	username := "SJZY_RWbid_ES"
	password := "SJZY@B4i4D5e6S"
	//addr := "172.17.189.140:27080"
	addr := "127.0.0.1:27083"
	direct := true
	if !strings.Contains(addr, "127") {
		direct = false
	}
	escapedUsername := url.QueryEscape(username)
	escapedPassword := url.QueryEscape(password)
	urls := fmt.Sprintf("mongodb://%s:%s@%s", escapedUsername, escapedPassword, addr)
	clientOptions := options.Client().ApplyURI(urls).SetDirect(direct)

	// 连接到MongoDB
	client, err := mongo.Connect(context.TODO(), clientOptions)
	if err != nil {
		log.Fatal(err)
	}
	defer func() {
		if err := client.Disconnect(context.TODO()); err != nil {
			log.Fatal(err)
		}
	}()

	// 检查连接
	err = client.Ping(context.Background(), nil)
	if err != nil {
		log.Fatal(err)
	}

	log.Println(1111)
	// 选择数据库和集合
	collection := client.Database("qfw").Collection("wcc_bank_winner_statistic")
	// 查询数据并排序
	// 设置查询条件
	filter := bson.D{
		//{"comeintime", bson.M{"$gte": 1640966400, "$lte": 1703952000}},
		//{"subtype", bson.M{"$in": []string{"中标", "单一", "成交", "合同"}}},
	}
	// 设置投影，排除 contenthtml 字段
	projection := bson.D{
		{"credit_no", 1}, // 0表示不返回该字段
		{"winner", 1},    // 0表示不返回该字段
		{"num", 1},       // 0表示不返回该字段
	}
	//findOptions := options.Find().SetProjection(projection)
	findOptions := options.Find().SetSort(map[string]int{"num": -1}).SetLimit(100000).SetProjection(projection)
	cursor, err := collection.Find(context.Background(), filter, findOptions)
	if err != nil {
		log.Fatal(err)
	}
	defer cursor.Close(context.Background())

	// 创建 Excel 文件
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("Sheet1")
	if err != nil {
		log.Fatal(err)
	}

	// 添加表头
	row := sheet.AddRow()
	row.AddCell().SetValue("Winner")
	row.AddCell().SetValue("Credit No")
	row.AddCell().SetValue("Num")

	// 遍历结果集并写入 Excel 文件
	for cursor.Next(context.Background()) {
		var winnerData WinnerData
		if err := cursor.Decode(&winnerData); err != nil {
			log.Fatal(err)
		}

		if !strings.Contains(winnerData.Winner, "公司") {
			continue
		}
		row = sheet.AddRow()
		row.AddCell().SetValue(winnerData.Winner)
		row.AddCell().SetValue(winnerData.CreditNo)
		row.AddCell().SetInt(winnerData.Num)
	}

	// 保存 Excel 文件
	outputFile, err := os.Create("exported_data.xlsx")
	if err != nil {
		log.Fatal(err)
	}
	defer outputFile.Close()

	err = file.Write(outputFile)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("数据已成功导出到 exported_data.xlsx")
}