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
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 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
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
ReplyDeleteThank 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
ReplyDeleteme 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
ReplyDeleteImpressive. Your story always brings hope and new energy. Keep up the good work.
business analytics course
I really enjoyed reading your blog. It was very well written and easy to understand. Unlike other blogs that I have read which are actually not very good. Thank you so much!
ReplyDeleteData Science Training in Bangalore
Extraordinary blog went amazed with the content that they have developed in a very descriptive manner. This type of content surely ensures the participants to explore themselves. Hope you deliver the same near the future as well. Gratitude to the blogger for the efforts.
ReplyDeleteMachine Learning Course in Bangalore
Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.
ReplyDeletedata analytics courses in bangalore with placement
I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
ReplyDeletedata science in bangalore
I think this is a really good article. You make this information interesting and engaging. You give readers a lot to think about and I appreciate that kind of writing.
ReplyDeletedata scientist training and placement in hyderabad
I really enjoyed reading your blog. It was very well written and easy to understand. Unlike other blogs that I have read which are actually not very good. Thank you so much!
ReplyDeleteData Science Training in Bangalore
I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
ReplyDeleteData Science Course in Chennai
All of these posts were incredible perfect. It would be great if you’ll post more updates and your website is really cool and this is a great inspiring article.
ReplyDeleteArtificial Intelligence course in Chennai
It's like you understand the topic well, but forgot to include your readers. Maybe you should think about it from several angles.
ReplyDeleteBusiness Analytics Course
I finally found a great article here. I will stay here again. I just added your blog to my bookmarking sites. Thank you. Quality postings are essential to get visitors to visit the website, that's what this website offers.
ReplyDeleteBest Data Science Courses in Bangalore
You actually make it seem like it's really easy with your acting, but I think it's something I think I would never understand. I find that too complicated and extremely broad. I look forward to your next message. I'll try to figure it out!
ReplyDeleteYou actually make it seem like it's really easy with your acting, but I think it's something I think I would never understand. I find that too complicated and extremely broad. I look forward to your next message. I'll try to figure it out!
wow ... what a great blog, this writer who wrote this article is really a great blogger, this article inspires me so much to be a better person.
ReplyDeleteDigital Marketing Course in Bangalore
Wow, happy to see this awesome post. I hope this think help any newbie for their awesome work and by the way thanks for share this awesomeness, i thought this was a pretty interesting read when it comes to this topic. Thank you..
ReplyDeleteArtificial Intelligence Course
You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.
ReplyDeleteBest Data Science Courses in Bangalore
Awesome article. I enjoyed reading your articles. this can be really a good scan for me. wanting forward to reading new articles. maintain the nice work!
ReplyDeleteData Science Courses in Bangalore
Your site is truly cool and this is an extraordinary moving article and If it's not too much trouble share more like that. Thank You..
ReplyDeleteDigital Marketing Course in Hyderabad
Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts
ReplyDeleteData Science Certification in Hyderabad
i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
ReplyDeletedata science course
This is a very nice one and gives in-depth information. I am really happy with the quality and presentation of the article. I’d really like to appreciate the efforts you get with writing this post. Thanks for sharing.
ReplyDeletePython classes in Ahmednagar
I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
ReplyDeletebusiness analytics course
I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
ReplyDeleteartificial intellingence training in chennai
i am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
ReplyDeleteartificial intelligence training in chennai
I wanted to leave a little comment to support you and wish you the best of luck. We wish you the best of luck in all of your blogging endeavors.
ReplyDeleteDigital Marketing Course in Bangalore
I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
ReplyDeleteData Science Training in Chennai
After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
ReplyDeletedigital marketing courses in hyderabad with placement
I have voiced some of the posts on your website now, and I really like your blogging style. I added it to my list of favorite blogging sites and will be back soon ...
ReplyDeleteDigital Marketing Training in Bangalore
I was actually browsing the internet for certain information, accidentally came across your blog found it to be very impressive. I am elated to go with the information you have provided on this blog, eventually, it helps the readers whoever goes through this blog. Hoping you continue the spirit to inspire the readers and amaze them with your fabulous content.
ReplyDeleteth
Data Science Course in Faridabad
It's like you understand the topic well, but forgot to include your readers. Maybe you should think about it from several angles.
ReplyDeleteBusiness Analytics Course
It took a while to understand all the comments, but I really enjoyed the article. It turned out to be really helpful for me and I'm positive for all the reviewers here! It's always nice to be able to not only be informed, but also entertained! I'm sure you enjoyed writing this article.
ReplyDeleteBest Data Science Courses in Bangalore
It fully emerged to crown Singapore's southern shores and has undoubtedly put it on the world residential monument map. Still, I scored more points than I have in one season for GS. I think it would be hard to find someone with the same consistency that I have had over the years, so I'm happy.
ReplyDeleteData Analytics Course in Bangalore
Really impressed! Everything is a very open and very clear clarification of the issues. It contains true facts. Your website is very valuable. Thanks for sharing.
ReplyDeleteDigital Marketing Training in Bangalore
You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.
ReplyDeleteArtificial Intelligence Training in Bangalore
A good blog always contains new and exciting information and as I read it I felt that this blog really has all of these qualities that make a blog.
ReplyDeleteData Science Training in Bangalore
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.
ReplyDeleteMachine Learning Course in Bangalore
It is late to find this act. At least one should be familiar with the fact that such events exist. I agree with your blog and will come back to inspect it further in the future, so keep your performance going.
ReplyDeleteDigital Marketing Training in Bangalore
I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
ReplyDeleteData Analytics Course
I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
ReplyDeleteiot course in bangalore
It's like you've got the point right, but forgot to include your readers. Maybe you should think about it from different angles.
ReplyDeleteData Analytics Course in Bangalore
I am glad to discover this page. I have to thank you for the time I spent on this especially great reading !! I really liked each part and also bookmarked you for new information on your site.
ReplyDeleteData Science Course in Noida
A good blog always comes-up with new and exciting information and while reading I have feel that this blog is really have all those quality that qualify a blog to be a one.
ReplyDeletei am glad to discover this page : i have to thank you for the time i spent on this especially great reading !! i really liked each part and also bookmarked you for new information on your site.
ReplyDeletedata science training in gurgaon
am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
ReplyDeleteservicenow training in Bangalore
I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.
ReplyDeleteData Scientist Course in Bangalore
After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
ReplyDeleteservicenow training in Bangalore
I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.
ReplyDeleteBest Cyber Security Training Institute in Bangalore
Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.
ReplyDeleteServiceNow Training in Chennai
Truly, this article is really one of the very best in the history of articles. I am a antique ’Article’ collector and I sometimes read some new articles if I find them interesting. And I found this one pretty fascinating and it should go into my collection. Very good work!
ReplyDeleteai course in hyderabad
Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing. data scientist course in patna
ReplyDeleteVery informative Blog! There is so much information here that can help thank you for sharing.
ReplyDeleteservicenow training and placement in hyderabad
I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it..
ReplyDeletecloud computing online training in hyderabad
Nice blog. Found this while searching through ai course in delhi
ReplyDeleteHey there! Do you know if they make any plugins to help with Search Engine Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains. If you know of any please share. Thank you!| cyber security course in delhi
ReplyDeleteAfter reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
ReplyDeleteMulesoft training in hyderabad
You know your projects stand out from the crowd. There is something special about them. I think they're all really cool!
ReplyDeleteBusiness Analytics Course in Durgapur
I like viewing this web page which comprehend the price of delivering the excellent useful resource free of charge and truly adored reading your posting. Thank you!
ReplyDeleteData Science Certification Course
Very informative message! There is so much information here that can help any business get started with a successful social media campaign!
ReplyDeleteData Science Training Institutes in Bangalore
I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, will provide more information on these topics in future articles.
ReplyDeletedata science course in london
I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
ReplyDeletedata science training in delhi
I am always searching online for articles that can help me and you made some good points in Features also. Keep working, great job
ReplyDeleteData Science Training
Good blog and absolutely exceptional. You can do a lot better, but I still say it's perfect. Keep doing your best.
ReplyDeleteData Science Training in Durgapur
Wonderful illustrated information. Thank you. It will certainly be very useful for my future projects. I would love to see more articles on the same topic!
ReplyDeleteData Science Training in Bangalore
I would like to say that this blog really convinced me to do it and thanks for informative post and bookmarked to check out new things of your post…
ReplyDeleteData Science Institute in Noida
I truly like you're composing style, incredible data, thankyou for posting.
ReplyDeletecyber security training malaysia
I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
ReplyDeletebusiness analytics course in varanasi
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.
ReplyDeleteData Science Training Institutes in Bangalore
I read your excellent post. It's a great job. I enjoyed reading your post for the first time. I want to thank you for this publication. Thank you...
ReplyDeleteData Science Course in Patna
I want to thank you for your time in this wonderful read which is really appreciable and put you in your favorites to see new things on your blog, a must-have blog!
ReplyDeleteBusiness Analytics Course in Noida
Wonderful blog post. It's absolute magic on your part! I have never seen a more wonderful article than this. You really made my day today with this. Hope you continue like this!
ReplyDeleteData Scientist Course in Patna
Nice post. This is a great article and am pretty much pleased with your good work. Very helpful information. Thank you.
ReplyDeleteBest Data Science Courses
I was looking for some decent stuff on the subject and have had no luck so far. You just had a new big fan! ...
ReplyDeleteData Scientist Training in Bangalore
Thank you for posting this information. I just want to let you know that I just visited your site and find it very interesting and informative. I look forward to reading most of your posts.
ReplyDeleteData Science Course in Patna
Its content is brilliant in many ways. I think it is an attractive and revealing material. Thank you very much for caring about your content and your readers.
ReplyDeleteBusiness Analytics Course in Patna
Through this post, I realize that your great information in playing with all the pieces was exceptionally useful. I advise this is the primary spot where I discover issues I've been scanning for. You have a smart yet alluring method of composing.
ReplyDeleteSome really good points you wrote here ... Great things ... I think you raised some really interesting points. Keep up the good work.
ReplyDeleteBest Data Analytics Courses in Bangalore
Pleasant data, important and incredible structure, as offer great stuff with smart thoughts and ideas, loads of extraordinary data and motivation, the two of which I need, because of offer such an accommodating data here.
ReplyDeleteI always search online for articles that can help me. Obviously, there is a lot to know about this. I think you made a few good points about the features as well. Keep up the good work!
ReplyDeleteData Scientist Course in Ernakulam
360DigiTMG, the top-rated organisation among the most prestigious industries around the world, is an educational destination for those looking to pursue their dreams around the globe. The company is changing careers of many people through constant improvement, 360DigiTMG provides an outstanding learning experience and distinguishes itself from the pack. 360DigiTMG is a prominent global presence by offering world-class training. Its main office is in India and subsidiaries across Malaysia, USA, East Asia, Australia, Uk, Netherlands, and the Middle East.
ReplyDeleteI was very happy to find this site. I wanted to thank you for this excellent reading !! I really enjoy every part and have bookmarked you to see the new things you post.
ReplyDeleteData Science Course in Durgapur
Very great post which I really enjoy reading this and it is not everyday that I have the possibility to see something like this. Thank You.
ReplyDeleteBest Online Data Science Courses
Very informative message! There is so much information here that can help any business start a successful social media campaign!
ReplyDeletedata science training in london
You have done a great job and will definitely dig it and personally recommend to my friends. Thank You.
ReplyDeleteData Science Online Training
Very informative Blog! There is so much information here that can help thank you for sharing.
ReplyDeleteData Analytics Course in Bangalore
Really, this article is truly one of the best in article history. I am a collector of old "items" and sometimes read new items if I find them interesting. And this one that I found quite fascinating and should be part of my collection. Very good work!
ReplyDeleteData Scientist Training in Bangalore
Thank you so much for this amazing blog. Keep sharing this type of content with us. If anyone wants to learn DATA SCIENCE in Delhi, I will recommend High Technologies Solutions training institute.
ReplyDeleteFor any further information please call +919311002620 or you can visit website
https://htsindia.com/Courses/python/python-with-data-science-training-course
Superb Information, I really appreciated with it, This is fine to read and valuable pro potential, I really bookmark it, pro broaden read. Appreciation pro sharing. I like it.Data Analytics Course in Dombivli
ReplyDeleteIt took me a while to read all the reviews, but I really enjoyed the article. This has proven to be very helpful to me and I'm sure all the reviewers here! It's always nice to be able to not only be informed, but also have fun!
ReplyDeleteData Science Course in Ernakulam
http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html
ReplyDeleteI was basically inspecting through the web filtering for certain data and ran over your blog. I am flabbergasted by the data that you have on this blog. It shows how well you welcome this subject. Bookmarked this page, will return for extra.digital marketing training malaysia
ReplyDeleteGet Data Science Certification from top-ranked universities UTM, Malaysia, and IBM. We provide extensive training for the future-ready workforce.data analytics course in rohtak
ReplyDeleteNice thanks for sharing informative post like this keep posting if like more details visit my website linkhttps://mulemasters.in/
ReplyDeleteThis is really very nice post you shared, i like the post, thanks for sharing..
ReplyDeletedata scientist course
We appreciate you sharing this helpful information with us. do continue doing this good work as it will be helpful to everyone. Do Visit at python training course in bhopal
ReplyDeleteThis post was so engaging that I have sent this link to many of my friends, so that they can understand and appreciate data science as much as I did. Data Science Training Certification In Chennai
ReplyDelete