Insert or Delete Rows, Columns and Cells in Excel with Python
In Excel, inserting and deleting rows, columns, and cells are crucial operations that can help you better manage and organize your data. Whether you need to add new data, reorganize existing data, or remove unnecessary information, these operations can assist you in maintaining a well-structured and accurate spreadsheet. In this article, we will explore how to insert and delete rows, columns, and cells in Excel using Python.
We will discuss the following topics:
- Insert Rows in Excel with Python
- Insert Columns in Excel with Python
- Insert Cells in Excel with Python
- Delete Rows in Excel with Python
- Delete Rows Containing a Specific Value in Excel with Python
- Delete Columns in Excel with Python
- Delete Columns Containing a Specific Value in Excel with Python
- Delete Cells in Excel with Python
- Delete Cells Containing a Specific Value in Excel with Python
Python Library to Insert and Delete Rows, Columns and Cells in Excel
To insert and delete rows, columns, and cells in Excel in Python, we can use the Spire.XLS for Python library. It is an easy-to-use and feature-rich library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.
You can install Spire.XLS for Python from PyPI by running the following commands in your terminal:
pip install Spire.XlsFor more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Insert Rows in Excel with Python
Inserting rows in Excel is a fundamental operation that allows you to add new rows to your spreadsheet. This is particularly useful when you want to expand your data range or insert new data into specific locations.
You can insert a row or multiple rows into an Excel worksheet by using the Worksheet.InsertRow(rowIndex: int, rowCount: int, insertOptions: InsertOptionsType) function provided by Spire.XLS for Python.
Here is a simple example that shows how to insert rows into an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Insert 2 rows into the worksheet at the 5th position. The inserted rows will be formatted as the above row
worksheet.InsertRow(5, 2, InsertOptionsType.FormatAsBefore)
# Save the result file
workbook.SaveToFile("InsertRows.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Insert Columns in Excel with Python
Inserting columns in Excel is a useful feature that enables you to add new columns to your spreadsheet. This is beneficial when you need to include additional categories or data fields in your dataset.
You can insert a column or multiple columns into an Excel worksheet by using the Worksheet.InsertColumn(columnIndex: int, columnCount: int, insertOptions: InsertOptionsType) function provided by Spire.XLS for Python.
Here is a simple example that shows how to insert columns into an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Insert 1 column into the worksheet at the 3rd position. The inserted column will be formatted as the left column
worksheet.InsertColumn(3, 1, InsertOptionsType.FormatAsBefore)
# Save the result file
workbook.SaveToFile("InsertColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Insert Cells in Excel with Python
Inserting cells in Excel allows you to add new cells within an existing range of cells in a worksheet. This operation is useful when you need to insert new data or shift existing data to accommodate changes in your spreadsheet.
You can insert a cell or range of cells into an Excel worksheet by using the Worksheet.InsertRange(rowIndex: int, columnIndex: int, rowCount: int, columnCount: int, moveOptions: InsertMoveOption, insertOptions: InsertOptionsType) function provided by Spire.XLS for Python.
Here is a simple example that shows how to insert cells into an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Insert cells into the worksheet at a specific position
worksheet.InsertRange(2, 1, 1, 2, InsertMoveOption.MoveRight, InsertOptionsType.FormatDefault)
# Save the result file
workbook.SaveToFile("InsertCells.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Rows in Excel with Python
Deleting rows in Excel allows you to remove unwanted or redundant rows from your spreadsheet. This operation is particularly useful when you have unnecessary or erroneous information that needs to be eliminated.
You can delete a row or multiple rows from an Excel worksheet by using the Worksheet.DeleteRow(rowIndex: int, rowCount: int) function provided by Spire.XLS for Python.
Here is a simple example that shows how to delete rows from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Delete 2 rows from the worksheet starting from the 5th row
worksheet.DeleteRow(5, 2)
# Save the result file
workbook.SaveToFile("DeleteRows.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Rows Containing a Specific Value in Excel with Python
Deleting rows containing a specific value in Excel allows you to remove entire rows from a worksheet that contain a particular value. This operation is useful when you want to eliminate rows that meet certain criteria or when you need to filter and clean up your data.
Here is a simple example that shows how to delete rows containing a specific value from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Find the cells containing a specific value
ranges = worksheet.FindAll("Components", FindType.Text, ExcelFindOptions.MatchEntireCellContent)
# Create a list to store the row indexes
row_indexes = []
# Loop through the found cells
for range in ranges:
# Get the index of the row where the cell is located
row_index = range.Row
# If the row index does not exist in the list, add it to the list
if row_index not in row_indexes:
row_indexes.append(row_index)
# Sort the list in reverse order by row index
row_indexes.sort(reverse=True)
# Remove the rows from the worksheet by row index
for row_index in row_indexes:
worksheet.DeleteRow(row_index)
# Save the result file
workbook.SaveToFile("DeleteRowsWithSpecificValue.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Columns in Excel with Python
Deleting columns in Excel enables you to remove unnecessary or redundant columns from your spreadsheet. This can be helpful when you have extraneous data fields or when you want to simplify your dataset.
You can delete a column or multiple columns from an Excel worksheet by using the Worksheet.DeleteColumn(columnIndex: int, columnCount: int) function provided by Spire.XLS for Python.
Here is a simple example that shows how to delete columns from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Delete 2 columns from the worksheet starting from the 3rd column
worksheet.DeleteColumn(3, 2)
# Save the result file
workbook.SaveToFile("DeleteColumns.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Columns Containing a Specific Value in Excel with Python
Deleting columns containing a specific value in Excel enables you to remove entire columns from a worksheet that contain a particular value. This operation is helpful when you want to eliminate columns that meet specific criteria or when you need to filter and reorganize your data.
Here is a simple example that shows how to delete columns containing a specific value from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Find the cells containing a specific value
ranges = worksheet.FindAll("Components", FindType.Text, ExcelFindOptions.MatchEntireCellContent)
# Create a list to store the column indexes
col_indexes = []
# Loop through the found cells
for range in ranges:
# Get the index of the column where the cell is located
col_index = range.Column
# If the column index does not exist in the list, add it to the list
if col_index not in col_indexes:
col_indexes.append(col_index)
# Sort the list in reverse order by column index
col_indexes.sort(reverse=True)
# Remove the columns from the worksheet by column index
for col_index in col_indexes:
worksheet.DeleteColumn(col_index)
# Save the result file
workbook.SaveToFile("DeleteColumnsWithSpecificValue.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Cells in Excel with Python
Deleting cells in Excel allows you to remove specific cells or a range of cells from a worksheet. This operation is helpful when you want to eliminate unwanted or redundant data or make adjustments to the layout of your spreadsheet.
You can delete a specific cell or range of cells from an Excel worksheet by using the Worksheet.DeleteRange(range: CellRange, deleteOption: DeleteOption) function provided by Spire.XLS for Python.
Here is a simple example that shows how to delete cells from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Get a cell range by specifying the start row index, start column index, end row index, and end column index (you can also get a cell range by specifying the range name like “A2:B3”)
range_to_delete = worksheet.Range[2, 1, 3, 2]
# Delete the cell range from the worksheet
worksheet.DeleteRange(range_to_delete, DeleteOption.MoveLeft)
# Save the result file
workbook.SaveToFile("DeleteCells.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Delete Cells Containing a Specific Value in Excel with Python
Deleting cells containing a specific value in Excel allows you to remove specific cells that contain a particular value in a worksheet. This operation is useful when you want to remove individual cells that meet certain criteria or when you need to clean up and refine your data.
Here is a simple example that shows how to delete cells containing a specific value from an Excel worksheet using Python and Spire.XLS for Python:
from spire.xls.common import *
from spire.xls import *
# Create a Workbook object
workbook = Workbook()
# Load an Excel file
workbook.LoadFromFile("Template.xlsx")
# Get the first worksheet
worksheet = workbook.Worksheets[0]
# Find the cells containing a specific value
ranges = worksheet.FindAll("Components", FindType.Text, ExcelFindOptions.MatchEntireCellContent)
# Loop through the found cells
for range in ranges:
# Delete each cell from the worksheet
worksheet.DeleteRange(range, DeleteOption.MoveLeft)
# Save the result file
workbook.SaveToFile("DeleteCellsWithSpecificValue.xlsx", ExcelVersion.Version2016)
workbook.Dispose()Conclusion
This article demonstrated how to insert and delete rows, columns, and cells in Excel files using Python. We hope you find it helpful.
