I'm trying to add fonts and alignment to an Excel worksheet using Python 2.7 and OpenPyXL 2.4.5. I'm getting the following error, but I don't understand why. Can anyone explain this to me? From my testing, it's related to me setting the font (incorrectly?), and not the actual save.
C:\Scripts\Stack_Overflow>xlsx_font_and_alignment.py
Traceback (most recent call last):
File "C:\Scripts\Stack_Overflow\xlsx_font_and_alignment.py", line 61, in <modu
le>
main()
File "C:\Scripts\Stack_Overflow\xlsx_font_and_alignment.py", line 59, in main
report_wb.save(report_fname)
File "C:\Python27\lib\openpyxl\workbook\workbook.py", line 342, in save
save_workbook(self, filename)
File "C:\Python27\lib\openpyxl\writer\excel.py", line 266, in save_workbook
writer.save(filename)
File "C:\Python27\lib\openpyxl\writer\excel.py", line 248, in save
self.write_data()
File "C:\Python27\lib\openpyxl\writer\excel.py", line 90, in write_data
stylesheet = write_stylesheet(self.workbook)
File "C:\Python27\lib\openpyxl\styles\stylesheet.py", line 199, in write_style
sheet
stylesheet.fonts = wb._fonts
File "C:\Python27\lib\openpyxl\descriptors\sequence.py", line 27, in __set__
seq = [_convert(self.expected_type, value) for value in seq]
File "C:\Python27\lib\openpyxl\descriptors\base.py", line 59, in _convert
raise TypeError('expected ' + str(expected_type))
TypeError: expected <class 'openpyxl.styles.fonts.Font'>
Here's my code.
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font
import os.path
def xlsx_font_and_alignment(ws_to_format, x_axis, y_axis):
""" Format Excel worksheet font and alignment and return the worksheet. """
report_font = Font(name='Courier New',
size=10, \
bold=False, \
italic=False, \
vertAlign=None, \
underline='none', \
strike=False, \
color='FF000000')
header_font = Font(name='Courier New', \
size=10, \
bold=True, \
italic=False, \
vertAlign=None, \
underline='none', \
strike=False, \
color='FF000000')
report_alignment = Alignment(horizontal='left', \
indent=1, \
vertical='center', \
wrap_text=True)
header_alignment = Alignment(horizontal='left', \
indent=2, \
vertical='center', \
wrap_text=True)
if x_axis == 1:
if ws_to_format.cell(row=1, column=y_axis).value:
ws_to_format.cell(row=1, column=y_axis).font = header_font
ws_to_format.cell(row=1, column=y_axis).font = header_alignment
if x_axis != 1:
if ws_to_format.cell(row=x_axis, column=y_axis).value:
ws_to_format.cell(row=x_axis, column=y_axis).font = report_font
ws_to_format.cell(row=x_axis, column=y_axis).alignment = report_alignment
return ws_to_format
def main():
""" Use OpenPyXL to format a XLSX. """
csv_list = [['Endpoint ID', 'Occurred At', 'Status', 'Message'],
['Foo EP', '2017-04-20T04:10:11', 'Foo Status', 'Foo Message'],
['Foo EP', '2017-04-20T04:10:11', 'Foo Status', 'Foo Message'],
['Foo EP', '2017-04-20T03:15:01', 'Foo Status', 'Foo Message'],
['Foo EP', '2017-04-20T03:15:01', 'Foo Status', 'Foo Message'],
['Foo EP', '2017-04-19T20:00:12', 'Foo Status', 'Foo Message']]
path = os.path.dirname(os.path.abspath(__file__))
report_fname = path + "\\" + "Test_Report_" + ".xlsx"
report_wb = Workbook()
report_wb.create_sheet("Font Test")
ws_to_format = report_wb.get_sheet_by_name("Font Test")
for row in csv_list:
ws_to_format.append(row)
row_count = ws_to_format.max_row
column_count = ws_to_format.max_column
for y_axis in range(1, column_count+1):
for x_axis in range(1, row_count+1):
ws_to_format = xlsx_font_and_alignment(ws_to_format, x_axis, y_axis)
report_wb.save(report_fname)
if __name__ == "__main__":
main()
The odd thing is that I don't get the error when I attempt to change the fonts and alignment in the following manner. However, I'd like to be able to use something similar to the above to avoid itterating through the entire data set when formatting.
def xlsx_font_and_alignment(ws_to_format):
""" Format Excel worksheet font and alignment and return the worksheet. """
report_font = Font(name='Courier New',
size=10, \
bold=False, \
italic=False, \
vertAlign=None, \
underline='none', \
strike=False, \
color='FF000000')
header_font = Font(name='Courier New', \
size=10, \
bold=True, \
italic=False, \
vertAlign=None, \
underline='none', \
strike=False, \
color='FF000000')
report_alignment = Alignment(horizontal='left', \
indent=1, \
vertical='center', \
wrap_text=True)
header_alignment = Alignment(horizontal='left', \
indent=2, \
vertical='center', \
wrap_text=True)
row_count = ws_to_format.max_row
column_count = ws_to_format.max_column
for y_axis in range(1, column_count+1):
for x_axis in range(1, row_count+1):
if ws_to_format.cell(row=x_axis, column=y_axis).value:
ws_to_format.cell(row=x_axis, column=y_axis).font = report_font
ws_to_format.cell(row=x_axis, column=y_axis).alignment = report_alignment
for y_axis in range(1, column_count+1):
ws_to_format.cell(row=1, column=y_axis).font = header_font
ws_to_format.cell(row=1, column=y_axis).alignment = header_alignment
return ws_to_format
