Python Xlsxwriter 简明教程

Python XlsxWriter - Fonts & Colors

Working with Fonts

要执行工作表单元格格式,我们需要使用格式对象,借助 add_format() 方法对其进行配置,并使用其属性或格式化方法对对象进行配置。

To perform formatting of worksheet cell, we need to use Format object with the help of add_format() method and configure it with its properties or formatting methods.

f1 = workbook.add_format()
f1 = set_bold(True)
# or
f2 = wb.add_format({'bold':True})

然后将此格式对象用作工作表 write() 方法的参数。

This format object is then used as an argument to worksheet’s write() method.

ws.write('B1', 'Hello World', f1)

Example

要使单元格 bold, underline, italicstrike through, 中的文本可以设置,我们可以使用这些属性或相应的函数。在下面的示例中,文本 Hello World 使用 set 方法进行编写。

To make the text in a cell bold, underline, italic or strike through, we can either use these properties or corresponding methods. In the following example, the text Hello World is written with set methods.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")

f1=wb.add_format()
f2=wb.add_format()
f3=wb.add_format()
f4=wb.add_format()

f1.set_bold(True)
ws.write('B1', '=A1', f1)

f2.set_italic(True)
ws.write('B2', '=A2', f2)

f3.set_underline(True)
ws.write('B3', '=A3', f3)

f4.set_font_strikeout(True)
ws.write('B4', '=A4', f4)

wb.close()

Output

以下是结果 −

Here is the result −

working with fonts1

Example

另一方面,我们也可以像以下示例中那样使用 font_color, font_namefont_size 属性来设置文本格式 -

On the other hand, we can use font_color, font_name and font_size properties to format the text as in the following example −

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")

f1=wb.add_format({'bold':True, 'font_color':'red'})
f2=wb.add_format({'italic':True,'font_name':'Arial'})
f3=wb.add_format({'font_size':20})
f4=wb.add_format({'font_color':'blue','font_size':14,'font_name':'Times New Roman'})

ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', '=A4', f4)

wb.close()

Output

打开工作表并使用 Excel 查看以上代码的输出 -

The output of the above code can be verified by opening the worksheet with Excel −

working with fonts2

Text Alignment

XlsxWriter 的 Format 对象也可以用对齐方法/属性创建。align 属性可以具有 left, right, centerjustify 值。

XlsxWriter’s Format object can also be created with alignment methods/properties. The align property can have left, right, center and justify values.

Example

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
for row in range(4):
   ws.write(row,0, "Hello World")
ws.set_column('B:B', 30)

f1=wb.add_format({'align':'left'})
f2=wb.add_format({'align':'right'})
f3=wb.add_format({'align':'center'})
f4=wb.add_format({'align':'justify'})
ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', 'Hello World', f4)

wb.close()

Output

以下输出显示了具有不同对齐方式的文本“Hello World”。请注意 B 列的宽度由工作表对象的 set_column() 方法设置为 30。

The following output shows the text "Hello World" with different alignments. Note that the width of B column is set to 30 by set_column() method of the worksheet object.

text alignment1

Example

Format 对象还具有 valign 属性来控制单元格的垂直位置。

Format object also has valign properties to control vertical placement of the cell.

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()

for row in range(4):
   ws.write(row,0, "Hello World")

ws.set_column('B:B', 30)

for row in range(4):
   ws.set_row(row, 40)
f1=wb.add_format({'valign':'top'})
f2=wb.add_format({'valign':'bottom'})
f3=wb.add_format({'align':'vcenter'})
f4=wb.add_format({'align':'vjustify'})

ws.write('B1', '=A1', f1)
ws.write('B2', '=A2', f2)
ws.write('B3', '=A3', f3)
ws.write('B4', '=A4', f4)

wb.close()

Output

在上面的代码中,行 1 到 4 的高度使用 set_row() 方法设置为 40。

In the above code, the height of rows 1 to 4 is set to 40 with set_row() method.

text alignment2

Cell Background and Foreground Colors

Format 对象有两个重要属性 bg_colorfg_color ,用于设置单元格的背景色和前景色。

Two important properties of Format object are bg_color and fg_color to set the background and foreground color of a cell.

Example

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.set_column('B:B', 30)

f1=wb.add_format({'bg_color':'red', 'font_size':20})
f2=wb.add_format({'bg_color':'#0000FF', 'font_size':20})

ws.write('B1', 'Hello World', f1)
ws.write('B2', 'HELLO WORLD', f2)
wb.close()

Output

上述代码结果如下所示 -

The result of above code looks like this −

cell background and foreground colors