Web data fill up in box from excel data

sanjoy2016ewu · February 14, 2023, 4:56am

Dear Sir,
Please help me I am trying to make a web test where I will input some data from excel to web input box. But I am unable to create the right code for it. Please help me so that I can do it. The html and the excel file are attached here.
excel sheet.xlsx (8.7 KB)
html.txt (26.7 KB)

discover.selenium · February 14, 2023, 5:21am

sanjoy2016ewu · February 14, 2023, 6:01am

I need proper codes sir

grylion54 · February 14, 2023, 6:42am

The problem is that to do the code “properly”, it sometimes means we need to know something about the app.

Maybe like:

Right-click on any of the elements of your page and select “Inspect”. You may have to do this twice. This will reveal the HTML of the page. From that, you can create pathways for the various elements, or have the Web Spy assist you creating them. Then, you can drag and drop the elements into your Test Case. Put the spreadsheet into the Data folder of your Katalon project. Then you can use the template of below to get you started. You WILL have to improve it.

import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import org.openqa.selenium.By as By
import org.openqa.selenium.Keys as Keys
import org.openqa.selenium.WebDriver as WebDriver
import org.openqa.selenium.WebElement as WebElement

import com.kms.katalon.core.testobject.ConditionType as ConditionType
import com.kms.katalon.core.testobject.TestObject as TestObject
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 as DriverFactory
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI


WebUI.openBrowser('')

WebUI.maximizeWindow()

WebUI.navigateToUrl('...//Sanjoy.html')
WebUI.waitForPageLoad(10)

def data = TestDataFactory.findTestData("Data Files/your spreadsheet name")
WebUI.comment("row count is ${data.getRowNumbers()}")

for (row = 1; row <= data.getRowNumbers(); row++) {
	
	xpath = 'id("P15_CUSTOM_CODE")'
	myItem = new TestObject(xpath)
	myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
	WebUI.verifyElementVisible(myItem)
	WebUI.setText(myItem, data.getValue(1, row))
	
	xpath = 'id("P15_CUSTOMS_NAME")'
	myItem = new TestObject(xpath)
	myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
	WebUI.verifyElementVisible(myItem)
	WebUI.setText(myItem, data.getValue(2, row))
	
	xpath = 'id("P15_BILL_DATE")'
	myItem = new TestObject(xpath)
	myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
	WebUI.verifyElementVisible(myItem)
	WebUI.setText(myItem, data.getValue(3, row))
	
	xpath = 'id("P15_LC_ID")'
	myItem = new TestObject(xpath)
	myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
	WebUI.verifyElementVisible(myItem)
	WebUI.setText(myItem, data.getValue(4, row))
	
	xpath = 'id("P15_FC_TOT")'
	myItem = new TestObject(xpath)
	myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
	WebUI.verifyElementVisible(myItem)
	WebUI.setText(myItem, data.getValue(5, row))
	
}

bionel · February 14, 2023, 8:34am

What do you mean ‘properly’?
You expect from our side to write the code for you so you can get paid at work?
It is not working like that.

Start to write some code, show us where you get stuck and perhaps somebody will guide you further.

sanjoy2016ewu · February 14, 2023, 9:04am

Dear Sir,
Sorry if I heart you by any chance. I have no intension like this. Don’t even think like that.

sanjoy2016ewu · February 14, 2023, 12:32pm

import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject

import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import org.openqa.selenium.By as By

import org.openqa.selenium.Keys as Keys

import org.openqa.selenium.WebDriver as WebDriver

import org.openqa.selenium.WebElement as WebElement

import com.kms.katalon.core.testobject.ConditionType as ConditionType

import com.kms.katalon.core.testobject.TestObject as TestObject

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 as DriverFactory

import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

WebUI.openBrowser(‘’)

WebUI.maximizeWindow()

WebUI.navigateToUrl(‘file:///C:/Users/SANJOYROY/Desktop/FINAL.html’)

WebUI.waitForPageLoad(10)

def data = TestDataFactory.findTestData(“Data Files/your spreadsheet name”)

WebUI.comment(“row count is ${data.getRowNumbers()}”)

for (row = 1; row <= data.getRowNumbers(); row++) {

            xpath = 'id("P15_CUSTOM_CODE")'

            myItem = new TestObject(xpath)

            myItem.addProperty("xpath", ConditionType.EQUALS, xpath)

            WebUI.verifyElementVisible(myItem)

            WebUI.setText(myItem, data.getValue(1, row))

           

            xpath = 'id("P15_CUSTOMS_NAME")'

            myItem = new TestObject(xpath)

            myItem.addProperty("xpath", ConditionType.EQUALS, xpath)

            WebUI.verifyElementVisible(myItem)

            WebUI.setText(myItem, data.getValue(2, row))

           

            xpath = 'id("P15_BILL_DATE")'

            myItem = new TestObject(xpath)

            myItem.addProperty("xpath", ConditionType.EQUALS, xpath)

            WebUI.verifyElementVisible(myItem)

            WebUI.setText(myItem, data.getValue(3, row))

           

            xpath = 'id("P15_LC_ID")'

            myItem = new TestObject(xpath)

            myItem.addProperty("xpath", ConditionType.EQUALS, xpath)

            WebUI.verifyElementVisible(myItem)

            WebUI.setText(myItem, data.getValue(4, row))

           

            xpath = 'id("P15_FC_TOT")'

            myItem = new TestObject(xpath)

            myItem.addProperty("xpath", ConditionType.EQUALS, xpath)

            WebUI.verifyElementVisible(myItem)

            WebUI.setText(myItem, data.getValue(5, row))

}

sanjoy2016ewu · February 14, 2023, 12:32pm

Sir I am using this but it is not working sir … please help me out

grylion54 · February 14, 2023, 1:26pm

Is this a school class project? Spend some time yourself. Create some Objects and see how nice it can be. Review the video that was provided by @discover.selenium. Again, spend some time on it.

Seriously, it looks like you didn’t even spend a moment and read what I typed.

def data = TestDataFactory.findTestData(“Data Files/**your spreadsheet name**”)

bionel · February 14, 2023, 1:34pm

No Sir, please provide me the full sollution, I don’t have time for RTFM

sanjoy2016ewu · February 14, 2023, 2:47pm

@bionel I am not pro like you. My 5 hours time is your 5 minutes to understand. Sorry man if I heart you.

bionel · February 14, 2023, 3:33pm

This is not about being pro, and I am sorry if I somehow heart your feelings.

But please.

don’t Sir me
don’t Sir anybody on this forum

I am glad tou skipped this time the ‘Sir’ part because:
Why?

If we look on www, there are a lot of debates on this matter.
It can be interpreted as a respect but also as a disrespect.
I will not provide any link, feel free to research about.

Therefore, knowing that we have various barrier languages, let us keep the discussions here at technical level and try to use (as much as we can) just technical terms.

So, ask by code and we will reply by code.
Thank you!

(side note, I noticed some accidity in your reply, does not matter, i preffer to be called directly than sir’ed)
And appologgies for abusing this in my previous reply.

sanjoy2016ewu · February 14, 2023, 3:51pm

I also apologize to you

sanjoy2016ewu · February 15, 2023, 5:16am

It is working nicely. But one thing more i have added here that is for clicking in the “CREATE” button i have added the following part:
xpath = ‘//table[@id=‘R33588865397933856’]/tbody/tr/td[2]/a[2]’
myItem = new TestObject(xpath)
myItem.addProperty(“xpath”, ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem).click();

The total Part is:
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData

import org.openqa.selenium.By as By
import org.openqa.selenium.Keys as Keys
import org.openqa.selenium.WebDriver as WebDriver
import org.openqa.selenium.WebElement as WebElement

import com.kms.katalon.core.testobject.ConditionType as ConditionType
import com.kms.katalon.core.testobject.TestObject as TestObject
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 as DriverFactory
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords as WebUI

WebUI.openBrowser(‘’)

WebUI.setEncryptedText(findTestObject(‘Object Repository/Page_Login/input_Password_P101_PASSWORD’), ‘52jebsXPwVzA5N4M3RxmWA==’)

WebUI.click(findTestObject(‘Object Repository/Page_Login/a_Login’))

WebUI.click(findTestObject(‘Object Repository/Page_Welcome/a_Transactions’))

WebUI.click(findTestObject(‘Object Repository/Page_Import Transactions/a_Bill of Entry Information Entry Search IM_d10e8b’))

def data = TestDataFactory.findTestData(“Data Files/JJJ”)
WebUI.comment(“row count is ${data.getRowNumbers()}”)

for (row = 1; row <= data.getRowNumbers(); row++) {

xpath = 'id("P15_CUSTOM_CODE")'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem)
WebUI.setText(myItem, data.getValue(1, row))

xpath = 'id("P15_BILL_NUMBER")'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem)
WebUI.setText(myItem, data.getValue(2, row))

xpath = 'id("P15_BILL_DATE|input")'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem)
WebUI.setText(myItem, data.getValue(3, row))

xpath = 'id("P15_LC_ID")'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem)
WebUI.setText(myItem, data.getValue(4, row))

xpath = 'id("P15_FC_TOT")'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem)
WebUI.setText(myItem, data.getValue(5, row))


    xpath = '//table[@id='R33588865397933856']/tbody/tr/td[2]/a[2]'
myItem = new TestObject(xpath)
myItem.addProperty("xpath", ConditionType.EQUALS, xpath)
WebUI.verifyElementVisible(myItem).click();

}

BUT it is not working. Can you help by identifying were I need to add something more.

OR Should i use “WebUI.click(findTestObject(‘Object Repository/TEST NEW/Page_Bill of Entry/a_Create’))”

sanjoy2016ewu · February 15, 2023, 11:30am

@grylion54 please help me

grylion54 · February 15, 2023, 2:55pm

The above statement won’t work. You will need to make two statements.

WebUI.verifyElementVisible(myItem)
WebUI.click(myItem)

Saying something “doesn’t work” limits us on the forum. There are ALL SORTS of things that can go wrong with coding. So, what is the something that is wrong. Do you have an error message? Do you have it not working as you want? What is wrong would be a good place to start.

And you can make objects for the Object Repository from the items that are in the loop. That would just make it all more consistent. Creating objects from the id that I list would be a good idea to use as an xpath.

Again, you know what you have to click and fill in order for your web page to run manually, so try to get your automation to do the same! You can remove the for loop if you want to get it to do the page at least once.

sanjoy2016ewu · February 16, 2023, 10:10am

@grylion54 Please help me to solve the problem i am facing here.
The codes i am using here can paste data from excel to WEB
1 - Copy.txt (2.8 KB)
2 - Copy.txt (2.9 KB)
FINAL.html (26.7 KB)
Box. But completing pasting all the data in row one i need to click on “CREATE” button. in this area I am facing problem. I am sharing my codes with you.

Boss help me @grylion54

grylion54 · February 16, 2023, 4:17pm

Sorry, but I see you have your code with a Create “button”, so what is the problem? Maybe you just need to put in some “wait” statement to ensure the form and elements are available.

WebUI.click(findTestObject('Create Click/Page_Bill of Entry/a_Create'))
WebUI.waitForPageLoad(10)

What is the pathway you are using for the “Create” hyperlink. I can give you either of two that should work:

//td[text()="Bill of Entry Reporting"]/following-sibling::td[1]/a[2]

or

//a[text()="Create"]

Again, I stress you need to imitate the automation as you would fill out the form manually. Spend some time and create some Objects that you can use to replace the TestObjects that I “create in code”. If this is a school project like I think it is, then it will look very suspicious that you are creating TestObjects in code.

Manage Web Test Objects in Katalon Studio | Katalon Docs