read_sheet.py

import xlrd
import openpyxl


# 读取指定xls文件的某一个sheet
def read_workbook_sheet_to_rows(filename: str, sheet_name: str):
    print('load workbook', filename)
    if filename.endswith('.xls'):
        return __xlrd_read_workbook_sheet_to_rows(filename, sheet_name)
    elif filename.endswith('.xlsx'):
        return __openpyxl_read_workbook_sheet_to_rows(filename, sheet_name)
    else:
        raise RuntimeError('file %s extension not recognized' % filename)


# 读取.xls
def __xlrd_read_workbook_sheet_to_rows(filename: str, sheet_name: str):
    book = xlrd.open_workbook(filename, on_demand=True)
    sheet = book.sheet_by_name(sheet_name)
    rows = []
    for rx in range(sheet.nrows):
        row = sheet.row(rx)
        new_row = []
        for cell in row:
            new_row.append(str(cell.value))
        rows.append(new_row)
    return rows

# 读取.xlsx
def __openpyxl_read_workbook_sheet_to_rows(filename: str, sheet_name: str):
    print('load workbook', filename)
    wb = openpyxl.load_workbook(filename, data_only=True, read_only=True)
    sheet = wb.get_sheet_by_name(sheet_name)
    rows = []
    assert sheet is not None, sheet_name
    for i, sheet_row in enumerate(sheet.rows):
        row = []
        for j, cell in enumerate(sheet_row):
            text = ''
            if cell.value is not None:
                text = str(cell.value)
            row.append(text.strip())
        rows.append(row)
    wb.close()
    return rows