Read excel content in robot framework

Question

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

`

Have you ever used Pandas? There is a nice function for Excel files Pandas.read_excel() — calico_
– calico_, Commented Jun 20, 2017 at 17:21
I am able to read all rows now, now i have to do same thing with csv. — P S
– P S, Commented Jun 20, 2017 at 18:29
Pandas has a function for reading CSV files as well: read_csv() — Xukrao
– Xukrao, Commented Jun 20, 2017 at 20:34
@PS can you provide an answer to your own question so that others who have the same problem can benefit? — A. Kootstra
– A. Kootstra, Commented Jun 21, 2017 at 17:33

Phil Pill · Accepted Answer · 2018-03-14 04:38:30Z

1

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

edited Mar 14, 2018 at 4:38

Phil Pill

3012 silver badges14 bronze badges

answered Jul 11, 2017 at 11:39

Hemant Kulkarni

1934 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

P S · Accepted Answer · 2017-06-21 21:06:06Z

0

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

answered Jun 21, 2017 at 21:06

P S

1581 gold badge2 silver badges12 bronze badges

Collectives™ on Stack Overflow

Read excel content in robot framework

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related