Delete Rows or Columns from Excel in Python

When working with large datasets, being able to remove unnecessary rows or columns can help keep your data organized and easy to manage. In this article, we will explore several effective methods to delete rows and columns in Excel using Python.

1. Delete a specified row or column in Excel
2. Delete multiple rows or columns in Excel
3. Delete blank rows or columns in Excel

Introduction to Python Excel Library

Here we will need the Spire.XLS for Python library. It is a powerful Python Excel library for processing, reading and converting XLS or XLSX files. To install it, use the following pip command:

pip install Spire.XLS

Delete a Specified Row or Column in Excel with Python

To remove the specified row & column, the following methods are used:

▶ Worksheet.DeleteRow(rowIndex) method: Deletes the specified row by index (row index starts from 1).

▶ Worksheet.DeleteColumn(columnIndex) method: Deletes the specified column by index (column index starts from 1).

Python Code:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# delete the 5th row
sheet.DeleteRow(5)

# Delete the 3rd column
sheet.DeleteColumn(3)

# Save to file
workbook.SaveToFile("RemoveRowAndColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Delete Multiple Rows or Columns in Excel with Python

If you want to remove multiple rows or columns at once, you can use the following methods:

▶ Worksheet.DeleteRow(startRowIndex, rowCount) method: Deletes multiple rows by specifying the start row index and the number of rows to be deleted.

▶ Worksheet.DeleteColumn(startColumnIndex, columnCount) method: Deletes multiple columns by specifying the start column index and the number of columns to be deleted.

Python Code:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Delete row 4, 5 and 6 (total of 3 rows)
sheet.DeleteRow(4, 3)

# Delete columns 2 and 3
sheet.DeleteColumn(2, 2)

# Save to file
workbook.SaveToFile("RemoveMultipleRowsColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Delete Blank Rows or Columns in Excel with Python

To remove blank rows and columns from an Excel worksheet, you need to loop through each row and column, determine if they are empty using the following two methods, and then remove the empty rows and columns.

▶ Worksheet.Row[rowIndex].IsBlank property: Determines if the specified row is blank.

▶ Worksheet.Column[columnIndex].IsBlank property: Determines if the specified column is blank.

Python Code:

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
workbook = Workbook()

# Load an Excel file
workbook.LoadFromFile("sample.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Delete blank rows
for i in range(sheet.Rows.Length - 1, -1, -1):
if sheet.Rows[i].IsBlank:
sheet.DeleteRow(i + 1)

# Delete blank columns
for j in range(sheet.Columns.Length - 1, -1, -1):
if sheet.Columns[j].IsBlank:
sheet.DeleteColumn(j + 1)

# Save to file
workbook.SaveToFile("RemoveBlankRowsColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()

Conclusion

Using Spire.XLS for Python library to automate the removal of blank rows and columns from Excel files can save you a significant amount of time compared to doing it manually. By running the code above, you can either delete one or multiple rows & columns from Excel, or delete only the blank rows & columns programmatically in Python

If you want to explore more Excel processing or conversion features supported by the Python library, click below for more demos.

Spire.XLS for Python Program Guide Content