python处理json文件_python读取json文件转成excel

python处理excel有xlwt，openpyxl等，而xlwt只支持excel2003，也就是最多有256列，而openpyxl则支持excel2007以上，最多65536列。下面是两个的程序。

xlwt为

import json

import xlwt

def readFromJson(file):

with open(file, 'r', encoding='utf8') as fr:

jsonData = json.load(fr)

return jsonData

def writeToExcel(file):

json = readFromJson(file)

excel = xlwt.Workbook()

sheet1 = excel.add_sheet('sheet1', cell_overwrite_ok=True)

sheet2 = excel.add_sheet('sheet2', cell_overwrite_ok=True)

length = len(json)

i = 0

while i < length:

eachLine = json[i]

questions = eachLine['questions']

answer = eachLine['answer']

questionSize = len(questions)

if (questionSize > 256):

print(i + 1, questionSize)

j = 0

while j < questionSize:

ques = questions[j]

eachQues = ques['question']

if j < 256:

sheet1.write(i, j, eachQues)

if j == 0:

sheet2.write(i, 0, eachQues)

j = j + 1

sheet2.write(i, 1, answer)

i = i + 1

excel.save('doc/answer.xls')

if __name__ == '__main__':

writeToExcel('doc/kb.json')

openpyxl为

import openpyxl

import json

def readFromJson(file):

with open(file, 'r', encoding='utf8') as fr:

jsonData = json.load(fr)

return jsonData

def writeToExcel(file):

json = readFromJson(file)

excel = openpyxl.Workbook()

sheet1 = excel.create_sheet('sheet1', index=0)

sheet2 = excel.create_sheet('sheet2', index=0)

length = len(json)

i = 0

while i < length:

eachLine = json[i]

questions = eachLine['questions']

answer = eachLine['answer']

questionSize = len(questions)

j = 0

while j < questionSize:

ques = questions[j]

eachQues = ques['question']

sheet1.cell(row=i + 1, column=j + 1, value=eachQues)

if j == 0:

sheet2.cell(row=i + 1, column=1, value=eachQues)

j = j + 1

sheet2.cell(row=i + 1, column=2, value=answer)

i = i + 1

excel.save('doc/answer.xlsx')

if __name__ == '__main__':

writeToExcel('doc/kb.json')

其核心在于读取json的方法和写入excel的方法。