Go语言处理json字符串并导出为表格保存至文件中

时间：2025-03-08 06:59:31

需求

  1. 每月定期拉json数据（调用api这块忽略，直接给json字符串），周期为每个月的月初如2020-06-01 00:00:00到月末2020-06-01 23:00:00
  2. 对获取的json串进行处理（默认获取的json串嵌套很多层json），从而获取我们需要的json串
  3. 把所处理后的json字符串定时追加到表格文件中

思路

  1. 若要实现自动化拉数据，写入文件，需要考虑到如何判断每个月有几天，周期范围要怎么去自动填写，需要用到time包来实现
  2. 由于原来的json串是多层嵌套，要想获取我们需要的json串还需要先进行反序列化成map[string]interface{}格式，然后找到我们想要的字段，写入文件

注意：以下代码仅供参考，不可直接复制粘贴运行（删除了一些获取json的代码），代码后边附有json原文件和处理后的文件

代码如下

package main

import (
	"encoding/csv"
	"encoding/json"
	"fmt"
	"os"
	"time"
)

// 写入到csv表格文件
func writeExcel(BillMonth, BusinessCodeName, RealTotalCost string)  {
	//now := ()
	//currentTime := ("%v-%v-%v",
	//	(), int(()) - 1, ())

	filePath := "d:/TencentCloudCost/"
	file, err := os.OpenFile(filePath, os.O_CREATE | os.O_APPEND, 0664)
	if err != nil {
		panic(err)
	}
	defer file.Close()

	file.WriteString("\xEF\xBB\xBF")  // 定义写入表格格式

	w := csv.NewWriter(file)
	w.Write([]string{BillMonth, BusinessCodeName, RealTotalCost})

	w.Flush()
}

// 处理时间，自动对闰年月份进行判断，返回想要的时间节点
func ProcessTime() (BeginTime, EndTime string) {

	now := time.Now()
	currentLocation := now.Location()

	// 当前月 - 1 代表当月拉取上月的数据
	currentMonth := now.Month() - 1
	currentYear := now.Year()
	
	// 获取月首时间字段格式
	firstOfMonth := time.Date(currentYear, currentMonth, 1, 0, 0, 0, 0, currentLocation)
	// 获取月末时间字段格式
	lastOfMonth := firstOfMonth.AddDate(0, 1, -1)
	
	// 对月首时间进行切片处理
	var arr1 [19]rune
	fom := []rune(fmt.Sprintf("%v", firstOfMonth))
	for i, v := range fom {
		if i >= len(arr1) {
			break
		}
		arr1[i] = v
	}
	BeginTime = fmt.Sprintf("%v", string(arr1[:]))
	
	// 对月末时间格式进行切片梳理
	var arr2 [10]rune
	lom := []rune(fmt.Sprintf("%v", lastOfMonth))
	for i, v := range lom {
		if i >= len(arr2) {
			break
		}
		arr2[i] = v
	}
	EndTime = fmt.Sprintf("%v", string(arr2[:]))
	
	// 返回月初和月末的时间戳
	return BeginTime, EndTime
}

func main() {
	
	// 定时拉数据由于已经有数据，就注释掉了
	// BeginTime, EndTime := ProcessTime()

	// params := ("{\"PayerUin\":\"123456789\",\"BeginTime\":\"%v\",\"EndTime\":\"%v 23:00:00\"}", BeginTime, EndTime)

	// 对原json字符串进行处理
	// 将str反序列化成map
	str := "原来的json数据段" 
	var data map[string]interface{}
	err = json.Unmarshal([]byte(str), &data)
	if err != nil {
		fmt.Printf("反序列化错误 = %v\n", err)
	}

	// 找到SummaryOverview字段，并赋值给cost变量
	var cost interface{}
	for _, value := range data {
		for key, val := range value.(map[string]interface{}) {
			if key == "SummaryOverview" {
				cost = val
			}
		}
	}
	// 强制转换cost为[]interface{}
	c := cost.([]interface{})

	// 写入excel表格
	// writeExcel("BillMonth", "BusinessCodeName", "RealTotalCost")
	for i := 0; i < len(c); i++ {
		BillMonth := fmt.Sprintf("%v", c[i].(map[string]interface{})["BillMonth"])
		BusinessCodeName := fmt.Sprintf("%v", c[i].(map[string]interface{})["BusinessCodeName"])
		RealTotalCost := fmt.Sprintf("%v", c[i].(map[string]interface{})["RealTotalCost"])

		writeExcel(BillMonth, BusinessCodeName, RealTotalCost)
	}

}

json原文件

{
    "Response":{
        "Ready":1,
        "SummaryTotal":{
            "RealTotalCost":"123456.99",
            "VoucherPayAmount":"0.00",
            "IncentivePayAmount":"0.00",
            "CashPayAmount":"123456.99"
        },
        "SummaryOverview":[
            {
				"BillMonth":"2020-06",
				"BusinessCode":"p_aaa",
				"BusinessCodeName":"网线",
				"CashPayAmount":"4397.30",
				"IncentivePayAmount":"0.00",
				"RealTotalCost":"4397.30",
				"RealTotalCostRatio":"76.67",
				"VoucherPayAmount":"0.00"
            },
            {
				"BillMonth":"2020-06",
				"BusinessCode":"p_bbb",
				"BusinessCodeName":"磁盘",
				"CashPayAmount":"532.50",
				"IncentivePayAmount":"0.00",
				"RealTotalCost":"532.50",
				"RealTotalCostRatio":"10.36",
				"VoucherPayAmount":"0.00"
            },
            {
				"BillMonth":"2020-06",
				"BusinessCode":"p_ccc",
				"BusinessCodeName":"电脑",
				"CashPayAmount":"2041.68",
				"IncentivePayAmount":"0.00",
				"RealTotalCost":"2041.68",
				"RealTotalCostRatio":"3.78",
				"VoucherPayAmount":"0.00"
            },
            {
				"BillMonth":"2020-06",
				"BusinessCode":"p_ddd",
				"BusinessCodeName":"电话",
				"CashPayAmount":"1000.00",
				"IncentivePayAmount":"0.00",
				"RealTotalCost":"1000.00",
				"RealTotalCostRatio":"3.13",
				"VoucherPayAmount":"0.00"
            }
        ],
        "RequestId":"abcderfg-cdsvg-bfgbfg"
    }
}

处理后的json字符串如下

[
    {
        "BillMonth":"2020-06",
        "BusinessCode":"p_aaa",
        "BusinessCodeName":"网线",
        "CashPayAmount":"4397.30",
        "IncentivePayAmount":"0.00",
        "RealTotalCost":"4397.30",
        "VoucherPayAmount":"0.00"
    },
    {
        "BillMonth":"2020-06",
        "BusinessCode":"p_bbb",
        "BusinessCodeName":"磁盘",
        "CashPayAmount":"532.50",
        "IncentivePayAmount":"0.00",
        "RealTotalCost":"532.50",
        "VoucherPayAmount":"0.00"
    },
    {
        "BillMonth":"2020-06",
        "BusinessCode":"p_ccc",
        "BusinessCodeName":"电脑",
        "CashPayAmount":"2041.68",
        "IncentivePayAmount":"0.00",
        "RealTotalCost":"2041.68",
        "VoucherPayAmount":"0.00"
    },
    {
        "BillMonth":"2020-06",
        "BusinessCode":"p_ddd",
        "BusinessCodeName":"电话",
        "CashPayAmount":"1000.00",
        "IncentivePayAmount":"0.00",
        "RealTotalCost":"1000.00",
        "VoucherPayAmount":"0.00"
    }
]

相关文章