Python XlsxWriter - Fonts & Colors

Working with Fonts

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})

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

ws.write( B1 ,  Hello World , f1)

Example

To make the text in a cell bold, underpne, itapc 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_itapc(True)
ws.write( B2 ,  =A2 , f2)

f3.set_underpne(True)
ws.write( B3 ,  =A3 , f3)

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

wb.close()

Output

Here is the result −

Example

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({ itapc :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

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

Text Apgnment

XlsxWriter s Format object can also be created with apgnment methods/properties. The apgn 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({ apgn : left })
f2=wb.add_format({ apgn : right })
f3=wb.add_format({ apgn : center })
f4=wb.add_format({ apgn : 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

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

Example

Format object also has vapgn 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({ vapgn : top })
f2=wb.add_format({ vapgn : bottom })
f3=wb.add_format({ apgn : vcenter })
f4=wb.add_format({ apgn : vjustify })

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

wb.close()

Output

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

Cell Background and Foreground Colors

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 pke this −