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


#1

Hi Friends,

Thank you in Advance

Kindly Resolve the Following Issue

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

Steps:

1)I had created 4 columns in the excel file out of which 2 columns need to read the data from excel and paste in the application (Which are working) and other 2 columns(Amount column and Profit column) in Excel File need to copy the data from web application and paste in the Same Excel File and need to repeat for 4 rows or till the iterations completed(Which is not working).

If u have the code kindly suggest as my code is wrong…

Note :Attached the Excel file screen shot for ur reference

Kat_DDF_Issue


#2

please share the relevant code - than we can check in the community


#3

Hi Masdesign,

Thanq for ur response.

i tried with the new code , with that code i can able write the data in excel but data in not displaying in correct Column

Attached Screenshot for ur reference

Code for ur reference

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.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcel {

@Keyword
public void demoKey(String 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 = sheet.getLastRowNum()-sheet.getFirstRowNum();
	Row row = sheet.createRow(rowCount+1);
	Cell cell = row.createCell(0);
	cell.setCellType(cell.CELL_TYPE_STRING);
	cell.setCellValue(name);
	FileOutputStream fos = new FileOutputStream("D:\\Katalon Studio\\TestData\\Katalon_DDF.xlsx");
	workbook.write(fos);
	fos.close();
}

}

Thanq

With Regards,
Mikky…!


#4

Hi Katalon Team and Friends,

Can anyone able to resolve my Above issue …

Awaiting for your Early Reply …

Thanq…

With Regards,
Mikky…!


#5

Hi,

your code not handle columns at all only rows are used

Test Case
List <String> excel = new ArrayList <String>();
excel.add("First");
excel.add("Second");
excel.add("Third");
excel.add("Fourth");
excel.add("Fifth");
excel.add("");
CustomKeywords.'spreadsheet.WriteToFile.writeToExcel'(excel, 1)

	@Keyword
	public void writeToExcel(List <String> excel, int rows){

		try {
			//create .xls and create a worksheet.
			FileOutputStream fos = new FileOutputStream("C:\\Users\\xxxx\\Desktop\\data\\data2excel2.xls");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet("SheetName");
			HSSFRow row;
			HSSFCell cell;
			HSSFCellStyle cellStyle;
			for (int i = 0;i < rows; i++){
				row = worksheet.createRow((short) i);
				for(int y = 0; y < excel.size(); y++){
					cell = row.createCell((short) y);
					cell.setCellValue(excel[y]);
					cellStyle = workbook.createCellStyle();
					cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
					cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
					cell.setCellStyle(cellStyle);
				}
			}
			//Save the workbook in .xls file
			workbook.write(fos);
			fos.flush();
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

#6

Hi Timo,

Thanq for your resposne…

Ur code states that it is creating a new workbook and Spreadsheet with the creation of new columns First ,Second ,Third… etc

when i use ur code my current data sheet is completely deleted and it is creating new columns as stated above…

My Requirement:

1)I had created 4 columns in the excel file out of which 2 columns need to read the data from excel and paste in the application (Which are working) and other 2 columns(Amount column and Profit column) in Excel File need to copy the data from web application and paste in the Same Excel File and need to repeat for 4 rows or till the iterations completed(Which is not working).

Thanq for your Help…

Note: Refer to the below screen shot for ur code
Kat_DDF_Issue02

Thanq ,

With Regards,
Mikky…!


#7

Hello,

My code is only example where you will start to improve your code.
It's show how to handle columns too.
Do not use it as it is written.
set here "true" as it will append file not overwrite it
FileOutputStream fos = new FileOutputStream("C:\\Users\\xxxx\\Desktop\\data\\data2excel2.xls", true);

#8

hello,

this will update column values but not check if cell is null

List <String> excel2 = new ArrayList <String>();
Random r = new Random();
int low = 100;
int high = 999;
int result = 0
for (int i = 0;i < 4; i++){
	result = r.nextInt(high-low) + low;
	excel2.add(String.valueOf(result))
	
}

CustomKeywords.'spreadsheet.WriteToFile.updateExcelFile'(excel2, 2)

	@Keyword
	public void updateExcelFile(List<String> excel, colIndex){
		FileInputStream fsIP= new FileInputStream(new File("C:\\Users\\xxxx\\Desktop\\data\\data3excel2.xls")); //Read the spreadsheet that needs to be updated
                  
                HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook                
                HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
				                 
                Cell cell = null; // declare a Cell object
				
				int ro = 1 
				for (String s : excel){
					cell = worksheet.getRow(ro).getCell(colIndex);//no null check
					worksheet.createRow(ro).createCell(colIndex).setCellValue(s)
					ro++
				}
                 
                fsIP.close(); //Close the InputStream               
                FileOutputStream output_file =new FileOutputStream(new File("C:\\Users\\fitim\\Desktop\\data\\data3excel2.xls"));  //Open FileOutputStream to write updates                
                wb.write(output_file); //write changes           
	}

#9

Hi,

ok this will check if cell is null

private static HSSFSheet ExcelWSheetH;
private static HSSFWorkbook ExcelWBookH;
private static HSSFCell eCellH;
private static HSSFRow eRowH;

List <String> excel2 = new ArrayList <String>();
Random r = new Random();
int low = 100;
int high = 999;
int result = 0
for (int i = 0;i < 9; i++){
	result = r.nextInt(high-low) + low;
	excel2.add(String.valueOf(result))
	
}
CustomKeywords.'spreadsheet.WriteToFile.updateExcelFile2'(excel2, 2)


@Keyword
public void updateExcelFile2(List<String> excel, colIndex){
	FileInputStream fsIP= new FileInputStream(new File("C:\\Users\\fitim\\Desktop\\data\\xlsData.xls")); //Read the spreadsheet that needs to be updated
	
	ExcelWBookH = new HSSFWorkbook (fsIP); //Access the workbook
	ExcelWSheetH = ExcelWBookH.getSheetAt(0); //Access the worksheet, so that we can update / modify it.		  
	
	int ro = 1
	for (String s : excel){
			
		eRowH = ExcelWSheetH.getRow(ro);
			
		eCellH = eRowH.getCell(colIndex, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		if (eCellH == null) {
			eCellH = eRowH.createCell(colIndex);
			eCellH.setCellValue(s);
		} else {
			eCellH.setCellValue(s);
		}
		ro++
	}
			 
	fsIP.close(); //Close the InputStream
	FileOutputStream output_file =new FileOutputStream(new File("C:\\Users\\fitim\\Desktop\\data\\xlsData.xls"));  //Open FileOutputStream to write updates
	ExcelWBookH.write(output_file); //write changes
}

#10

Hello Timo,

thanq for your code …But i think we both are not on the same page … kindly review my question once again …i am explaining Elaborately …

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

Framework: Data Driven approach

Procedure:
a)I am having 10 columns to read the data which can read and paste successfully in web Application.
b) i need to get AccountNumber and amount from the web page and paste in the same excel .GetText Method i had used which is working .

c)i had created AccountNumber and Amount columns in the same excel .now i need to paste the AccountNumber(11354874845) and Amount(10000) in the Accountnumber Cells and AmountCells.

d)i am having 10 rows of data.so i will get 10 Account numbers which should paste in AccountNumber Column and 10 amounts which should paste in for Amount Column.

My attached Screenshot will give u an idea…

Hope you understand my Issue…

Note:I am a mid level programmer .

if u have the code u can share it.

Thanq

With Regards,
Mikky…


#11

whaaat you will mean “not on the same page”

I am not resolving your issue, it’s your job
My code will update column data (e.g. Amount column).
Note this is only example how to update column values in a excel file!
You can modify my example as you will need it


#12

Hi Timo,

Will try ur code …thanq …

Actually its a framework issue …i had farword this issue to Katalon Team also .

Lets see …

Thanq for ur time …

with Regards,
Mikky…


#13
Okay, this will do so,
update 10 numbers in AccountNumber & values Amount columns

Read your AccountNumber and amount from the web to the Map

private static HSSFCell eCellNumbers;
private static HSSFCell eCellValues;

long x = 1234567L;
long y = 23456789L;

//CustomKeywords.'spreadsheet.WriteToFile.updateExcelFile2'(excel2, 2)
HashMap<String, String> hmap = new HashMap<String, String>();

for (int l = 0; l < 10; l++){
	val = r.nextInt(high-low) + low;
	long number = x+((long)(r.nextDouble()*(y-x)));
	hmap.put(String.valueOf(number),String.valueOf(val))
}
CustomKeywords.'spreadsheet.WriteToFile.updateNumberValue'(hmap, 0, 2)

@Keyword
public void updateNumberValue(HashMap<String, String> hmap, colIndexNumbers, colIndexValues){
	FileInputStream fsIP= new FileInputStream(new File("C:\\Users\\xxxx\\Desktop\\data\\xlsData.xls")); //Read the spreadsheet that needs to be updated
		
	ExcelWBookH = new HSSFWorkbook (fsIP); //Access the workbook
	ExcelWSheetH = ExcelWBookH.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
		
	int ro = 1
	Set set = hmap.entrySet();
	Iterator iterator = set.iterator();
	while(iterator.hasNext()){
		Map.Entry mentry = (Map.Entry)iterator.next();
		eRowH = ExcelWSheetH.getRow(ro);
		
		eCellNumbers = eRowH.getCell(colIndexNumbers, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		eCellValues = eRowH.getCell(colIndexValues, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		if (eCellNumbers == null || eCellValues == null) {
			eCellNumbers = eRowH.createCell(colIndexNumbers);
			eCellValues = eRowH.createCell(colIndexValues);
			eCellNumbers.setCellValue(mentry.getKey());
			eCellValues.setCellValue(mentry.getValue());
		} else {
			eCellNumbers.setCellValue(mentry.getKey());
			eCellValues.setCellValue(mentry.getValue());
		}
		ro++
	}
			 
	fsIP.close(); //Close the InputStream
	FileOutputStream output_file =new FileOutputStream(new File("C:\\Users\\xxxx\\Desktop\\data\\xlsData.xls"));  //Open FileOutputStream to write updates
	ExcelWBookH.write(output_file); //write changes
}

excel


#14

(post withdrawn by author, will be automatically deleted in 24 hours unless flagged)


#15

Hello

use these imports then (used for .xlsx)
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

instead of (used for .xls)

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

#16

Let me try and will get back to you …

Thanq…


#17

yeah

Test case
long x = 1234567L;
long y = 23456789L;

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

for (int l = 0; l < 10; l++){
	val = r.nextInt(high-low) + low;
	long number = x+((long)(r.nextDouble()*(y-x)));
	hmap.put(String.valueOf(number),String.valueOf(val))
}
CustomKeywords.'spreadsheet.WriteToFile.updateNumberValueXlsx'(hmap, 0, 2)

Custom keyword class

public class some_class{

private static XSSFSheet ExcelWSheetX;
private static XSSFWorkbook ExcelWBookX;
private static XSSFCell eCellNumbersX;
private static XSSFCell eCellValuesX;
private static XSSFRow eRowX;

@Keyword
public void updateNumberValueXlsx(HashMap<String, String> hmap, colIndexNumbers, colIndexValues)throws FileNotFoundException, IOException, InvalidFormatException{
	FileInputStream fsIP= new FileInputStream(new File("C:\\Users\\xxxx\\Desktop\\data\\file_example_XLSX_10.xlsx")); //Read the spreadsheet that needs to be updated
		
	ExcelWBookX = new XSSFWorkbook (fsIP); //Access the workbook
	ExcelWSheetX = ExcelWBookX.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
		
	int ro = 1
	Set set = hmap.entrySet();
	Iterator iterator = set.iterator();
	while(iterator.hasNext()){
		Map.Entry mentry = (Map.Entry)iterator.next();
		eRowX = ExcelWSheetX.getRow(ro);
			
		eCellNumbersX = eRowX.getCell(colIndexNumbers, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		eCellValuesX = eRowX.getCell(colIndexValues, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		if (eCellNumbersX == null || eCellValuesX == null) {
			eCellNumbersX = eRowX.createCell(colIndexNumbers);
			eCellValuesX = eRowX.createCell(colIndexValues);
			eCellNumbersX.setCellValue(mentry.getKey());
			eCellValuesX.setCellValue(mentry.getValue());
		} else {
			eCellNumbersX.setCellValue(mentry.getKey());
			eCellValuesX.setCellValue(mentry.getValue());
		}
		ro++
	}
		 
		fsIP.close(); //Close the InputStream
		FileOutputStream output_file =new FileOutputStream(new File("C:\\Users\\xxxx\\Desktop\\data\\file_example_XLSX_10.xlsx"));  //Open FileOutputStream to write updates
		ExcelWBookX.write(output_file); //write changes
	}
}

#18

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.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
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 SampleExcel{

@Keyword
public void updateNumberValue(HashMap<String, String> hmap, colIndexNumbers, colIndexValues){
	FileInputStream fsIP= new FileInputStream(new File("D:\\Katalon Studio\\TestData\\Katalon_DDF.xlsx")); //Read the spreadsheet that needs to be updated

	XSSFWorkbook wb= new XSSFWorkbook (fsIP); //Access the workbook
	XSSFSheet sheet1 = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it.

	int ro = 1
	Set set = hmap.entrySet();
	Iterator iterator = set.iterator();
	while(iterator.hasNext()){
		Map.Entry mentry = (Map.Entry)iterator.next();
		def eRowH,eCellNumbers,eCellValues;//Declaration
		eRowH = sheet1.getRow(ro);

		eCellNumbers = eRowH.getCell(colIndexNumbers, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		eCellValues = eRowH.getCell(colIndexValues, Row.RETURN_BLANK_AS_NULL);//check if cell is null
		if (eCellNumbers == null || eCellValues == null) {
			eCellNumbers = eRowH.createCell(colIndexNumbers);
			eCellValues = eRowH.createCell(colIndexValues);
			eCellNumbers.setCellValue(mentry.getKey());
			eCellValues.setCellValue(mentry.getValue());
		} else {
			eCellNumbers.setCellValue(mentry.getKey());
			eCellValues.setCellValue(mentry.getValue());
		}
		ro++
	}

	fsIP.close(); //Close the InputStream
	FileOutputStream output_file =new FileOutputStream(new File("D:\\Katalon Studio\\TestData\\Katalon_DDF.xlsx"));  //Open FileOutputStream to write updates
	sheet1.write(output_file); //write changes
}

}

since it showing some errors …i had modified the code …kindly check once …


#19

ugh,

you are still using .xls
you will get lot of errors, there are used both excel formation .xls & xlsx in your code


#20

I am using xlsx