如何用Python的xlrd库获取XLSX文件中指定合并单元格的值？

Hey there! Let's work through this issue to get that merged cell value showing up like you expect.

First, the "no output" quick fix #

Your current code calculates the cell values but doesn’t print them! You need to wrap those cell_value calls in print() statements to see results. But even with that, there’s a quirk with merged cells in xlrd we need to address.

Why merged cells act this way in xlrd #

xlrd only stores the value of a merged cell in the top-left cell of the merged region. All other cells in the merged area will return empty/blank values. So if B11 and C11 are merged, only the cell at row 10 (0-indexed), column 1 (B column) holds PCHGFT001KS—the cell at (10,2) (C column) will be empty by default.

Solution: Detect merged regions and fetch the correct value #

We can write a simple helper function to check if a target cell is part of a merged region, and pull the value from the top-left cell if it is. Here’s the updated code:

import xlrd

def get_merged_cell_value(sheet, row, col):
    # Loop through all merged cell regions in the sheet
    for (r_start, r_end, c_start, c_end) in sheet.merged_cells:
        # Check if the target cell is inside this merged region (xlrd uses left-open intervals)
        if r_start <= row < r_end and c_start <= col < c_end:
            # Return the value from the top-left cell of the merged region
            return sheet.cell_value(r_start, c_start)
    # If not part of a merged cell, return the cell's own value
    return sheet.cell_value(row, col)

path = "C:/myfilepath/data.xlsx"
workbook = xlrd.open_workbook(path)
sheet = workbook.sheet_by_index(0)

# Get and print values for both cells
print(get_merged_cell_value(sheet, 10, 1))  # B11
print(get_merged_cell_value(sheet, 10, 2))  # C11

How this works: #

• sheet.merged_cells returns a list of tuples representing merged regions, formatted as (row_start, row_end, col_start, col_end). These are left-closed, right-open intervals—so (10,11,1,3) means row 10 only, columns 1 and 2 (B11 to C11).
• The helper function checks if your target cell falls within any merged region. If yes, it grabs the value from the top-left corner (the only place the value is stored). If not, it returns the cell’s normal value.

Running this code will output PCHGFT001KS for both cells, matching your expected result.

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