Openpyxl and json round 2

Posted on Thursday, February 6, 2020









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")





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.



  > 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


105 comments:

  1. 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.

    ReplyDelete
  2. Gone 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.

    ReplyDelete
  3. 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.

    360DigiTMG Data Science Certification

    ReplyDelete
  4. Very interesting article with unique content and helpful information thank you.
    Data Science Course in Hyderabad 360DigiTMG

    ReplyDelete
  5. Really nice and interesting blog information shared was valuable and enjoyed reading this one. Keep posting. Thanks for sharing.
    Data Science Training in Hyderabad

    ReplyDelete
  6. 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.

    Artificial Intelligence Course in Bangalore

    ReplyDelete
  7. 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

    ReplyDelete
  8. 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

    ReplyDelete
  9. 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

    ReplyDelete
  10. wonderful 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.
    This 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

    ReplyDelete

  11. Impressive. Your story always brings hope and new energy. Keep up the good work.
    business analytics course

    ReplyDelete
  12. 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!

    Data Science Training in Bangalore

    ReplyDelete
  13. 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.

    Machine Learning Course in Bangalore

    ReplyDelete
  14. 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.

    data analytics courses in bangalore with placement

    ReplyDelete
  15. 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.
    data science in bangalore

    ReplyDelete
  16. 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.
    data scientist training and placement in hyderabad

    ReplyDelete
  17. 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!

    Data Science Training in Bangalore

    ReplyDelete
  18. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Science Course in Chennai

    ReplyDelete
  19. 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.
    Artificial Intelligence course in Chennai

    ReplyDelete
  20. It's like you understand the topic well, but forgot to include your readers. Maybe you should think about it from several angles.
    Business Analytics Course

    ReplyDelete
  21. 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.

    Best Data Science Courses in Bangalore

    ReplyDelete
  22. 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!
    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!

    ReplyDelete
  23. 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.
    Digital Marketing Course in Bangalore

    ReplyDelete
  24. 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..
    Artificial Intelligence Course

    ReplyDelete
  25. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.
    Best Data Science Courses in Bangalore

    ReplyDelete
  26. 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!
    Data Science Courses in Bangalore

    ReplyDelete
  27. 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..
    Digital Marketing Course in Hyderabad

    ReplyDelete
  28. Thank a lot. You have done excellent job. I enjoyed your blog . Nice efforts
    Data Science Certification in Hyderabad

    ReplyDelete
  29. 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.
    data science course

    ReplyDelete
  30. 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.
    Python classes in Ahmednagar

    ReplyDelete
  31. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    business analytics course


    ReplyDelete
  32. 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.
    artificial intellingence training in chennai

    ReplyDelete
  33. 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.
    artificial intelligence training in chennai

    ReplyDelete
  34. 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.

    Digital Marketing Course in Bangalore

    ReplyDelete
  35. 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.
    Data Science Training in Chennai

    ReplyDelete
  36. 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.
    digital marketing courses in hyderabad with placement

    ReplyDelete
  37. 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 ...

    Digital Marketing Training in Bangalore

    ReplyDelete
  38. 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.
    th
    Data Science Course in Faridabad

    ReplyDelete
  39. It's like you understand the topic well, but forgot to include your readers. Maybe you should think about it from several angles.

    Business Analytics Course

    ReplyDelete
  40. 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.
    Best Data Science Courses in Bangalore

    ReplyDelete
  41. 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.
    Data Analytics Course in Bangalore

    ReplyDelete
  42. 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.

    Digital Marketing Training in Bangalore

    ReplyDelete
  43. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog.

    Artificial Intelligence Training in Bangalore

    ReplyDelete
  44. 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.

    Data Science Training in Bangalore

    ReplyDelete
  45. 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.

    Machine Learning Course in Bangalore

    ReplyDelete
  46. 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.

    Digital Marketing Training in Bangalore

    ReplyDelete
  47. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course

    ReplyDelete
  48. 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.
    iot course in bangalore

    ReplyDelete
  49. It's like you've got the point right, but forgot to include your readers. Maybe you should think about it from different angles.


    Data Analytics Course in Bangalore

    ReplyDelete
  50. 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.
    Data Science Course in Noida

    ReplyDelete
  51. 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.

    ReplyDelete
  52. 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.
    data science training in gurgaon

    ReplyDelete
  53. am sure it will help many people. Keep up the good work. It's very compelling and I enjoyed browsing the entire blog.
    servicenow training in Bangalore

    ReplyDelete
  54. 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.


    Data Scientist Course in Bangalore

    ReplyDelete
  55. 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.
    servicenow training in Bangalore

    ReplyDelete
  56. 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.


    Best Cyber Security Training Institute in Bangalore

    ReplyDelete
  57. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.
    ServiceNow Training in Chennai

    ReplyDelete
  58. 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!
    ai course in hyderabad

    ReplyDelete
  59. 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

    ReplyDelete
  60. Very informative Blog! There is so much information here that can help thank you for sharing.
    servicenow training and placement in hyderabad

    ReplyDelete
  61. 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..
    cloud computing online training in hyderabad


    ReplyDelete
  62. Nice blog. Found this while searching through ai course in delhi

    ReplyDelete
  63. Hey 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

    ReplyDelete
  64. 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.
    Mulesoft training in hyderabad

    ReplyDelete
  65. You know your projects stand out from the crowd. There is something special about them. I think they're all really cool!

    Business Analytics Course in Durgapur

    ReplyDelete
  66. 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!
    Data Science Certification Course

    ReplyDelete
  67. Very informative message! There is so much information here that can help any business get started with a successful social media campaign!

    Data Science Training Institutes in Bangalore

    ReplyDelete
  68. 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.
    data science course in london

    ReplyDelete
  69. 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
    data science training in delhi

    ReplyDelete
  70. I am always searching online for articles that can help me and you made some good points in Features also. Keep working, great job
    Data Science Training

    ReplyDelete
  71. Good blog and absolutely exceptional. You can do a lot better, but I still say it's perfect. Keep doing your best.

    Data Science Training in Durgapur

    ReplyDelete
  72. 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!

    Data Science Training in Bangalore

    ReplyDelete
  73. 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…
    Data Science Institute in Noida

    ReplyDelete
  74. I truly like you're composing style, incredible data, thankyou for posting.
    cyber security training malaysia

    ReplyDelete
  75. 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
    business analytics course in varanasi

    ReplyDelete
  76. 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.

    Data Science Training Institutes in Bangalore

    ReplyDelete
  77. 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...

    Data Science Course in Patna

    ReplyDelete
  78. 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!
    Business Analytics Course in Noida

    ReplyDelete
  79. 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!

    Data Scientist Course in Patna

    ReplyDelete
  80. Nice post. This is a great article and am pretty much pleased with your good work. Very helpful information. Thank you.
    Best Data Science Courses

    ReplyDelete
  81. I was looking for some decent stuff on the subject and have had no luck so far. You just had a new big fan! ...

    Data Scientist Training in Bangalore

    ReplyDelete
  82. 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.

    Data Science Course in Patna

    ReplyDelete
  83. 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.

    Business Analytics Course in Patna

    ReplyDelete
  84. 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.

    ReplyDelete
  85. Some really good points you wrote here ... Great things ... I think you raised some really interesting points. Keep up the good work.

    Best Data Analytics Courses in Bangalore

    ReplyDelete
  86. 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.

    ReplyDelete
  87. I 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!

    Data Scientist Course in Ernakulam

    ReplyDelete
  88. 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.

    ReplyDelete
  89. I 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.

    Data Science Course in Durgapur

    ReplyDelete
  90. 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.
    Best Online Data Science Courses

    ReplyDelete
  91. Very informative message! There is so much information here that can help any business start a successful social media campaign!
    data science training in london

    ReplyDelete
  92. You have done a great job and will definitely dig it and personally recommend to my friends. Thank You.
    Data Science Online Training

    ReplyDelete
  93. Very informative Blog! There is so much information here that can help thank you for sharing.
    Data Analytics Course in Bangalore

    ReplyDelete
  94. 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!

    Data Scientist Training in Bangalore

    ReplyDelete
  95. 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.
    For any further information please call +919311002620 or you can visit website
    https://htsindia.com/Courses/python/python-with-data-science-training-course

    ReplyDelete
  96. 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

    ReplyDelete
  97. It 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!

    Data Science Course in Ernakulam

    ReplyDelete
  98. http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html

    ReplyDelete
  99. I 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

    ReplyDelete
  100. Get 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

    ReplyDelete
  101. Nice thanks for sharing informative post like this keep posting if like more details visit my website linkhttps://mulemasters.in/

    ReplyDelete
  102. This is really very nice post you shared, i like the post, thanks for sharing..
    data scientist course

    ReplyDelete
  103. 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

    ReplyDelete
  104. This 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