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)

Answer 1

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

Answer 2

use pandas

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