如何在 Python 中编辑 Excel 文件

Open or Create New Project

Create a New Project or open an existing one in your IDE as per requirement. We are creating a new Project in MS Visual Studio 2022. You can use any IDE. The code will remain the same for each IDE.

Load Excel Workbook

The very first step is to load an Excel file workbook into our project. We will load the following sample Excel File. We will use the same Excel for all examples discussed in this tutorial.

The following code will load the Excel file into our project.

from ironxl import *     

# Set the IronXL license key
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook
workbook = WorkBook.Load("test_excel.xlsx")

from ironxl import *     

# Set the IronXL license key
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook
workbook = WorkBook.Load("test_excel.xlsx")

The above code snippet demonstrates how to use the IronXL for Python library to load an Excel file named "test_excel.xlsx" into a workbook object. First, it sets the IronXL license key for authentication. Then, it imports the required modules from IronXL. Finally, it loads the Excel file using the WorkBook.Load() method, allowing developers to access and manipulate the data within the workbook using IronXL's functionality.

Select Excel Spreadsheet

The next step is to select Excel Spreadsheets in Python. There could be multiple Excel sheets in one file, so we need to specify the active sheet.

# Select the worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Select the worksheet at index 0
worksheet = workbook.WorkSheets[0]

The above line of code selects the worksheet located at index 0 within the workbook object, enabling developers to manipulate Excel spreadsheets effectively. By specifying the index, it allows access to a specific Excel sheet within the Excel file for further analysis or modification.

Write Excel files using Python

Now, let's write data to our Excel file by adding a new row to our dataset. The following code will accomplish that:

# Python edit Excel file: insert data into specific cells
worksheet["A11"].IntValue = 10
worksheet["B11"].Value = 'Kate'
worksheet["C11"].Value = 'Brown'
worksheet["D11"].Value = 'Female'
worksheet["E11"].Value = 'Indonesia'
worksheet["F11"].IntValue = 26
worksheet["G11"].Value = '16/10/2024'
worksheet["H11"].IntValue = 6548

# Save the workbook with modifications
workbook.Save()

# Python edit Excel file: insert data into specific cells
worksheet["A11"].IntValue = 10
worksheet["B11"].Value = 'Kate'
worksheet["C11"].Value = 'Brown'
worksheet["D11"].Value = 'Female'
worksheet["E11"].Value = 'Indonesia'
worksheet["F11"].IntValue = 26
worksheet["G11"].Value = '16/10/2024'
worksheet["H11"].IntValue = 6548

# Save the workbook with modifications
workbook.Save()

The above code modifies existing Excel files by inserting data into specific cells. It assigns cell values to cells in columns A to H for row 11, appending data in the existing spreadsheet. After making these changes, the workbook is saved, ensuring that the modifications are persisted. This functionality enables automated editing of Excel files, facilitating tasks such as data entry, updates, or appending new records.

Using Formula in Excel file

In this next step, we'll use IronXL to compute the average age of customers directly within the Excel file. By harnessing the power of formulas, we can efficiently calculate aggregate statistics, enhancing data analysis capabilities. We can streamline the process of performing complex calculations, empowering users to derive valuable insights from their Excel data effortlessly. Let's dive into computing the average age of customers in our Excel file.

# Apply average calculation to all numeric cells within the range
avgAge = worksheet["F2:F11"].Avg()

# Display the average age
print(f"Average Age is: {avgAge}")

# Apply average calculation to all numeric cells within the range
avgAge = worksheet["F2:F11"].Avg()

# Display the average age
print(f"Average Age is: {avgAge}")

The above code calculates the average value of numeric cells within the specified range "F2:F11" in the Excel worksheet. IronXL's capabilities enable seamless computation of statistical metrics directly within Excel files, enhancing data analysis workflows. By simply invoking the Avg() method on the range, IronXL efficiently computes the average age of customers, demonstrating its ability to streamline complex calculations within Excel documents.