使用xlrd读取Excel并通过字典推导式构建复杂嵌套字典的咨询

Alright, let's tackle this nested dictionary build with xlrd and dict comprehensions—this is a perfect use case for nested comprehensions once you map out your Excel structure. Let's break it down step by step, assuming we're working with a standard Excel layout (I'll note where to adjust if your sheet structure is different).

You already have your workbook open with file = xlrd.open_workbook(filename), so we'll build from there. First, let's define a reasonable Excel structure that aligns with your target dict (adjust if yours differs):

• Each sheet has label names in the first row (row index 0)
• The data type for each label is in the second row (row index 1)
• Actual data starts from the third row (row index 2) onward, with each column corresponding to a label

Here's the full code using nested dict comprehensions to build your exact target structure:

import xlrd

filename = "your_data.xlsx"
file = xlrd.open_workbook(filename)

# Build the nested dictionary as requested
target_dict = {
    # Outer comprehension: map sheet names to their data dictionaries
    sheet.name: {
        # Inner comprehension: map each label to its [datatype, data] structure
        sheet.row_values(0)[col]: [
            [sheet.row_values(1)[col]],  # Wrap datatype in a sublist
            # Extract all rows starting from the 3rd row (index 2)
            [
                str(cell) if cell != "" else ""
                for row_idx in range(2, sheet.nrows)
                for cell in [sheet.row_values(row_idx)[col]]
            ]
        ]
        for col in range(sheet.ncols)
    }
    for sheet in file.sheets()
}

Let's unpack what's happening here:

1. Outer Dict Comprehension: {sheet.name: ... for sheet in file.sheets()} loops through every sheet in your workbook, using the sheet name as the top-level key.
2. Inner Dict Comprehension: {sheet.row_values(0)[col]: ... for col in range(sheet.ncols)} iterates over each column in the sheet. The label name comes from the first row of the column.
3. Label Value Structure:
   • The first element [sheet.row_values(1)[col]] grabs the data type from the second row and wraps it in a sublist (matching your [['datatype']] requirement).
   • The second element is a list comprehension that pulls every cell value from the column starting at row 3. We convert non-empty cells to strings (adjust this if you need to keep numeric/date types) and leave empty cells as "" to match your example.

Quick Adjustments for Different Excel Structures #

If your sheet layout doesn't match the assumption above, tweak the indices accordingly:

• If labels are in the first column instead of rows: Loop over rows instead of columns, and pull labels from sheet.row_values(row)[0]
• If data types are in a different row: Change sheet.row_values(1) to the correct row index (e.g., sheet.row_values(5) for the 6th row)

Heads-Up About xlrd Compatibility #

xlrd versions 2.0 and above no longer support .xlsx files. If you're working with newer Excel formats, either install an older version (pip install xlrd==1.2.0) or switch to openpyxl—the comprehension logic will be nearly identical, just using openpyxl's sheet methods.

内容的提问来源于stack exchange，提问作者El'tar