How To Merge Cells In Excel Using Python Xlwt

Merging cells in Excel is a common task when combining data in multiple cells into a single cell, improving readability and organization.

Python, with its powerful library for working with Excel files, xlwt (a library for writing data and formatting information to older .xls files), can easily perform this task. In this tutorial, we’ll show you how to merge cells in Excel using Python and xlwt.

Step 1: Install Xlwt Library

First, you need to install the xlwt library. To do that, open your command prompt or terminal and enter the following command:

1
pip install xlwt

This command installs the library, making it available for your Python projects.

Step 2: Import Necessary Packages

To start, import the necessary packages to your Python script. For this tutorial, you need to import xlwt.

1
import xlwt

Step 3: Create a New Workbook and Worksheet

Next, create a new workbook. A workbook contains one or more worksheets, which are individual sheets in an Excel file.

1
2
3
# Create a new workbook and add a worksheet to it
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Merged Cells')

Step 4: Write Data to the Worksheet

Now, write some data to your worksheet. For this example, we’ll create a basic table with headers.

1
2
3
4
5
6
7
8
9
10
11
12
# Write table headers
worksheet.write(0, 0, 'ID')
worksheet.write(0, 1, 'Name')
worksheet.write(0, 2, 'Age')
 
# Write sample data
worksheet.write(1, 0, '1')
worksheet.write(1, 1, 'John Doe')
worksheet.write(1, 2, '25')
worksheet.write(2, 0, '2')
worksheet.write(2, 1, 'Jane Smith')
worksheet.write(2, 2, '30')

Step 5: Merge Cells

Now you can merge cells. In this example, we’ll merge the ‘Name’ and ‘Age’ cells for the ‘John Doe’ row.

1
worksheet.write_merge(1, 1, 1, 2, 'John Doe & Age')

The write_merge function takes five arguments:

  1. The first row number of the merged cell range (0-indexed)
  2. The last row number of the merged cell range (0-indexed)
  3. The first column number of the merged cell range (0-indexed)
  4. The last column number of the merged cell range (0-indexed)
  5. The content of the merged cell

Step 6: Save the Workbook

Finally, save the workbook to an Excel file.

1
workbook.save('merged_cells.xls')

This command saves your workbook to a file called ‘merged_cells.xls’ in the current directory.

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
import xlwt
 
# Create a new workbook and add a worksheet to it
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Merged Cells')
 
# Write table headers
worksheet.write(0, 0, 'ID')
worksheet.write(0, 1, 'Name')
worksheet.write(0, 2, 'Age')
 
# Write sample data
worksheet.write(1, 0, '1')
worksheet.write(1, 1, 'John Doe')
worksheet.write(1, 2, '25')
worksheet.write(2, 0, '2')
worksheet.write(2, 1, 'Jane Smith')
worksheet.write(2, 2, '30')
 
# Merge cells
worksheet.write_merge(1, 1, 1, 2, 'John Doe & Age')
 
# Save the workbook
workbook.save('merged_cells.xls')

Output

Conclusion

In this tutorial, we’ve shown you how to merge cells in Excel using Python and the xlwt library. With this knowledge, you can create and manage complex Excel files, improving the organization and readability of your data.