如何在 Python 中读取 Excel 文件

1. Creating a File in Visual Studio Code

Open Visual Studio Code and create a new Python file named ReadExcel.py. This file will contain our script to read Excel files using IronXL.

2. Installing IronXL

Open the command line within Visual Studio Code by selecting Terminal > New Terminal from the menu.

Install IronXL by running the following command:

pip install ironxl

pip install ironxl

Importing Libraries

from ironxl import *
import sys

from ironxl import *
import sys

Here, we import everything from the ironxl library, which provides the functionality to work with Excel files. sys is used to set the path where Python is installed, which is necessary for IronXL to function correctly.

Setting Python Installation Path

sys.prefix = r'C:\Users\User Name\AppData\Local\Programs\Python\Python312'

sys.prefix = r'C:\Users\User Name\AppData\Local\Programs\Python\Python312'

sys.prefix is assigned the path to your Python installation directory. This preparatory step might not be necessary in every environment but is essential in some configurations to prevent path-related issues.

Setting License Key

License.LicenseKey = "License-Key"

License.LicenseKey = "License-Key"

The IronXL library requires a valid license key to unlock all its features. This line of code License.LicenseKey = "License-Key" is where you would insert your obtained license key. Without a valid license, IronXL will not work.

Loading the Workbook

workbook = WorkBook.Load("data.xlsx")

workbook = WorkBook.Load("data.xlsx")

This section of the script, workbook = WorkBook.Load("data.xlsx"), demonstrates how to load an Excel workbook. WorkBook.Load is a method provided by IronXL to open an existing Excel file, specified by its file path.

The example uses "data.xlsx", assuming this file is located in the same directory as the script. If your file is elsewhere, you'll need to provide the complete file path.

Selecting a Worksheet

worksheet = workbook.WorkSheets[0]

worksheet = workbook.WorkSheets[0]

After loading the workbook, the next step is to select a worksheet. The workbook.WorkSheets[0] line accesses the first worksheet in the Excel file. Worksheets are indexed starting at 0, so this code snippet effectively selects the first sheet.

Reading and Displaying Data

print("Sample data from A2:A5:")
for cell in worksheet["A2:A5"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

print("Sample data from A2:A5:")
for cell in worksheet["A2:A5"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

This part of the script exemplifies how to read and display data from specific cells. By iterating over a cell range for the cells in the worksheet["A2:A5"], we access each cell's address and text value. You can retrieve information from predefined areas within a sheet.

Performing Calculations

IronXL provides functions to perform simple calculations directly on data ranges within an Excel sheet. The following sections of the code demonstrate how to perform simple calculations like sum, minimum, and maximum on data within a specified range.

total_sum = worksheet["B2:B10"].Sum()
print("Total Sum of B2:B10: ", total_sum)

minimum_value = worksheet["C2:C10"].Min()
print("Minimum Value in C2:C10: ", minimum_value)

maximum_value = worksheet["D2:D10"].Max()
print("Maximum Value in D2:D10: ", maximum_value)

total_sum = worksheet["B2:B10"].Sum()
print("Total Sum of B2:B10: ", total_sum)

minimum_value = worksheet["C2:C10"].Min()
print("Minimum Value in C2:C10: ", minimum_value)

maximum_value = worksheet["D2:D10"].Max()
print("Maximum Value in D2:D10: ", maximum_value)

Finding Unique Values

This part of the script demonstrates how to identify unique values within a specified range of an Excel sheet:

unique_values = set(cell.Text for cell in worksheet["F2:F10"])
print("Unique Values in F2:F10: ", unique_values)

unique_values = set(cell.Text for cell in worksheet["F2:F10"])
print("Unique Values in F2:F10: ", unique_values)

To achieve this, a set comprehension is utilized. Sets in Python are collections that automatically eliminate duplicate entries, making them perfect for finding unique values. The comprehension iterates over each cell in the range F2:F10, accessing the text content of each cell using cell.Text.

By placing this iteration inside a set, we ensure that only unique values are kept.

Counting Occurrences of a Specific Value

Finally, this snippet counts how many times a specific value occurs in a given range.

specific_value = '5'  # Adjust this as needed based on your data
occurrences = sum(cell.Text == specific_value for cell in worksheet["G2:G10"])
print(f"Occurrences of value '{specific_value}' in G2:G10: ", occurrences)

specific_value = '5'  # Adjust this as needed based on your data
occurrences = sum(cell.Text == specific_value for cell in worksheet["G2:G10"])
print(f"Occurrences of value '{specific_value}' in G2:G10: ", occurrences)

The goal here is to quantify the occurrence of a specific value, labelled here as specific_value. The code employs a generator expression that iterates over each cell in the range G2:G10, comparing the cell's text content to specific_value.

The expression cell.Text == specific_value evaluates to True for each cell containing the target value and False otherwise. The sum function adds up these Boolean values, treating True as 1 and False as 0, effectively counting the number of times the specific value occurs.

Full Code Example

Here is the complete code for a better understanding of the code:

from ironxl import *
import sys

# Setting Python installation path
sys.prefix = r'C:\Users\User Name\AppData\Local\Programs\Python\Python312'

# Setting the license key for IronXL
License.LicenseKey = "License-Key"

# Load the workbook
workbook = WorkBook.Load("data.xlsx")

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

# Display values in a specific range as an example
print("Sample data from A2:A5:")
for cell in worksheet["A2:A5"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

# Calculate the sum of values in a different range
total_sum = worksheet["B2:B10"].Sum()
print("Total Sum of B2:B10: ", total_sum)

# Calculate the minimum value in another range
minimum_value = worksheet["C2:C10"].Min()
print("Minimum Value in C2:C10: ", minimum_value)

# Calculate the maximum value in a different range
maximum_value = worksheet["D2:D10"].Max()
print("Maximum Value in D2:D10: ", maximum_value)

# Find unique values in a specified range
unique_values = set(cell.Text for cell in worksheet["F2:F10"])
print("Unique Values in F2:F10: ", unique_values)

# Count occurrences of a specific value in a different range
specific_value = '5'  # Adjust this as needed based on your data
occurrences = sum(cell.Text == specific_value for cell in worksheet["G2:G10"])
print(f"Occurrences of value '{specific_value}' in G2:G10: ", occurrences)

from ironxl import *
import sys

# Setting Python installation path
sys.prefix = r'C:\Users\User Name\AppData\Local\Programs\Python\Python312'

# Setting the license key for IronXL
License.LicenseKey = "License-Key"

# Load the workbook
workbook = WorkBook.Load("data.xlsx")

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

# Display values in a specific range as an example
print("Sample data from A2:A5:")
for cell in worksheet["A2:A5"]:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

# Calculate the sum of values in a different range
total_sum = worksheet["B2:B10"].Sum()
print("Total Sum of B2:B10: ", total_sum)

# Calculate the minimum value in another range
minimum_value = worksheet["C2:C10"].Min()
print("Minimum Value in C2:C10: ", minimum_value)

# Calculate the maximum value in a different range
maximum_value = worksheet["D2:D10"].Max()
print("Maximum Value in D2:D10: ", maximum_value)

# Find unique values in a specified range
unique_values = set(cell.Text for cell in worksheet["F2:F10"])
print("Unique Values in F2:F10: ", unique_values)

# Count occurrences of a specific value in a different range
specific_value = '5'  # Adjust this as needed based on your data
occurrences = sum(cell.Text == specific_value for cell in worksheet["G2:G10"])
print(f"Occurrences of value '{specific_value}' in G2:G10: ", occurrences)

Output

After running the ReadExcel.py Python file, you'll see the following output displayed in your console, which reflects the results of the operations carried out by the script on the Excel data.