如何将含字典元素的List数据按指定格式写入Excel？求代码

If you're working with Python, using the pandas library is the most efficient way to handle this task—even for hundreds of records. It simplifies converting your list of dictionaries into tabular data and writing it directly to Excel.

Step-by-Step Implementation #

1. Install Required Libraries #

First, make sure you have pandas and openpyxl (needed for Excel file writing) installed. Run this command in your terminal:

pip install pandas openpyxl

2. Full Working Code #

import pandas as pd

# Replace this sample list with your actual 100+ records
your_data = [
    {"Row ID": 7565.0, "Product ID": "test11223", "Postal Code": 98103.0},
    {"Row ID": 7567.0, "Product ID": "test11213", "Postal Code": 98101.0},
    # Add all your remaining records here
]

# Convert the list of dictionaries to a DataFrame
df = pd.DataFrame(your_data)

# Rename columns to match your required headers
df = df.rename(columns={
    "Row ID": "Row Id",
    "Product ID": "Order ID"
})

# Clean numeric columns (convert float values to integers)
df["Row Id"] = df["Row Id"].astype(int)
df["Postal Code"] = df["Postal Code"].astype(int)

# Write the DataFrame to an Excel file
output_filename = "formatted_data.xlsx"
df.to_excel(output_filename, index=False, engine="openpyxl")

print(f"Success! Data saved to {output_filename}")

Key Details #

• Data Conversion: Pandas automatically maps each dictionary in your list to a row in the Excel sheet.
• Column Renaming: We directly map the original keys from your list to the exact column headers you specified.
• Data Cleaning: Since your sample data has float values for Row ID and Postal Code, we convert them to integers to match your desired output format.
• Excel Output: The index=False parameter ensures we don't write pandas' default index column to the Excel file, keeping your output clean.

Quick Notes #

• If your dataset has missing values (e.g., some entries lack a Postal Code), you can handle them with pandas functions like df.fillna(0) (to fill blanks with 0) or df.dropna() (to remove incomplete rows).
• This code works seamlessly for thousands of records—pandas is optimized for large tabular datasets.

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