Python读取excel文件

  1. 安装xlrd:pip install xlrd
  2. 按要求组装数据:列表形式、字典形式、类的形式
  3. 读取excel代码实现如下
     1 import os
     2 import xlrd
     3 from day08_0325.read_excel.case_info import CaseInfo
     4 
     5 # 列表的方式实现 [ [用例编号,用例名称],[],[] ]   外部一个列表元素一个用例  内部列表就是用例详情
     6 def read_excel_data_convert_case_info(excel_path):
     7     workbook=xlrd.open_workbook(excel_path)#打开excel工作簿
     8     sheet=workbook.sheet_by_index(0)#选择工作表
     9     case_infos=[]
    10     rows=sheet.nrows#确定行数
    11     cols=sheet.ncols#确定列数
    12     for i in range(1,rows):
    13         case_info=[]
    14         for j in range(0,cols):
    15             case_info.append(sheet.cell_value(i,j))
    16         case_infos.append(case_info)
    17     return case_infos
    18 
    19 
    20 # 类的方式实现  用来现实中的事物 描述用例  [CaseInfo(),CaseInfo(),CaseInfo()...]
    21 def read_excel_data_convert_case_info_01(excel_path):
    22     workbook=xlrd.open_workbook(excel_path)
    23     sheet=workbook.sheet_by_index(0)
    24     case_infos_01=[]
    25     for i in range(1,sheet.nrows):
    26         case_id = sheet.cell_value(i,0)
    27         case_name = sheet.cell_value(i,1)
    28         module = sheet.cell_value(i,2)
    29         pri = sheet.cell_value(i,3)
    30         case_step = sheet.cell_value(i,4)
    31         except_result = sheet.cell_value(i,5)
    32         case_info=CaseInfo(case_id,case_name,module,pri,case_step,except_result)
    33         case_infos_01.append(case_info)
    34     return case_infos_01
    35 
    36 
    37 # 字典
    38 # 方式一
    39 # { 'case01':{'case_name': ,'module': ,},'case02':{} }
    40 def read_excel_data_convert_case_info_02(excel_path):
    41     workbook=xlrd.open_workbook(excel_path)
    42     sheet=workbook.sheet_by_index(0)
    43     dict_info = {}
    44     for i in range(1, sheet.nrows):
    45         dict_info[sheet.cell_value(i, 0)] = {'case_name': sheet.cell_value(i, 1),
    46                                              'module': sheet.cell_value(i, 2),
    47                                              'pri': sheet.cell_value(i, 3),
    48                                              'case_step': sheet.cell_value(i, 4),
    49                                              'except_result': sheet.cell_value(i, 5)}
    50     return dict_info
    51 
    52 # 方式二:
    53 # [ {'case_no': ,'case_name': ,} , {'case_no': ,'case_name': ,} ]
    54 def read_excel_data_convert_case_info_03(self):
    55     workbook = xlrd.open_workbook(self.excel_path)
    56     sheet = workbook.sheet_by_index(0)
    57     case_infos_03 = []
    58     for i in range(1, sheet.nrows):
    59         case_info = {}
    60         case_info['case_id'] = sheet.cell_value(i, 0)
    61         case_info['case_name'] = sheet.cell_value(i, 1)
    62         case_info['module'] = sheet.cell_value(i, 2)
    63         case_info['pri'] = sheet.cell_value(i, 3)
    64         case_info['case_step'] = sheet.cell_value(i, 4)
    65         case_info['except_result'] = sheet.cell_value(i, 5)
    66         case_infos_03.append(case_info)
    67     return case_infos_03
    68 
    69 
    70 if __name__=='__main__':
    71     current_path=os.path.dirname(__file__)
    72     excel_path=os.path.join(current_path,'../test_case/case_data.xlsx')
    73     print(read_excel_data_convert_case_info_02(excel_path)['case_01'])

    上面的代码中类CaseInfo代码如下:

    1 class CaseInfo:
    2     def __init__(self, case_id, case_name, module, pri, case_step, except_result):
    3         self.case_id = case_id
    4         self.case_name = case_name
    5         self.module = module
    6         self.pri = pri
    7         self.case_step = case_step
    8         self.except_result = except_result

     

  4. 运行结果:
posted @ 2020-12-13 21:45  重走青春LV  阅读(302)  评论(0编辑  收藏  举报