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

**URL:** <https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955>\
**Category:** Web Testing\
**Tags:** katalon-studio\
**Created:** [March 12, 2019, 7:02am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955 "2019-03-12T07:02:44Z")\
**Posts on this page:** 10\
**Page:** 2

<div class="post-metadata">

**Author:** ![Timo\_Kuisma1](https://dub1.discourse-cdn.com/katalon/user_avatar/forum.katalon.com/timo_kuisma1/32/16103_2.png) [@Timo\_Kuisma1](https://forum.katalon.com/u/Timo_Kuisma1)\
**Post date:** [March 17, 2019, 9:38pm UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/23 "2019-03-17T21:38:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![teja3535](https://avatars.discourse-cdn.com/v4/letter/t/7feea3/32.png) [@teja3535](https://forum.katalon.com/u/teja3535)\
**Post date:** [March 17, 2019, 9:40pm UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/24 "2019-03-17T21:40:51Z")

</div>

In Test Case u had mentioned

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

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

Thanqqq

---

<div class="post-metadata">

**Author:** ![teja3535](https://avatars.discourse-cdn.com/v4/letter/t/7feea3/32.png) [@teja3535](https://forum.katalon.com/u/teja3535)\
**Post date:** [March 17, 2019, 10:14pm UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/25 "2019-03-17T22:14:35Z")

</div>

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/](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)

---

<div class="post-metadata">

**Author:** ![Timo\_Kuisma2](https://avatars.discourse-cdn.com/v4/letter/t/45deac/32.png) [@Timo\_Kuisma2](https://forum.katalon.com/u/Timo_Kuisma2)\
**Post date:** [March 18, 2019, 5:12am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/26 "2019-03-18T05:12:41Z")

</div>

hello

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

```auto
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)

---

<div class="post-metadata">

**Author:** ![teja3535](https://avatars.discourse-cdn.com/v4/letter/t/7feea3/32.png) [@teja3535](https://forum.katalon.com/u/teja3535)\
**Post date:** [March 18, 2019, 5:36am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/27 "2019-03-18T05:36:34Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![Timo\_Kuisma2](https://avatars.discourse-cdn.com/v4/letter/t/45deac/32.png) [@Timo\_Kuisma2](https://forum.katalon.com/u/Timo_Kuisma2)\
**Post date:** [March 18, 2019, 5:45am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/28 "2019-03-18T05:45:50Z")

</div>

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
````

---

<div class="post-metadata">

**Author:** ![teja3535](https://avatars.discourse-cdn.com/v4/letter/t/7feea3/32.png) [@teja3535](https://forum.katalon.com/u/teja3535)\
**Post date:** [March 18, 2019, 5:49am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/29 "2019-03-18T05:49:32Z")

</div>

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 …??

---

<div class="post-metadata">

**Author:** ![Timo\_Kuisma2](https://avatars.discourse-cdn.com/v4/letter/t/45deac/32.png) [@Timo\_Kuisma2](https://forum.katalon.com/u/Timo_Kuisma2)\
**Post date:** [March 18, 2019, 6:05am UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/30 "2019-03-18T06:05:05Z")

</div>

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 🙂

---

<div class="post-metadata">

**Author:** ![teja3535](https://avatars.discourse-cdn.com/v4/letter/t/7feea3/32.png) [@teja3535](https://forum.katalon.com/u/teja3535)\
**Post date:** [March 19, 2019, 6:11pm UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/31 "2019-03-19T18:11:37Z")

</div>

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](https://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 3K

 ![Katalon_DDF_Issue_closed](https://europe1.discourse-cdn.com/katalon/original/2X/9/9606d15bd2da0ac0b9b709a13102d8cef57e6b70.jpeg)

All the Best those who r trying this scenario …

Thanq…  
With Regards,  
Mikky…

---

<div class="post-metadata">

**Author:** ![Timo\_Kuisma](https://avatars.discourse-cdn.com/v4/letter/t/8baadc/32.png) [@Timo\_Kuisma](https://forum.katalon.com/u/Timo_Kuisma)\
**Post date:** [March 19, 2019, 6:51pm UTC](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955/32 "2019-03-19T18:51:35Z")

</div>

Good boy 🕶

[Previous page](https://forum.katalon.com/t/can-read-the-data-from-excel-but-cannot-able-to-write-the-data-in-same-excel/20955.md?page=1)
