[Unity工具]python导表工具02：excel转json

参考链接：

https://www.cnblogs.com/zjzyh/p/5769069.html

https://blog.csdn.net/Nick_Li_/article/details/90139383

 

关于collections.OrderedDict()：https://www.cnblogs.com/gide/p/6370082.html

是一种有序的字典

 

代码如下：

excel2json.py

import xlrd
import collections
import json
import codecs

# excelPath:读取的excel路径
# jsonPath:输出的json路径
# keyRow:key所处的行
# typeRow:type所处的行
# valueRow:value开始的行
def excel2json(excelPath, jsonPath, keyRow=1, typeRow=2, valueRow=3):
    wb = xlrd.open_workbook(excelPath)
    sheet = wb.sheet_by_index(0)
    keys = sheet.row_values(keyRow)
    types = sheet.row_values(typeRow)
    data = {}
    
    for row in range(valueRow,sheet.nrows):
        rowData = sheet.row_values(row)
        temp = collections.OrderedDict()
        for col in range(sheet.ncols):
            key = keys[col]
            colType = types[col]
            value = rowData[col]
            if colType == 'json':
                temp[key] = json.loads(value)
            elif colType == 'int':
                temp[key] = int(value)
            else: #string float在读取excel时已区分
                temp[key] = value
        data[int(rowData[0])] = temp

    jsonStr = json.dumps(data,ensure_ascii=False,indent=4)
    with codecs.open(jsonPath,"w","utf-8") as f:
        f.write(jsonStr)

    return jsonStr

 

测试：

test.py

import excel2json
import json2lua
import json

jsonStr = excel2json.excel2json("./xls/zz.xls", "./json/zz.json")
print(jsonStr)

 

表格：

 

输出：

{
    "2": {
        "id": 2,
        "name": "一",
        "desc": [
            [
                1,
                2
            ],
            [
                3,
                4
            ],
            5,
            [
                6,
                7
            ]
        ],
        "para": 1.2
    },
    "4": {
        "id": 4,
        "name": "二",
        "desc": {
            "id": 5,
            "num": [
                1
            ]
        },
        "para": 3.45
    },
    "6": {
        "id": 6,
        "name": "三",
        "desc": [
            {
                "a": 10
            }
        ],
        "para": 6.0
    }
}