Adding formatting to the xlsx file with xlsxwriter Python

Sometimes manual formatting in an excel sheet becomes a very tedious task to do. In this piece, I’ll explain the use of XlsxWriter to format data of an excel sheet. XlsxWriter is a python package that can be used to write text, numbers, formulas, etc. It also supports formatting. In this story, we’ll learn to format font properties using XlsxWriter.

Setup

Let’s start with an installation part.

We can install a library using the below command.

pip install xlsxwriter

XlsxWriter is installed now. Let’s start with formatting examples.

Formatting of Dataframe Output

Change font color and weight

We will change the font color to blue and font to bold.

1. set_bold() — Turns bold on. Also, we can pass True(Also turns bold on) or False(Turns bold off) as an argument to the set_bold().
2. set_font_color(color) — Set the font color. The type of color argument is a string.

change_font_color_&_weight.py

In the above code, I’ve added a blank add_format() method and changed font properties after that. We can set format to a particular row(using set_row()) or column(using set_column()). I’ve set created format to the Age column.

Also, we can pass a format to the add_format() method as below.

cell_format = workbook.add_format({'bold': True, 'font_color': 'blue'})

Below is the output of the above code.

Output of change_font_color_&_weight.py

Change font type and size

Now we’ll change the font type and size of the Name column.

1. set_font_name(name) — Set the font. The type of name argument is a string.
2. set_font_name(size) — Set the size of the font. The type of size argument is an integer.

change_font_type_&_size.py

In the above code, I’ve changed the font type to Bodoni MT Black and font size to 15.

Here is an output of the above code.

Output of change_font_type_&_size.py

Formatting of the Dataframe headers

We’ve worked on the formatting of data in the above examples. Pandas write the dataframe header with a default cell format. The set_column() method can not override the format of the header cells. So, If we want to format headers using our format then we have to turn off the automatic header from Pandas and write your own.

Change some properties of the header font

In this example, we’ll change the font color, type, and some properties. Below are the new methods.

1. set_italic() — Turns italic on. Also, we can pass True(Also turns italic on) or False(Turns italic off) as an argument to the set_italic().
2. set_underline() — Turns underline on. Also, we can pass True(Also turns on underline) or False(Turns off underline) as an argument to the set_underline().

format_dataframe_headers.py

In the above code, I’ve changed the below properties.

1. Set font to Bodoni MT Black.
2. Set font size to 12.
3. Set font color to green.
4. Set font to italic.
5. Set the font to underline.

Here is an output of the above code.

Output of format_dataframe_headers.py

Thanks for reading this article. If you like it, click on 👏 to rate it out of 50 and also share it with your friends. It means a lot to me.

Would you like to check out my other articles?

Working with Excel sheets in Python using openpyxl

Using Python Pandas With Excel