單元格拆分

def get_index(capital):
    """
    大寫字母（Excel列頭）轉數字
    :param capital: 'A' --> 0, 'AA' --> 26
    :return: int
    """
    number = 0
    capital = capital.upper()
    for char in capital:
        number = number * 26 + ord(char) - ord('A') + 1
    return number - 1

需注意xlrd的版本，只支持xls（version > 2.x），既支持xls又支持xlsx（version < 2.x）

讀取整列(xlrd)

import xlrd


def read_col(io, sheet, cell='A1'):
    """
    讀取列
    :param io: Excel文件路徑
    :param sheet: 讀取哪一張表，str, int   eg: 'Sheet1' or 0
    :param cell: 從哪一個單元格開始讀取
    :return: value --> list
    """
    wb = xlrd.open_workbook(io)
    if isinstance(sheet, str):
        ws = wb.sheet_by_name(sheet)
    elif isinstance(sheet, int):
        ws = wb.sheet_by_index(sheet)
    else:
        raise TypeError('sheet must be int or str, not %s' % type(sheet))
    pos_col, pos_row = re.findall('[A-Z]+|[0-9]+', cell)
    col_index = get_index(pos_col)
    value = ws.col_values(col_index, start_rowx=int(pos_row) - 1)
    if value:
        return value
    return ''

讀取整列(xlwings)

import xlwings as xw


def read_col(io, sheet=0, cell='A1') -> list:
    """
    讀取Excel表格的某一列
    :param io: 文件
    :param sheet: 工作表
    :param cell: 單元格，如：A1
    :return: list[str]
    """
    assert isinstance(sheet, (str, int)), "sheet 必須為str或int類型"
    app = xw.App(visible=False)  # 后臺運行
    workbook = xw.Book(io)  # 打開Excel文件
    # workbook = app.books.open(io)  # WPS use
    # 讀取Excel表格的某一列
    if isinstance(sheet, str):
        data = workbook.sheets(sheet).range(cell).expand().value
    else:
        data = workbook.sheets[sheet].range(cell).expand().value
    # 關閉Excel文件
    workbook.close()
    app.quit()
    return data or []

讀取整行

def read_row(io, sheet, cell='A1'):
    """
    讀取行
    :param io: Excel文件路徑
    :param sheet: 讀取哪一張表，str, int   eg: 'Sheet1' or 0
    :param cell: 從哪一個單元格開始讀取
    :return: value --> list
    """
    wb = xlrd.open_workbook(io)
    if isinstance(sheet, str):
        ws = wb.sheet_by_name(sheet)
    elif isinstance(sheet, int):
        ws = wb.sheet_by_index(sheet)
    else:
        raise TypeError('sheet must be int or str, not %s' % type(sheet))
    pos_col, pos_row = re.findall('[A-Z]+|[0-9]+', cell)
    col_index = get_index(pos_col)
    value = ws.row_values(col_index, start_colx=int(pos_row) - 1)
    if value:
        return value
    return ''