Python+pandas+excel

import openpyxl
import pandas as pd
from openpyxl.styles import PatternFill
from openpyxl.styles.colors import WHITE


class ExcelUtil:

    def __init__(self):
        self.filepath = "..\data\data.xlsx"

    def ReadExcelList(self):
        excel = pd.read_excel(self.filepath)
        print("行列数", excel.shape)
        print("列数", excel.shape[1])
        print("行数", excel.shape[0])
        print("RequestMethod 列 第一行的数据", excel.loc[0, 'RequestMethod'])
        # print("获取指定行数的值", excel.sample(1).values)
        print("表头", excel.columns[0])
        # print("指定具体行列数据", excel.iloc[1, 1])
        productcode = excel.values

        apidataset = []
        for Line in range(excel.shape[0]):
            # 字典必须放在这里,要不然会重复被覆盖
            apidata = {}
            for Column in range(excel.shape[1]):
                apidata.update({excel.columns[Column]: excel.iloc[Line, Column]})
            # print(apidata)
            apidataset.append(apidata)
        print(apidataset)
        return apidataset

    # 根据Excel值获取对应的行号和列号(列号是表头列的列号)
    def GetExcelRowCol(self, num_value, excel_header):
        global row, col
        demo_df = pd.read_excel(self.filepath)
        for indexs in demo_df.index:
            for i in range(len(demo_df.loc[indexs].values)):
                if (str(demo_df.loc[indexs].values[i]) == num_value):
                    row = indexs + 2
                    print('行号', indexs + 2)
        list = demo_df.columns
        for indexs in range(len(list)):
            if (list[indexs] == excel_header):
                col = indexs + 1
                print('列号', indexs + 1)
        return row, col

    # 根据Excel单元格的值获得对应的行号和列号(不包括表头行)
    def GetExcelRowColExceptHeader(self, value):
        demo_df = pd.read_excel(self.filepath)
        for indexs in demo_df.index:
            for i in range(len(demo_df.loc[indexs].values)):
                if (demo_df.loc[indexs].values[i] == value):
                    row = indexs + 2
                    col = i + 1
                    print('行号', row, '列号', col)
                    return row, col

    def writeCell(self, rowNo, colsNo, content):
        '''
        :param rowNo: 行号
        :param colsNo: 列好
        :return: 传入行号和列好，对单元格回写返回的内容,且Fail单元格标黄
        '''
        book = openpyxl.load_workbook(self.filepath)
        if content == 'Fail':
            sheet = book['api']
            white = PatternFill(fill_type='solid', fgColor="FFC125")
            cell = sheet.cell(rowNo, colsNo)
            cell.fill = white
        else:
            sheet = book['api']
            white = PatternFill(fill_type='solid', fgColor=WHITE)
            cell = sheet.cell(rowNo, colsNo)
            cell.fill = white
        sheet = book['api']
        sheet.cell(rowNo, colsNo).value = content
        book.save(self.filepath)
        book.close()

    def statistical(self):
        excel = pd.read_excel(self.filepath)
        # 总得用例数
        Totalcount = excel.shape[0]
        # 通过的用例数
        Passcount = len(excel.loc[(excel['Practical'] == 'Pass')])
        # 失败的用例数据
        failcount = len(excel.loc[(excel['Practical'] == 'Fail')])
        Passrate = '{:.2%}'.format(Passcount / Totalcount)
        print(Passrate)
        return Totalcount, Passcount, failcount, Passrate


excel = ExcelUtil()
if __name__ == '__main__':
    pa = ExcelUtil()

    print(pa.statistical())