通过Python将JSON转换为Excel [英] Convert JSON to Excel by Python
本文介绍了通过Python将JSON转换为Excel的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有一个需要转换为Excel的JSON.我正在将Python 3.8与xlsxwriter库一起使用.以下是示例JSON.
{" companyId":"123456"，" companyName":"测试" ;，"companyStatus":活动"，文档":{员工":{"employeeId":"EM1567"，"employeeLastName":"Test Last"，"employeeFirstName":测试拳头"；}，"expenseEntry":[{分配":[{"allocationId":"03B249B3598"，新闻":[{"journalAccountCode":"888"，"journalPayee":"EMPL"，"journalPayer":"COMP"，"taxGuid":["51645A638114E"]}，{"journalAccountCode":"999"，"journalPayee":"EMPL"，"journalPayer":"EMPL"，"taxGuid":["8114E51645A63"]}，]，税":[{"taxCode":"TAX123"，" taxSource" ;:" SYST"}，{"taxCode":"TAX456"，" taxSource" ;:" SYST"}]}]，"approvedAmount":200.0，"entryDate":"2020-12-10"，"entryId":"ENTRY9988"；}]，报告":{" currencyCode":"USD"，"reportCreationDate":"2020-12-10"，"reportId":"ACA849BBB"，"reportName":测试报告"，总批准金额":200.0}}，"id":"c71b7d756f549"} 以及我当前的代码:
我尝试了json_excel_converter
来自json_excel_converter导入转换器的 从json_excel_converter.xlsx导入Writer转换= Converter()conv.convert(data，Writer(file ='test.xlsx')) 得到结果
这是我的期望
在这种情况下，有人可以帮助我吗?非常感谢.
解决方案
以下是您要查找的代码.我使用 XlsxWriter 包来完成此操作.首先，我用一些单元格格式的东西制作了模板.之后，我根据您的JSON输入了值.
<代码>导入xlsxwriter从itertools导入zip_longest数据= [{" companyId":"123456"，" companyName":"测试" ;，"companyStatus":活动"，文档":{员工":{"employeeId":"EM1567"，"employeeLastName":"Test Last"，"employeeFirstName":测试拳头"；}，"expenseEntry":[{分配":[{"allocationId":"03B249B3598"，新闻":[{"journalAccountCode":"888"，"journalPayee":"EMPL"，"journalPayer":"COMP"，"taxGuid":["51645A638114E"]}，{"journalAccountCode":"999"，"journalPayee":"EMPL"，"journalPayer":"EMPL"，"taxGuid":["8114E51645A63"]}，]，税":[{"taxCode":"TAX123"，" taxSource" ;:" SYST"}，{"taxCode":"TAX456"，" taxSource" ;:" SYST"}]}]，"approvedAmount":200.0，"entryDate":"2020-12-10"，"entryId":"ENTRY9988"；}]，报告":{" currencyCode":"USD"，"reportCreationDate":"2020-12-10"，"reportId":"ACA849BBB"，"reportName":测试报告"，总批准金额":200.0}}，"id":"c71b7d756f549"}]xlsx_file ='您的文件名_此处.xlsx'#定义excel文件工作簿= xlsxwriter.Workbook(xlsx_file)#为我们的工作创建一个工作表，默认为Sheet1.工作表= workbook.add_worksheet()#通用合并格式merge_format = workbook.add_format({'align':'center'，'valign':'vcenter'})#将所有列宽设置为20worksheet.set_column('A:V'，20)#列明智的模板创建(A-V)worksheet.merge_range(0，0，4，0，'companyId'，merge_format)#Aworksheet.merge_range(0，1，4，1，'companyName'，merge_format)#Bworksheet.merge_range(0，2，4，2，'companyStatus'，merge_format)#Cworksheet.merge_range(0，3，0，20，'document'，merge_format)#C-Uworksheet.merge_range(1、3、1、5，员工"，merge_format)#D-Fworksheet.merge_range(2，3，4，3，'employeeId'，merge_format)#Dworksheet.merge_range(2，4，4，4，'employeeLastName'，merge_format)#Eworksheet.merge_range(2，5，4，5，'employeeFirstName'，merge_format)#Fworksheet.merge_range(1、6、1、15，'expenseEntry'，merge_format)#G-Pworksheet.merge_range(2，6，2，12，'allocation'，merge_format)#G-Mworksheet.merge_range(3，6，4，6，'allocationId'，merge_format)#Gworksheet.merge_range(3，7，3，10，'journal'，merge_format)#H-Kworksheet.write(4，7，'journalAccountCode')#Hworksheet.write(4，8，'journalPayee')#我worksheet.write(4，9，'journalPayer')#Jworksheet.write(4，10，'taxGuid')#Kworksheet.merge_range(3，11，3，12，'tax'，merge_format)#L-Mworksheet.write(4，11，'taxCode')#Lworksheet.write(4，12，'taxSource')#Mworksheet.merge_range(2，13，4，13，'approvedAmount'，merge_format)#Nworksheet.merge_range(2，14，4，14，'entryDate'，merge_format)#Oworksheet.merge_range(2，15，4，15，'entryId'，merge_format)#Pworksheet.merge_range(1，16，1，20，'report'，merge_format)#Q-Uworksheet.merge_range(2，16，4，16，'currencyCode'，merge_format)#Qworksheet.merge_range(2，17，4，17，'reportCreationDate'，merge_format)#Rworksheet.merge_range(2，18，4，18，'reportId'，merge_format)#Sworksheet.merge_range(2，19，4，19，'reportName'，merge_format)#Tworksheet.merge_range(2，20，4，20，'totalApprovedAmount'，merge_format)#Uworksheet.merge_range(0，21，4，21，'id'，merge_format)#V#插入数据行= 5对于数据中的obj:worksheet.write(row，0，obj.get('companyId'))worksheet.write(row，1，obj.get('companyName'))worksheet.write(row，2，obj.get('companyStatus'))document = obj.get('document'，{})#员工详细信息员工= document.get('员工'，{})worksheet.write(第3行，employee.get('employeeId'))worksheet.write(第4行，employee.get('employeeLastName'))worksheet.write(第5行，employee.get('employeeFirstName'))#报告详细信息报告= document.get('报告'，{})worksheet.write(第16行，report.get('currencyCode'))worksheet.write(第17行，report.get('reportCreationDate'))worksheet.write(第18行，report.get('reportId'))worksheet.write(第19行，report.get('reportName'))worksheet.write(第20行，report.get('totalApprovedAmount'))worksheet.write(row，21，obj.get('id'))#费用条目详细信息ensem_entries = document.get('expenseEntry'，[])对于expense_entries中的expense_entry:worksheet.write(第13行，expense_entry.get('approvedAmount'))worksheet.write(第14行，expense_entry.get('entryDate'))worksheet.write(第15行，expense_entry.get('entryId'))#分配细节分配= Expense_entry.get('分配'，[])用于分配中的分配:worksheet.write(第6行，location.get('allocationId'))#日记帐和税款详细信息日志= location.get('journal'，[])税=分配.get('税'，[])用于列表中的journal_and_tax(zip_longest(期刊，税款)):日记帐，税费= journal_and_taxworksheet.write(第7行，journal.get('journalAccountCode'))worksheet.write(第8行，journal.get('journalPayee'))worksheet.write(第9行，journal.get('journalPayer'))worksheet.write(第11行，tax.get('taxCode'))worksheet.write(第12行，tax.get('taxSource'))#taxGuid详细信息tax_guides = journal.get('taxGuid'，[])如果不是tax_guides:行=行+ 1继续对于tax_guides中的tax_guide:worksheet.write(第10行，tax_guide)行=行+ 1#最终关闭创建的Excel文件workbook.close() 一件事，您可以制作自己的模板并将其保存在其他位置，而无需在脚本中创建模板.然后获取该模板的副本，并仅使用脚本添加数据.这将使您有机会制作自己的基本模板，否则，您必须使用脚本来格式化excel，例如边框格式，合并单元格等.
我使用了 zip_longest python内置函数，可从 itertools 压缩为新闻和 tax 对象.只需遵循 Python – Itertools.zip_longest()或https://repl.it/@tonyiscoming/jsontoexcel
I tried with pandas
import pandas as pd
df = pd.json_normalize(data, max_level=5)
df.to_excel('test.xlsx', index=False)
And got the result
I tried with json_excel_converter
from json_excel_converter import Converter
from json_excel_converter.xlsx import Writer
conv = Converter()
conv.convert(data, Writer(file='test.xlsx'))
And got the result
This is my expectation
Would anyone please help me in this case? Thank you so much.
解决方案
Here is the code what you are looking for. I did this using XlsxWriter package. First I made the template with some cell format stuff. After that, I entered values using according to your JSON.
import xlsxwriter
from itertools import zip_longest
data = [
{
"companyId": "123456",
"companyName": "Test",
"companyStatus": "ACTIVE",
"document": {
"employee": {
"employeeId": "EM1567",
"employeeLastName": "Test Last",
"employeeFirstName": "Test Fist"
},
"expenseEntry": [
{
"allocation": [
{
"allocationId": "03B249B3598",
"journal": [
{
"journalAccountCode": "888",
"journalPayee": "EMPL",
"journalPayer": "COMP",
"taxGuid": [
"51645A638114E"
]
},
{
"journalAccountCode": "999",
"journalPayee": "EMPL",
"journalPayer": "EMPL",
"taxGuid": [
"8114E51645A63"
]
},
],
"tax": [
{
"taxCode": "TAX123",
"taxSource": "SYST"
},
{
"taxCode": "TAX456",
"taxSource": "SYST"
}
]
}
],
"approvedAmount": 200.0,
"entryDate": "2020-12-10",
"entryId": "ENTRY9988"
}
],
"report": {
"currencyCode": "USD",
"reportCreationDate": "2020-12-10",
"reportId": "ACA849BBB",
"reportName": "Test Report",
"totalApprovedAmount": 200.0
}
},
"id": "c71b7d756f549"
}
]
xlsx_file = 'your_file_name_here.xlsx'
# define the excel file
workbook = xlsxwriter.Workbook(xlsx_file)
# create a sheet for our work, defaults to Sheet1.
worksheet = workbook.add_worksheet()
# common merge format
merge_format = workbook.add_format({'align': 'center', 'valign': 'vcenter'})
# set all column width to 20
worksheet.set_column('A:V', 20)
# column wise template creation (A-V)
worksheet.merge_range(0, 0, 4, 0, 'companyId', merge_format) # A
worksheet.merge_range(0, 1, 4, 1, 'companyName', merge_format) # B
worksheet.merge_range(0, 2, 4, 2, 'companyStatus', merge_format) # C
worksheet.merge_range(0, 3, 0, 20, 'document', merge_format) # C-U
worksheet.merge_range(1, 3, 1, 5, 'employee', merge_format) # D-F
worksheet.merge_range(2, 3, 4, 3, 'employeeId', merge_format) # D
worksheet.merge_range(2, 4, 4, 4, 'employeeLastName', merge_format) # E
worksheet.merge_range(2, 5, 4, 5, 'employeeFirstName', merge_format) # F
worksheet.merge_range(1, 6, 1, 15, 'expenseEntry', merge_format) # G-P
worksheet.merge_range(2, 6, 2, 12, 'allocation', merge_format) # G-M
worksheet.merge_range(3, 6, 4, 6, 'allocationId', merge_format) # G
worksheet.merge_range(3, 7, 3, 10, 'journal', merge_format) # H-K
worksheet.write(4, 7, 'journalAccountCode') # H
worksheet.write(4, 8, 'journalPayee') # I
worksheet.write(4, 9, 'journalPayer') # J
worksheet.write(4, 10, 'taxGuid') # K
worksheet.merge_range(3, 11, 3, 12, 'tax', merge_format) # L-M
worksheet.write(4, 11, 'taxCode') # L
worksheet.write(4, 12, 'taxSource') # M
worksheet.merge_range(2, 13, 4, 13, 'approvedAmount', merge_format) # N
worksheet.merge_range(2, 14, 4, 14, 'entryDate', merge_format) # O
worksheet.merge_range(2, 15, 4, 15, 'entryId', merge_format) # P
worksheet.merge_range(1, 16, 1, 20, 'report', merge_format) # Q-U
worksheet.merge_range(2, 16, 4, 16, 'currencyCode', merge_format) # Q
worksheet.merge_range(2, 17, 4, 17, 'reportCreationDate', merge_format) # R
worksheet.merge_range(2, 18, 4, 18, 'reportId', merge_format) # S
worksheet.merge_range(2, 19, 4, 19, 'reportName', merge_format) # T
worksheet.merge_range(2, 20, 4, 20, 'totalApprovedAmount', merge_format) # U
worksheet.merge_range(0, 21, 4, 21, 'id', merge_format) # V
# inserting data
row = 5
for obj in data:
worksheet.write(row, 0, obj.get('companyId'))
worksheet.write(row, 1, obj.get('companyName'))
worksheet.write(row, 2, obj.get('companyStatus'))
document = obj.get('document', {})
# employee details
employee = document.get('employee', {})
worksheet.write(row, 3, employee.get('employeeId'))
worksheet.write(row, 4, employee.get('employeeLastName'))
worksheet.write(row, 5, employee.get('employeeFirstName'))
# report details
report = document.get('report', {})
worksheet.write(row, 16, report.get('currencyCode'))
worksheet.write(row, 17, report.get('reportCreationDate'))
worksheet.write(row, 18, report.get('reportId'))
worksheet.write(row, 19, report.get('reportName'))
worksheet.write(row, 20, report.get('totalApprovedAmount'))
worksheet.write(row, 21, obj.get('id'))
# expenseEntry details
expense_entries = document.get('expenseEntry', [])
for expense_entry in expense_entries:
worksheet.write(row, 13, expense_entry.get('approvedAmount'))
worksheet.write(row, 14, expense_entry.get('entryDate'))
worksheet.write(row, 15, expense_entry.get('entryId'))
# allocation details
allocations = expense_entry.get('allocation', [])
for allocation in allocations:
worksheet.write(row, 6, allocation.get('allocationId'))
# journal and tax details
journals = allocation.get('journal', [])
taxes = allocation.get('tax', [])
for journal_and_tax in list(zip_longest(journals, taxes)):
journal, tax = journal_and_tax
worksheet.write(row, 7, journal.get('journalAccountCode'))
worksheet.write(row, 8, journal.get('journalPayee'))
worksheet.write(row, 9, journal.get('journalPayer'))
worksheet.write(row, 11, tax.get('taxCode'))
worksheet.write(row, 12, tax.get('taxSource'))
# taxGuid details
tax_guides = journal.get('taxGuid', [])
if not tax_guides:
row = row + 1
continue
for tax_guide in tax_guides:
worksheet.write(row, 10, tax_guide)
row = row + 1
# finally close the created excel file
workbook.close()
One thing, instead of creating a template in the script you can make your own one and save it somewhere else. Then get the copy of that template and just add data using the script. This will give you a chance to make your own base template, otherwise, you have to format your excel using the script, such as border formattings, merge cells, etc.
I used zip_longest python built-in function from itertools to zip journal and tax objects. Just follow Python – Itertools.zip_longest() or Python's zip_longest Function article for examples. If you didn't understand anything from my code, please comment below.
这篇关于通过Python将JSON转换为Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
