如何使用 Python 在 Excel 文件中插入行

How to Insert Rows in an Excel File Using Python

1. Setup the Python Environment on your machine
2. Install the Excel Python Library
3. Load the Excel file using the Excel Python library
4. Insert a single row in the Excel file using the InsertRow method
5. Insert multiple rows in the Excel file using the InsertRows method
6. Save the updated Excel file using the Save method

Setting Up the Python Environment for Working with Excel Files

The first step in any Python project is to set up an appropriate environment that can support the project's requirements without affecting other Python projects. This isolation is achieved through virtual environments. Here’s why setting up a virtual environment is beneficial:

• Isolation: Avoids dependency conflicts between projects.
• Control: Gives you control over the Python and library versions your project uses.
• Replicability: Makes it easier to replicate your environment on other machines or with other team members.

To start, ensure Python is installed on your computer. If not, download it from python.org. Following installation, you can establish a virtual environment with these steps in Visual Studio Code:

Install the Python Extension: First, ensure you have the Python extension installed in Visual Studio Code. You can find it in the Visual Studio Code Marketplace and install it directly.

Select Python Interpreter: Once the extension is installed, you can select a Python interpreter by clicking on the Python version in the bottom-left corner of the status bar or by opening the command palette (Ctrl+Shift+P on Windows/Linux, Cmd+Shift+P on macOS) and typing “Python: Select Interpreter”. You can choose one of the existing interpreters or install a new one.

Create a Virtual Environment:

• Open the command palette and type “Python: Create Virtual Environment”.
• Enter a name for your virtual environment and select a location to store it.
• Once created, Visual Studio Code will automatically prompt you to select the new environment as your active Python interpreter.

Installing IronXL Library

IronXL for Python relies on .NET 6.0 as its underlying technology. Hence, please make sure you have the .NET 6.0 runtime installed on your machine.

IronXL is not included in the standard library and needs to be installed using pip, Python's package manager. Here’s how to install IronXL using the following pip command:

pip install IronXL

pip install IronXL

The pip install command fetches the IronXL package from the Python Package Index (PyPI) and installs it into your virtual environment. This process integrates IronXL seamlessly into your Python environment, making its classes and methods available for importing into your scripts.

Steps to Insert Rows

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

# Add a row before row 2 (index is zero-based)
worksheet.InsertRow(1)

# Save changes to the file
workbook.Save()

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

# Add a row before row 2 (index is zero-based)
worksheet.InsertRow(1)

# Save changes to the file
workbook.Save()

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

# Insert three rows after row 3 (index is zero-based)
worksheet.InsertRows(3, 3)

# Save changes to the file
workbook.Save()

from ironxl import *     

# Set your IronXL license key
License.LicenseKey = "License-Key"

# Load the Excel workbook
workbook = WorkBook.Load("Sample.xlsx")
worksheet = workbook.DefaultWorkSheet

# Insert three rows after row 3 (index is zero-based)
worksheet.InsertRows(3, 3)

# Save changes to the file
workbook.Save()

# Save the Excel file
workbook.Save("updated.xlsx")
# You can also save as other formats such as CSV, TSV, JSON, XML, HTML, etc.
# workbook.SaveAs("updated.xlsx");

# Save the Excel file
workbook.Save("updated.xlsx")
# You can also save as other formats such as CSV, TSV, JSON, XML, HTML, etc.
# workbook.SaveAs("updated.xlsx");

Handling Large Excel Files and Performance Considerations

When working with large Excel files, performance can become an issue. IronXL is designed to handle large files efficiently, but there are best practices you should follow to optimize performance, such as loading only the necessary sheets and minimizing read/write operations.

Conclusion

IronXL provides a comprehensive suite of tools for working with Excel files in Python, making it a valuable asset for any developer needing to read, write, or modify Excel documents programmatically. Its straightforward API, combined with the robust handling of large files and extensive formatting capabilities, makes IronXL a top choice for Python developers. To enhance your spreadsheet's functionality, learn to swiftly delete columns, insert rows, and clean up deleting rows while managing the column index for seamless integration of new data across multiple columns.

IronXL offers a free trial starting at $799, providing full support for enterprise needs and advanced functionalities.

IronXL provides thorough documentation and useful code examples to help you get started. To know more about IronXL for Python, please visit their website.