Openpyxl Formatting

Posted on Saturday, February 8, 2020




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]


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
https://gist.github.com/patmandenver/2cc1c21dac4233e8a585b72964c46af9
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
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]        Openpyxl column widths and row heights
http://www.whiteboardcoder.com/2020/02/openpyxl-column-widths-and-row-heights.html
Accessed 02/2020
[4]        Working with styles
https://openpyxl.readthedocs.io/en/stable/styles.html
Accessed 02/2020

6 comments:

  1. Pallavi reddyMay 7, 2021 at 12:01 PM

    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.
    data scientist course in bangalore

    ReplyDelete
  2. princikaMay 19, 2021 at 2:57 AM

    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.
    data analytics courses in bangalore

    ReplyDelete
  3. Data ScienceMay 29, 2021 at 12:37 AM

    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.
    Data Science Course Syllabus

    ReplyDelete
  4. Data Science Course in Bhilai - 360DigiTMGJune 28, 2021 at 9:11 AM

    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.

    data science certification in bangalore

    ReplyDelete
  5. Priya RathodJuly 19, 2021 at 3:18 AM

    Great Blog to read, Its gives more useful information. Thank lot.
    Data Science Training in Hyderabad
    Data Science Course in Hyderabad

    ReplyDelete
  6. BhuvanaJuly 20, 2021 at 12:45 AM

    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.

    Data Science Training in Hyderabad

    ReplyDelete