Python handles Excel with XLWT,OPENPYXL, and XLWT only supports excel2003, which is up to 256 columns, while OPENPYXL supports excel2007 above and up to 65536 columns. Here are the two programs.
XLWT for
Import jsonimport xlwtdef Readfromjson (file): With open (file, ' R ', encoding= ' UTF8 ') as Fr:jsondata = Json.load ( FR) Return jsondatadef writetoexcel (file): JSON = Readfromjson (file) Excel = XLWT. Workbook () Sheet1 = Excel.add_sheet (' Sheet1 ', cell_overwrite_ok=true) Sheet2 = Excel.add_sheet (' Sheet2 ', CELL_OVERWR ite_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 ex Cel.save (' Doc/answer.xls ') if __name__ = = ' __main__ ': Writetoexcel (' Doc/kb.json ')
Openpyxl to
Import openpyxlimport jsondef readfromjson (file): With open (file, ' R ', encoding= ' UTF8 ') as Fr:jsonda Ta = Json.load (FR) Return jsondatadef 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 '] Answ er = eachline[' answer ' questionsize = Len (questions) j = 0 while J < Questionsize:que s = 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.cel L (row=i + 1, column=2, value=answer) i = i + 1 excel.save (' doc/answer.xlsx ') if __name__ = = ' __main__ ': writet oexcel (' Doc/kb.json ')
The core is the way to read the JSON and write to Excel.
Python reads JSON files into Excel