CODEWITHSUNDEEP
pythonrobotframework
P S
P S

Reputation: 158

Read excel content in robot framework

I am new to robot framework and python. I am working on web services using SudsLibrary. I want to read data content from excel file. I have written below code for it, but it reads only 1 row from file. I want it to read all the rows from the file.

Test.robot 

*** Settings ***
Library    DataReader.py

*** Variables ***
 ${file}   ${CURDIR}${/}Book2.xls
 ${sheet}  ABC

*** Test Cases *** 
Test data provider
[Setup]   prepare data
Create Soap Client    http://test.asmx?WSDL
${ABC}    Create Wsdl Object   ABC
:FOR  ${ABC.Col1}  ${ABC.Col2}  ${ABC.Col3}  ${ABC.Col4}  ${ABC.Col5}      ${ABC.Col6}  ${ABC.Col7}   in   @{testData}
\  ${ABC.Col1}    Set Variable    ${ABC.Col1}
\  ${ABC.Col2}   Set Variable    ${ABC.Col2}
\  ${ABC.Col3}  Set Variable    ${ABC.Col3}
\  ${ABC.Col4}  Set Variable    ${ABC.Col4}
\  ${ABC.Col4} =  convert to integer     ${ABC.Col4}
\  ${ABC.Col5}  Set Variable   ${ABC.Col5}
\  ${ABC.Col6}  Set Variable     ${ABC.Col6}
\  ${ABC.Col6}=  convert to integer     ${ABC.Col6}
\  ${ABC.Col7}   Set Variable    ${ABC.Col7}
\  ${ABC.Col7}=  convert to integer     ${ABC.Col7}
\  Set Test Variable    ${ABC}
\  Call Soap Method    ABC    ${ABC}
\  ${soap_response}    Get Last Received
\  Log    ${soap_response}
\  Element Text Should Be    ${soap_response}    2.991880011689


*** Keywords ***
prepare data
${data}=   getDataFromSpreadsheet    ${file}   ${sheet}
Set Test Variable   ${testData}     ${data}

DataReader.py

import xlrd

def getDataFromSpreadsheet(fileName, sheetname) : 
workbook = xlrd.open_workbook(fileName)
worksheet = workbook.sheet_by_name(sheetname)
print worksheet
rowEndIndex = worksheet.nrows - 1
colEndIndex = worksheet.ncols - 1 
rowStartIndex = 1
colStartIndex = 0
testData = []
dataRow = []

curr_row = rowStartIndex
while curr_row <= rowEndIndex:
     cur_col = colStartIndex
     while cur_col <= colEndIndex:
         cell_type = worksheet.cell_type(curr_row, cur_col)

         value = worksheet.cell_value(curr_row, cur_col)
         dataRow.append(value)
         cur_col+=1
     curr_row += 1
     # testData.append(dataRow)
# return testData  
return dataRow

`

Upvotes: 4

Views: 19750

Answers (2)

Hemant Kulkarni
Hemant Kulkarni

Reputation: 193

Here is sample code to read values from Excel file-

Open Excel    ${CURDIR}/${EXCEL_FILE_NAME}

${strColCount} =  Get Column Count  ${EXCEL_SHEET_NAME}

Log To Console  \nCols are => ${strColCount}

${strRowCount} =  Get Row Count ${EXCEL_SHEET_NAME}

Log To Console  \nRows are=> ${strRowCount}

Set Test Variable   ${ROW_ID}   3

:FOR    ${colIndex}    IN RANGE    1    ${strColCount}
    \   ${strTempColValue}  Read Cell Data By Coordinates   ${EXCEL_SHEET_NAME} ${colIndex} ${ROW_ID}

Variable strTempColValue will now have value of desired column index & given row. Here in this example, we have given row as 3.

http://navinet.github.io/robotframework-excellibrary/ExcelLibrary-KeywordDocumentation.html

Upvotes: 1

P S
P S

Reputation: 158

To read data row by row from csv, i used below python code.

csvLibrary.py

import csv
class csvLibrary(object):

def read_csv_file(self, filename):
    '''This creates a keyword named "Read CSV File"

    This keyword takes one argument, which is a path to a .csv file. It
    returns a list of rows, with each row being a list of the data in 
    each column.
    '''
    data = []
    with open(filename, 'rb') as csvfile:
        reader = csv.reader(csvfile)
        next(reader,None)
        for row in reader:
            for i in row:
                data.append(i)
    return data

Upvotes: 0

Related Questions