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
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.
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_);_(@'
|
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
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
Accessed 02/2020
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.
ReplyDeleteGreat Article
ReplyDeleteCyber 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
Fantastic article with valuable information found very helpful waiting for next blog thank you.
ReplyDeletetypeerror nonetype object is not subscriptable
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!
ReplyDeleteCyber Security Training in Bangalore
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
ReplyDeleteAmazing article with informative information found valuable and enjoyed reading it thanks for sharing.
ReplyDeleteData Analytics Course Online
Fantastic article with informative content. Information shared was valuable and enjoyed reading it looking forward for next blog thank you.
ReplyDeleteEthical Hacking Course in Bangalore
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.
ReplyDeleteData Science Course
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.
ReplyDeleteData Analyst Course
I have to search sites with relevant information ,This is a
ReplyDeletewonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
Data Science Course in Bangalore
I have to search sites with relevant information ,This is a
ReplyDeletewonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
Data Science Training in Bangalore
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.
ReplyDeletedata analytics courses in bangalore
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.
ReplyDeletecyber security training in bangalore
หาคุณกำลังหาเกมส์ออนไลน์ที่สามารถสร้างรายได้ให้กับคุณ เรามีเกมส์แนะนำ เกมยิงปลา รูปแบบใหม่เล่นง่ายบนมือถือ คาสิโนออนไลน์ บนคอม เล่นได้ทุกอุปกรณ์รองรับทุกเครื่องมือ มีให้เลือกเล่นหลายเกมส์ เล่นได้ทั่วโลกเพราะนี้คือเกมส์ออนไลน์แบบใหม่ เกมยิงปลา
ReplyDeleteI 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 ...
ReplyDeleteDigital Marketing Training in Bangalore
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.
ReplyDeleteData Science Training in Chennai
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.
ReplyDeleteData Science Course in Bangalore
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
ReplyDeletelove your writing very so much! share we keep in touch extra about your article on AOL Jeff Pan
ReplyDeleteJeff Pan
Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing. data scientist course in delhi
ReplyDeleteThanks for posting the best information and the blog is very good.data science course in Lucknow
ReplyDeleteIt 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.
ReplyDeleteDigital Marketing Training in Bangalore
able to find good information from your blog posts. snowboarder instructor
ReplyDeleteI 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.
ReplyDeletebusiness analytics courses
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.
ReplyDeleteWe 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
ReplyDeleteThat'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.
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.
ReplyDeleteBest Institute for Cloud Computing in Bangalore
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
ReplyDeleteI 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
ReplyDeletedata science training in trivandrum
Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting.
ReplyDeleteA debt of gratitude is in order for sharing.business analytics course in kolhapur
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.
ReplyDeleteBest Data Analytics Courses in Bangalore
Amazingly by and large very interesting post. I was looking for such an information and thoroughly enjoyed examining this one.
ReplyDeleteKeep posting. An obligation of appreciation is all together for sharing.
business analytics course in gwalior
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.
ReplyDeletefull stack web development course malaysia
Statistics students and professor are worried to find the deviation calculator because their work depends on it. monster energy jacket
ReplyDeleteNice Blog !
ReplyDeleteHere We are Specialist in Manufacturing of Movies, Gaming, Casual, Faux Leather Jackets, Coats And Vests See james bond peacoat
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.
ReplyDeleteOur Data Science certification training with a unique curriculum and methodology helps you to get placed in top-notch companies.
ReplyDeletedata analytics course in gorakhpur
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
ReplyDelete