自動化測試如何解析excel文件？

• 2019 年 12 月 12 日
• 筆記

來源：http://www.51testing.com

　前言

自動化測試中我們存放數據無非是使用文件或者資料庫，那麼文件可以是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

星雲測試

http://www.teststars.cc

奇林軟體

http://www.kylinpet.com

聯合通測

http://www.quicktesting.net