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



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

Head over to https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html [2]



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

Also placed as gist on https://gist.github.com/patmandenver/8ee9a044164ad3a64eafb32043b6c025
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?


Looking at https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html [2] there is not 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 page https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68 [3]

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_);_(@'

Gist https://gist.github.com/patmandenver/cfbb2425fc73fecb09d349d428c770a1
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
http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html
Accessed 02/2020
[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
https://support.office.com/en-us/article/Number-format-codes-5026bbd6-04bc-48cd-bf33-80f18b4eae68
Accessed 02/2020


2 comments:

  1. ShanejoJuly 7, 2020 at 12:35 AM

    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. Devi.AngularjsJuly 19, 2020 at 2:47 AM

    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