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 4^th 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

15 comments:

Tech Institute September 30, 2020 at 7:44 AM
Fantastic article with valuable information found very helpful waiting for next blog thank you.
typeerror nonetype object is not subscriptable
Ethical Hacking CourseOctober 24, 2020 at 3:57 AM
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
Data Science Course BLRNovember 12, 2020 at 2:21 AM
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
skJuly 8, 2021 at 12:03 AM
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
skJuly 15, 2021 at 7:05 AM
love your writing very so much! share we keep in touch extra about your article on AOL Jeff Pan
Jeff Pan
skSeptember 27, 2021 at 11:53 PM
able to find good information from your blog posts. snowboarder instructor
John P. KetchumOctober 7, 2021 at 3:11 AM
Criminals are getting more violent residential security in UKand sophisticated in their operations to ensure they get whatever they want. They can strike when you least expected because they monitor your movements and plan well in advance. Even the security gadgets you have at home can not deter them from penetrating your home.
John P. KetchumOctober 13, 2021 at 12:32 AM
We acknowledge the fact that a vibrant society with active citizens needs a company that understands the security threats the celebrities, dignitaries, and executives are likely to face. top security companies in London
That's why UK Close Protection Services strives to consistently provide unparalleled services. We know that times change, and criminals are developing new ways of attacking their targets.
skFebruary 3, 2022 at 8:41 PM
I personally thought youd have something fascinating to express. All I hear is really a bunch of whining about something that you could fix should you werent too busy seeking attention. candela laser machine
traininginstituteMarch 31, 2022 at 3:43 AM
What a really awesome post this is. Truly, one of the best posts I've ever witnessed to see in my whole life. Wow, just keep it up.
full stack web development course malaysia
Jobi JohnsonMarch 31, 2022 at 4:44 AM
Statistics students and professor are worried to find the deviation calculator because their work depends on it. monster energy jacket
eddielydonApril 6, 2022 at 12:01 AM
Nice Blog !
Here We are Specialist in Manufacturing of Movies, Gaming, Casual, Faux Leather Jackets, Coats And Vests See james bond peacoat
PMP Training in MalaysiaApril 18, 2022 at 8:04 PM
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.
data science course in gorakhpurJune 29, 2022 at 1:29 AM
Our Data Science certification training with a unique curriculum and methodology helps you to get placed in top-notch companies.
data analytics course in gorakhpur
skJanuary 20, 2023 at 6:52 AM
I came across it by using Bing and I’ve got to admit that I am now subscribed to your website, it is very decentAnswering Service

Openpyxl number format

Posted on Saturday, February 8, 2020

Simple test

Simple python script to create xls file from json

No accounting format?

Number Format codes

References

15 comments: