Swapnil Swapnil - 1 year ago 167
Java Question

Gatling :- scala Read JSON object from xls file and write response back to it

I want to read requestPayLoad Json object from some my.xls file and also want to write response back to it. How can I go for it.

I'm very new to scala, so Code spinet will help.

I'm referring this library but it's not working


I wrote java code as below, but not able to simulate same code in the scala

public class ExcelUtils {

private static Sheet excelWorkSheet;
private static Workbook excelWorkBook;
private static Cell cell;

public static void setExcelFile(String path, String sheetName) throws Exception {
InputStream excelFileInputStream = new FileInputStream(path);

String fileExtensionName = path.substring(path.indexOf("."));

if (fileExtensionName.equals(".xlsx")) {
excelWorkBook = new XSSFWorkbook(excelFileInputStream);
} else if(fileExtensionName.equals(".xls")) {
excelWorkBook = new HSSFWorkbook(excelFileInputStream);

excelWorkSheet = excelWorkBook.getSheet(sheetName);

public static String getCellData(int rowNum, int colNum) throws Exception {
cell = excelWorkSheet.getRow(rowNum).getCell(colNum);
String cellData = cell.getStringCellValue();
return cellData;

public static Sheet getExcelWorkSheet() throws IOException {
return excelWorkSheet;

Answer Source

Finally I'm able to sort it out.

package mypackage

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.hssf.usermodel.HSSFSheet

class ExcelUtils {

   private var excelWorkSheetXlxs: Sheet = null;
   private var excelWorkBookXlxs: XSSFWorkbook = null;

   private var excelWorkSheetXls: Sheet = null;
   private var excelWorkBookXls: HSSFWorkbook = null;
   private var cell: Cell = null;

    def setExcelFile(path:String, sheetName:String, rowNum:Int, colNum:Int) : String = {
        val excelFileInputStream = new FileInputStream(path);

        val fileExtensionName = path.substring(path.indexOf("."));

        if (fileExtensionName.equals(".xlsx")) {
            excelWorkBookXlxs = new XSSFWorkbook(excelFileInputStream);
            excelWorkSheetXlxs = excelWorkBookXlxs.getSheet(sheetName);
        } else if(fileExtensionName.equals(".xls")) {
            excelWorkBookXls = new HSSFWorkbook(excelFileInputStream);
            excelWorkSheetXls = excelWorkBookXls.getSheet(sheetName);

    def getCellData(rowNum:Int, colNum:Int): String = {
        cell = excelWorkSheetXlxs.getRow(rowNum).getCell(colNum);
        val cellData = cell.getStringCellValue();
        return cellData;

    def getExcelWorkSheet(): Sheet = {
        return excelWorkSheetXlxs;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download