Write to Excel Keyword example


#1

Here is something I got based upon online research and modified to work in Katalon. I did not write the original. Obviously file path should be replaced and you’ll have to modify to do additional cells. Still should be a good starting point.

package writeToFile

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;
import java.text.*
import filePath.FilePath


/*
 * Author: B. L. 
 * reference https://www.geeksforgeeks.org/reading-writing-data-excel-file-using-apache-poi/
 * Last Modified: 01/04/2019
 * Last Modified By: B. L.
 * Purpose: This writes PO number to a spreedsheet, used for data generation
 *
 */



public class WritePOToExcel {


	@Keyword
	String FilePath = FilePath.getFilePath()
	public void demoKey(String load, String test, String username) throws IOException{
		FileInputStream fis = new FileInputStream(iTMSFilePath + "FILENAMEHERE.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(load);
		cell = row.createCell(1);
		cell.setCellValue(test);
		cell = row.createCell(2);
		cell.setCellValue(username);
		String timeStamp = new SimpleDateFormat("yyyy.MM.dd.HH.mm.ss").format(new java.util.Date());
		cell = row.createCell(3);
		cell.setCellValue(timeStamp);
		FileOutputStream fos = new FileOutputStream(iTMSFilePath + "\\FILENAMEHERE.xlsx");
		workbook.write(fos);
		fos.close();
	}