如何用pandas和xlrd为字典填充多Excel文件的工作表信息？

Hey there, let's get this sorted for you! You want to build a dictionary where each key is an Excel file in your current directory, and the value is a list of all sheets inside that file—even when you don't know how many sheets each file has. Here's a straightforward, efficient way to do it:

Step 1: Install the right library #

First, we'll use openpyxl (a reliable tool for working with Excel files) because it lets us read sheet names without loading the entire workbook (great for performance, especially with large files). Install it if you haven't already:

pip install openpyxl

Step 2: Full working code #

This script will automatically find all Excel files in your current directory, extract their sheet names, and populate your dictionary:

from glob import glob
from openpyxl import load_workbook

# Initialize your empty dictionary
dic_sheets = {}

# Grab all Excel files (.xlsx and .xls) in the current directory
excel_files = glob("*.xlsx") + glob("*.xls")
# Add *.xlsm to the list above if you need to handle macro-enabled files too

# Loop through each file to extract sheet names
for file_name in excel_files:
    try:
        # Open the workbook in read-only mode (faster, uses less memory)
        workbook = load_workbook(filename=file_name, read_only=True)
        # Get all sheet names as a list—no need to count sheets manually!
        sheet_list = workbook.sheetnames
        # Add to the dictionary: key = file name, value = list of sheets
        dic_sheets[file_name] = sheet_list
        # Clean up by closing the workbook
        workbook.close()
    except Exception as e:
        # Handle any issues (like corrupted files) without crashing the script
        print(f"Oops, couldn't process {file_name}: {str(e)}")

# Optional: Print the results to verify
for file, sheets in dic_sheets.items():
    print(f"File: {file}")
    print(f"Sheets: {sheets}\n")

How this works: #

• glob("*.xlsx") + glob("*.xls"): Scans your current directory and collects all Excel files matching those extensions.
• workbook.sheetnames: This built-in property returns a list of all sheet names in the workbook—regardless of how many sheets there are, so you never have to guess or use index ranges.
• Error handling: The try/except block ensures that if one file is corrupted or not a valid Excel file, the script will just log the issue and keep processing other files.

If you prefer using Python's modern pathlib module (great for cleaner file path handling), here's an alternative version:

from pathlib import Path
from openpyxl import load_workbook

dic_sheets = {}

# Get all Excel files using Pathlib
excel_files = list(Path(".").glob("*.xlsx")) + list(Path(".").glob("*.xls"))

for file_path in excel_files:
    file_name = str(file_path)
    try:
        workbook = load_workbook(filename=file_name, read_only=True)
        dic_sheets[file_name] = workbook.sheetnames
        workbook.close()
    except Exception as e:
        print(f"Error processing {file_name}: {e}")

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