将Excel文件数据转换为Go中的JSON字符串,而无需任何结构定义。

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

Convert Excel file data to JSON string without any struct definition in Go

问题

我是新手学习Go语言。我有一个需求,需要将应用程序读取的Excel文件转换为JSON字符串,而不依赖于任何定义的结构体。我已经探索了一些库,其中要么需要这个结构体定义,要么至少需要Excel的列标题。例如,

github.com/xuri/excelize
github.com/onkarvhanumante/Excel2JsonTree
github.com/FerdinaKusumah/excel2json

然而,我找不到任何可以处理原始Excel数据并将其转换为JSON的库。

请指导我,谢谢!

英文:

I am new to Go language. I have got a requirement where the application will read an Excel file and convert it to JSON string, without relying upon any defined struct. I have explored some libraries where either this struct definition is required or at least the Excel column headers have to be there. For example,

github.com/xuri/excelize
github.com/onkarvhanumante/Excel2JsonTree
github.com/FerdinaKusumah/excel2json

However, I am not able to find any such where it can just process the raw Excel data and convert it to a JSON.

Please guide me, thank you!

答案1

得分: 2

如果可以将所有值都视为字符串,那么你可以按照下面代码片段所示的方式进行操作。该脚本读取所有工作表,并以两种格式创建JSON文件,一种是带有标题行,一种是不带标题行。

package main

import (
	"encoding/json"
	"fmt"
	"os"

	"github.com/xuri/excelize/v2"
)

func main() {
	f, err := excelize.OpenFile("test.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	// 可能有多个工作表
	sheets := f.GetSheetList()
	for _, sheetName := range sheets {
		d, err := f.GetRows(sheetName)
		if err != nil {
			fmt.Println("读取工作表出错", sheetName, ":", err)
			return
		}

		saveAsJSON(d, sheetName+".json")
		saveAsJSONWithHeaders(d, sheetName+"_with_headers.json")
	}

}

func saveAsJSONWithHeaders(rows [][]string, filename string) error {
	data := make([]map[string]string, len(rows)-1)
	headers := rows[0]
	// 排除标题行
	for i, row := range rows[1:] {
		data[i] = make(map[string]string)
		for j, cellValue := range row {
			data[i][headers[j]] = cellValue
		}
	}

	return saveAsJSON(data, filename)
}

func saveAsJSON(data interface{}, filename string) error {
	file, err := os.Create(filename)
	if err != nil {
		return err
	}
	defer file.Close()
	encoder := json.NewEncoder(file)
	if err := encoder.Encode(data); err != nil {
		return err
	}
	return nil
}
英文:

If it is okay to expect all values as String then you can do it as shown in the snippet below. The script reads all sheets(tabs) and creates JSON files in two formats, with and without headers.

package main

import (
	"encoding/json"
	"fmt"
	"os"

	"github.com/xuri/excelize/v2"
)

func main() {
	f, err := excelize.OpenFile("test.xlsx")
	if err != nil {
		fmt.Println(err)
		return
	}
	defer func() {
		if err := f.Close(); err != nil {
			fmt.Println(err)
		}
	}()

	// could have multiple sheets
	sheets := f.GetSheetList()
	for _, sheetName := range sheets {
		d, err := f.GetRows(sheetName)
		if err != nil {
			fmt.Println("error reading sheet", sheetName, ":", err)
			return
		}

		saveAsJSON(d, sheetName+".json")
		saveAsJSONWithHeaders(d, sheetName+"_with_headers.json")
	}

}

func saveAsJSONWithHeaders(rows [][]string, filename string) error {
	data := make([]map[string]string, len(rows)-1)
	headers := rows[0]
    // excluding header row
	for i, row := range rows[1:] {
		data[i] = make(map[string]string)
		for j, cellValue := range row {
			data[i][headers[j]] = cellValue
		}
	}

	return saveAsJSON(data, filename)
}

func saveAsJSON(data interface{}, filename string) error {
	file, err := os.Create(filename)
	if err != nil {
		return err
	}
	defer file.Close()
	encoder := json.NewEncoder(file)
	if err := encoder.Encode(data); err != nil {
		return err
	}
	return nil
}

huangapple
  • 本文由 发表于 2023年4月25日 19:53:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/76100854.html
匿名

发表评论

匿名网友

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

确定