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 I created a post about formatting number http://www.whiteboardcoder.com/2020/02/openpyxl-number-format.html [2]
I am going to reuse some of that code but now I want to
change the column widths and the row heights.
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
header_dict = { "1": "Month",
"2": "food",
"3": "heating",
"4": "rent",
}
#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)])
#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):
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).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("col_width_row_height.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/86540c1c8c38448f3c9ecdd28e196d5f
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
Set
auto width for columns
For a first simple setting lets fix the width to auto size based on text size
For a first simple setting lets fix the width to auto size based on text size
|
> 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",
}
#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)])
#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):
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
ws.column_dimensions[get_column_letter(col)].auto_size
= True
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("col_width_row_height.xlsx")
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("col_width_row_height.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/b9412bfbcac7a899237e21a9a5844cc6
Now run it
|
> ./createxls_from_json_multiple_sheets.py
|
And open it
Now the columns are auto width J
If I put a huge number in it
It will resize J
Set size
What about having a set size
|
#!/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",
}
#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)])
#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):
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
ws.column_dimensions[get_column_letter(col)].width
= 16.1
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("col_width_row_height.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/14e69116cd0692eab27718dfdd939fcb
Now run it
|
> ./createxls_from_json_multiple_sheets.py
|
And open it
I do now have a set width
But it is not exactly what I set to be…
I set it to be 16.1, but got 15.43…. Why?
I set it to be 16.1, but got 15.43…. Why?
The short answer is its complicated…
For more reading see https://bitbucket.org/openpyxl/openpyxl/issues/293/column-width-issue [3]
For more reading see https://bitbucket.org/openpyxl/openpyxl/issues/293/column-width-issue [3]
It looks like if I want to get an exact width I am going to
need to create a table to factor in a little nudge number based on the range.
For my purposes this is more than I need….. I will either auto-size it or just tweak my number larger than it needs to be to get the results I want…
For my purposes this is more than I need….. I will either auto-size it or just tweak my number larger than it needs to be to get the results I want…
Now moving on the row height!
Set row height
Tweaking the code some more.
|
#!/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",
}
#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)])
#Populate the rows
def populate_row(row_num, data_dict, ws):
#Set month
column
ws.cell(row_num,1,data_dict["Month"])
#Set row height
ws.row_dimensions[row_num].height = 35.5
#Set the number
format for each $ cell
for col in
range(2,len(data_dict)+1):
col_value =
data_dict[header_dict[str(col)]]
ws.cell(row_num, col, col_value).number_format = fmt_acct
ws.column_dimensions[get_column_letter(col)].width = 16.1
#ws.column_dimensions[get_column_letter(col)].auto_size = True
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("col_width_row_height.xlsx")
|
Also placed as gist on https://gist.github.com/patmandenver/c854b5831c1f3394e577e266242d7022
Now run it
|
> ./createxls_from_json_multiple_sheets.py
|
And open it
Looks like I am again close to what I want but not exact…
Well good enough for now.
Well good enough for now.
Otherwise the default is to autosize based on data in the
row
Calling it good !
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] column width issue.
https://bitbucket.org/openpyxl/openpyxl/issues/293/column-width-issue
Accessed 02/2020
https://bitbucket.org/openpyxl/openpyxl/issues/293/column-width-issue
Accessed 02/2020
Hi Patrick,
ReplyDeleteexcellent article and very informative.
I used it as a reference to build my report but I'm stuck on the row height.
I can get the row height to work if I identify the row but I need it to only work based on a condition.
Is this possible?
https://stackoverflow.com/questions/64480687/can-you-adjust-row-height-based-on-a-condition-in-openpyxl
Thanks