Reading values from Excel

Libraries
1

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

2

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 :slight_smile:

Regards

1 Like
3

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 :blush:

4

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

3 Likes
5

Thank you for supporting charlie, It is working for me and really appreciate and thank you for your great guidance. :blush:
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}

2 Likes
6

i need to have excel value instead of the cell formula

7

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 :slight_smile: )

*** 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 :

image

2024-03-16_17h59_44
2024-03-16_17h59_441027×107 12.9 KB

Regards.
Charlie

2 Likes
8

Hi @CharlieScene is this -->${sheet1} a sheet name to be mentioned?

9

Hi @CharlieScene
what is this doc_id in def open_excel_document(self, filename: str, doc_id: str) → str:

what to pass instead of 0 in the keyword: Open Excel Document ${file} 0

10

Facing this issue to read data from excel

image
image1481×446 25.7 KB

11

Hi,

From the documentation the ‘0’ is an ID identifier you give to the opened document.
My guess is that if you open several files, you can identify docs in an easier way by recalling by ID with other Excel Library keywords.

So error might not be here, but probably from the path. Verify its consistency upon slash signs /, // or
You could use ${/} which is an all platform path separator.

Regards
Charlie

12

Yes, it’s sheet/tab name, but as said above the Get Sheet doesn’t activate the sheet

13

Ok Thanks @CharlieScene

14

hello, Is there a way to search for a specific value in Excel without using a loop? I want to select certain data efficiently. Is there a search value library in Robot Framework for Excel? I’ve been using a loop to find the data, but it’s taking a long time.

thank you for your help

15

Hi,

On RF side, I’m not sure if you can do better than a loop.
But you could either directly use a loop through a python script with excel libraries, or use something like this:

Regards
Charlie