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