golang实现读取excel文件并转换为JSON格式

devtools/2025/3/5 2:49:20/

需求

假如有如下表格：
在这里插入图片描述
我希望能够得到如下的JSON数组以供后续使用：

json">[{"create_date": "2025-02-25 06:31:11","id": "26","indicator_value": "0.25","params": "1，4","primary_id": "1","project_id": "1","second_id": "1","status": "0","update_date": "2025-02-25 06:31:11"},{"create_date": "2025-02-25 06:31:18","id": "27","indicator_value": "0.5","params": "1，2","primary_id": "1","project_id": "2","second_id": "1","status": "0","update_date": "2025-02-25 06:31:18"}
]

由于暂时没找到能实现该功能的第三方依赖，所以只能自己写个简单的方法实现此功能

实现

首先安装excelize依赖以便读取excel文件

go get github.com/xuri/excelize/v2

然后创建方法readExcelToJSON

import ("encoding/json""strings""github.com/xuri/excelize/v2"
)// 读取Excel文件并转换为JSON
func readExcelToJSON(filePath, sheetName string) (string, error) {f, err := excelize.OpenFile(filePath)if err != nil {return "", err}rows, err := f.GetRows(sheetName)if err != nil {return "", err}// 提取标题行中每列的名称headers := make([]string, len(rows[0]))for j, col := range rows[0] {headers[j] = strings.TrimSpace(col)}var jsonDataSlice []string// 忽略标题行rows = rows[1:]// 遍历所有行for _, row := range rows {rowData := make(map[string]interface{})// 填充数据到map中，使用列名作为keyfor j, col := range row {header := headers[j]                // 当前列的列名cellValue := strings.TrimSpace(col) // 单元格的值// 不需要转换数据类型rowData[header] = cellValue}jsonData, err := json.Marshal(rowData)if err != nil {return "", err}jsonDataSlice = append(jsonDataSlice, string(jsonData))}// 将结果切片转换为标准格式JSON字符串jsonResult := "[" + strings.Join(jsonDataSlice, ",") + "]"return jsonResult, nil
}

如果需要转换数据类型，可以把rowData[header] = cellValue替换成下面的代码

			if num, err := strconv.Atoi(cellValue); err == nil {rowData[header] = num} else if floatNum, err := strconv.ParseFloat(cellValue, 64); err == nil {rowData[header] = floatNum} else if boolVal, err := strconv.ParseBool(cellValue); err == nil {rowData[header] = boolVal} else {rowData[header] = cellValue}

测试

import ("testing"
)func TestReadExcelRows(t *testing.T) {rows, err := readExcelToJSON("C:\\Users\\ThinkPad\\Desktop\\tproject_input.xlsx", "Result 1")if err != nil {t.Errorf("error = %v", err)return}t.Logf("rows = %v", rows)
}