Can Read the data from Excel But Cannot able to write the data in Same Excel

Read my example again, there are separated test case and keyword phase

In Test Case u had mentioned

CustomKeywords.‘spreadsheet.WriteToFile.updateNumberValueXlsx’(hmap, 0, 2)

Can i know what is (hmap, 0, 2)

Thanqqq

and the Test Case looks like this …

initially i am trying for 2 sets of data only(2 Iterations) with the Example Katalon Login.

WebUI.openBrowser(’’)

WebUI.navigateToUrl(‘https://www.katalon.com/’)

WebUI.maximizeWindow()

long x = 1234567

long y = 23456789

HashMap<String, String> hmap = new HashMap<String, String>()

for (int l = 0; l <1 ; l++) {
	val = (r.nextInt(high - low) + low)

	long number = x + ((r.nextDouble() * (y - x)) as long)

	hmap.put(String.valueOf(number), String.valueOf(val))
}

‘Click on Login Button’
WebUI.click(findTestObject(‘SampleProjects/Katalon_Login/Page_Katalon Studio Simplify API We/a_Login’))

WebUI.delay(2)

WebUI.setText(findTestObject(‘SampleProjects/Katalon_Login/Page_Sign in Katalon Studio/input_Enter your details below’),
UserName)

WebUI.setText(findTestObject(‘SampleProjects/Katalon_Login/Page_Sign in Katalon Studio/input_Enter your details below_1’),
Passw)

String AccountNo = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_Sign in Katalon Studio/Page_Sign in Katalon Studio_01/div_Enter your details below’))

System.out.println(AccountNo) //Output will be “Enter your details below”

CustomKeywords.‘myPack.SampleExcel.updateNumberValueXlsx’(findTestObject(null), null, null)

WebUI.click(findTestObject(‘SampleProjects/Katalon_Login/Page_Sign in Katalon Studio/input_Remember me_login-btn’))

WebUI.delay(5)

WebUI.click(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/a_Company’))

String AccountNo2 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_3’))

System.out.println(AccountNo2) ////Output will be 3

CustomKeywords.‘myPack.SampleExcel.updateNumberValueXlsx’(findTestObject(null), null, null)

String AccountNo3 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_28K’))

System.out.println(AccountNo3) ////Output will be 140

String AccountNo4 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_140’))

System.out.println(AccountNo4) ////Output will be 28

CustomKeywords.‘myPack.SampleExcel.updateNumberValueXlsx’(findTestObject(null), null, null)

hello

CustomKeywords.‘spreadsheet.WriteToFile.updateNumberValueXlsx’(hmap, 0, 2)
hmap have account numbers & amount values pair (“123456789”, “12345”)

hmap.put(String.valueOf(<account number>),String.valueOf(<amount value>))

and 0 is column number where account numbers are in excel & 2 column where amount values are.

this is odd
“CustomKeywords.‘myPack.SampleExcel.updateNumberValueXlsx’(findTestObject(null), null, null)”
should be
CustomKeywords.‘spreadsheet.WriteToFile.updateNumberValueXlsx’(hmap, 0, 2)

Hi Timo,

Something Tricky …cannot understand

the below s the code …correct me if i am wrong

String AccountNo2 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_3’))

System.out.println(AccountNo2) ////Output will be 10

something like this …???
CustomKeywords.‘spreadsheet.WriteToFile.updateNumberValueXlsx’(AccountNo2, 0, 3)

Where 3 is the column number
AccountNo2 in the column name

Thanq…

hi,

NOTE! my code is only example how to update excel sheets data
```
put values to the Map

hmap.put("AccountNo","1500")
hmap.put("AccountNo2","2500")
```
CustomKeywords.‘spreadsheet.WriteToFile.updateNumberValueXlsx’(hmap, <column number in excel account number>>, <column number in excel amount >)

google, how to use Java Map

ok thanqq…

ur code combines both AccountNo and AccountNo2.

but i will get account number Value at line number 30 and AccountNo2 value at line number 150.

how to perform …??

hi,

first run my code without any WebUI elements, then you are able to see how it’s working.
I think after that you can modify it to your needs.
Again it’s only example not your solution :slight_smile:

And Finally Got the Solution …

A special Thanks to Timo and Ali for their Guidance …

Issue:Can Read the data from Excel But Cannot able to write the data in Same Excel

Framework: Data Driven approach

OS: Windows 10

Approach :Web based

Below is the code…

–>Creation of Custom Key Word

package myPack
import static com.kms.katalon.core.checkpoint.CheckpointFactory.findCheckpoint
import static com.kms.katalon.core.testcase.TestCaseFactory.findTestCase
import static com.kms.katalon.core.testdata.TestDataFactory.findTestData
import static com.kms.katalon.core.testobject.ObjectRepository.findTestObject
import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.checkpoint.Checkpoint
import com.kms.katalon.core.checkpoint.CheckpointFactory
import com.kms.katalon.core.mobile.keyword.MobileBuiltInKeywords
import com.kms.katalon.core.model.FailureHandling
import com.kms.katalon.core.testcase.TestCase
import com.kms.katalon.core.testcase.TestCaseFactory
import com.kms.katalon.core.testdata.TestData
import com.kms.katalon.core.testdata.TestDataFactory
import com.kms.katalon.core.testobject.ObjectRepository
import com.kms.katalon.core.testobject.TestObject
import com.kms.katalon.core.webservice.keyword.WSBuiltInKeywords
import com.kms.katalon.core.webui.keyword.WebUiBuiltInKeywords
import internal.GlobalVariable
import MobileBuiltInKeywords as Mobile
import WSBuiltInKeywords as WS
import WebUiBuiltInKeywords as WebUI
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFCellStyle
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class WriteExcel{

private  static int i=0;

@Keyword
public static void demoKey(String name,String Column_Name) throws IOException{
	FileInputStream fis = new FileInputStream("D:\\Katalon Studio\\TestData\\Katalon_DDF.xlsx");
	XSSFWorkbook workbook = new XSSFWorkbook(fis);
	XSSFSheet sheet = workbook.getSheet("Sheet1");
	int rowCount = i;
	if (Column_Name=='Credential1'){
		Row row = sheet.getRow(rowCount+1);
		Cell cell = row.createCell(2,0);
		cell.setCellType(cell.CELL_TYPE_STRING);
		cell.setCellValue(name);
	}

	if (Column_Name=='Credential2'){
		Row row2 = sheet.getRow(rowCount+1);
		Cell cell2 = row2.createCell(3,0);
		cell2.setCellType(cell2.CELL_TYPE_STRING);
		cell2.setCellValue(name);
                    i = rowCount+1;
	}

FileOutputStream fos = new FileOutputStream("D:\\Katalon 
   Studio\\TestData\\Katalon_DDF.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Test Case Code :

String Credential1 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_Sign in Katalon Studio/Page_Sign in Katalon Studio_01/div_Enter your details below’))

CustomKeywords.‘myPack.WriteExcel.demoKey’(Credential1,‘Credential1’)

System.out.println(Credential1) //Output will be “Enter your details below”

//WebUI.scrollToElement(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/a_Company’,5))

WebUI.click(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/a_Company’))

String Credential2 = WebUI.getText(findTestObject(‘Object Repository/SampleProjects/Katalon_DDF/Page_About Us Katalon Studio_DDF_READ_Write/div_3’))

CustomKeywords.‘myPack.WriteExcel.demoKey’(Credential2,‘Credential2’)

System.out.println(Credential2)//Output will be 3KKatalon_DDF_Issue_closed

All the Best those who r trying this scenario …

Thanq…
With Regards,
Mikky…

Good boy :sunglasses: