Need to read and write the value in excel with reference of column and row name in python

Question

I am trying to read and write values in the excel using python. I have already done with the cell number but that is not the sufficient way. Could anyone please guide how to achieve the same. E.g.

Test no TestCase Build Result Expected Actual Username Password 1 loginWithValidID_StatusCode admin admin 2 loginWithValidID_Body 403 admin admin 3 loginWithValidID_Login_Token TRUE admin admin 4 loginWithValidID_VersionedUR http://xx.xxx.xx.xxx:xxx/ admin admin

5 loginWithInvalidID_StatusCode 403 admin admin1 6 loginWithInvalidID_Body empty admin admin1 7 loginWithInvalidID_Login_Token empty admin admin1 8 loginWithInvalidID_VersionedUR empty admin admin1

Here first I need to read the expected value first against the test case name and then fill the actual and result in the respective column. Please help.

score 0 · Answer 1 · edited May 23 '17 at 10:26

Could you supply your code and example data? It is not really clear what you want to do.

Here is some code (adapted from here: http://www.numbergrinder.com/2008/10/pulling-data-from-excel-using-python-xlrd/ and here Finding the index of an item given a list containing it in Python) to read in data from an excel sheet and to get the value of a cell based on the row and column name you need.

import xlrd

book = xlrd.open_workbook("data.xls") #open xls file


sheet = book.sheet_by_index(0) #get the first sheet from your excel file (assuming that your data is in the first sheet

r = sheet.row_values(0) #returns all the Values of row 0 as a list
c = sheet.col_values(0) #returns all the VALUES of column 0 as a list

row_you_need_to_access = 'row_name_a'
column_you_need_to_access = 'col_name_a'


for index_r, value_r  in enumerate(r):
    if value_r == row_you_need_to_access:
        row_index = index_r
        for index_c, value_c in enumerate(c):
            if value_c == column_you_need_to_access:
                column_index = index_c
                value_you_need = sheet.cell(row_index,column_index).value

You then have the row_index, column_index and value for a cell that you need to find the match to. Without an example file, it is difficult to know how to go on, but I hope this gets you going.

To write to excel, you could 'import xlwt'. I guess it is a good idea to write into a new file in order to avoid overwriting data.

Need to read and write the value in excel with reference of column and row name in python

1 Answers1