I wrote a recent article
going over how to convert json to xlsx and back again using openpyxl in python.
See http://www.whiteboardcoder.com/2020/02/openpyxl-and-json.html
[1]
Now I want to up the ante and do the same thing but with a
more complex json that will convert into multiple sheets in the excel file.
Simple test
Let me create a simple json file that represents a budget
where it also has years that will be converted into sheets in Excel
{
"2018":{
"January": {
"food": 240.5,
"heating": 89.2,
"rent": 1709.10
},
"February": {
"food": 202.5,
"heating": 112.2,
"rent": 1709.10
},
"March":
{
"food": 320.5,
"heating": 45.2,
"rent": 1709.10
}
},
"2019":{
"January": {
"food": 120.5,
"heating": 88.2,
"rent": 1809.10
},
"February": {
"food": 102.5,
"heating": 122.2,
"rent": 1809.10
},
"March":
{
"food": 120.5,
"heating": 35.2,
"rent": 1809.10
}
},
"2020":{
"January": {
"food": 220.5,
"heating": 18.2,
"rent": 1909.10
},
"February": {
"food": 223.5,
"heating": 12.2,
"rent": 1909.10
},
"March":
{
"food": 120.5,
"heating": 25.2,
"rent": 1909.10
}
}
}
|
Save this in a file called original.jsonYou can use a tool
like https://jsonlint.com/
To confirm it is in the correct format.
Or you can use jq from the command line
> jq .
original.json
|
Simple python script to create xls file from json
> vi createxls_from_json_multiple_sheets.py
|
And place the following in it
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
def populate_sheet(json_data, sheet):
sheet.cell(1,1,
"Month")
sheet.cell(1,2,
"food")
sheet.cell(1,3,
"heating")
sheet.cell(1,4,
"rent")
row = 1
for month in
json_data.keys():
row+=1
sheet.cell(row,1,month)
sheet.cell(row,2,float(json_data[month]["food"]))
sheet.cell(row,3,float(json_data[month]["heating"]))
sheet.cell(row,4,float(json_data[month]["rent"]))
#############################################
# MAIN
#############################################
if __name__ == '__main__':
json_data = {}
with
open("original.json") as json_file:
json_data =
json.load(json_file)
wb = Workbook()
#When you make a
new workbook you get a new blank active sheet
#We need to
delete it since we do not want it
wb.remove(wb.active)
for year in
json_data.keys():
sheet =
wb.create_sheet(title=year)
populate_sheet(json_data[year], sheet)
#Save it to excel
wb.save("test_json.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/92e22a2772befc57ba858333175433ce
Now chmod it and run it
> chmod u+x
createxls_from_json_multiple_sheets.py
> ./createxls_from_json_multiple_sheets.py
|
Now open it up
Boom
Now let me create another program to convert the xls file
into a json file.
> vi
createjson_from_xslx_multiple_sheet.py
|
And place the following in it
#!/usr/bin/env
python3
import
openpyxl
import
json
from
openpyxl import load_workbook
def
get_sheet_dict(ws):
year_dict = {}
column_headers = {}
#Returns a Tuple that are cell type
first_row = ws[1]
#Skip the first column
for cell in first_row[1:]:
column_headers[cell.column]=cell.internal_value
for row in ws.iter_rows(min_row=2):
month = row[0].internal_value
temp_cell_dict = {}
#Skip the month cell
for cell in row[1:]:
#store the data in a dict with keys as
column names
col_name = column_headers[cell.column]
temp_cell_dict[col_name]=cell.internal_value
#Set the month key to be contents of row
year_dict[month] = temp_cell_dict
return year_dict
#############################################
# MAIN
#############################################
if
__name__ == '__main__':
wb =
load_workbook(filename="test_json.xlsx")
budget_dict = {}
for ws in wb:
year_dict = get_sheet_dict(ws)
budget_dict[ws.title] = year_dict
#Convert dict to JSON
data_json = json.dumps(budget_dict)
#Write json file
with open("new.json",
"w") as f:
f.write(data_json)
|
> chmod u+x
createjson_from_xslx_multiple_sheet.py
> ./createjson_from_xslx_multiple_sheet.py
|
Now use the jq tool to see it
> jq . new.json
|
Use diff to see it is identical to the original json
> diff
original.json new.json
|
The files are not exactly identical…
But is the json itself a match we can use jq to help us figure this out.
But is the json itself a match we can use jq to help us figure this out.
> diff <(jq
-S . original.json) <(jq -S . new.json)
|
And if it returns nothing then it is a match.
Let me change one thing by hand in the new.json and see if
it catches it.
> diff <(jq
-S . original.json) <(jq -S . new.json)
|
Yep J
References
[1] openpyxl and json
Thanks for the informative article. This is one of the best resources I have found in quite some time.
ReplyDeleteXamarin Training in Chennai
Xamarin Course in Chennai
German Classes in Chennai
IELTS Coaching in Chennai
spoken english centre in chennai
Japanese Classes in Chennai
Spoken English Classes in Chennai
spanish classes in chennai
content writing course in chennai
Xamarin Training in OMR
Xamarin Training in Porur
IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.
DeleteIEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.
corporate training in chennai corporate training in chennai
corporate training companies in india corporate training companies in india
corporate training companies in chennai corporate training companies in chennai
I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai
Innovative blog thanks for sharing this information.
ReplyDeleteSelenium Training in chennai | Selenium Training in annanagar | Selenium Training in omr | Selenium Training in porur | Selenium Training in tambaram | Selenium Training in velachery
It's very useful article with inforamtive and insightful content and i had good experience with this information. We, at the CRS info solutions ,help candidates in acquiring certificates, master interview questions, and prepare brilliant resumes.Go through some helpful and rich content Salesforce Admin syllabus from learn in real time team. This Salesforce Development syllabus is 100% practical and highly worth reading. Recently i have gone through Salesforce Development syllabus and Salesforce Admin syllabus which includes Salesforce training in USA so practically designed.
ReplyDeleteGone through this wonderful coures called Salesforce Certification Training in Dallas who are offering fully practical course, who parent is Salesforce Training in USA and they have students at Salesforce Training classes in Canada institutes.
ReplyDeleteI was looking for some decent stuff on the subject and have had no luck so far. You just had a new big fan! ...
ReplyDelete360DigiTMG Data Science Courses
I just found this blog and hope it continues. Keep up the great work, it's hard to find good ones. I added to my favorites. Thank you.
ReplyDelete360DigiTMG Data Science Certification
Very interesting article with unique content and helpful information thank you.
ReplyDeleteData Science Course in Hyderabad 360DigiTMG
Woohoo! It is an amazing and useful article. I really like. It's so good and so amazing. I am amazed. I hope you will continue to do your job in this way in the future as well.
ReplyDeleteBusiness Analytics Course in Bangalore
Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
ReplyDeleteData Analytics Course in Bangalore
Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
ReplyDeleteCyber Security Course in Bangalore
Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
ReplyDeleteCyber Security Training in Bangalore
Really nice and interesting blog information shared was valuable and enjoyed reading this one. Keep posting. Thanks for sharing.
ReplyDeleteData Science Training in Hyderabad
Happy to chat on your blog, I feel like I can't wait to read more reliable posts and think we all want to thank many blog posts to share with us.
ReplyDeleteArtificial Intelligence Course in Bangalore
Make video how you do it and post on youtube. If you want to post your video on tiktok too, you will need to buy tiktok likes from this site https://soclikes.com/buy-tiktok-likes
ReplyDeleteGreat article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
ReplyDeletetypeerror nonetype object is not subscriptable
Fantastic blog with high quality content found very valuable and enjoyed reading thank you.
ReplyDeleteData Science Course
Thank you for sharing such a useful post with us, it will useful for everybody, so keep it up that is decent work.data science training in Hyderabad
ReplyDeleteI've read this post and if I could I desire to suggest you some interesting things or suggestions. Perhaps you could write next articles referring to this article. I want to read more things about it!
ReplyDeletedata science training
me up with some sort of fake birth certificate because as we know robots are not born. An artificial intelligence system could indeed have a better chance of winning elections by merely surfing the news, finding out what was on data science course in india
ReplyDeletewonderful article contains lot of valuable information. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
ReplyDeleteThis article resolved my all queries.good luck an best wishes to the team members.continue posting.learn digital marketing use these following link
Digital Marketing Course in Chennai
I have to search sites with relevant information ,This is a
ReplyDeletewonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
Data Science Course in Bangalore
I have to search sites with relevant information ,This is a
ReplyDeletewonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
Data Science Training in Bangalore
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
ReplyDeleteData Science Course in Bangalore
I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
ReplyDeletedata analytics course in bangalore
ReplyDeleteImpressive. Your story always brings hope and new energy. Keep up the good work.
business analytics course
I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
ReplyDeleteartificial intelligence course in pune
I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
ReplyDeletedata analytics course in bangalore