How to use python to read xlsx Table

Today, I would like to share with you how to use python to read xlsx table related knowledge points, detailed content, clear logic, I believe that most people still know too much about this knowledge, so share this article for your reference, I hope you can learn something after reading this article, let's take a look at it.

Preface

Chinese New year is coming, now I don't want to do things at work, and I can't learn at all. The operation on xlsx was written yesterday, but sadly, I forgot to release it and turned off the browser and found that it had been lost.

The following operations are compared with the table operation:

Read operation

The method of getting sheet

Get the sheet table through the index:

Table = worbook.sheets () [0] table = worbook.sheet_by_index (0)

Obtain the following through the sheet name:

Table = worbook.sheet_by_name (sheet_name='case')

Get all the sheet in xlsx:

Table = worbook.sheet_names () print (table) print: case

Get rows and columns

Get the number of valid rows in sheet:

Row = table.nrowsprint (row) print: 8

Get the number of valid columns in sheet:

Col = table.ncolsprint (col) print: 10

Gets how many columns of data are in a row:

Col = table.row_len (0) print (col)

Gets all the data in the specified row:

'' rowx means to get the number of rows of data start_col indicates how much the index starts End_colx indicates the end of the index from end_colx to None means that there is no limit to getting the data in the specified row and returns''table_list = table.row_values (rowx=0, start_colx=0, end_colx=None) print (table_list) in the form of a list: [' run', 'headers',' pre_case_id', 'pre_fields',' request_body', 'expect_result',' assert_type', 'pass' 'update_time',' response']

Get the data in the column:

'' colx means to get the data in which column start_rowx indicates how much the index starts End_rowx indicates how much the index ends end_rowx means that there is no limit to getting the data in the specified column and returns''table_list = table.col_values (colx=0, start_rowx=0, end_rowx=None) print (table_list) print in the form of a list: [' run', 'yes',' no', 'yes',' no', 'no']

Get the median of the cell

Gets the value in the specified cell:

Table = worbook.sheet_by_name (sheet_name='case') value = table.cell_value (rowx=0, colx=1) print (value) print: headers

Let's write an example, which is to print out all the lines where run is yes. In fact, in our daily work, we just execute run as a use case of yes, although we don't use excel to store test cases. Let's directly define it as a decorator.

Import xlrdclass Readxlrd (): def _ _ init__ (self,func): self.func = func def _ _ call__ (self, * args * * kwargs): self.func (* args) worbook = xlrd.open_workbook (filename=args [0]) table = worbook.sheet_by_name (sheet_name=args [1]) row = table.nrows for i in range (row): if I > = 1: combined_dict = {} table_list = table.row_values (rowx=i, start_colx=0) End_colx=None) table_head = table.row_values (rowx=0, start_colx=0, end_colx=None) for k, v in zip (table_head, table_list): combined_ if combined_dict [k] = v if combined_dict ['run'] =' yes': print (combined_dict) @ Readxlrddef test (route) Sheet): print ('the path entered is {} The sheet entered is {} '.format (route,sheet)) print: the path entered is C:\ Users\ 86182\ Desktop\ case.xlsx The sheet entered is case {'run':' yes', 'headers':' {"Content-Type": "application/x-www-form-urlencoded"}', 'pre_case_id':-1. 0,' pre_fields':'[]', 'request_body':' {"phone": "18262966312", "pwd": "123456"}', 'expect_result':' 0', 'assert_type':' code' 'pass':' True', 'update_time': 44447.6884722222,' response':'} {'run':' yes', 'headers':' {"token": "token"}', 'pre_case_id': 1.0',' pre_fields':'[{"field": "token", "scope": "header"]', 'request_body':' {}', 'expect_result':' 0' 'assert_type':' code', 'pass':' True', 'update_time': 44447.6892476852,' response':'} above are all the contents of the article "how to use python to read xlsx tables" Thank you for reading! I believe you will gain a lot after reading this article. The editor will update different knowledge for you every day. If you want to learn more knowledge, please pay attention to the industry information channel.