如何读取.xls文件生成.py文件并转为Python字典？

Got it, let's break down how to build this Python app step by step. I'll walk you through the entire process with code examples that you can tweak to fit your exact needs.

Step 1: Install Required Libraries #

First, you'll need tools to handle the old .xls format and tabular data. We'll use pandas for easy data manipulation and xlrd (version 1.2.0, since newer versions drop support for .xls) to read the Excel file. Install them via pip:

pip install pandas xlrd==1.2.0

Step 2: Full Implementation Code #

Here's a complete script that reads all sheets in your .xls file, converts each row to a Python dictionary, and writes everything to a .py file that external tools can import directly:

import pandas as pd

def excel_to_py_config(excel_path, output_py_path):
    # Load the Excel file
    excel_file = pd.ExcelFile(excel_path)
    # Initialize a dictionary to hold config data per sheet
    config_data = {}

    # Iterate over every sheet in the workbook
    for sheet_name in excel_file.sheet_names:
        # Read the sheet into a DataFrame, using the first row as headers
        df = excel_file.parse(sheet_name)
        
        # Handle empty values (replace with None or your preferred default)
        df = df.fillna(None)
        
        # Convert each row to a dictionary, storing all rows as a list
        sheet_records = df.to_dict('records')
        # Map the sheet's name to its list of dictionaries
        config_data[sheet_name] = sheet_records

    # Generate the content for the .py file
    py_content = f"""# Auto-generated configuration file from {excel_path}
# You can import CONFIG_DATA in other Python scripts directly
CONFIG_DATA = {repr(config_data)}
"""
    # Write the content to the output .py file
    with open(output_py_path, 'w', encoding='utf-8') as f:
        f.write(py_content)

# Example usage
if __name__ == "__main__":
    # Replace with your input .xls path and desired output .py path
    excel_to_py_config("your_config.xls", "generated_config.py")

Step 3: Key Details & Customizations #

Let's go over some tweaks you might need based on your specific config file:

Custom Header Mapping #

If your Excel headers don't match the key names you want in your dictionaries, add a header rename step:

# Add this right after parsing the sheet
header_mapping = {
    "Original Header 1": "desired_key_1",
    "Original Header 2": "desired_key_2"
}
df = df.rename(columns=header_mapping)

Data Type Conversion #

If you need to convert Excel values to specific Python types (e.g., "Yes"/"No" to booleans, strings to integers), use apply():

# Example: Convert a "Status" column from "是"/"否" to booleans
def convert_status(value):
    if value == "是":
        return True
    elif value == "否":
        return False
    return value

df["status"] = df["status"].apply(convert_status)

Skip Empty Rows #

To remove rows where all columns are empty, add this after parsing the sheet:

df = df.dropna(how='all')

How External Tools Can Use the Generated .py File #

Other Python scripts can simply import the config data like this:

from generated_config import CONFIG_DATA

# Access data from a specific sheet
users_sheet = CONFIG_DATA["Users"]
# Access the first row's data
first_user = users_sheet[0]
print(first_user["username"])

内容的提问来源于stack exchange，提问作者Vijaysachin