python excel操作同时兼容xlsx和xls

可以使用openpyxl和xlrd两个库分别处理.xlsx和.xls文件。

首先需要安装这两个库：

pip install openpyxl
pip install xlrd

然后可以按照以下示例代码来操作Excel文件：

import os
import openpyxl
import xlrd

def read_excel(file_path):
    # 判断文件类型
    file_type = os.path.splitext(file_path)[1]
    if file_type == '.xlsx':
        workbook = openpyxl.load_workbook(file_path)
        sheet = workbook.active
        data = []
        for row in sheet.rows:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
            data.append(row_data)
        return data
    elif file_type == '.xls':
        workbook = xlrd.open_workbook(file_path)
        sheet = workbook.sheet_by_index(0)
        data = []
        for row_index in range(sheet.nrows):
            row_data = []
            for col_index in range(sheet.ncols):
                row_data.append(sheet.cell_value(row_index, col_index))
            data.append(row_data)
        return data
    else:
        raise ValueError('Unsupported file type: {}'.format(file_type))

def write_excel(file_path, data):
    # 判断文件类型
    file_type = os.path.splitext(file_path)[1]
    if file_type == '.xlsx':
        workbook = openpyxl.Workbook()
        sheet = workbook.active
        for row_data in data:
            sheet.append(row_data)
        workbook.save(file_path)
    elif file_type == '.xls':
        workbook = xlwt.Workbook(encoding='utf-8')
        sheet = workbook.add_sheet('Sheet1')
        for row_index, row_data in enumerate(data):
            for col_index, cell_value in enumerate(row_data):
                sheet.write(row_index, col_index, cell_value)
        workbook.save(file_path)
    else:
        raise ValueError('Unsupported file type: {}'.format(file_type))

使用示例：

# 读取Excel文件
data = read_excel('example.xlsx')
print(data)

# 写入Excel文件
data = [['姓名', '年龄', '性别'], ['张三', 20, '男'], ['李四', 25, '女']]
write_excel('example.xlsx', data)

注意，openpyxl只支持.xlsx文件，而xlrd只支持.xls文件，因此要根据文件类型选择合适的库进行操作。