格式化字体和对齐方式 [英] Formatting Fonts and Alignment
本文介绍了格式化字体和对齐方式的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用Python 2.7和OpenPyXL 2.4.5向Excel工作表添加字体和对齐方式.我收到以下错误，但我不明白为什么.谁能向我解释一下?根据我的测试，这与我设置字体(不正确?)有关，而不是与实际保存有关.
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'>
这是我的代码.
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
推荐答案
我认为问题是您正在将单元格的font属性设置为对齐对象.
I think the issue is you are setting the font attribute of the cell to an alignment object.
在代码的这一部分中，您有此内容.
In this section of your code you have this.
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
在此部分的最后一行中，请注意..... 字体 =标头_ 对齐方式
Notice in the last line of this section you have .....font = header_alignment
尝试将其更改为对齐方式，看看它是否适合您
Try changing that to alignment and see if it works for you
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).alignment = header_alignment
这篇关于格式化字体和对齐方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
