python接口自动化之读取&写入execl文件

整体思想

通过python xlrd和xlwt模块操作execl表格
源码如下:

# ProjectName:apiAutoTest1 
# FileName:operation_execl 
# Author:wangchao
# CreateTime:2021/5/28 19:12
# Description: 封装类操作execl
import xlrd
import xlwt
from xlutils.copy import copy


class operationExecl:
    # 初始化方法
    # def __init__(self, path):  # 初始化赋值
    # self.path = path
    # get方法
    def get_path(self):
        return self.path

    # set方法
    def set_path(self, value):
        if not isinstance(value, str):
            raise ValueError("路径应该是字符串类型")
        self.path = value

    def read_xls_execl(self):
        #打开当前目录下的文件
        workbook = xlrd.open_workbook_xls(self.path)
        # workbook = xlrd.open_workbook(path)
        # 读取xlx表格
        sheet_names = workbook.sheet_names()
        print(sheet_names)
        sheet = workbook.sheet_by_index(0)
        # 打印每一行值
        print("sheet名字：", sheet.name, "sheet多少行:", sheet.nrows, "sheet多少列:", sheet.ncols)
        for i in range(0, sheet.nrows):
            print("第", i + 1, "行数据:", sheet.row_values(i))
        # 打印每一列的值
        for i in range(0, sheet.ncols):
            print("第", i + 1, "列数据:", sheet.col_values(i))
        # 每一个单元格的值
        for i in range(0, sheet.nrows):
            for j in range(0, sheet.ncols):
                print(sheet.cell_value(i, j), end=" ")
            print()

    def write_xls_execl(self):
        # 打开已有的一个文件
        old_execl = xlrd.open_workbook_xls(self.path)
        # copy
        new_execl = copy(old_execl)
        # 获取第二个sheet
        my_sheet = new_execl.get_sheet(1)

        # my_sheet = my_work_book.add_sheet('test')
        myStyle = xlwt.easyxf('font: name Times New Roman, color-index red, bold on', num_format_str='#,##0.00')  # 数据格式
        my_sheet.write(0, 0, '标题1', myStyle)

        my_sheet.write(2, 0, 1)
        my_sheet.write(2, 1, 2)
        new_execl.save(self.path)


if __name__ == "__main__":
    print("fd")
    opExecl = operationExecl()
    opExecl.set_path('testCases.xls')
    #纯读取execl
    opExecl.read_xls_execl()
    #读取已经存在的execl并且更新此文件
    opExecl.write_xls_execl()