Read and write data from excel in katalon studio

Read and write data from excel in katalon studio or you can say data-driven testing in the katalon studio.In this tutorial, I am going to show you how to do data driven testing using katalon studio.

Read Data from excel file in katalon studio

 Step 1: create a new data file(excel) example “demo.xlsx”.

Step 2: And enter some data into that file.

Step 3: Create a new data file in katalon studio.

Step 4: And browse your external file and select the sheet.

Step 5: Create test case and define some variable in the test case.

Step 6: Now create a TestSuite And configure a test case. And bind the variables with the column name. If you stuck in between please follow the video tutorial for the same.

Step 6: Execute your TestSuite.

Write Data from excel file in katalon studio.

 Katalon team still not provides any built-in keyword to write data in the external file so we need to use the custom keyword here by using POI library.

How to create custom keyword in katalon studio. Please follow the tutorial.

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("E:\\ExcelSheets\\Demo1.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("E:\\ExcelSheets\\Demo1.xlsx");
    workbook.write(fos);
    fos.close();
    
  }

}

Call that keyword inside your test case and pass the data it will store the data in targeting file.

WebUI.openBrowser('')
WebUI.navigateToUrl('http://demoaut.katalon.com/')
String result = WebUI.getText(findTestObject('Object Repository/Page_CURA Healthcare Service/a_Make Appointment'))
CustomKeywords.'myPack.WriteExcel.demoKey'(result)
WebUI.closeBrowser()

For more clarity, you can watch the video tutorial.

6 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

2 + 19 =