How is the spreadsheet data arranged? In two rows or two columns or …?
Setting up your Excel spreadsheet is based on:
Read data from excel sheet using Katalon Studio (mundrisoft.com)
Debit and Credit maybe like:
The below code finds the SL No. and puts a check mark on the appropriate checkbox. Currently, it does not remove the check mark before checking the next one, but that can be changed if needed. So, at the end, the Debit will have 3 check marks and the Credit will have 1 check mark.
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
import org.openqa.selenium.By
import org.openqa.selenium.WebElement
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testdata.TestDataFactory as TestDataFactory
import com.kms.katalon.core.webui.driver.DriverFactory
WebUI.openBrowser('')
WebUI.maximizeWindow()
WebUI.navigateToUrl('C://Users//Mike//Desktop//HTML FOR MY TABLE.html')
WebUI.waitForPageLoad(10)
driver = DriverFactory.getWebDriver()
def data = TestDataFactory.findTestData("Data Files/Honda")
WebUI.comment("row count is ${data.getRowNumbers()}")
for (row = 1; row <= data.getRowNumbers(); row++) {
def debitRequestedRef = data.getValue(1, row)
def creditRequestedRef = data.getValue(2, row)
WebUI.comment("debit ref is ${debitRequestedRef}")
WebUI.comment("debit ref is ${creditRequestedRef}")
WebElement table = driver.findElement(By.xpath('id("grdDebitTrn")/tbody'));
List<WebElement> rowsOfTable = table.findElements(By.tagName('tr'));
int rowCnt = rowsOfTable.size();
if (debitRequestedRef != "") {
Loop:
for (int cnt = 1; cnt < rowCnt; cnt++) {
List<WebElement> columnCells = rowsOfTable.get(cnt).findElements(By.tagName('td'));
cellText = columnCells.get(1).findElement(By.tagName('span')).getText();
if (cellText == debitRequestedRef ) {
columnCells.get(0).findElement(By.tagName('span')).click();
break Loop;
}
}
}
table = driver.findElement(By.xpath('id("grdCreditTrn")/tbody'));
rowsOfTable = table.findElements(By.tagName('tr'));
rowCnt = rowsOfTable.size();
if (creditRequestedRef != "") {
Loop:
for (int cnt = 1; cnt < rowCnt; cnt++) {
columnCells = rowsOfTable.get(cnt).findElements(By.tagName('td'));
cellText = columnCells.get(1).findElement(By.tagName('span')).getText();
if (cellText == creditRequestedRef) {
columnCells.get(0).findElement(By.tagName('span')).click();
break Loop;
}
}
}
}
Sorry sir for late reply. Actually my laptop has lost today. That’s why I had a busy day today. And I am late to reply you .sorry sir.
Sir thank you sir your code for selection of checkbox from excel is working properly. It’s so nice to have you for me sir. Thank you again.
But sir here one problem is when I am giving the last SL NO for selection. It is taking almost 20 minutes to check the check box. Which is very slow. Is there any way for doing selection quickly.
Wow. I’m not sure what can be causing it to take so long. When I run it it takes under 15 seconds.
Review the times of each step and see which one is taking its time (pun intended).
Perhaps you can clean up your “temp” files and Internet History and see if that speeds up your machine.
Ok sir I am trying
Sir I clean up browser history and deleted temp files.
And I reviewed the time.
Situation 1: there are 3500 data in debit table. When I am giving SL NO “3193263” katalon selecting quickly. But when I am giving SL NO “3742061” katalon taking a lot of time for selection. 3742061 is the last SL NO in debit table sir. Same happening for the rest of transaction those are in the situated in the last. Scrolling very slow. Otherwise it’s working but taking a lot of time…and excel files contains at least 2
300 serial number those I want to check.
I am thinking that here this code may be finding the serial number one by one . That’s why may be it is taking too much time for those serial number which are present in the last of the table.
So I played with the code some more to remove the line by line search. It is faster including with serial numbers at the bottom but it will still take some time to do 2,300 serial numbers. Try it and see if it is an improvement on your system.
Debit and Credit maybe like:
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import org.openqa.selenium.By
import org.openqa.selenium.WebElement
import com.kms.katalon.core.testdata.TestData as TestData
import com.kms.katalon.core.testdata.TestDataFactory as TestDataFactory
import com.kms.katalon.core.webui.driver.DriverFactory
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI
WebUI.openBrowser('')
WebUI.maximizeWindow()
WebUI.navigateToUrl('C://Users//Mike//Desktop//HTML FOR MY TABLE.html')
WebUI.waitForPageLoad(10)
driver = DriverFactory.getWebDriver()
WebElement myItem;
def data = TestDataFactory.findTestData("Data Files/Honda")
WebUI.comment("row count is ${data.getRowNumbers()}")
for (row = 1; row <= data.getRowNumbers(); row++) {
def debitRequestedRef = data.getValue(1, row)
def creditRequestedRef = data.getValue(2, row)
WebUI.comment("debit ref is ${debitRequestedRef}")
WebUI.comment("debit ref is ${creditRequestedRef}")
if (debitRequestedRef != "") {
try {
myItem = driver.findElement(By.xpath("//span[text()='${debitRequestedRef}']"));
myItem.findElement(By.xpath('.//parent::td/preceding-sibling::td/span')).click();
} catch (Exception) {WebUI.comment("Serial #${debitRequestedRef} does not exist!" ) }
}
if (creditRequestedRef != "") {
try {
myItem = driver.findElement(By.xpath("//span[text()='${creditRequestedRef}']"));
myItem.findElement(By.xpath('.//parent::td/preceding-sibling::td/span')).click();
} catch (Exception) {WebUI.comment("Serial #${creditRequestedRef} does not exist!" ) }
}
}
Edit: Just a note that the placement of quotation marks is important in getting the code to run, so be warned if you try to rewrite this instead of copy and paste.
1 Like
Sir perfect solution … I have tested 14 data and it took only 45 seconds…
Thank you sir. Its pleasure for me that I have found you here in this forum.
Love you sir. You are amazing 
Thank you @grylion54 for your dedication and timely support for @sanjoy2016ewu. This is great to see the conversation finally got its solution and new connection has been matched.
Thank you for your contribution to Katalon Community guys!