Excel数据转换为json

execl数据:

data.png

# -*- coding=utf-8 -*-
import xlrd
import warnings
import sys
from collections import OrderedDict
import json
import codecs
# reload(sys)
# sys.setdefaultencoding('utf8')
 
warnings.filterwarnings("ignore")
 
def excel2json(excelPath, jsonPath, fileName):
    wb = xlrd.open_workbook('{excelPath}{fileName}.xlsx'.format(excelPath=excelPath, fileName=fileName))
 
    convert_list = []
    for sheetNo in range(0, len(wb.sheets())):
        sheetName = wb.sheet_by_index(sheetNo).name
        sh = wb.sheet_by_index(sheetNo)
        if sh.nrows == 0:
            continue
        title = sh.row_values(0)
 
        single = OrderedDict()
        subdict = OrderedDict()
        for rownum in range(1, sh.nrows):
            rowvalue = sh.row_values(rownum)
            subdict1 = OrderedDict()
            for colnum in range(3, len(rowvalue)):
                subdict1[title[colnum]] = rowvalue[colnum]
            
            if rowvalue[1]:
                subdict = {}
                subdict[rowvalue[2]] = subdict1
                single[rowvalue[1]] = subdict
                temp = rowvalue[1]

            else:
                subdict[rowvalue[2]] = subdict1
                single[temp] = subdict
                if rownum == sh.nrows-1:
                    convert_list.append(single)

        j = json.dumps(single)
        with codecs.open('{jsonPath}{fileName}-{sheetName}.json'.format(jsonPath=jsonPath, fileName=fileName, sheetName=sheetName), "w", "utf-8") as f:
            f.write(j)
 
# Batch Test
excelPath = 'D:/work/vsback/temp/data/'
jsonPath = 'D:/work/vsback/temp/data/'
fileName = 'data'
excel2json(excelPath, jsonPath, fileName)

得到json：

{
    "MyPage": {
        "care": {
            "find_type": "xpath",
            "selector": "//div[contains(text(),'\u5173\u6ce8')]"
        },
        "fans": {
            "find_type": "xpath",
            "selector": "//div[contains(text(),'\u7c89\u4e1d')]"
        },
        "wanted": {
            "find_type": "xpath",
            "selector": "//div[contains(text(),'\u60f3\u8981')]"
        },
        "smelt": {
            "find_type": "xpath",
            "selector": "//div[contains(text(),'\u95fb\u8fc7')]"
        },
        "have": {
            "find_type": "css",
            "selector": "div.col:nth-child(5)"
        },
        "fav": {
            "find_type": "css",
            "selector": ".mine-act > div:nth-child(1)"
        },
        "remind": {
            "find_type": "css",
            "selector": "div.tixing:nth-child(2)"
        },
        "letter": {
            "find_type": "css",
            "selector": "div.tixing:nth-child(3)"
        },
        "setting": {
            "find_type": "css",
            "selector": ".mine-act > div:nth-child(4)"
        },
        "order": {
            "find_type": "css",
            "selector": "order"
        },
        "cart": {
            "find_type": "css",
            "selector": "cart"
        },
        "wished": {
            "find_type": "css",
            "selector": "wished"
        },
        "sign_perfume": {
            "find_type": "css",
            "selector": "sign_perfume"
        }
    },
    "LoginPage": {
        "input_username": {
            "find_type": "css",
            "selector": "div.userName > input"
        },
        "input_password": {
            "find_type": "css",
            "selector": "div.passWord > input"
        },
        "login_btn": {
            "find_type": "css",
            "selector": ".login_btn"
        }
    }
}

测试一下json：

import json
with open('./data/data-data.json','r') as load_f:
    selectors = json.load(load_f)

print(selectors['MyPage']['fans']['find_type'])
print(selectors['LoginPage'])
print(selectors['MyPage'])
print(selectors.get('MyPage').get('fans'))

测试结果：

xpath
{'input_username': {'find_type': 'css', 'selector': 'div.userName > input'}, 'input_password': {'find_type': 'css', 'selector': 'div.passWord > input'}, 'login_btn': {'find_type': 'css', 'selector': '.login_btn'}}
{'care': {'find_type': 'xpath', 'selector': "//div[contains(text(),'XX')]"}, 'fans': {'find_type': 'xpath', 'selector': "//div[contains(text(),'XX')]"}, 'wanted': {'find_type': 'xpath', 'selector': "//div[contains(text(),'XX')]"}, 'smelt': {'find_type': 'xpath', 'selector': "//div[contains(text(),'XX')]"}, 'have': {'find_type': 'css', 'selector': 'div.col:nth-child(5)'}, 'fav': {'find_type': 'css', 'selector': '.mine-act > div:nth-child(1)'}, 'remind': {'find_type': 'css', 'selector': 'div.tixing:nth-child(2)'}, 'letter': {'find_type': 'css', 'selector': 'div.tixing:nth-child(3)'}, 'setting': {'find_type': 'css', 'selector': '.mine-act > div:nth-child(4)'}, 'order': {'find_type': 'css', 'selector': 'order'}, 'cart': {'find_type': 'css', 'selector': 'cart'}, 'wished': {'find_type': 'css', 'selector': 'wished'}, 'sign_perfume': {'find_type': 'css', 'selector': 'sign_perfume'}}
{'find_type': 'xpath', 'selector': "//div[contains(text(),'XX')]"}