How To Delete A Row In Excel Sheet Using Python?

Question

I am working on a Selenium program using Python where I want to delete a row in the Excel sheet using Openpyxl library. The issue is I don't know how to implement the delete function in my program. Below here have 2 classes, AutoTest.py which is the testing class and NewCard.py which is the class where I implemented POM(Page Object Model). May I know how to implement the function to delete just 1 row in accordance with my program?

AutoTest.py

import unittest
import HtmlTestRunner
from selenium import webdriver
import openpyxl
import sys
sys.path.append("C:\Users\lukegoh\Desktop\Python Projects\SoftwareAutomationTesting")
from pageObjects.LoginPage import LoginPage
from pageObjects.HomePage import Homepage
from pageObjects.NewCard import NewCard

excpath = r"C:\Users\lukegoh\Desktop\Python Projects\SoftwareAutomationTesting\excel\new\ABT0475EC Card Init Detailed Rpt Test 01 - v13_1.xlsx"
excsheetName = "ABT0475EC Card Initialization D"

class AutoTest(unittest.TestCase):
    baseURL = "https://10.2.5.215:33000/viewTopUpRequest"
    username = "ezltest2svc"
    password = "Password123!"
    driver = webdriver.Chrome()

    @classmethod
    def setUpClass(cls):
        cls.driver.get(cls.baseURL)
        cls.driver.maximize_window()
        cls.driver.implicitly_wait(10)


    def test_1(self):  #This is scenario 1- to create request for new card
        lp = LoginPage(self.driver)
        hp = Homepage(self.driver)
        np = NewCard(self.driver)
        lp.setUsername(self.username)
        lp.setPassword(self.password)
        lp.clickLogin()
        hp.clickutil()
        hp.clickreqNewCard()
        np.setJobOrder()
        np.clickExcel()
        np.setTopUpAmount()
        np.calculateAmount()
        if not np.clickSubmit():
            np.deleteRow(excpath,excsheetName,3)
        else:
            print("Test Passed")


   # @classmethod
   # def tearDownClass(cls):
   #     cls.driver.close()
   #     print("Test Completed")


#if __name__ == '__main__':
#    unittest.main(testRunner=HtmlTestRunner.HTMLTestRunner(output='C:/Users/lukegoh/Desktop/Python Projects/SoftwareAutomationTesting/reports'))

NewCard.py

import openpyxl

class NewCard:

    jobOrder_xpath="//body/div[@id='wrapper']/div[3]/div[1]/div[1]/div[1]/div[1]/input[1]"
    excelButton_xpath="//input[@id='img']"
    topup_textbox_xpath="//input[@id='input-live']"
    calculateAmount_xpath="(//button[@type='button'])[4]"
    buttonSubmit_xpath="(//button[@type='button'])[5]"


    def __init__(self,driver):
        self.driver=driver


    def setJobOrder(self):
        self.driver.find_element_by_xpath(self.jobOrder_xpath).send_keys("AutoTest1")

    def clickExcel(self):
        self.driver.find_element_by_xpath(self.excelButton_xpath).send_keys(r"C:\Users\lukegoh\Desktop\Python Projects\SoftwareAutomationTesting\excel\new\ABT0475EC Card Init Detailed Rpt Test 01 - v13_1.xlsx")

    def setTopUpAmount(self):
        self.driver.find_element_by_xpath(self.topup_textbox_xpath).send_keys("10")

    def calculateAmount(self):
        self.driver.find_element_by_xpath(self.calculateAmount_xpath).click()

    def clickSubmit(self):
        self.driver.find_element_by_xpath(self.buttonSubmit_xpath).click()

    def deleteRow(file, sheetName, rownum):
        workbook = openpyxl.load_workbook(file)
        sheet = workbook.get_sheet_by_name(sheetName)
        sheet.cell(row=rownum).
        workbook.save(file)

This is covered in the openpyxl documentation. Is there anything there you don't understand? — Charlie Clark
– Charlie Clark, Commented Jan 24, 2021 at 16:17
why the downvote? I didn't do anything that's insufficient in terms of the information presented — TechGeek49
– TechGeek49, Commented Jan 25, 2021 at 1:12
I did but I had trouble figuring out how to do it since it's my maiden attempt in Python though — TechGeek49
– TechGeek49, Commented Jan 25, 2021 at 12:29

Kuldeep Singh Sidhu · Accepted Answer · 2021-01-24 12:28:41Z

8

If you check OpenPyXL docs, it has something called delete_rows()

Syntax delete_rows(idx, amount=1), it deletes from idx and goes on deleting for amount

import openpyxl

filename = "example.xlsx"
wb = openpyxl.load_workbook(filename)
sheet = wb['Sheet1']
sheet.delete_rows(row_number, 1)
wb.save(filename)

Docs for delete_rows(): https://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html#openpyxl.worksheet.worksheet.Worksheet.delete_rows

answered Jan 24, 2021 at 12:28

Kuldeep Singh Sidhu

3,8762 gold badges16 silver badges23 bronze badges

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

Comments

sepideh_ssh · Accepted Answer · 2021-01-24 12:28:32Z

-3

use pandas

 import pandas as pd
 df = pd.read_excel("test.xlsx")
 df = df.drop(df.index[1])
 df.to_excel('test1.xlsx')

answered Jan 24, 2021 at 12:28

sepideh_ssh

1982 silver badges8 bronze badges

5 Comments

Chris Over a year ago

OP is using openpyxl, not Pandas. Why should they install a new, fairly large library just to delete a row? Surely openpyxl can handle that?

sepideh_ssh Over a year ago

Thanks @Chris . I only suggested pandas because it is more famous and stronger and given that in the title of the question asked "how to delete a row in Excel".

sepideh_ssh Over a year ago

Thanks @TomerShetah. Next time I will be more careful in answering the questions.

Chris Over a year ago

I see. Please read the question as well as the title. There should necessarily be additional useful information there; it can't all fit in the title. And please pay particular attention to the question's tags. This one is explicitly tagged with the openpyxl tag.

sepideh_ssh Over a year ago

Thanks @Chris. I will pay attention to the points you said

Collectives™ on Stack Overflow

How To Delete A Row In Excel Sheet Using Python?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related