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. Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
    Cyber Security Course in Bangalore

    ReplyDelete
  2. Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
    Cyber Security Training in Bangalore

    ReplyDelete