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  阅读(299)  评论(0)  编辑  收藏  举报