If you have additional pieces of information to include in your spreadsheet, inserting rows or columns can provide room for these new fields. In addition,
adding blank rows or columns between data sets can also help to effectively separate different categories of information, making them easier to read and analyze. This article will demonstrate how to insert rows and columns in Excel in Python using Spire.XLS for 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
Insert a Row and a Column in Excel in Python
Spire.XLS for Python provides the Worksheet.InsertRow(rowIndex: int) and Worksheet.InsertColumn(columnIndex: int) methods for inserting a blank row and a blank column in an Excel worksheet. The following are the detailed steps:
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Insert a row into the worksheet using Worksheet.InsertRow(rowIndex: int) method.
- Insert a column into the worksheet using Worksheet.InsertColumn(columnIndex: int) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "input.xlsx" outputFile = "InsertRowAndColumn.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel document workbook.LoadFromFile(inputFile) # Get a specified worksheet worksheet = workbook.Worksheets[0] # Insert a blank row as the 5th row in the worksheet worksheet.InsertRow(5) # Insert a blank column as the 4th column in the worksheet worksheet.InsertColumn(4) # Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2016) workbook.Dispose()
Insert Multiple Rows and Columns in Excel in Python
To insert multiple rows and columns into a worksheet, you can use the Worksheet.InsertRow(rowIndex: int, rowCount: int) and Worksheet.InsertColumn(columnIndex: int, columnCount: int) methods. The following are detailed steps.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Insert multiple rows into the worksheet using Worksheet.InsertRow(rowIndex: int, rowCount: int) method.
- Insert multiple columns into the worksheet using Worksheet.InsertColumn(columnIndex: int, columnCount: int) method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "input.xlsx" outputFile = "InsertRowsAndColumns.xlsx" # Create a Workbook instance workbook = Workbook() # Load an Excel document workbook.LoadFromFile(inputFile) # Get a specified worksheet worksheet = workbook.Worksheets[0] # Insert three blank rows into the worksheet worksheet.InsertRow(5, 3) #Insert two blank columns into the worksheet worksheet.InsertColumn(4, 2) # Save the result file workbook.SaveToFile(outputFile, 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.
