Golang 操作数据库、写入excel

	package main

import (
	"bytes"
	"context"
	"crypto/hmac"
	"crypto/sha256"
	"encoding/hex"
	"encoding/json"
	"errors"
	"flag"
	"fmt"
	"io/ioutil"
	"log"
	"math/rand"
	"net/http"
	"net/url"
	"strconv"
	"strings"
	"time"

	"github.com/tealeg/xlsx"
	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

type BaiduClient struct {
	url          string
	accessKey    string
	accessSecret string
	mapUrl       string
	lbsKey       string
}

var defaultClient *BaiduClient

type DeviceInfo struct {
	Sn  string `json:"sn"`
	Did string `json:"did"`
	Mid string `json:"mid"`
	Res string `json:"resource"`
	Fc  string `json:"fc"`
	Pk  string `json:"pk"`
	Ak  string `json:"ak"`
	Sk  string `json:"sk"`
}

func main() {
	cPath := flag.String("c", "", "")
	flag.Parse()

	content, err := ioutil.ReadFile(*cPath)
	if err != nil {
		log.Fatal("read config file failed, error: ", err)
	}
	cfg := Config{}
	err = json.Unmarshal(content, &cfg)

	if err != nil {
		log.Fatal("transform config file failed, error: ", err)
	}

	ctx, _ := context.WithTimeout(context.Background(), 60*time.Second)
	setupMongo(&Mongo{
		Uri:      cfg.Mongo.Uri, //TODO
		Database: cfg.Mongo.Database,
		Username: cfg.Mongo.Username, //TODO
		Password: cfg.Mongo.Password, //TODO
	})
	bakColl := mongoMgr.Database.Collection("baidu_audio_key1")
	insertEmptyFcPkTest(bakColl, ctx)

	fc, pk := cfg.Fc, cfg.Pk //TODO
	sn := "E0CO12345C164Q6V0006"
	token := "K9E3CDZiKabzJrflWlLL3omXldSMbtlnhekQT5DcYF4"

	snArr := getSnArr(sn, 260)
	deviceInfoArr := []DeviceInfo{}

	//获取设备did
	for _, sn := range snArr {
		err, di := getDeviceInfoFromSn("https://iop-ui.ecouser.net/api/dim/devmanager.do",
			token,
			sn,
			ctx)
		if err != nil {
			log.Println("无效sn", sn)
		}
		di.Sn = sn
		deviceInfoArr = append(deviceInfoArr, di)
	}

	for k, v := range deviceInfoArr {
		if v.Did == "" {
			continue
		}
		filterMap := make(map[string]interface{})
		filterMap["did"] = v.Did
		filterMap["fc"] = fc
		filterMap["pk"] = pk
		bakRes := bakColl.FindOne(ctx, filterMap)
		if bakRes.Err() != nil {
			if bakRes.Err() == mongo.ErrNoDocuments { //无数据
				filterMap["did"] = nil
				// did绑定四元组
				err, ak, sk := bindBaiduKey(bakColl, ctx, filterMap, v.Did)
				if err != nil {
					log.Println("bind baidu key err", err)
					// TODO  绑定失败的
					continue
				}
				deviceInfoArr[k].Fc = fc
				deviceInfoArr[k].Pk = pk
				deviceInfoArr[k].Ak = ak
				deviceInfoArr[k].Sk = sk
			}
			continue
		}
		result := bakDoc{}
		if err := bakRes.Decode(&result); err != nil {
			continue
		}
		deviceInfoArr[k].Fc = fc
		deviceInfoArr[k].Pk = pk
		deviceInfoArr[k].Ak = result.Ak
		deviceInfoArr[k].Sk = result.Sk

	}
	log.Println("deviceInfoArr:", deviceInfoArr)
	//写入excel
	genExcel(deviceInfoArr)

	//----------------------------------------------------------get ak sk -------------------------------------------------
	// const fc, pk, BaiduKeysOnceApplyCount = "", "", 0
	// defaultClient = &BaiduClient{
	// 	url:          "",
	// 	accessKey:    "",
	// 	accessSecret: "",
	// 	mapUrl:       "",
	// 	lbsKey:       "",
	// }
	// ctx, _ := context.WithTimeout(context.Background(), 60*time.Second)
	// GetAkSk(fc, pk, int(BaiduKeysOnceApplyCount), ctx)
}

func genExcel(deviceInfo []DeviceInfo) {
	file := xlsx.NewFile()
	sheet, err := file.AddSheet("sheet1")
	if err != nil {
		panic(err)
	}
	row := sheet.AddRow()
	row.SetHeight(20)
	cell := row.AddCell()
	cell.Value = "SN"
	cell = row.AddCell()
	cell.Value = "DID"
	cell = row.AddCell()
	cell.Value = "MID"
	cell = row.AddCell()
	cell.Value = "RES"

	cell = row.AddCell()
	cell.Value = "FC"
	cell = row.AddCell()
	cell.Value = "PK"
	cell = row.AddCell()
	cell.Value = "AK"
	cell = row.AddCell()
	cell.Value = "SK"
	for _, v := range deviceInfo {
		row := sheet.AddRow()
		cell := row.AddCell()
		cell.Value = v.Sn
		cell = row.AddCell()
		cell.Value = v.Did
		cell = row.AddCell()
		cell.Value = v.Mid
		cell = row.AddCell()
		cell.Value = v.Res

		log.Println("fc", v.Fc)
		cell = row.AddCell()
		cell.Value = v.Fc
		cell = row.AddCell()
		cell.Value = v.Pk
		cell = row.AddCell()
		cell.Value = v.Ak
		cell = row.AddCell()
		cell.Value = v.Sk
	}
	err = file.Save("file1.xlsx") //TODO
	if err != nil {
		panic(err)
	}
}

type bakDoc struct {
	Fc       string `bson:"fc"`
	Pk       string `bson:"pk"`
	Ak       string `bson:"ak"`
	Sk       string `bson:"sk"`
	CreateTs int64  `bson:"createTs"`
	Did      string `bson:"did"`
	BindTs   int64  `bson:"bindTs"`
	QueryTs  int64  `bson:"queryTs"`
}

func bindBaiduKey(sdsColl *mongo.Collection, ctx context.Context, filterMap map[string]interface{}, did string) (err error, ak, sk string) {
	result := bakDoc{}
	// TODO  加Session
	err = sdsColl.FindOneAndUpdate(ctx, filterMap, bson.M{"$set": bson.M{
		"did":     did,
		"bindTs":  time.Now().UTC().UnixNano() / 1e6,
		"queryTs": time.Now().UTC().UnixNano() / 1e6,
	}}).Decode(&result)

	if err != nil {
		log.Println("use an unuse key and update mongo err", err.Error())
		return err, "", ""
	}
	return nil, result.Ak, result.Sk
}

var tracingClient = &http.Client{}

type DeviceInfoResp struct {
	Ret  string       `json:"ret"`
	Data []DeviceInfo `json:"data"`
}

func getDeviceInfoFromSn(deviceUrl, token, sn string, ctx context.Context) (err error, di DeviceInfo) {
	req := map[string]interface{}{
		"auth": map[string]string{
			"with":        "oauth",
			"accesstoken": token,
		},
		"td": "GetDeviceInfoFromSN",
		"sn": sn,
	}
	b, err := json.Marshal(req)
	if err != nil {
		log.Println("json marsual err", err)
		return err, di
	}
	request, err := http.NewRequest("POST", deviceUrl, bytes.NewReader(b))
	request.Header.Add("content-type", "application/json")
	response, err := tracingClient.Do(request.WithContext(ctx))
	if err != nil {
		return err, di
	}
	defer response.Body.Close()

	body, err := ioutil.ReadAll(response.Body)
	if err != nil {
		return err, di
	}

	resultDecode, err := url.QueryUnescape(string(body))
	// log.Println("body", string(body), "status code", response.Status, "resultDecode", resultDecode)
	var resp DeviceInfoResp
	err = json.Unmarshal([]byte(resultDecode), &resp)
	if len(resp.Data) <= 0 {
		return errors.New("无效sn"), di
	}
	// log.Println("body", resp.Data[0].Did)
	return nil, resp.Data[0]

}


func getSnArr(sn string, num int) []string {
	//E0CO12345C164Q6V0006
	res := []string{}
	for i := 6; i <= num; i++ {
		str := strconv.Itoa(i)
		res = append(res, sn[:len(sn)-len(str)]+str)
	}
	return res
}

type Config struct {
	Mongo Mongo
	Fc    string
	Pk    string
}
type Mongo struct {
	Uri      string
	Database string
	Username string
	Password string
}

var mongoMgr *MongoMgr

type MongoMgr struct {
	Client   *mongo.Client   //mongo客户端
	Database *mongo.Database //数据库对象
}

func setupMongo(mongoCfg *Mongo) {
	var (
		ctx      context.Context
		opts     *options.ClientOptions
		client   *mongo.Client
		err      error
		database *mongo.Database
	)
	ctx, _ = context.WithTimeout(context.Background(), time.Duration(5000)*time.Millisecond) // ctx
	uri := mongoCfg.Uri
	username := mongoCfg.Username
	password := mongoCfg.Password
	opts = options.Client().ApplyURI(uri)
	if username != "" && password != "" {
		opts.SetAuth(options.Credential{Username: username, Password: password})
	}
	if client, err = mongo.Connect(ctx, opts); err != nil {
		log.Fatal("mongo connect fail: ", err, mongoCfg)
	}
	database = client.Database(mongoCfg.Database)
	mongoMgr = &MongoMgr{
		Client:   client,
		Database: database,
	}
	log.Println("------------  mongo connected !  ---------------")
}

func insertEmptyFcPkTest(bakColl *mongo.Collection, ctx context.Context) {
	documents := []interface{}{}
	createTs := time.Now().UTC().UnixNano() / 1e6
	for i := 0; i < 2; i++ {
		document := bson.M{
			"fc":       "omni",
			"pk":       "omni",
			"ak":       "ak" + strconv.Itoa(i),
			"sk":       "sk" + strconv.Itoa(i),
			"createTs": createTs,
		}
		documents = append(documents, document)
	}
	_, err := bakColl.InsertMany(ctx, documents)
	if err != nil {
		log.Println("InsertMany new key docs  err:", err.Error())
		return
	}
}