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]
Also I created a post about formatting number http://www.whiteboardcoder.com/2020/02/openpyxl-number-format.html [2]
Also one about column and row size http://www.whiteboardcoder.com/2020/02/openpyxl-column-widths-and-row-heights.html [3]
Also I created a post about formatting number http://www.whiteboardcoder.com/2020/02/openpyxl-number-format.html [2]
Also one about column and row size http://www.whiteboardcoder.com/2020/02/openpyxl-column-widths-and-row-heights.html [3]
I am going to reuse some of that code but now I want to format
the cells. Ex. Make the font a different size, use borders,
background highlight etc.
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.
> 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
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
header_dict = { "1": "Month",
"2": "food",
"3": "heating",
"4": "rent",
"5": "",
"6": "Total"
}
#Accounting format
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($*
-_0_0_);_(@'
#Populate the headers
def populate_header_row(ws):
for col in
range(1, len(header_dict)+1):
ws.cell(1,col,
header_dict[str(col)])
ws.column_dimensions[get_column_letter(col)].auto_size = True
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month
column
ws.cell(row_num,1,data_dict["Month"])
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
if
header_dict[str(col)]:
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
#Add the sum
cell
#sum_func =
("=sum(" + get_column_letter(2) + str(row_num)
# + ":" +
get_column_letter(4) + str(row_num) + ")")
avg_func =
("=AVERAGE(" + get_column_letter(2) + str(row_num)
+
":" + get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num,
6, avg_func).number_format = fmt_acct
def populate_sheet(json_data, ws):
populate_header_row(ws)
row_num = 1
for month in
json_data.keys():
row_num+=1
data_dict =
json_data[month]
data_dict["Month"] = month
populate_row(row_num, data_dict, ws)
#############################################
# 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("format_test.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/bfd62ee5c71340a4b774f56f22573056
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
Create a font
style
For more information go check out https://openpyxl.readthedocs.io/en/stable/styles.html
[4]
Now let me tweak the header row with a customized style
That just changes the font.
Let me make it very simple.
> 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
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
from
openpyxl.styles import colors
from
openpyxl.styles import Font, Color
from
openpyxl.styles import Border, Side, PatternFill, Font, GradientFill,
Alignment, NamedStyle
header_dict = { "1": "Month",
"2": "food",
"3": "heating",
"4": "rent",
"5": "",
"6": "Total"
}
#Accounting format
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($*
-_0_0_);_(@'
#Setting
styles
header_style
= NamedStyle(name="hearder_style")
header_style.font
= Font(name="Times New Roman", bold=True, size=20, color='FF0000')
#Populate the headers
def populate_header_row(ws):
for col in
range(1, len(header_dict)+1):
ws.cell(1,col,
header_dict[str(col)]).style = header_style
ws.column_dimensions[get_column_letter(col)].auto_size
= True
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month
column
ws.cell(row_num,1,data_dict["Month"])
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
if
header_dict[str(col)]:
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
#Add the sum
cell
#sum_func =
("=sum(" + get_column_letter(2) + str(row_num)
# + ":" +
get_column_letter(4) + str(row_num) + ")")
avg_func =
("=AVERAGE(" + get_column_letter(2) + str(row_num)
+
":" + get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num,
6, avg_func).number_format = fmt_acct
def populate_sheet(json_data, ws):
populate_header_row(ws)
row_num = 1
for month in
json_data.keys():
row_num+=1
data_dict =
json_data[month]
data_dict["Month"] = month
populate_row(row_num, data_dict, ws)
#############################################
# 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("format_test.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/a9763ae31b1d776a250a3d6495a71970
Now run it
> ./createxls_from_json_multiple_sheets.py
|
And open it
That worked
Alignment
Now let me fiddle with the alignment
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles import Border, Side, PatternFill,
Font, GradientFill, Alignment, NamedStyle
header_dict = { "1": "Month",
"2": "food",
"3": "heating",
"4": "rent",
"5": "",
"6": "Total"
}
#Accounting format
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($*
-_0_0_);_(@'
#Setting
styles
header_style
= NamedStyle(name="hearder_style")
header_style.font
= Font(name="Times New Roman", bold=True, size=20, color='FF0000')
header_style.alignment =
Alignment(horizontal="center", vertical="center")
#Populate the headers
def populate_header_row(ws):
for col in
range(1, len(header_dict)+1):
ws.cell(1,col,
header_dict[str(col)]).style = header_style
ws.column_dimensions[get_column_letter(col)].auto_size = True
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month column
ws.cell(row_num,1,data_dict["Month"])
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
if
header_dict[str(col)]:
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
#Add the sum
cell
#sum_func =
("=sum(" + get_column_letter(2) + str(row_num)
# + ":" +
get_column_letter(4) + str(row_num) + ")")
avg_func =
("=AVERAGE(" + get_column_letter(2) + str(row_num)
+ ":" +
get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num,
6, avg_func).number_format = fmt_acct
def populate_sheet(json_data, ws):
populate_header_row(ws)
row_num = 1
for month in
json_data.keys():
row_num+=1
data_dict =
json_data[month]
data_dict["Month"] = month
populate_row(row_num, data_dict, ws)
#############################################
# 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("format_test.xlsx")
|
Now run it
> ./createxls_from_json_multiple_sheets.py
|
And open it
Now we have the first row center aligned and center vertical
aligned
borders
Now let me fiddle with borders
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles import Border, Side, PatternFill,
Font, GradientFill, Alignment, NamedStyle
header_dict = { "1": "Month",
"2": "food",
"3":
"heating",
"4": "rent",
"5": "",
"6": "Total"
}
#Accounting format
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($*
-_0_0_);_(@'
#Setting styles
header_style = NamedStyle(name="hearder_style")
header_style.font = Font(name="Times New Roman",
bold=True, size=20, color='FF0000')
header_style.alignment =
Alignment(horizontal="center", vertical="center")
double
= Side(border_style="thick", color="000000")
header_style.border
= Border(bottom=double)
#header_style.border = Border(top=double, left=double,
right=double, bottom=double)
#Populate the headers
def populate_header_row(ws):
for col in
range(1, len(header_dict)+1):
ws.cell(1,col,
header_dict[str(col)]).style = header_style
ws.column_dimensions[get_column_letter(col)].auto_size = True
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month
column
ws.cell(row_num,1,data_dict["Month"])
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
if
header_dict[str(col)]:
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
#Add the sum cell
#sum_func =
("=sum(" + get_column_letter(2) + str(row_num)
# + ":" +
get_column_letter(4) + str(row_num) + ")")
avg_func =
("=AVERAGE(" + get_column_letter(2) + str(row_num)
+
":" + get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num,
6, avg_func).number_format = fmt_acct
def populate_sheet(json_data, ws):
populate_header_row(ws)
row_num = 1
for month in
json_data.keys():
row_num+=1
data_dict =
json_data[month]
data_dict["Month"] = month
populate_row(row_num, data_dict, ws)
#############################################
# 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("format_test.xlsx")
|
I put it up as a gist
Now run it
> ./createxls_from_json_multiple_sheets.py
|
And open it
Now we have borders J
fill
Now let me fiddle with fill color
#!/usr/bin/env python3
import openpyxl
import json
from openpyxl import Workbook
from openpyxl.styles import numbers
from openpyxl.utils import get_column_letter
from openpyxl.styles import colors
from openpyxl.styles import Font, Color
from openpyxl.styles import Border, Side, PatternFill,
Font, GradientFill, Alignment, NamedStyle
header_dict = { "1": "Month",
"2": "food",
"3": "heating",
"4": "rent",
"5": "",
"6": "Total"
}
#Accounting format
fmt_acct = u'_($* #,##0.00_);[Red]_($* (#,##0.00);_($*
-_0_0_);_(@'
#Setting styles
header_style = NamedStyle(name="hearder_style")
header_style.font = Font(name="Times New Roman",
bold=True, size=20, color='FF0000')
header_style.alignment =
Alignment(horizontal="center", vertical="center")
double = Side(border_style="thick",
color="000000")
header_style.border = Border(bottom=double)
#header_style.border = Border(top=double, left=double,
right=double, bottom=double)
header_style.fill
= PatternFill("solid", fgColor="FFFF00")
#Populate the headers
def populate_header_row(ws):
for col in
range(1, len(header_dict)+1):
ws.cell(1,col,
header_dict[str(col)]).style = header_style
ws.column_dimensions[get_column_letter(col)].auto_size = True
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month
column
ws.cell(row_num,1,data_dict["Month"])
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
if header_dict[str(col)]:
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
#Add the sum
cell
#sum_func =
("=sum(" + get_column_letter(2) + str(row_num)
# + ":" + get_column_letter(4)
+ str(row_num) + ")")
avg_func =
("=AVERAGE(" + get_column_letter(2) + str(row_num)
+
":" + get_column_letter(4) + str(row_num) + ")")
ws.cell(row_num,
6, avg_func).number_format = fmt_acct
def populate_sheet(json_data, ws):
populate_header_row(ws)
row_num = 1
for month in
json_data.keys():
row_num+=1
data_dict =
json_data[month]
data_dict["Month"] = month
populate_row(row_num, data_dict, ws)
#############################################
# 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("format_test.xlsx")
|
Now run it
> ./createxls_from_json_multiple_sheets.py
|
And open it
Now we have some background color J
References
[1] Openpyxl and json round 2
[2] Openpyxl number format
http://www.whiteboardcoder.com/2020/02/openpyxl-number-format.html
Accessed 02/2020
http://www.whiteboardcoder.com/2020/02/openpyxl-number-format.html
Accessed 02/2020
[3] Openpyxl column widths and row heights
http://www.whiteboardcoder.com/2020/02/openpyxl-column-widths-and-row-heights.html
Accessed 02/2020
http://www.whiteboardcoder.com/2020/02/openpyxl-column-widths-and-row-heights.html
Accessed 02/2020
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.
ReplyDeleteCyber Security Course in Bangalore
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.
ReplyDeleteData Science Course 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 scientist course in bangalore
Informative blog
ReplyDeletedata analytics courses in hyderabad
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.
ReplyDeletebest data science courses in bangalore
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!
ReplyDeletedata analytics course 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.
ReplyDeleteData Science Course Syllabus
I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
ReplyDeletedata analytics training in bangalore
Impressive blog to be honest definitely this post will inspire many more upcoming aspirants. Eventually, this makes the participants to experience and innovate themselves through knowledge wise by visiting this kind of a blog. Once again excellent job keep inspiring with your cool stuff.
ReplyDeletedata science certification in bangalore
Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.
ReplyDeletedata science course in faridabad
Great Blog to read, Its gives more useful information. Thank lot.
ReplyDeleteData Science Training in Hyderabad
Data Science Course in Hyderabad
You are so interesting! I do not suppose I’ve read through anything like this before. So good to discover another person with a few genuine thoughts on this topic. Seriously. thank you for starting this up.
ReplyDeleteData Science Training in Hyderabad
Thanks for posting the best information and the blog is very important.data science course in Lucknow
ReplyDelete