Openpyxl number format

Posted on Saturday, February 8, 2020




I recently wrote a article going over how to use json to make an xls file in python using openpyxl http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html [1]


I am going to reuse some of that code but now I want to format numbers correctly so I can display $ signs, dates, etc correctly.




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("formatted.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 tweak this code to do something new and format all the dollar amounts into


This is where it all gets a little bit of fun.




If I look in Excel I can see that there are several default number formats I can use.

·         General
·         Number
·         Accounting

Etc.

For me I am usually using Accounting and percentage for a lot of things I do.






Using built in types




Here you can see some built in formats.  Here is the built in format for percentage.

Let me apply it and see the results.



Here is some updated code


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = numbers.FORMAT_PERCENTAGE



#############################################
#  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("formatted.xlsx")


Here you can see that we imported the numbers from the openpyxl and then we applied the percentage to the 4th cell in each row



Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up




That worked now I can see that those cells are using the Percentage format.



No accounting format?






So we are forced to make a custom number format.
Just FYI, you can create custom number formats in excel itself… but I will not go over that in this post… OK maybe I will real quick




Click on More Number Formats








Click on custom.
Now you can see all these funny little numbers.
This funny stuff which looks kinda regular expresiony is its own language.

We need to create a custom type in python now using openpyxl




Number Format codes



Looking at this post it shows that number formats have four parts






So let me grow an example that will eventually be equal to what the accounting format is.



Let me update my code

Here is some updated code


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   fmt_acct = u'$#,##0.00;'
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = fmt_acct



#############################################
#  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("formatted.xlsx")


Here I created a format that will only apply to positive numbers


   fmt_acct = u'$#,##0.00;'





Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up



You can see that the number is listed as “Custom” format and you can see that you have this nice dollar sign.

If I update the number with a negative number





Nothing shows up because we did not define how to do negative numbers



Here is some updated code


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);'
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = fmt_acct



#############################################
#  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("formatted.xlsx")


Here I created a format that will only apply to positive and negative numbers


   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);'





Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up




Make one of the number negative and you should see results like this.



Now put a 0 in a field




We have not yet formatted what to do in the case of a 0…

Here is some updated code


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);-;'
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = fmt_acct



#############################################
#  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("formatted.xlsx")


Here I created a format that will only apply to positive, negative, and a zero number (but not text)


   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);-;'




Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up




Now you can see if you put a 0 in you get a –

But if you put any text in…





So let’s fix that



Here is some updated code


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);-;@'
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = fmt_acct



#############################################
#  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("formatted.xlsx")

Now this covers all number and even text


   fmt_acct = u'$#,##0.00;[Red]$(#,##0.00);-;@'




Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up





Now we have it covered.

But it’s not exactly like the accounting field
Here is my final code.


#!/usr/bin/env python3

import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers

def populate_sheet(json_data, ws):
   ws.cell(1,1, "Month")
   ws.cell(1,2, "food")
   ws.cell(1,3, "heating")
   ws.cell(1,4, "rent")

   row = 1
   fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($* -_0_0_);_(@'
   for month in json_data.keys():
     row+=1
     ws.cell(row,1,month)
     cell = ws.cell(row,2,float(json_data[month]["food"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,3,float(json_data[month]["heating"]))
     cell.number_format = fmt_acct
     cell = ws.cell(row,4,float(json_data[month]["rent"]))
     cell.number_format = fmt_acct



#############################################
#  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("formatted.xlsx")


Now this covers all number and even text


fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($* -_0_0_);_(@'



Now run it


  >  ./createxls_from_json_multiple_sheets.py


Now open it up




That is getting me what I want J





References


[1]        Openpyxl and json round 2
[2]        Source code for openpyxl.styles.numbers
https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html
Accessed 02/2020
[3]        Source code for


41 comments:

  1. It's very useful blog post with inforamtive and insightful content and i had good experience with this information.I have gone through CRS Info Solutions Home which really nice. Learn more details About Us of CRS info solutions. Here you can see the Courses CRS Info Solutions full list. Find Student Registration page and register now. Go through Blog post of crs info solutions. I just read these Reviews of crs really great. You can now Contact Us of crs info solutions. You enroll for Pega Training at crs info solutions.

    ReplyDelete
  2. Great Article
    Cyber Security Projects

    projects for cse

    Networking Projects

    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete
  3. Fantastic article with valuable information found very helpful waiting for next blog thank you.
    typeerror nonetype object is not subscriptable

    ReplyDelete
  4. 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
  5. You have completed certain reliable points there. I did some research on the subject and found that almost everyone will agree with your blog. PMP Training in Hyderabad

    ReplyDelete
  6. Amazing article with informative information found valuable and enjoyed reading it thanks for sharing.
    Data Analytics Course Online

    ReplyDelete
  7. Fantastic article with informative content. Information shared was valuable and enjoyed reading it looking forward for next blog thank you.
    Ethical Hacking Course in Bangalore

    ReplyDelete
  8. They are produced by high level developers who will stand out for the creation of their polo dress. You will find Ron Lauren polo shirts in an exclusive range which includes private lessons for men and women.

    Business Analytics Course in Bangalore

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

    Data Science Course

    ReplyDelete
  10. Very good message. I stumbled across your blog and wanted to say that I really enjoyed reading your articles. Anyway, I will subscribe to your feed and hope you post again soon.

    Business Analytics Course

    ReplyDelete
  11. Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
    Data Analyst Course

    ReplyDelete
  12. 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
  13. 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
  14. 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
  15. Thanks for posting the best information and the blog is very informative.Data science course in Faridabad

    ReplyDelete
  16. 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 analytics courses in bangalore

    ReplyDelete
  17. 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
  18. 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
  19. 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
  20. อีกทั้งเรายังให้บริการ เกมสล็อต ยิงปลา แทงบอลออนไลน์ รองรับทุกการใช้งานในอุปกรณ์ต่าง ๆ HTML5 คอมพิวเตอร์ แท็บเล็ต สมาทโฟน คาสิโนออนไลน์ และมือถือทุกรุ่น เล่นได้ตลอด 24ชม. ไม่ต้อง Downloads เกมส์ให้ยุ่งยาก ด้วยระบบที่เสถียรที่สุดในประเทศไทย

    ReplyDelete
  21. หาคุณกำลังหาเกมส์ออนไลน์ที่สามารถสร้างรายได้ให้กับคุณ เรามีเกมส์แนะนำ เกมยิงปลา รูปแบบใหม่เล่นง่ายบนมือถือ คาสิโนออนไลน์ บนคอม เล่นได้ทุกอุปกรณ์รองรับทุกเครื่องมือ มีให้เลือกเล่นหลายเกมส์ เล่นได้ทั่วโลกเพราะนี้คือเกมส์ออนไลน์แบบใหม่ เกมยิงปลา

    ReplyDelete
  22. A good blog always comes-up with new and exciting information and while reading I have felt that this blog really has all those qualities that qualify a blog to be a one.

    Best Data Science courses in Hyderabad

    ReplyDelete
  23. All access to ufabet direct website, not through agents Come to here, the only place in the world, the center for direct access to UFABET. All links, a complete approach to UEFA Bet Whether it is UFABET live casino online 1 day that you all gamblers have been using the service for a long time.

    ReplyDelete
  24. Online slots (Slot Online) may be the release of a gambling machine. Slot computer As stated before Used to produce electrical games known as online slots, on account of the development era, folks have looked to gamble through computer systems. Will achieve slot video games making internet gambling video games Via the world wide web network device Which players can have fun with through the slot plan or will have fun with Slots with the system provider's site Which internet slots games are actually available within the kind of participating in guidelines. It's similar to participating in on a slot machine. The two practical photos as well as sounds are equally thrilling since they go to lounge in the casino on the globe.บาคาร่า
    ufa
    ufabet
    แทงบอล
    แทงบอล
    แทงบอล

    ReplyDelete
  25. It's really nice and meaningful. it's a really cool blog.you have really helped lots of people who visit blogs and provide them useful information.
    digital marketing courses in hyderabad with placement

    ReplyDelete
  26. pgslot ซึ่งเกมคาสิโนออนไลน์เกมนี้เป็นเกมที่เรียกว่าเกม สล็อตเอ็กซ์โอ คุณรู้จักเกมส์เอ็กซ์โอหรือไม่ 90% ต้องรู้จักเกมส์เอ็กซ์โออย่างแน่นอนเพราะในตอนนี้เด็กนั้นเราทุกคนมักที่จะเอาก็ได้ขึ้นมา สล็อต เล่นเกมส์เอ็กซ์โอกับเพื่อนเพื่อนแล้วคุณรู้หรือไม่ว่าในปัจจุบันนี้เกมส์เอ็กซ์โอนั้นกลายมาเป็นเกมซะลอสออนไลน์ที่ให้บริการด้วยเว็บคาสิโนออนไลน์คุณสามารถเดิมพันเกมส์เอ็กซ์โอกับเว็บคาสิโนออนไลน์ได้โดยที่จะทำให้คุณนั้นสามารถสร้างกำไรจากการเล่นเกมส์เดิมพันออนไลน์ได้เราแนะนำเกมส์ชนิดนี้ให้คุณได้รู้จักก็เพราะว่าเชื่อว่าทุก

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

    Data Science Training in Bangalore

    ReplyDelete
  28. 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
  29. I found Habit to be a transparent site, a social hub that is a conglomerate of buyers and sellers willing to offer digital advice online at a decent cost.

    Artificial Intelligence Training in Bangalore

    ReplyDelete
  30. Excellent effort to make this blog more wonderful and attractive.
    business analytics course

    ReplyDelete
  31. Thanks for posting the best information and the blog is very important.data science institutes in hyderabad

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

    ReplyDelete
  33. Fantastic article I ought to say and thanks to the info. Instruction is absolutely a sticky topic. But remains one of the top issues of the time. I love your article and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  34. simply stumbled upon your weblog and wished to mention that I have really enjoyed surfing around your weblog posts. After all I will be subscribing on your rss feed and I am hoping you write again very soon! Candela Mini Gentlelase

    ReplyDelete
  35. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
    Data Analytics Course in Bangalore

    ReplyDelete
  36. cool! Some extremely valid points! I appreciate you writing this write-up plus the rest of the site is also really good. best coffee beans
    decaf coffee beans
    organic coffee beans

    ReplyDelete
  37. love your writing very so much! share we keep in touch extra about your article on AOL Jeff Pan
    Jeff Pan

    ReplyDelete
  38. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
    AWS Training in Hyderabad
    AWS Course in Hyderabad

    ReplyDelete
  39. 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
  40. Tremendous blog quite easy to grasp the subject since the content is very simple to understand. Obviously, this helps the participants to engage themselves in to the subject without much difficulty. Hope you further educate the readers in the same manner and keep sharing the content as always you do.

    data science course in faridabad

    ReplyDelete