JSON转Excel转换,使用Go语言的映射数据结构,每次输出的结果都不相同。

huangapple go评论86阅读模式
英文:

JSON to Excel conversion, by using go lang map data structure does not produce same output every time

问题

我正在尝试在Go语言中构建一个JSON到Excel的转换器。这里有一个示例JSON:

[{
    "App": "Instagram",
    "Company": "Facebook",
    "Category": "Social Media"
  },
  {
    "App": "WeChat",
    "Company": "Tencent",
    "Category": "Social Media"
  }
}]

由于JSON中的数据可以是随机的,所以我没有使用结构体,在这里使用接口来存储数据,对JSON进行解组,然后使用断言技术尝试提取数据。但是由于数据结构使用的是映射,因此在迭代数据时,列不会按顺序存储在Excel表中,每次输出的列顺序都是随机的,因此存储在Excel表中的数据不合适。由于JSON中的数据类型不固定,无法在Go程序中使用结构体。

使用下面的代码,我能够获取列并在Excel中设置列名为App、Company、Category。但是,每次迭代时列的顺序都不同,这在进一步的步骤中需要填充值,但是由于遍历映射始终给出随机输出顺序,因此无法继续。

如何处理这部分呢?

file, err := ioutil.ReadFile("test.json")
if err != nil {
    panic(err)
}
var data interface{}
json.Unmarshal(file, &data) // 读取所有的JSON数据
fmt.Println(data)
t, ok := data.([]interface{}) // 断言接口
_ = ok
fmt.Printf("%[1]v  %[1]T\n", t) // map[string]interface {}
myMap := t[0] // 目标是获取App、Company和Category,它们将成为Excel表的列名
columnData, _ := myMap.(map[string]interface{}) // 从接口中提取底层具体数据
keys := make([]string, 0, len(columnData)) // 创建和初始化用于存储列的切片
for k := range columnData {
    fmt.Printf("%[1]v %[1]T\n", k)
    keys = append(keys, k)
}

xlsx := excelize.NewFile()
sheetName := "Sheet1"
xlsx.SetSheetName(xlsx.GetSheetName(1), sheetName)
c := 'A'
asciiValue := int(c)
var a string
for i := 0; i < len(keys); i++ {
    a = string(asciiValue)
    xlsx.SetCellValue(sheetName, a+"1", keys[i])
    asciiValue++
}
err := xlsx.SaveAs("./Onkar.xlsx")
if err != nil {
    fmt.Println(err)
    return
}
fmt.Println("Excel文件生成成功")
英文:

I am trying to build a JSON to Excel converter in the Go language. Here is some example JSON:

[{
    &quot;App&quot;: &quot;Instagram&quot;,
    &quot;Company&quot;: &quot;Facebook&quot;,
    &quot;Category&quot;: &quot;Social Media&quot;
  },
  {
    &quot;App&quot;: &quot;WeChat&quot;,
    &quot;Company&quot;: &quot;Tencent&quot;,
    &quot;Category&quot;: &quot;Social Media&quot;
  }
}]

I am not using structure here, as Data in JSON can be random, hence using interface to store data, unmarshaling JSON and then using assertion technique tried to extract data, but as data structure used as map, hence while iterating data columns are not stored in sequential manners in excel sheet, with every output columns sequence is random hence data stored inside excel sheet is not appropriate. As data is not fixed type in JSON can not use structure in go program.

With the below code, I am able to get column and set column in Excel as App, Company, Category. But with every iterations sequence of columns is not same, which should be as further step is to feed the value, but as traversing map always gives random output sequence, hence could not move further.

How to handle this part?

file, err  := ioutil.ReadFile(&quot;test.json&quot;)
 if err != nil {
    panic(err)
 }
 var data interface{}
 json.Unmarshal(file, &amp;data) // reading all json data
 fmt.Println(data)
 t, ok := data.([]interface{})  // assertion Interface
 _ = ok
 fmt.Printf(&quot;%[1]v  %[1]T\n&quot;, t)  //map[string]interface {}
 myMap := t[0]   // aim here to get APP, Company and Category which will be the column name of Excel sheet 
columnData, _  := myMap.(map[string]interface {})  // extract the underlying concrete data from interface
keys := make([]string, 0, len(columnData)) // creating and initializing slice to store column 
for k := range columnData {
     fmt.Printf(&quot;%[1]v %[1]T\n&quot;, k)
     keys = append(keys, k)
  }

 xlsx := excelize.NewFile()
 sheetName := &quot;Sheet1&quot;
 xlsx.SetSheetName(xlsx.GetSheetName(1), sheetName)
 c := &#39;A&#39;
 asciiValue := int(c)
 var a string
 for i := 0; i &lt; len(keys); i++ {
    a = string(asciiValue)
    xlsx.SetCellValue(sheetName, a + &quot;1&quot;, keys[i])
    asciiValue++
}
err := xlsx.SaveAs(&quot;./Onkar.xlsx&quot;)
if err != nil {
   fmt.Println(err)
   return
}
fmt.Println(&quot;Excel file generated sucessfully&quot;)

答案1

得分: 3

通过查看代码,你正在使用for := range循环来遍历map,但是这种方式不能保证顺序。所以,如果顺序对你很重要,我建议你对键进行排序。

你可以在这里找到更多信息:
https://stackoverflow.com/questions/18342784/how-to-iterate-through-a-map-in-golang-in-order

英文:

By looking the code, you are using for :=range, with on maps, does not guarantee order, so since order is important to you i suggest you use, sort the keys.
Here you can find more info.
https://stackoverflow.com/questions/18342784/how-to-iterate-through-a-map-in-golang-in-order

huangapple
  • 本文由 发表于 2021年8月2日 19:45:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/68621039.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定