python 获取合并单元格value xlrd

xlrd 1.2.0 通吃xls、xlsx

import xlrd, arrow
from datetime import datetime
from datetime import timedelta


def convert_to_number(letter, columnA=0):
    """
    字母列号转数字
    columnA: 你希望A列是第几列(0 or 1)? 默认0
    return: int
    """
    ab = '_ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    letter0 = letter.upper()
    w = 0
    for _ in letter0:
        w *= 26
        w += ab.find(_)
    return w - 1 + columnA


def get_merged_cell_value(sheet, row_index,col_index):
    merged = sheet.merged_cells
    cell_value = None
    for (rlow, rhigh, clow, chigh) in merged:
        if (row_index >= rlow and row_index < rhigh):
            if (col_index >= clow and col_index < chigh):
                cell_value = sheet.cell_value(rlow, clow)
    return cell_value



xls_path = r"E:\code\dock_project\excel_image\excel_files\20220916\CABG_20220916.xlsx"

data = xlrd.open_workbook(xls_path)
table = data.sheet_by_name("By Brand對比")
nrows = table.nrows
ncols = table.ncols

print(nrows, ncols)

cell=table.cell(2,convert_to_number('B')).value

# print('cell:',cell)

for i in range(nrows):
    total_value = table.cell(i,convert_to_number('C')).value.strip()
    if total_value == 'Total':
        # print('total_value:', i, total_value)
        customer_name = cell_value = get_merged_cell_value(table, i, convert_to_number('B'))
        diff = table.cell(i,convert_to_number('F')).value
        diff = round(diff)
        # if customer_name:
        #     print('customer_name:',customer_name, diff)
        # # print('customer_name:',customer_name)


# this_month = (datetime.now() - timedelta(months=1)).strftime('%b')
last_month = arrow.now().shift(months=-1).format('MMM')
this_month = arrow.now().format('MMM')

print('last_month:',last_month)
print('this_month:',this_month)

for i in range(ncols):
    cell_value = table.cell(2,i).value
    # print('cell_value:',cell_value)
    if cell_value == last_month:
        last_month_value = table.cell(nrows-1,i).value
        last_month_value = round(last_month_value)
        print('last_month_value:',last_month_value)
    if cell_value == this_month:
        this_month_value = table.cell(nrows-1,i).value
        this_month_value = round(this_month_value)

        month_diff = table.cell(nrows-1,i + 2).value
        month_diff = round(month_diff)
        print('this_month_value:',this_month_value)
        print('month_diff:',month_diff)


# 简单输出一下
month_int = arrow.now().format('M')
month_int = int(month_int)
print('month_int:',month_int)
print("当前季度是:", int((month_int - 1) / 3) + 1, "当前月份是:", month_int)

def get_merged_cell_value(sheet, row_index,col_index):
    merged = sheet.merged_cells
    cell_value = None
    for (rlow, rhigh, clow, chigh) in merged:
        if (row_index >= rlow and row_index < rhigh):
            if (col_index >= clow and col_index < chigh):
                cell_value = sheet.cell_value(rlow, clow)
    return cell_value

参考
https://blog.csdn.net/Dragon_King_Boss/article/details/119991618