Reading values from Excel

[Javed (Mohammed Javed)]

Hi everyone,

Can anyone help me with how to call values from different sheets in Excel? For example, the username will be in Sheet1 and the password will be in Sheet2. I’ve tried some methods, but it always seems to call the values from the last sheet in the Excel file. Please find the code below for your reference. I would really appreciate your help

Open Excel Document ${file} 0
Get Sheet ${sheet1}
${Username} Read Excel Cell 2 1
Log ${Username}
Get Sheet ${sheet2}
${Password} Read Excel Cell 2 1
Log ${Password}
Close Current Excel Document

[CharlieScene (Charlie)]

Hi,

I assume you’re using ExcelLibrary, and I see the same issue as you do.
But this seems to be linked to the way the ‘Get Sheet’ keywords returns the value, or a bug :
Here :

${getsheetreturn}    Get Sheet   Sheet1 

returns :

${getsheetreturn} = <Worksheet "Sheet1">

This can’t be used directly in the keyword Read Excel Cell, and I’m not sure how to pass it nor if it activates really the sheet for reading.

However for your problem I would rather consider to directly pass the sheet’s name or variable to Read keyword :

Excel_Test
    Open Excel Document    ${file}    0
    ${Username}    Read Excel Cell    2    1    ${sheet1}
    Log    message=${Username}
    ${Password}    Read Excel Cell  2   1    ${sheet2}
    Log     ${Password}

This works well, and saves lines

Regards

[Javed (Mohammed Javed)]

Hi Charlie , Thanks for replying.

But for the Read Excel Cell keyword , we cannot extra keywords where we can only use
Read Excel Cell row_num col_num.

but thanks for letting me know that Get Sheet keywords has some issue

[CharlieScene (Charlie)]

Hi,

Here Excellibrary allows the use of the sheet_name keyword :

https://rawgit.com/peterservice-rnd/robotframework-excellib/master/docs/ExcelLibrary.html

 Returns content of a cell.
 **Args:**
 *row_num*: row number, starts with 1.
 *col_num*: column number, starts with 1.
 *sheet_name*: sheet name, where placed cell, that need to be read.

I had tested the example I gave above, and it worked fine. The example they gave in documentation part does not show this, but the above args provides it.

Not sure for the rest if it’s an issue, or what the Get Sheet keyword should do (activate the sheet ? )

Regards

[Javed (Mohammed Javed)]

Thank you for supporting charlie, It is working for me and really appreciate and thank you for your great guidance.
below mentioned logic is worked fine for me as you said ,
Excel_Test
Open Excel Document ${file} 0
${Username} Read Excel Cell 2 1 ${sheet1}
Log message=${Username}
${Password} Read Excel Cell 2 1 ${sheet2}
Log ${Password}

[Sayyed (Sayyed Anam Zehra Hasan)]

i need to have excel value instead of the cell formula

[CharlieScene (Charlie)]

Hi,

I checked ExceLibrary, and it doesn’t seem to be implemented…
Maybe there are some other libraries that provide this, but I was wondering if something not too complicated can be done, so here it is (assuming you mainly read values )

*** Settings ***
Library    SeleniumLibrary
Library    readexcel.py

*** Variables ***
${file_path}    path_to_your_file

*** Test Cases ***
Excel_Read
    ${value3}    ReadExCell    ${file_path}    Sheet1    2    3    False

This is a quick custom .py with keyword that reads from the file. Last argument is True by default (reads values), that can be passed to False to get formulas :

from openpyxl import load_workbook
from robot.api.deco import keyword

@keyword("ReadExCell")
 def ReadExcelRow(file_path, sheetname, searchrow, searchcolumn, data_only=True):
    searchrow = int(searchrow)
    searchcolumn = int(searchcolumn)

    # Get workbook and required sheet
    wb = load_workbook(file_path, data_only=data_only)
    sheet = wb[sheetname]

    # Cell read
    cell = sheet.cell(row=searchrow, column=searchcolumn)
    return cell.value

Here’s the result :

Regards.
Charlie