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")





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")




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

2 comments:

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