Python实现Excel转JSON

参考文章1：将excel表格数据转换为字典
字符串 & 字典 & Json 对比

# -*- coding: utf-8 -*-

# json数据类型

a = "python"   # 'python'字符串

b = {"name":"python"}   # {'name':'python'} 字典

c = '{"name":"python"}'   # json数据类型字符串（始终还是字符串）

1、用pandas将Excel转字典

# -*- coding: utf-8 -*-
# excel转字典

import pandas as pd

dict = {}
data = pd.read_excel(r're.xlsx')
data.fillna("",inplace = True)

list = []

for i in data.index.values:
    line = data.loc[i,['年份','分类','月份','A市','B市','C市']].to_dict()
    list.append(line)
dict['data'] = list
print(dict)

运行结果

D:\python\Python3.9.6\python.exe "E:/Data Analysis/Pycharm/Web Scraping/tr_json/t2.py"
{'data': [{'年份': '2018年', '分类': '游客人数', '月份': '1月', 'A市': 562, 'B市': 264, 'C市': 481}, {'年份': '2018年', '分类': '旅游收入', '月份': '2月', 'A市': 294, 'B市': 186, 'C市': 985}, {'年份': '2018年', '分类': '游客人数', '月份': '1月', 'A市': 264, 'B市': 241, 'C市': 156}, {'年份': '2018年', '分类': '旅游收入', '月份': '2月', 'A市': 502, 'B市': 564, 'C市': 594}]}

Process finished with exit code 0

格式化校验报错

问题：json格式双引号

2、用xlrd将Excel转字典

xlrd.biffh.XLRDError:Excel xlsx file；not support
报错参考1
AttributeError: ‘ElementTree‘ object has no attribute ‘getiterator‘
报错参考2

# -*- coding: utf-8 -*-

# xlrd -- excel转字典

import xlrd
def get_data(sheet1):
    data = xlrd.open_workbook(r're.xlsx')
    table = data.sheets()[sheet1]
    # print(table)
    nor = table.nrows
    nol = table.ncols
    dict = {}
    for i in range(1,nor):
        for j in range(nol):
            title = table.cell_value(0,j)
            value = table.cell_value(i,j)
            dict[title] = value
        yield dict

if __name__ == '__main__':
    for i in get_data(0):
        print(i)

运行结果

D:\python\Python3.9.6\python.exe "E:/Data Analysis/Pycharm/Web Scraping/tr_json/t2.py"
{'年份': '2018年', '分类': '游客人数', '月份': '1月', 'A市': 562.0, 'B市': 264.0, 'C市': 481.0}
{'年份': '2018年', '分类': '旅游收入', '月份': '2月', 'A市': 294.0, 'B市': 186.0, 'C市': 985.0}
{'年份': '2018年', '分类': '游客人数', '月份': '1月', 'A市': 264.0, 'B市': 241.0, 'C市': 156.0}
{'年份': '2018年', '分类': '旅游收入', '月份': '2月', 'A市': 502.0, 'B市': 564.0, 'C市': 594.0}

Process finished with exit code 0

统一解决双引号问题
参考：解决python控制台输出单引号如何变成双引号的问题

# -*- coding: utf-8 -*-

import json
import os
import random

list01 = {"esm_id": random.randint(10000, 100000), "one": "01", "a": "4"}
list02 = {"esm_id": random.randint(10000, 100000), "two": "02", "b": "5"}
list03 = {"esm_id": random.randint(1000, 100000), "three": "03", "c": "6"}

def lists(li, ran):
    arr = []
    for k in range(1, ran):
        arr.insert(k, li)
    return arr
    # print arr

# os.system('pdf2txt.py -o "E:\PDF\output.txt" %s' % (strSource))

# print(lists(list02,101))
print(json.dumps(lists(list02, 101)))

运行结果

D:\python\Python3.9.6\python.exe "E:/Data Analysis/Pycharm/Web Scraping/tr_json/t2.py"
[{"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}, {"esm_id": 75651, "two": "02", "b": "5"}]

Process finished with exit code 0

参考：（在python中输出双引号问题的解决方案）不是我想要的

a="Albert Einstein once said,"
b="A person who never made a mistake never tried anything new."
full=a+"\""+b+"\""
print(full)
full=a+'"'+b+'"'
print(full)

参考：（python 从excel中读取数据转换成json字符串）
就是这样的效果

#-*- encoding:utf-8 -*-
import json
import xlrd

def readExcel():
    # 打开excel表单
    filename = u're.xlsx'
    excel = xlrd.open_workbook(filename)

    # 得到第一张表单
    sheet1 = excel.sheets()[0]
    #找到有几列几列
    nrows = sheet1.nrows #行数
    ncols = sheet1.ncols #列数

    totalArray=[]
    title=[]
    # 标题
    for i in range(0,ncols):
        title.append(sheet1.cell(0,i).value);

    #数据
    for rowindex in range(1,nrows):
        dic={}
        for colindex in range(0,ncols):
            s=sheet1.cell(rowindex,colindex).value
            dic[title[colindex]]=s
        totalArray.append(dic);

    return json.dumps(totalArray,ensure_ascii=False)

print(readExcel())

运行结果

[{"年份": "2018年", "分类": "游客人数", "月份": "1月", "A市": 562.0, "B市": 264.0, "C市": 481.0}, {"年份": "2018年", "分类": "旅游收入", "月份": "", "A市": 294.0, "B市": 186.0, "C市": 985.0}, {"年份": "2018年", "分类": "游客人数", "月份": "1月", "A市": "", "B市": 241.0, "C市": 156.0}, {"年份": "2018年", "分类": "旅游收入", "月份": "2月", "A市": 502.0, "B市": 564.0, "C市": 594.0}]

参考：Python: 使用xlrd读取Excel文件

参考：JSON日期格式转换

修改：三个表同时转换

# -*- coding: utf-8 -*-
# Excel to json by xlrd

import xlrd
def get_data(sheet1):
    data = xlrd.open_workbook(r'try_data.xlsx')
    table = data.sheets()[sheet1]

    nor = table.nrows
    nol = table.ncols
    dict = {}

# 资产损益表
    for i in range(1,4):   # 指定损益表key从第一行到第n列
        for j in range(nol):   # 指定损益表value遍历
            key = table.cell_value(0,j)
            value = table.cell_value(i,j)
            dict[key] = value
        yield dict

# 资产负债表
    for i in range(5,8):
        for j in range(nol):
            key = table.cell_value(0, j)
            value = table.cell_value(i, j)
            dict[key] = value
        yield dict

# 现金流量表
    for i in range(9,12):
        for j in range(nol):
            key = table.cell_value(0, j)
            value = table.cell_value(i, j)
            dict[key] = value
        yield dict

if __name__ == '__main__':
    for i in get_data(0):
        print(i)

参考：（Excel中文本替换函数）解决符号替换问题，单引号替换为双引号

参考：Python+xlrd对excel表格数据处理统计

参考：用python打包exe应用程序-PyInstaller

参考：python界面-用python编写简单ui界面窗口

参考：常用的格式化字符串：

符号	说明
%d	整数
%f%F	浮点数
%e%E	科学计数
%g%G	e 和%f/%E 和%F 的简写
%%	输出%

参考：bytes类型转字符串 字典字符串之间的转换 eval 函数