Python selenium using openpyxl to write result to excel file but there is only case is written to excel file

Question

I just start learning about Selenium using Python last week, i write a test case with 4 step, at step 3 and 4 if this pass/fail then it will be write result to a excel file. But when i run the test case, there is only one step can write result to file excel, any help? Btw, sorry for my bad english :P

My scenario
Step 1: Enter username
Step 2: Enter password
Step 3: Click login button
Step 4: Wait for Gmail's logo visible

Here's my code:

from selenium import webdriver
from selenium.webdriver.support.ui import WebDriverWait
import unittest
from openpyxl import Workbook

class BasicTest(unittest.TestCase):

def setUp(self):
    self.driver = webdriver.Firefox()
    self.driver.get("https://www.gmail.com")
    self.LoginXpath = "//input[@value='Sign in']"
    self.LogoXpath = "//a[contains(@href, '#inbox')]"
    self.gmailUserName = "username"
    self.gmailPassword = "password"
    self.emailFieldID = "Email"
    self.passFieldID = "Passwd"
    self.InboxXpath = "id(':4b')/x:div/x:div[1]/x:span/x:a"

def test_Basic(self):
    driver = self.driver
    driver.find_element_by_id(self.emailFieldID).send_keys(self.gmailUserName)
    driver.find_element_by_id(self.passFieldID).send_keys(self.gmailPassword)


    try:
        driver.find_element_by_xpath(self.LoginXpath).click()
        self.write_test_case_result('PASS', 'A1')
        print('Find login button: PASS')
    except:
        self.write_test_case_result('FAIL', 'A1')
        print('Find login button: FAIL')

    try:
        WebDriverWait(driver, 15).until(lambda driver: self.InboxXpath)
        driver.find_element_by_xpath(self.LogoXpath).click()
        self.write_test_case_result('PASS', 'A2')
        print('Find logo xpath: PASS')
    except:
        self.write_test_case_result('FAIL', 'A2')
        print('Find logo xpath: FAIL')

def write_test_case_result(self, result, location):
    wb = Workbook()
    ws1 = wb.worksheets[0]
    ws1.title = 'Test result'
    dest_filename = 'Test_Result.xlsx'

    while True:
        if result == "PASS":
            ws1.cell(location).value = "PASSED"
            break
        else:
            ws1.cell(location).value = "FAILED"
            break
        break
    # Save the file
    wb.save(filename = dest_filename)


def tearDown(self):
    self.driver.quit()

if __name__ == "__main__":
unittest.main()

Sorry my bad! Step 1: Enter username Step 2: Enter password Step 3: Click on login button Step 4: Wait for Gmail's logo then the test cases is complete :D — Dat Van
– Dat Van, Commented May 11, 2015 at 2:39
@DatVan I would suggest you to use HTML test Runner: tungwaiyip.info/software/HTMLTestRunner.html , so that you get your results more in detail and it is a good practice. — karunakar sapogu
– karunakar sapogu, Commented May 12, 2015 at 4:55

Shibu · Accepted Answer · 2018-05-04 10:53:16Z

1

First, install openpyxl using a command -- "pip install openpyxl" and now create a new file python file and write below code--

from openpyxl import load_workbook

def userName(row_Num, cell_Num, sheet):
    wb = load_workbook('./test.xlsx')
    sheet = wb.get_sheet_by_name(sheet)
    username = sheet.cell(row=row_Num, column=cell_Num).value
    if username is not None:
        return username
    else:
        return '' #Null Value pass

Now go where you created testScript and import python file, remember all file should be same folder including excellSheet.

 driver.find_element_by_xpath("//input[@id='user']").send_keys(userName(7,1,'Sheet1'))
 #Row num, Col Num, Sheet Name

answered May 4, 2018 at 10:53

Shibu

91 silver badge7 bronze badges

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

Comments

Charlie Clark · Accepted Answer · 2015-05-11 07:31:12Z

0

The formatting is off but I think you should not be embedding what is essentially logging code within a test method. test_Basic will overwrite the file every time it is run, which is presumably why it only ever has one result.

Try writing your test initially just to use Python's logging module. Once this is working fine you can replace the logging object with some code that writes Excel files.

answered May 11, 2015 at 7:31

Charlie Clark

19.7k4 gold badges56 silver badges64 bronze badges

Collectives™ on Stack Overflow

Python selenium using openpyxl to write result to excel file but there is only case is written to excel file

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related