Openpyxl column widths and row heights

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]

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

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

The short answer is its complicated…
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…

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.

Otherwise the default is to autosize based on data in the row

Calling it good !

References

[1]        Openpyxl and json round 2

http://www.whiteboardcoder.com/2020/02/openpyxl-and-json-round-2.html
Accessed 02/2020

[2]        Openpyxl number format
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