Swapnil Swapnil - 1 year ago 70
Scala Question

Gatling :- scala Read JSON object from excel file

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. Added gradle dependencies in build.gradle file

dependencies {
//  compile ('commons-collections:commons-collections:3.2')
    compile ('org.scala-lang:scala-library:2.11.8')
    compile ('org.scala-lang:scala-compiler:2.11.8')
    compile ('info.folone:poi-scala_2.11:0.14')

And scala code is

package mypackage

import java.io.FileInputStream

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.xssf.usermodel.XSSFWorkbook

class ExcelUtils {

//  private var cellData: String = null;

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

    val fileExtensionName = path.substring(path.indexOf("."));
    var cellData: String = null;

    if (fileExtensionName.equals(".xlsx")) {
      val excelWorkBookXlxs = new XSSFWorkbook(excelFileInputStream);
      val excelWorkSheetXlxs = excelWorkBookXlxs.getSheet(sheetName);
      val cell = excelWorkSheetXlxs.getRow(rowNum).getCell(colNum);
      cellData = cell.getStringCellValue();
    } else if (fileExtensionName.equals(".xls")) {
      val excelWorkBookXls = new HSSFWorkbook(excelFileInputStream);
      val excelWorkSheetXls = excelWorkBookXls.getSheet(sheetName);
      val cell = excelWorkSheetXls.getRow(rowNum).getCell(colNum);
      cellData = cell.getStringCellValue();
    return cellData;