go操作excel

简介

Excelize 是 Go 语言编写的用于操作 Office Excel 文档类库

实例

接收mqtt中数据并写入excel

package main

import (
	"fmt"
	"github.com/360EntSecGroup-Skylar/excelize"
	MQTT "github.com/eclipse/paho.mqtt.golang"
	"github.com/tidwall/gjson"
	"log"
	"platform/base/uuid"
	"platform/logs"
	"strconv"
	"time"
)
var data map[string]string
var data1 map[string]string
var lines int
var lines1 int
type Mqtt_client struct {
	client MQTT.Client
	state bool
}

func (c *Mqtt_client) Mqtt_init( client_id string,addr string,push_only bool){
	opts := MQTT.NewClientOptions().AddBroker(addr).
		SetClientID(client_id).
		SetConnectionLostHandler(func(client MQTT.Client, err error) {
			// 连接被关闭后的回调函数
			logs.LogInstance().Error("Mqtt is disconnected!", "clientId", client_id, "reason", err.Error())
		}).SetOnConnectHandler(func(client MQTT.Client) {
		// 连接被建立后的回调函数
		logs.LogInstance().Info("Mqtt is connected!", "clientId", client_id)
		if !push_only{
			//订阅
			token := c.client.Subscribe("openface_result", 0, recv_openface)
			if token.Wait() && token.Error() != nil {
				logs.LogInstance().Error("Subscribe error",token.Error())
			}
			token = c.client.Subscribe("emotion_result", 0, emotion_result)
			if token.Wait() && token.Error() != nil {
				logs.LogInstance().Error("Subscribe error",token.Error())
			}
		}
	}).
		SetAutoReconnect(true).
		SetKeepAlive(120 * time.Second).
		SetPingTimeout(15 * time.Second).
		SetWriteTimeout(150 * time.Millisecond)
	c.client = MQTT.NewClient(opts)

	go func() {
		for{
			token := c.client.Connect()

			if token.Wait() && token.Error() != nil {
				c.state = false
				logs.LogInstance().Error("Connect error %v",token.Error())
				time.Sleep(1*time.Second)
			}else{
				c.state = true

			}
			if c.state{
				break
			}
		}
	}()

}

type OPENFACE_CONTROL struct{
	Uid string `json:"uid"`		//返回状态 0: 成功 -1:失败
	Img string `json:"img"` //返回信息
}
func (c *Mqtt_client) Push_img(uid string,img string){
	//发送
	//_data ,_:=json.Marshal(OPENFACE_CONTROL{Uid:uid,Img:img})
	token := c.client.Publish(uid, 0, false, img)
	if token.Wait() && token.Error() != nil {
		logs.LogInstance().Error("Publish error",token.Error())
	}
}
func (c *Mqtt_client) close_mqtt(){
	c.client.Disconnect(250)
}
var recv_openface MQTT.MessageHandler = func(client MQTT.Client, msg MQTT.Message) {
	print("openface : %s",time.Now().String())
	reg_string :=""
	class_string :=""
	reg:=GetAUReg(string(msg.Payload()))
	class:=GetAUClass(string(msg.Payload()))
	num :=strconv.Itoa(lines)
	for i := 0; i < len(reg); i++ {
		reg_string +=FloatToString(reg[i])+";"
	}
	for i := 0; i < len(class); i++ {
		class_string +=FloatToString(class[i])+";"
	}
	data["B"+num] =reg_string
	data["C"+num] =class_string
	lines++
	lines1++
}
var emotion_result MQTT.MessageHandler = func(client MQTT.Client, msg MQTT.Message) {
	result:=string(msg.Payload())
	num1 :=strconv.Itoa(lines1)
	maxVal:=GetEmotion(result)[0].Float()
	maxValIndex := 0
	for i := 0; i < len(GetEmotion(result)); i++ {
		//从第二个元素开始循环比较,如果发现有更大的数,则交换
		if maxVal < GetEmotion(result)[i].Float() {
			maxVal = GetEmotion(result)[i].Float()
			maxValIndex = i
		}
	}
	emotions:=[...]string{"anger","Contempt", "Disgust", "Fear", "Joy", "Neutral", "Sadness", "Surprise"}
	data1["B"+num1] = emotions[maxValIndex]
	data1["C"+num1] =FloatToString(float32(GetEmotion(result)[0].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[1].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[2].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[3].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[4].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[5].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[6].Float()))+"; "+
		FloatToString(float32(GetEmotion(result)[7].Float()))
}
func GetAUReg(result interface{})[]float32{
	var regs =[]float32{
		float32(gjson.Get(result.(string),"auReg.AU01").Float()),
		float32(gjson.Get(result.(string),"auReg.AU02").Float()),
		float32(gjson.Get(result.(string),"auReg.AU04").Float()),
		float32(gjson.Get(result.(string),"auReg.AU05").Float()),
		float32(gjson.Get(result.(string),"auReg.AU06").Float()),
		float32(gjson.Get(result.(string),"auReg.AU07").Float()),
		float32(gjson.Get(result.(string),"auReg.AU09").Float()),
		float32(gjson.Get(result.(string),"auReg.AU10").Float()),
		float32(gjson.Get(result.(string),"auReg.AU12").Float()),
		float32(gjson.Get(result.(string),"auReg.AU14").Float()),
		float32(gjson.Get(result.(string),"auReg.AU15").Float()),
		float32(gjson.Get(result.(string),"auReg.AU17").Float()),
		float32(gjson.Get(result.(string),"auReg.AU20").Float()),
		float32(gjson.Get(result.(string),"auReg.AU23").Float()),
		float32(gjson.Get(result.(string),"auReg.AU25").Float()),
		float32(gjson.Get(result.(string),"auReg.AU26").Float()),
		float32(gjson.Get(result.(string),"auReg.AU45").Float())}
	return regs
}
func GetAUClass(result interface{})[]float32{
	var class =[]float32{
		float32(gjson.Get(result.(string),"auClass.AU01").Float()),
		float32(gjson.Get(result.(string),"auClass.AU02").Float()),
		float32(gjson.Get(result.(string),"auClass.AU04").Float()),
		float32(gjson.Get(result.(string),"auClass.AU05").Float()),
		float32(gjson.Get(result.(string),"auClass.AU06").Float()),
		float32(gjson.Get(result.(string),"auClass.AU07").Float()),
		float32(gjson.Get(result.(string),"auClass.AU09").Float()),
		float32(gjson.Get(result.(string),"auClass.AU10").Float()),
		float32(gjson.Get(result.(string),"auClass.AU12").Float()),
		float32(gjson.Get(result.(string),"auClass.AU14").Float()),
		float32(gjson.Get(result.(string),"auClass.AU15").Float()),
		float32(gjson.Get(result.(string),"auClass.AU17").Float()),
		float32(gjson.Get(result.(string),"auClass.AU20").Float()),
		float32(gjson.Get(result.(string),"auClass.AU23").Float()),
		float32(gjson.Get(result.(string),"auClass.AU25").Float()),
		float32(gjson.Get(result.(string),"auClass.AU26").Float()),
		float32(gjson.Get(result.(string),"auClass.AU28").Float()),
		float32(gjson.Get(result.(string),"auClass.AU45").Float())}
	return class
}
func GetEmotion(result interface{})[]gjson.Result{
	return gjson.Get(result.(string),"emotion").Array()
}

func FloatToString(input_num float32) string {
	// to convert a float number to a string
	return strconv.FormatFloat(float64(input_num), 'f', 6, 64)
}

func main(){
	lines = 2
	lines1 = 2
	xlsx := excelize.NewFile()
	//创建新表单
	index := xlsx.NewSheet("数据表")
	index1 := xlsx.NewSheet("表情表")

	//写入数据
	data = map[string]string{
		"B1": "au_reg",
		"C1": "au_class",
	}
	data1 = map[string]string{
		"B1": "max emotion",
		"C1": "anger     , Contempt,Disgust,  Fear,         Joy,         Neutral,   Sadness,  Surprise",
	}
	mqtt:=new(Mqtt_client)

	cmd := "127.0.0.1"
	fmt.Println("输入目标地址:")
	fmt.Scanln(&cmd)
	uid := uuid.Must(uuid.NewV4()).String()
	mqtt.Mqtt_init(uid,"tcp://"+cmd+":1883",false)


	fmt.Println("按回车键保存数据!")
	fmt.Scanln(&cmd)
	mqtt.close_mqtt()
	time.Sleep(time.Millisecond*1000)

	for k, v := range data {
		//设置单元格的值
		xlsx.SetCellValue("数据表", k, v)
	}

	//设置默认打开的表单
	xlsx.SetActiveSheet(index)

	for k, v := range data1 {
		//设置单元格的值
		xlsx.SetCellValue("表情表", k, v)
	}
	//设置默认打开的表单
	xlsx.SetActiveSheet(index1)

	//保存文件到指定路径
	err := xlsx.SaveAs("./算法数据.xlsx")
	if err != nil {
		log.Fatal(err)
	}


}

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×