Deleting rows and columns from Excel is crucial for maintaining clean and organized data. For example, when a worksheet accumulates blank rows or columns that serve no purpose and clutter the data, removing them becomes necessary. By deleting these blank rows and columns, you can effectively reduce the file size and enhance the spreadsheet's readability. In this article, we will explain how to delete rows and columns from Excel in Python using Spire.XLS for Python.
- Delete a Specific Row and Column from Excel in Python
- Delete Multiple Rows and Columns from Excel in Python
- Delete Blank Rows and Columns from Excel in Python
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Delete a Specific Row and Column from Excel in Python
The Worksheet.DeleteRow(rowIndex) and Worksheet.DeleteColumn(columnIndex) methods provided by Spire.XLS for Python enable you to delete a specific row and column from an Excel worksheet. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Delete the desired row from the worksheet by its index (1-based) using Worksheet.DeleteRow(rowIndex) method.
- Delete the desired column from the worksheet by its index (1-based) using Worksheet.DeleteColumn(columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Delete the 9th row
sheet.DeleteRow(9)
# Delete the 3rd column
sheet.DeleteColumn(3)
# Save the result file
workbook.SaveToFile("DeleteSpecificRowAndColumn.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Delete Multiple Rows and Columns from Excel in Python
Spire.XLS for Python enables you to delete multiple adjacent rows and columns from an Excel worksheet at once by using the Worksheet.DeleteRow(startRowIndex, rowCount) and Worksheet.DeleteColumn(startColumnIndex, columnCount) methods. The detailed steps are as follows.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Delete the desired rows from the worksheet using Worksheet.DeleteRow(startRowIndex, rowCount) method.
- Delete the desired columns from the worksheet using Worksheet.DeleteColumn(startColumnIndex, columnCount) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Delete the 5th, 6th and 7th rows
sheet.DeleteRow(5, 3)
# Delete the 3rd and 4th columns
sheet.DeleteColumn(3, 2)
# Save the result file
workbook.SaveToFile("DeleteMultipleRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Delete Blank Rows and Columns from Excel in Python
You can use the Worksheet.Row[rowIndex].IsBlank and Worksheet.Column[columnIndex].IsBlank properties to detect whether a specific row and column are blank or not. If the result is True, you can remove them from your woeksheet using the Worksheet.DeleteRow(rowIndex) and Worksheet.DeleteColumn(columnIndex) methods.
The following steps show how to delete the blank rows and columns from an Excel worksheet.
- Create an object of the Workbook class.
- Load an Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
- Loop through the used rows in the worksheet.
- Find the blank rows using Worksheet.Row[rowIndex].IsBlank property and then delete them from the worksheet using Worksheet.DeleteRow(rowIndex) method.
- Loop through the used columns in the worksheet.
- Find the blank columns using Worksheet.Column[columnIndex].IsBlank property and then delete them from the worksheet using Worksheet.DeleteColumn(columnIndex) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import *
from spire.xls.common import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Input1.xlsx")
# Get the first worksheet
sheet = workbook.Worksheets[0]
# Delete blank rows from the worksheet
for i in range(sheet.Rows.Length - 1, -1, -1):
if sheet.Rows[i].IsBlank:
sheet.DeleteRow(i + 1)
# Delete blank columns from the worksheet
for j in range(sheet.Columns.Length - 1, -1, -1):
if sheet.Columns[j].IsBlank:
sheet.DeleteColumn(j + 1)
# Save the result file
workbook.SaveToFile("DeleteBlankRowsAndColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
