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


44 comments:

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

      IEEE 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

      Delete
  2. 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
  3. 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
  4. I was looking for some decent stuff on the subject and have had no luck so far. You just had a new big fan! ...

    360DigiTMG Data Science Courses

    ReplyDelete
  5. 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
  6. Very interesting article with unique content and helpful information thank you.
    Data Science Course in Hyderabad 360DigiTMG

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

    Data Analytics Course in Bangalore

    ReplyDelete
  8. 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.
    Cyber Security Course in Bangalore

    ReplyDelete
  9. 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!
    Cyber Security Training in Bangalore

    ReplyDelete
  10. 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
  11. 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
  12. 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
  13. Great article with excellent information found resourceful and enjoyed reading it thank you, looking forward for next blog.
    typeerror nonetype object is not subscriptable

    ReplyDelete
  14. Fantastic blog with high quality content found very valuable and enjoyed reading thank you.
    Data Science Course

    ReplyDelete
  15. 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
  16. 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
  17. 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
  18. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    Data Science Course in Bangalore

    ReplyDelete
  19. I have to search sites with relevant information ,This is a
    wonderful blog,These type of blog keeps the users interest in
    the website, i am impressed. thank you.
    Data Science Training in Bangalore

    ReplyDelete
  20. 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!
    data analytics course in bangalore

    ReplyDelete

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

    ReplyDelete
  22. 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.
    artificial intelligence course in pune

    ReplyDelete
  23. 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!
    data analytics course in bangalore

    ReplyDelete
  24. I really enjoy every part and have bookmarked you to see the new things you post. Well done for this excellent article. Please keep this work of the same quality.
    Artificial Intelligence course in Chennai

    ReplyDelete
  25. Fantastic blog with excellent information and valuable content just added your blog to my bookmarking sites thank for sharing.
    Data Science Course in Chennai

    ReplyDelete
  26. 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.
    cyber security training in bangalore

    ReplyDelete
  27. 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
  28. Really wonderful blog completely enjoyed reading and learning to gain vast knowledge. Eventually, this blog helps in developing certain skills which in turn helpful in implementing those skills. Thanking the blogger for delivering such beautiful content and keep posting the contents in the upcoming days.

    Digital Marketing Training in Bangalore

    ReplyDelete
  29. The truly mind-blowing blog went amazed with the subject they have developed the content. This kind of post is really helpful to gain knowledge of unknown things which surely triggers to motivate and learn the new innovative contents. Hope you deliver the similar successive contents forthcoming as well.

    Cyber Security Course

    ReplyDelete
  30. 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
  31. I need to thank you for this very good read and i have bookmarked to check out new things from your post. Thank you very much for sharing such a useful article and will definitely saved and revisit your site.
    Data Science Course

    ReplyDelete
  32. 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 Institute in Bangalore

    ReplyDelete
  33. 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
  34. 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.
    Data Science Course in Bangalore

    ReplyDelete
  35. 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
  36. 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
  37. Very awesome!!! When I searched for this I found this website at the top of all blogs in search engines.
    business analytics course

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

    ReplyDelete
  39. 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
  40. Really wonderful blog completely enjoyed reading and learning to gain vast knowledge. Eventually, this blog helps in developing certain skills which in turn helpful in implementing those skills. Thanking the blogger for delivering such beautiful content and keep posting the contents in the upcoming days.

    Digital Marketing Training in Bangalore

    ReplyDelete
  41. Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feel excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with 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.

    Cyber Security Course

    ReplyDelete
  42. Truly incredible blog found to be very impressive due to which the learners who go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such phenomenal content. Hope you arrive with similar content in the future as well.

    Machine Learning Course in Bangalore

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