I found nullPointerException during writing data in Excel using same code as java selenium…
i.e java.lang.NullPointerException: Cannot invoke method createCell() on null object
I found some solution like during writing data in excel we need to put data in that cell in which we creating cell then only it overwrite and write data in other cell in that row only.
This code is suitable to write data into xlsx file.
_ String excelFileName = “C:\\results.xls”;_
_ File src = new File(excelFileName);_
_ FileInputStream fis = new FileInputStream(src);_
_ HSSFWorkbook wb = new HSSFWorkbook(fis);_
_ HSSFSheet sheet = wb.getSheetAt(0);_
_ HSSFRow row = sheet.createRow(recordcount-1);_
_ WebUI.delay(2)_
_ HSSFCell cell = row.createCell(0);_
_ cell.setCellValue(file.getName());_
_ cell = row.createCell(1);_
_ cell.setCellValue(status);_
_ cell = row.createCell(2);_
_ cell.setCellValue(page);_
_ WebUI.delay(2)_
_ FileOutputStream fileOut = new FileOutputStream(excelFileName);_
_ wb.write(fileOut);_
_ fileOut.flush();_
_ fileOut.close();_
plaidshirtakos said:
This code is suitable to write data into xlsx file.
_ String excelFileName = “C:\\results.xls”;_
_ File src = new File(excelFileName);_
_ FileInputStream fis = new FileInputStream(src);_
_ HSSFWorkbook wb = new HSSFWorkbook(fis);_
_ HSSFSheet sheet = wb.getSheetAt(0);_
_ HSSFRow row = sheet.createRow(recordcount-1);_
_ WebUI.delay(2)_
_ HSSFCell cell = row.createCell(0);_
_ cell.setCellValue(file.getName());_
_ cell = row.createCell(1);_
_ cell.setCellValue(status);_
_ cell = row.createCell(2);_
_ cell.setCellValue(page);_
_ WebUI.delay(2)_
_ FileOutputStream fileOut = new FileOutputStream(excelFileName);_
_ wb.write(fileOut);_
_ fileOut.flush();_
_ fileOut.close();_
we just write the plain code in the script of our test case or do we need to create a keyword, i have tried many keywords out there but all of them clean my whole spreadsheet and just write the new text katalon send.
Why would you like to create new keyword? You can simply put that code to your test case.
Hi,
one way to write to excel 
TESTCASE
List excel = new ArrayList ();
excel.add(“First”);
excel.add(“Second”);
excel.add(“Third”);
excel.add(“Fourth”);
excel.add(“Fifth”);
excel.add("");
CustomKeywords.‘spreadsheet.WriteToFile.writeToExcel’(excel, 1)
KEYWORD
import org.apache.poi.ss.usermodel.Workbook
import com.kms.katalon.core.annotation.Keyword
import bsh.org.objectweb.asm.Label
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.hssf.util.HSSFColor;
public void writeToExcel(List <String> excel, int rows){
try {
//create .xls and create a worksheet.
FileOutputStream fos = new FileOutputStream("C:\\Users\\xxx\\Desktop\\data\\data2excel.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();
}
}