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

14 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. 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. 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. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. Thanks for posting the best information and the blog is very important.data science course in Lucknow

    ReplyDelete