excel 转 json

前言

在最近的苏州磐石项目中，出现了一个将 excel 转成 json，再发送给海关的场景。
我便查找资料后写了段 excel 转 json 的代码，现在分享给大家：

# -*- coding:utf-8 -*-
# @Date    : 2019-07-28 22：00
# @Function: 把Excel表单转化为json文件
# @Author  : luwenjun

import xlrd
import json
import codecs
import os


# 把excel表格中指定sheet转为json
def Excel2Json(file_path):
    # 打开excel文件
    if get_data(file_path) is not None:
        book = get_data(file_path)
        # 抓取所有sheet页的名称
        worksheets = book.sheet_names()
        print("该Excel包含的表单列表为：\n")
        # for sheet in worksheets:
        #     print ('%s,%s' % (worksheets.index(sheet), sheet))
        # inp  = sheet
        # #inp = raw_input(u'请输入表单名对应的编号，对应表单将自动转为json:\n')
        sheet = book.sheet_by_index(0)
        row_0 = sheet.row(0)  # 第一行是表单标题
        nrows = sheet.nrows  # 行号
        ncols = sheet.ncols  # 列号

        result = {}  # 定义json对象
        result["title"] = file_path  # 表单标题
        result["rows"] = nrows  # 行号
        result["children"] = []  # 每一行作为数组的一项
        # 遍历所有行，将excel转化为json对象
        for i in range(nrows):
            if i == 0:
                continue
            tmp = {}
            # 遍历当前行所有列
            for j in range(ncols):
                # 获取当前列中文标题
                title_de = str(row_0[j])#.decode('utf-8')
                title_cn = title_de.split("'")[1]
                # 获取单元格的值
                tmp[title_cn] = sheet.row_values(i)[j]
            result["children"].append(tmp)
        json_data = json.dumps(result, indent=4, sort_keys=True)#.decode('unicode_escape')

        saveFile(os.getcwd(), worksheets[0], json_data)
        print(json_data)


# 获取excel数据源
def get_data(file_path):
    """获取excel数据源"""
    try:
        data = xlrd.open_workbook(file_path)
        return data
    except Exception as e:
        print(u'excel表格读取失败：%s' % e)
        return None


def saveFile(file_path, file_name, data):
    output = codecs.open(file_path + "/" + file_name + ".json", 'w', "utf-8")
    output.write(data)
    output.close()


if __name__ == '__main__':
    file_path =  r"C:\Users\lwj\Desktop\磐石订单模板.XLS"
    #file_path = input(u'请输入excel文件路径：\n')
    json_data = Excel2Json(file_path)

输出的结果为

{
    "children": [
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700389",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u4e94\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-03KY000",
            "\u5ba2\u6237\u7f16\u53f7": 170.0,
            "\u5e8f\u53f7": 1.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*3*1000",
            "\u8ba2\u5355\u7f16\u53f7": "M20180100200"
        },
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700390",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u4e94\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-03L1000",
            "\u5ba2\u6237\u7f16\u53f7": 170.0,
            "\u5e8f\u53f7": 2.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*3*850",
            "\u8ba2\u5355\u7f16\u53f7": "M20180100200"
        },
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700391",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u4e94\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-03ET000",
            "\u5ba2\u6237\u7f16\u53f7": 170.0,
            "\u5e8f\u53f7": 3.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*5*590",
            "\u8ba2\u5355\u7f16\u53f7": "M20171100204"
        },
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700392",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u5317\u56db\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-030E000",
            "\u5ba2\u6237\u7f16\u53f7": "040",
            "\u5e8f\u53f7": 4.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*3*800",
            "\u8ba2\u5355\u7f16\u53f7": "M20171100207"
        },
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700393",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u5317\u56db\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-02TJ000",
            "\u5ba2\u6237\u7f16\u53f7": "040",
            "\u5e8f\u53f7": 5.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*3*850",
            "\u8ba2\u5355\u7f16\u53f7": "M20171100207"
        },
        {
            "\u51fa\u8d27\u5355\u53f7": "V20190700394",
            "\u51fa\u8d27\u65e5\u671f": "20190726",
            "\u5355\u4f4d": "PCS",
            "\u54c1\u540d": "\u62a4\u89d2",
            "\u5ba2\u6237\u540d\u79f0": "\u540d\u7855\u5317\u56db\u5382",
            "\u5ba2\u6237\u6599\u53f7": "1534-030S000",
            "\u5ba2\u6237\u7f16\u53f7": "040",
            "\u5e8f\u53f7": 6.0,
            "\u6574\u6570\u5305\u88c5\u91cf": "1",
            "\u89c4\u683c": "50*50*3*900",
            "\u8ba2\u5355\u7f16\u53f7": "M20171100207"
        }
    ],
    "rows": 7,
    "title": "C:\\Users\\lwj\\Desktop\\\u78d0\u77f3\u8ba2\u5355\u6a21\u677f.XLS"
}

希望能对大家有所帮助。