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
please share the relevant code - than we can check in the community
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…!
1 Like
Hi Katalon Team and Friends,
Can anyone able to resolve my Above issue …
Awaiting for your Early Reply …
Thanq…
With Regards,
Mikky…!
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();
}
}
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
Thanq ,
With Regards,
Mikky…!
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);
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
}
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
}
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…
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
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…
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
}
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;
Let me try and will get back to you …
Thanq…
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
}
}
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 …
ugh,
you are still using .xls
you will get lot of errors, there are used both excel formation .xls & xlsx in your code
