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

14 comments:

  1. Cyber SecurityNovember 7, 2020 at 1:54 AM

    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. Datascience Course AnalystApril 26, 2021 at 11:55 PM

    Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.
    Data Science Course in Bangalore

    ReplyDelete
  3. 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
  4. DeekshithaMay 7, 2021 at 11:26 PM

    Informative blog
    data analytics courses in hyderabad

    ReplyDelete
  5. Pallavi reddyMay 17, 2021 at 11:50 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.
    best data science courses in bangalore

    ReplyDelete
  6. InstituteBlrMay 18, 2021 at 10:38 PM

    I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics course in bangalore

    ReplyDelete
  7. 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
  8. 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
  9. data analytics booksMay 30, 2021 at 9:31 PM

    I am a new user of this site, so here I saw several articles and posts published on this site, I am more interested in some of them, hope you will provide more information on these topics in your next articles.
    data analytics training in bangalore

    ReplyDelete
  10. 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
  11. Data Science Course in Bhilai - 360DigiTMGJuly 15, 2021 at 2:16 AM

    Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    data science course in faridabad

    ReplyDelete
  12. 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
  13. 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
  14. MallelaJuly 28, 2021 at 5:09 AM

    Thanks for posting the best information and the blog is very important.data science course in Lucknow

    ReplyDelete