Python Operations Excel Table file--Using the XLRD module

Original:

Http://www.cnblogs.com/lhj588/archive/2012/01/06/2314181.html

Introduction:

In the actual work, it is possible to use Excel tables in many cases, such as if you do not need a very formal use case tool to manage use cases, most companies choose to use Excel to manage use cases, including API Automation test in the design of the interface test cases, the interface is usually the first URL, method, parameters, messages, The interface description is maintained in Excel, and then the interface information is read from the Excel table;

The actual steps to use are as follows:

1. Install the XLRD module:

Very simple, Pip install xlrd can;

2. Import the module

Import xlrd

3. Open Excel file to read data, create file object to assign value to Workfile

Workfile = Xlrd.open_workbook (' excelfile.xlsx ')

4. Get a worksheet (sheet), create a Table object to assign a value to the variable table, three methods:

Table = workfile.sheets () [0]          #

table = Workfile.sheet_by_index (0) #

Table = workfile.sheet_by_name (U ' Sheet1 ') # gets by name

5. Get values for entire rows and columns (list)

table.row_values (0) #获取第一行的值, returns as a list

Table.col_values (0) #获取第一列的值, returned as a list

6. get the number of rows and columns

nrows = Table.nrows

  Ncols = table.ncols 7. Data for all rows based on number of rows for I in range (nrows): Print (Table.row_values (i))  8. Gets the data for the specified cell, note The index values for rows and columns are starting from 0 cell_a1 = Table.cell (0,0). Value #表格中A1位置的数据 cell_c4 = Table.cell (2,3). Value #表格中C4位置的数据  9. Use a row and column index to determine Data for the order cell cell_a1 = Table.row (0) [0].value #表格中第1行第1个位置 cell_a2 = table.col (1) [0].value #表格中第2列第一个位置  10. Simple Write, table. Put_cell () help Table.put_cell (), you can see that the Put_cell () method has 5 parameters: Rowx, Colx, CType, Value, Xf_index (1) ROWX # Row index to write (2) CO LX # column index to write (3) CType # Type of value to write:  0 empty, 1 string,  2 number,  3 date,  4 boolean,  5 Err or (4) value # Data to write to (5) Xf_index # extended formatting, default is 0

>>> Table.put_cell (321'python'0) >>> Table.cell (32). Value'python'

11. Do not need to close the file object, the Workfile file object does not have the close () method

　　

12.XLRD Application code example

#-*-coding:utf-8-*-import xdrlib, sysimport xlrddef open_excel (file='File.xls'):    Try: Data=xlrd.open_workbook (file)returndata except Exception,e:print str (e) #根据索引获取Excel表格中的数据 parameter: File:excel file path Colnameindex: Table header column name is the row, so B Y_index: Index of the table def excel_table_byindex (file='File.xls', colnameindex=0, by_index=0): Data=open_excel (file) Table=data.sheets () [By_index] nrows=table.nrows #行数 ncols=table.ncols #列数 colnames=table.row_values (colnameindex) #某一行数据 list=[]     forRowNuminchRange1, nrows): Row=table.row_values (rownum)ifRow:app= {}              forIinchRange (len (colnames)): App[colnames[i]]=Row[i] List.append (APP)returnlist# Gets the data parameter in the Excel table by name: File:excel file path Colnameindex: The header column name is the row, so the By_name:sheet1 name Def excel_table_byname (file< /c3>='File.xls', colnameindex=0, By_name=u'Sheet1'): Data=open_excel (file) Table=data.sheet_by_name (by_name) nrows=table.nrows #行数 colnames=table.row_values (colnameindex) #某一行数据 list=[]     forRowNuminchRange1, nrows): Row=table.row_values (rownum)ifRow:app= {}              forIinchRange (len (colnames)): App[colnames[i]]=Row[i] List.append (APP)returnListdef Main (): Tables=Excel_table_byindex () forRowinchtables:print Row Tables=Excel_table_byname () forRowinchTables:print Rowif__name__=="__main__": Main ()

Python Operations Excel Table file--Using the XLRD module