Python XlsxWriter - Textbox

In Excel, a text box is a graphic object that can be placed anywhere on the worksheet, and can be moved around if needed. Desired formatting features such as font (color, size, name etc.), alignment, fill effects, orientation etc. can be applied on the text contained in the text box.

Working with XlsxWriter – Textbox

In XlsxWriter, there is insert_textbox() method to place text box on the worksheet. The cell location of the text box and the text to be written in it must be given. Additionally, different formatting options are given in the form of a dictionary object.

Example

The following code displays a text box at cell C5, the given string is displayed with font and alignment properties as shown below −

import xlsxwriter

wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'Welcome to TutorialsPoint'

options = {'font': {'color': 'red','size': 14},
   'align': {'vertical': 'middle','horizontal': 'center'}}
worksheet.insert_textbox('C5', text, options)

wb.close()

Output

Open the worksheet 'hello.xlsx' with Excel app. The text box appears as below −

Text Box

Textbox Options – fill

The text box is by default 192X120 pixels in size (corresponds to 3 columns and 6 rows). This size can be changed with width and height parameters, both given in pixels. One of the parameters acceptable to inset_textbox() method is the fill parameter. It takes a predefined color name or color representation in hexadecimal as value.

Example

The following code displays a multi-line string in the custom sized text box having background filled with red color.

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'TutorialsPoint - Simple Easy Learning\nThe best resource for Online Education'

options = {
   'width': 384,
   'height':80,
   'font': {'color': 'blue', 'bold':True, 'size': 14},
   'align': {'vertical': 'middle', 'horizontal': 'center'},
   'fill':{'color':'red'},
}
worksheet.insert_textbox('C5', text, options)
wb.close()

As we can see in the figure below, a text box with multiple lines is rendered at cell C5.

Text Box With Multiple Lines

Textbox Options – text_rotation

Another important property is the text_rotation. By default, the text appears horizontally. If required, you may change its orientation by giving an angle as its value. Look as the following options.

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
worksheet = wb.add_worksheet()
text = 'TutorialsPoint - Simple Easy Learning\nThe best resource for Online Education'

options = {
   'width': 128,
   'height':200,
   'font': {'bold':True, 'name':'Arial', 'size': 14},
   'text_rotation':90,
}
worksheet.insert_textbox('C5', text, options)
wb.close()

The text now appears in the text box with its vertical orientation.

Text Rotation

The object_position parameter controls the behaviour of the text box. It can have the following possible values and their effect −

  • "1" − Move and size with cells (the default).

  • "2" − Move but don't size with cells.

  • "3" − Don't move or size with cells.

Advertisements