python操作excel，支持xls,xlsx

# coding=utf-8
import logging
import xlrd  # 读模块
import xlwt  # 写模块

logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)


def read_excel(excel_path, sheet_index=None, sheet_name=None, has_table_head=True):
    """
        读取excel文件方法说明
    :return:
    """
    global sheet_v
    data = xlrd.open_workbook(excel_path)
    logger.info(str(data.sheet_names()))
    if sheet_index is not None:
        sheet_v = data.sheet_by_index(sheet_index)  # 索引法
    elif sheet_name:
        sheet_v = data.sheet_by_name(sheet_name)  # 表名法
    else:
        sheet_v = data.sheet_by_index(0)
    logger.info("row number: " + str(sheet_v.nrows))
    logger.info("row number: " + str(sheet_v.ncols))
    cell_list = []
    if has_table_head:
        table_head_list = sheet_v.row(0)
        for r in range(1, sheet_v.nrows):
            row_dict = {}
            for c in range(0, sheet_v.ncols):
                row_dict[table_head_list[c].value] = sheet_v.cell(r, c).value
            cell_list.append(row_dict)
    else:
        for i in range(0, sheet_v.nrows):
            row_list = []
            for j in range(0, sheet_v.ncols):
                row_list.append(sheet_v.cell(i, j).value)
            cell_list.append(row_list)
    return cell_list


def write_excel(file_name, sheet_name, data, table_head=None):
    """
    :param file_name: excel文件名。例：'test.xls'
    :param sheet_name: excel工作表名。例：Sheet1
    :param data: 二维数列，一维为列表，二维为字典，将要写入表内的数据:行*列
    :param table_head: 表头列表。例：[id, name, sex]
    :return:
    """
    file_v = xlwt.Workbook()
    sheet1 = file_v.add_sheet(sheet_name, cell_overwrite_ok=True)
    begin_row = 0
    end_row = len(data)
    if table_head:
        begin_row = 1
        end_row += 1
        for i in range(0, len(table_head)):
            sheet1.write(0, i, table_head[i])

    for row in range(begin_row, end_row):
        for col in range(0, len(table_head)):
            sheet1.write(row, col, str(data[row - begin_row].generate_api_testcase(table_head[col])))
    file_v.save(file_name)
    logger.info('已生成excel文件！')

if __name__ == '__main__':
    write_excel(
        sheet_name='friendSheet',
        data=[[1, '小丽', 'girl'], [2, '小明', 'boy'], [3, '小王', 'boy']],
        file_name='friend.xls'
    )