Borders and Images
Borders and Images¶
In this section, we build on how to create borders and images after we work through the title function creation solution.
Title Function Creation Solution¶
Below is the solution to the problem. As well, we can see the last line sets the alignment to horizontal by creating an Alignment object.
In [21]:
from openpyxl.styles.alignment import Alignment
# A title function for the worksheet
def create_title(ws: Worksheet, area: str, text: str) -> None:
"""
Writes a title on to the passed worksheet
Parameters
----------
ws : Worksheet
The worksheet to modify
area : str
The area to put the title in (of the form A1:A5 or similar)
text : str
The text for the title
"""
# Merge the cells in the area
ws.merge_cells(area)
# Given an area such as A1:A5, we want to first isolate the first cell
# Split will break the area into the two components, we take the first one
first_cell = area.split(":")[0]
# Find the coordinates
first_cell = coordinate_from_string(first_cell)
# Convert the components to the numerical values for column and row
col = column_index_from_string(first_cell[0])
row = first_cell[1]
# Change the text of the merged cell to whatever was passed as input
ws.cell(row=row, column=col).value = text
# Change the font of the title
font = font = Font(size=16,
bold=True,
color="283747")
ws.cell(row=row, column=col).font = font
# Change alignment to be centered
ws.cell(row=row, column=col).alignment = Alignment(horizontal="center")
# Update the treasury sheet function
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
"""
Function to fill out the treasury curve data
Parameters
----------
ws : Worksheet
The worksheet to modify
yield_curve_table : DataFrame
The yield curve data
"""
#Write the yield curve table
write_to_excel(ws, 3, 1, yield_curve_table)
#Color the yield curve changes red/green
red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
#Adjust the column widths for the table
adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
#Bold the index and columns
bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
# Create title
create_title(ws, 'A1:F1', "Treasury Yield Curve")
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")
Borders¶
For borders, a cell can be modified by using its border attribute.
What needs to be passed is a Border object in which we specify the borders for each side left, right, top and bottom. These borders can be any of the permissible excel borders.
Below is an example of creating borders for a cell.
In [22]:
from openpyxl.styles import Border, Side
# Create the base workbook
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 3, 1, yield_curve_table)
# With borders we need to specify the styles we want to use for each border
border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
# Set the border
temp = ws.cell(row=5,column=5)
temp.border = border
wb.save("Workbooks/Example 8.xlsx")
Now that we know how borders work, how about we build a function for giving thin borders in a region of our worksheet.
In [23]:
#Create a function which takes a starting position and the length vertically and horizontally
#and then creates borders around all of them
def create_border(ws: Worksheet, start_row: int, start_column: int,
vertical_length: int, horizontal_length: int) -> None:
"""
Function which takes a starting position and the length vertically
and horizontally, then creates thin borders around all of them
Parameters
----------
ws : Worksheet
The worksheet to modify
start_row : int
The row to begin at
start_column : int
The column to begin at
vertical_length : int
How far down borders should be applied
horizontal_length : int
How far right borders should be applied
"""
# Create the border
border = Border(left=Side(border_style='thin'),
right=Side(border_style='thin'),
top=Side(border_style='thin'),
bottom=Side(border_style='thin'))
# Iterate and set the border
for row in range(vertical_length):
for column in range(horizontal_length):
ws.cell(row=start_row+row,column=start_column+column).border = border
# Create the base notebook
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 3, 1, yield_curve_table)
# Write the border
create_border(ws, 4, 2, yield_curve_table.shape[0], yield_curve_table.shape[1])
wb.save("Workbooks/Example 9.xlsx")
Naturally, we can update our main function for writing at this point.
In [24]:
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
"""
Function to fill out the treasury curve data
Parameters
----------
ws : Worksheet
The worksheet to modify
yield_curve_table : DataFrame
The yield curve data
"""
# Write the yield curve table
write_to_excel(ws, 3, 1, yield_curve_table)
# Color the yield curve changes red/green
red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
# Adjust the column widths for the table
adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
# Bold the index and columns
bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
# Create title
create_title(ws, 'A1:F1', "Treasury Yield Curve")
# Add borders
create_border(ws, 4, 2, yield_curve_table.shape[0], yield_curve_table.shape[1])
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")
Gridlines¶
For the excel worksheet, there is an attribute sheet_view which has a few other modifications we can make. One of those is the attribute showGridLines which when set to false will get rid of the gridlines in the worksheet. Below is an example of turning it off.
In [25]:
# Write the base worksheet
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 3, 1, yield_curve_table)
# We might be interested in switching off gridlines
ws.sheet_view.showGridLines = False
wb.save("Workbooks/Example 10.xlsx")
Then update the write_treasury_sheet function.
In [26]:
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
"""
Function to fill out the treasury curve data
Parameters
----------
ws : Worksheet
The worksheet to modify
yield_curve_table : DataFrame
The yield curve data
"""
# Write the yield curve table
write_to_excel(ws, 3, 1, yield_curve_table)
# Color the yield curve changes red/green
red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
# Adjust the column widths for the table
adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
# Bold the index and columns
bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
# Create title
create_title(ws, 'A1:F1', "Treasury Yield Curve")
# Add borders
create_border(ws, 4, 2, yield_curve_table.shape[0], yield_curve_table.shape[1])
# Get rid of gridlines
ws.sheet_view.showGridLines = False
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")
Adding Images¶
With the function add_image, we just need to specify and image and where to place it and we will then have it placed nicely in our worksheet. For example, below, we pull in the treasury yield curve image we created in the last lesson as an image that we want to add to our workbook. Then we call add image and give it the location of H1 that we want it placed at.
In [27]:
from openpyxl.drawing.image import Image
# Create the base worksheet
wb = openpyxl.Workbook()
ws = wb.active
write_to_excel(ws, 3, 1, yield_curve_table)
# Images can be put into excel worksheets by loading them then adding image to a given position
img = Image('Images/Treasury Yield Curves.png')
ws.add_image(img, 'H1')
wb.save("Workbooks/Example 11.xlsx")
Add the final modifications to our write_treasury_sheet function.
In [28]:
def write_treasury_sheet(ws: Worksheet, yield_curve_table: DataFrame) -> None:
"""
Function to fill out the treasury curve data
Parameters
----------
ws : Worksheet
The worksheet to modify
yield_curve_table : DataFrame
The yield curve data
"""
# Write the yield curve table
write_to_excel(ws, 3, 1, yield_curve_table)
# Color the yield curve changes red/green
red_green_map(ws, yield_curve_table[["1 Year Change"]], 4, 4)
red_green_map(ws, yield_curve_table[["5 Year Change"]], 4, 6)
# Adjust the column widths for the table
adjust_column_widths(ws,2,[15]*len(yield_curve_table.columns))
# Bold the index and columns
bold_cells_line(ws, 4, 1, len(yield_curve_table), vertical=True)
bold_cells_line(ws, 3, 2, len(yield_curve_table.columns), vertical=False)
# Create title
create_title(ws, 'A1:F1', "Treasury Yield Curve")
# Add borders
create_border(ws, 4, 2, yield_curve_table.shape[0], yield_curve_table.shape[1])
# Get rid of gridlines
ws.sheet_view.showGridLines = False
# Add yield curve graphs
img = Image('Images/Treasury Yield Curves.png')
ws.add_image(img, 'H1')
wb = openpyxl.Workbook()
ws = wb.active
write_treasury_sheet(ws, yield_curve_table)
wb.save("Workbooks/Economic Report.xlsx")