自动化测试如何解析excel文件?

发表于:2019-5-14 10:30  作者:linux超   来源:博客园

字体: | 上一篇 | 下一篇 |我要投稿 | 推荐标签: 软件测试技术 自动化测试

  前言
  自动化测试中我们存放数据无非是使用文件或者数据库,那么文件可以是csv,xlsx,xml,甚至是txt文件,通常excel文件往往是我们的首选,无论是编写测试用例还是存放测试数据,excel都是很方便的。那么今天我们就把不同模块处理excel文件的方法做个总结,直接做封装,方便我们以后直接使用,增加工作效率。
  openpyxl
  openpyxl是个第三方库,首先我们使用命令 pip install openpyxl 直接安装
  注:openpyxl操作excel时,行号和列号都是从1开始计算的
  封装代码
   """
  ------------------------------------
  @Time : 2019/5/13 18:00
  @Auth : linux超
  @File : ParseExcel.py
  @IDE  : PyCharm
  @Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
  ------------------------------------
  """
  from openpyxl import load_workbook
  from openpyxl.styles import Font
  from openpyxl.styles.colors import BLACK
  from collections import namedtuple
  class ParseExcel(object):
  """解析excel文件"""
  def __init__(self, filename, sheet_name=None):
  try:
  self.filename = filename
  self.sheet_name = sheet_name
  self.wb = load_workbook(self.filename)
  if self.sheet_name is None:
  self.work_sheet = self.wb.active
  else:
  self.work_sheet = self.wb[self.sheet_name]
  except FileNotFoundError as e:
  raise e
  def get_max_row_num(self):
  """获取最大行号"""
  max_row_num = self.work_sheet.max_row
  return max_row_num
  def get_max_column_num(self):
  """获取最大列号"""
  max_column = self.work_sheet.max_column
  return max_column
  def get_cell_value(self, coordinate=None, row=None, column=None):
  """获取指定单元格的数据"""
  if coordinate is not None:
  try:
  return self.work_sheet[coordinate].value
  except Exception as e:
  raise e
  elif coordinate is None and row is not None and column is not None:
  if isinstance(row, int) and isinstance(column, int):
  return self.work_sheet.cell(row=row, column=column).value
  else:
  raise TypeError('row and column must be type int')
  else:
  raise Exception("Insufficient Coordinate of cell!")
  def get_row_value(self, row):
  """获取某一行的数据"""
  column_num = self.get_max_column_num()
  row_value = []
  if isinstance(row, int):
  for column in range(1, column_num + 1):
  values_row = self.work_sheet.cell(row, column).value
  row_value.append(values_row)
  return row_value
  else:
  raise TypeError('row must be type int')
  def get_column_value(self, column):
  """获取某一列数据"""
  row_num = self.get_max_column_num()
  column_value = []
  if isinstance(column, int):
  for row in range(1, row_num + 1):
  values_column = self.work_sheet.cell(row, column).value
  column_value.append(values_column)
  return column_value
  else:
  raise TypeError('column must be type int')
  def get_all_value_1(self):
  """获取指定表单的所有数据(除去表头)"""
  max_row_num = self.get_max_row_num()
  max_column = self.get_max_column_num()
  values = []
  for row in range(2, max_row_num + 1):
  value_list = []
  for column in range(1, max_column + 1):
  value = self.work_sheet.cell(row, column).value
  value_list.append(value)
  values.append(value_list)
  return values
  def get_all_value_2(self):
  """获取指定表单的所有数据(除去表头)"""
  rows_obj = self.work_sheet.iter_rows(min_row=2, max_row=self.work_sheet.max_row,
  values_only=True)  # 指定values_only 会直接提取数据不需要再使用cell().value
  values = []
  for row_tuple in rows_obj:
  value_list = []
  for value in row_tuple:
  value_list.append(value)
  values.append(value_list)
  return values
  def get_excel_title(self):
  """获取sheet表头"""
  title_key = tuple(self.work_sheet.iter_rows(max_row=1, values_only=True))[0]
  return title_key
  def get_listdict_all_value(self):
  """获取所有数据,返回嵌套字典的列表"""
  sheet_title = self.get_excel_title()
  all_values = self.get_all_value_2()
  value_list = []
  for value in all_values:
  value_list.append(dict(zip(sheet_title, value)))
  return value_list
  def get_list_nametuple_all_value(self):
  """获取所有数据,返回嵌套命名元组的列表"""
  sheet_title = self.get_excel_title()
  values = self.get_all_value_2()
  excel = namedtuple('excel', sheet_title)
  value_list = []
  for value in values:
  e = excel(*value)
  value_list.append(e)
  return value_list
  def write_cell(self, row, column, value=None, bold=True, color=BLACK):
  """
  指定单元格写入数据
  :param work_sheet:
  :param row: 行号
  :param column: 列号
  :param value: 待写入数据
  :param bold: 加粗, 默认加粗
  :param color: 字体颜色,默认黑色
  :return:
  """
  try:
  if isinstance(row, int) and isinstance(column, int):
  cell_obj = self.work_sheet.cell(row, column)
  cell_obj.font = Font(color=color, bold=bold)
  cell_obj.value = value
  self.wb.save(self.filename)
  else:
  raise TypeError('row and column must be type int')
  except Exception as e:
  raise e
  if __name__ == '__main__':
  pe = ParseExcel('testdata.xlsx')
  # sheet = pe.get_sheet_object('testcase')
  column_row = pe.get_max_column_num()
  print('最大列号:', column_row)
  max_row = pe.get_max_row_num()
  print('最大行号:', max_row)
  #
  cell_value_1 = pe.get_cell_value(row=2, column=3)
  print('第%d行, 第%d列的数据为: %s' % (2, 3, cell_value_1))
  cell_value_2 = pe.get_cell_value(coordinate='A5')
  print('A5单元格的数据为: {}'.format(cell_value_2))
  value_row = pe.get_row_value(3)
  print('第{}行的数据为:{}'.format(3, value_row))
  value_column = pe.get_column_value(2)
  print('第{}列的数据为:{}'.format(2, value_column))
  #
  values_1 = pe.get_all_value_1()
  print('第一种方式获取所有数据\n', values_1)
  values_2 = pe.get_all_value_2()
  print('第二种方式获取所有数据\n', values_2)
  title = pe.get_excel_title()
  print('表头为\n{}'.format(title))
  dict_value = pe.get_listdict_all_value()
  print('所有数据组成的嵌套字典的列表:\n', dict_value)
  #
  namedtuple_value = pe.get_list_nametuple_all_value()
  print('所有数据组成的嵌套命名元组的列表:\n', namedtuple_value)
  pe.write_cell(1, 2, 'Tc_title')
  xlrd
  安装xlrd,此模块只支持读操作, 如果要写需要使用xlwt或者使用xlutils配合xlrd, 但是使用xlwt只能对新的excel文件进行写操作,无法对原有文件进行写, 所以这里选择是用xlutils
  但是还有一个问题就是,如果使用xlutils, 那么我们的excel文件需要以.xls 为后缀。因为以xlsx为后缀无法实现写,会报错(亲测,因为formatting_info参数还没有对新版本的xlsx的格式完成兼容)
  注:xlrd操作excel时,行号和列号都是从0开始计算的
  封装代码
   """
  ------------------------------------
  @Time : 2019/5/13 21:22
  @Auth : linux超
  @File : ParseExcel_xlrd.py
  @IDE  : PyCharm
  @Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
  ------------------------------------
  """
  import xlrd
  from xlutils import copy
  from collections import namedtuple
  class ParseExcel(object):
  # xlrd 解析excel, 行号和列号都是从0开始的
  def __init__(self, filename, sheet):
  try:
  self.filename = filename
  self.sheet = sheet
  self.wb = xlrd.open_workbook(self.filename, formatting_info=True)
  if isinstance(sheet, str):
  self.sheet = self.wb.sheet_by_name(sheet)
  elif isinstance(sheet, int):
  self.sheet = self.wb.sheet_by_index(sheet)
  else:
  raise TypeError('sheet must be int or str')
  except Exception as e:
  raise e
  def get_max_row(self):
  """获取表单的最大行号"""
  max_row_num = self.sheet.nrows
  return max_row_num
  def get_max_column(self):
  """获取表单的最大列号"""
  min_row_num = self.sheet.ncols
  return min_row_num
  def get_cell_value(self, row, column):
  """获取某个单元格的数据"""
  if isinstance(row, int) and isinstance(column, int):
  values = self.sheet.cell(row-1, column-1).value
  return values
  else:
  raise TypeError('row and column must be type int')
  def get_row_values(self, row):
  """获取某一行的数据"""
  if isinstance(row, int):
  values = self.sheet.row_values(row-1)
  return values
  else:
  raise TypeError('row must be type int')
  def get_column_values(self, column):
  """获取某一列的数据"""
  if isinstance(column, int):
  values = self.sheet.col_values(column-1)
  return values
  else:
  raise TypeError('column must be type int')
  def get_table_title(self):
  """获取表头"""
  table_title = self.get_row_values(1)
  return table_title
  def get_all_values_dict(self):
  """获取所有的数据,不包括表头,返回一个嵌套字典的列表"""
  max_row = self.get_max_row()
  table_title = self.get_table_title()
  value_list = []
  for row in range(2, max_row):
  values = self.get_row_values(row)
  value_list.append(dict(zip(table_title, values)))
  return value_list
  def get_all_values_nametuple(self):
  """获取所有的数据,不包括表头,返回一个嵌套命名元组的列表"""
  table_title = self.get_table_title()
  max_row = self.get_max_row()
  excel = namedtuple('excel', table_title)
  value_list = []
  for row in range(2, max_row):
  values = self.get_row_values(row)
  e = excel(*values)
  value_list.append(e)
  return value_list
  def write_value(self, sheet_index, row, column, value):
  """写入某个单元格数据"""
  if isinstance(row, int) and isinstance(column, int):
  if isinstance(sheet_index, int):
  wb = copy.copy(self.wb)
  worksheet = wb.get_sheet(sheet_index)
  worksheet.write(row-1, column-1, value)
  wb.save(self.filename)
  else:
  raise TypeError('{} must be int'.format(sheet_index))
  else:
  raise TypeError('{} and {} must be int'.format(row, column))
  if __name__ == '__main__':
  pe = ParseExcel('testdata.xls', 'testcase')
  print('最大行号:', pe.get_max_row())
  print('最大列号:', pe.get_max_column())
  print('第2行第3列数据:', pe.get_cell_value(2, 3))
  print('第2行数据', pe.get_row_values(2))
  print('第3列数据', pe.get_column_values(3))
  print('表头:', pe.get_table_title())
  print('所有的数据返回嵌套字典的列表:', pe.get_all_values_dict())
  print('所有的数据返回嵌套命名元组的列表:', pe.get_all_values_nametuple())
  pe.write_value(0, 1, 3, 'test')
  pandas
  pandas是一个做数据分析的库, 总是感觉在自动化测试中使用pandas解析excel文件读取数据有点大材小用,不论怎样吧,还是把pandas解析excel文件写一下把
  我这里只封装了读,写的话我这有点小问题,后面改好再追加代码吧。
  请先pip install pandas安装pandas
  封装代码
   """
  ------------------------------------
  @Time : 2019/5/13 14:00
  @Auth : linux超
  @File : ParseExcel_pandas.py
  @IDE  : PyCharm
  @Motto: Real warriors,dare to face the bleak warning,dare to face the incisive error!
  ------------------------------------
  """
  import pandas as pd
  class ParseExcel(object):
  def __init__(self, filename, sheet_name=None):
  try:
  self.filename = filename
  self.sheet_name = sheet_name
  self.df = pd.read_excel(self.filename, self.sheet_name)
  except Exception as e:
  raise e
  def get_row_num(self):
  """获取行号组成的列表, 从0开始的"""
  row_num_list = self.df.index.values
  return row_num_list
  def get_cell_value(self, row, column):
  """获取某一个单元格的数据"""
  try:
  if isinstance(row, int) and isinstance(column, int):
  cell_value = self.df.ix[row-2, column-1] # ix的行参数是按照有效数据行,且从0开始
  return cell_value
  else:
  raise TypeError('row and column must be type int')
  except Exception as e:
  raise e
  def get_table_title(self):
  """获取表头, 返回列表"""
  table_title = self.df.columns.values
  return table_title
  def get_row_value(self, row):
  """获取某一行的数据, 行号从1开始"""
  try:
  if isinstance(row, int):
  row_data = self.df.ix[row-2].values
  return row_data
  else:
  raise TypeError('row must be type int')
  except Exception as e:
  raise e
  def get_column_value(self, col_name):
  """获取某一列数据"""
  try:
  if isinstance(col_name, str):
  col_data = self.df[col_name].values
  return col_data
  else:
  raise TypeError('col_name must be type str')
  except Exception as e:
  raise e
  def get_all_value(self):
  """获取所有的数据,不包括表头, 返回嵌套字典的列表"""
  rows_num = self.get_row_num()
  table_title = self.get_table_title()
  values_list = []
  for i in rows_num:
  row_data = self.df.ix[i, table_title].to_dict()
  values_list.append(row_data)
  return values_list
  if __name__ == '__main__':
  pe = ParseExcel('testdata.xlsx', 'testcase')
  print(pe.get_row_num())
  print(pe.get_table_title())
  print(pe.get_all_value())
  print(pe.get_row_value(2))
  print(pe.get_cell_value(2, 3))
  print(pe.get_column_value('Tc_title'))
  总结
  使用了3种方法,4个库 xlrd,openpyxl,xlwt,pandas 操作excel文件,个人感觉还是使用openpyxl比较适合在自动化中使用,当然不同人有不同选择,用哪个区别也不是很大。
  以上3种方法,都可以拿来直接使用,不需要再做封装了 !
  真的勇士, 敢于直面惨淡的warning、 敢于正视淋漓的error

      上文内容不用于商业目的,如涉及知识产权问题,请权利人联系博为峰小编(021-64471599-8017),我们将立即处理。

【调查报告】你以为的测试行业现状,其实是这样的!

评 论

论坛新帖

顶部 底部


建议使用IE 6.0以上浏览器,800×600以上分辨率,法律顾问:上海瀛东律师事务所 张楠律师
版权所有 上海博为峰软件技术股份有限公司 Copyright©51testing.com 2003-2019, 沪ICP备05003035号
投诉及意见反馈:webmaster@51testing.com; 业务联系:service@51testing.com 021-64471599-8017

沪公网安备 31010102002173号

51Testing官方微信

51Testing官方微博

扫一扫 测试知识全知道