Thursday, June 4, 2020

Excel Spreadsheet font manipulation with Python

import openpyxl, os
from openpyxl.styles import Font
from openpyxl.styles.colors import Color

'''
 Adapted from https://automatetheboringstuff.com/2e/chapter13/
 Data files used can be found at this link

 Customizing the Font Style of Cells
 Import the Font() function from the openpyxl.styles module.

 Table 13-2: Keyword Arguments for Font Objects
 ==============================================
 Keyword argument   Data type    Description
 ----------------   ---------    -----------
 name               String       The font name, such as 'Calibri' or 'Times New Roman'
 size               Integer      The point size
 bold               Boolean      True, for bold font
 italic             Boolean      True, for italic font

 Call Font() to create a Font object and store that Font object in a variable.
 Then assign that variable to a Cell object’s font attribute.
'''

os.chdir('/Google Drive/python_work/MyPythonScripts/datafiles')
print(os.getcwd())

wb = openpyxl.Workbook() # creates a WB with a single sheet called 'Sheet'
wb.create_sheet() # Creates a second shhet called 'Sheet1'
print(wb.sheetnames)
wb.create_sheet(index=0, title='S1')
wb.create_sheet(index=1, title='S2')
wb.create_sheet(index=2, title='S3')
print(wb.sheetnames)
# Deleting worksheets
del wb['Sheet']
del wb['Sheet1']
try:
    del wb['First']
except KeyError:
    print('Worksheet "First" does not exist')
print(wb.sheetnames)

### Font Objects
sheet = wb['S3']
# Create a font object.
italic24Font = Font(size=24, italic=True)
# Apply the font to A1.
sheet['A1'].font = italic24Font
sheet['A1'] = 'Hello!'
#
# Add a value to cell A2
sheet['A2'] = 'Bye'
# Create a font object.
myFont = Font(name='New Courier', strike=True, color='CC0000', size=16, italic=False, shadow=True, bold=True)
# Apply the font to A2.
sheet['A2'].font = myFont
sheet['A2'] = 'This is my font'
#
# Create a font object.
myFont = Font(name='New Courier', color='00FF00', size=10, shadow=True, bold=True)
# Add a value and apply the font to A3.
sheet['A3'] = 'This is my other font'
sheet['A3'].font = myFont

sheet = wb['S1']
# Set the Area's cell font values using two for loops:
# The outer for goes over each row in the slice;
#   The nested for loop goes through each cell in that row,
#    sets the font to myFont
#    and sets the value to the cells coordinate name (.coordinate)
for rowOfCellObjects in sheet['A1':'C4']:
    for cellObj in rowOfCellObjects:
        cellObj.font = myFont
        cellObj.value = str(cellObj.coordinate)

print(wb.active)
wb.save('styles.xlsx')
wb.close() 

No comments:

Post a Comment

Please add comments so I may update the material to accommodate platform modification to various commands. Also if you have some real-world caveats, do please share.

Search Duke

About the Author

My photo
Central Florida, United States