Microsoft Excel is widely used for organizing and managing data efficiently. However, dealing with empty rows and columns in Excel spreadsheets (XLS or XLSX) can be a tedious and time-consuming task. Blank rows and columns can often be a result of importing data from various sources or due to incomplete data entry. Regardless of the reason, these empty cells can disrupt the accuracy of our analysis and may increase file size. Our goal is to identify and delete such rows and columns swiftly, saving time and effort. We can automate this process and streamline our data cleaning workflow programmatically in Python. In this article, we will explore a powerful technique to delete blank rows and columns in Excel using Python.
This article covers the following topics:
- Python API to Delete Blank Rows and Columns in Excel
- Delete Blank Rows in an Excel Worksheet Using Python
- Delete Blank Columns in an Excel Worksheet Using Python
- Update Reference While Deleting Blank Rows and Columns
- Free Learning Resources
Python Library to Delete Excel Rows and Columns
Aspose.Cells is a popular library for working with Microsoft Excel files, and it provides a set of APIs for various programming languages, including Python. We will use Aspose.Cells for Python via .NET to delete blank rows and columns in Excel worksheets. It allows you to create, manipulate, and convert Excel files in your Python applications.
Please download the package or install the API from PyPI using the following pip command in the console:
pip install aspose-cells-python
Delete All Blank Rows in Excel using Python
For eliminating empty or blank rows in Excel, the API provides the delete_blank_rows() method to remove any rows with missing or empty values. We can delete blank rows in an Excel worksheet by following the steps below:
- Load the Excel file using the Workbook class.
- Access the desired worksheet from the worksheet collection.
- After that, delete the blank rows using the cells.delete_blank_rows() method.
- Finally, save the resultant file using the workbook.save(string) method.
The following code sample shows how to delete blank rows from an Excel worksheet in Python.
Delete All Blank Rows in Excel using Python
Delete Blank Columns in Excel using Python
Deleting blank columns follows a similar approach. The API provides delete_blank_columns() method to remove any columns with missing or empty values. We can delete blank columns in an Excel worksheet by following the steps below:
- Load the Excel file using the Workbook class.
- Access the desired worksheet from the worksheets collection.
- After that, delete all the blank columns using the cells.delete_blank_columns() method.
- Finally, save the resultant file using the workbook.save(string) method.
The following code sample shows how to delete blank columns from an Excel worksheet in Python.
Delete Blank Columns in Excel using Python
Update References When Delete Blank Rows and Columns in Excel using Python
In certain cases, when we remove empty columns and rows from a worksheet, it can lead to invalid references in other worksheets. We can prevent this issue by utilizing the DeleteOptions.update_reference property and set it to True. It ensures that references to the current worksheet in other sheets are updated accordingly.
We can update references in other worksheets while deleting blank columns and rows in a worksheet by following the steps below:
- Load the Excel file using the Workbook class.
- Access the desired worksheet from the worksheets collection.
- Calculate formulas of the workbook using the workbook.calculate_formula() method.
- Specify the DeleteOptions.update_reference to true.
- Delete blank rows or columns using the delete_blank_rows(DeleteOptions) or delete_blank_columns(DeleteOptions) methods.
- Recalculate formulas of the workbook using the workbook.calculate_formula() method.
- Finally, save the resultant file using the workbook.save(string) method.
The following code sample shows how to update references in other worksheets while deleting blank rows or columns in Excel using Python.
Get a Free API License
You can get a free temporary license in order to use the API without evaluation limitations.
Delete Blank Lines in Excel – Learning Resources
Besides deleting blank rows and columns from Excel worksheets, learn more about creating, manipulating, and converting Excel files, and explore various other features of the library using the resources below:
Conclusion
In this article, we have demonstrated how to delete blank rows and columns in Excel effortlessly using Python. By leveraging the power of Aspose.Cells for Python, we can efficiently clean our data and enhance accuracy. By automating this task, you can save valuable time and focus on other aspects of your project. In case of any ambiguity, please contact us on our free support forum.