How to call excel sheet headers

Product Forums Katalon Studio
,
1

Hi
I added a custome key to get the excel file , sheet name , row indexx and column index :
> @Keyword

	//###### read data from excel sheet in files file , fileName should be in filename.xlsx format
	def readDataFromExcel(String fileName,int sheetIndex, int rowNum, int cellNum){

		String pathToApp = RunConfiguration.getProjectDir() + ("\\files\\")+(fileName)
		FileInputStream file = new FileInputStream (new File(pathToApp))
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
		String Data_fromCell=sheet.getRow(rowNum).getCell(cellNum).getStringCellValue();
		return Data_fromCell

	}

it works perfectly for index
but I want to call column through headers names , tried the following but it is not working :

	@Keyword
	def readDataFromExcel1(String fileName,String sheetName, int rowNum, String cellName){

		String pathToApp = RunConfiguration.getProjectDir() + ("\\files\\")+(fileName)
		FileInputStream file = new FileInputStream (new File(pathToApp))
		XSSFWorkbook workbook = new XSSFWorkbook(file);
		XSSFSheet sheet = workbook.getSheet(sheetName);
		int colIdx = CellReference.convertColStringToIndex(cellName);
		String Data_fromCell = CellUtil.getCell(rowNum, colIdx);
}

do you have any idea how ?

2

Please describe how it is not working.
What value you gave for the parameters fileName, sheetName, rowNum, cellName?
What did you expected as returned value?
What value did you get actually?
Any error messages in the Console?

3

fileName : Borrower data - Copy.xlsx (inside katalon file foldere)
sheetName : ‘Borrower’ the first sheet on the excel file
rowNum : 0
cellName : Carol (located at 0 row , 1 column)

expected to pass row index and column name to get the cell value for example : row 7 , column Carol the value is carol@gmail.com

the error in getcell method , and convertColStringToIndex is returning a bug number 104606 but seems its converting the name to ASCI or sth else

4

I use a method to get the column index from the column name.

	/**
	 * Get the column number for Column_Name
	 * @param sheet
	 * @param Column_Name
	 * @return
	 */
	public static int findColumn(XSSFSheet sheet, String Column_Name, short maxColIx) {
		//for (Row row : sheet) {
		Row row = sheet.getRow(0)
		//for (Cell cell : row) {
		for (short colIx=0; colIx<maxColIx; colIx++) {
			Cell cell = row.getCell(colIx)
			if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
				if(cell.getRichStringCellValue().getString().trim().contains(Column_Name)) {
					// If Column_Name is blank, return, else find next.
					return cell.getColumnIndex();

				}
			}
		}
		//}
		return 0
	}
5

how can I specify the excel file ? in sheet I should pass what exactly ?

6

No, this is not what the API expect you to give.

You should read the API document of the CellReference class first.

https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/CellReference.html#convertColStringToIndex-java.lang.String-

takes in a column reference portion of a CellRef and converts it from ALPHA-26 number format to 0-based base 10. ‘A’ → 0 ‘Z’ → 25 ‘AA’ → 26 ‘IV’ → 255

CellReference.convertColStringToIndex(ref) is a primitive method; all it does is …

assert CellReference.convertColStringToIndex('A') == 0
assert CellReference.convertColStringToIndex('Z') == 25
assert CellReference.convertColStringToIndex('AA') == 26
assert CellReference.convertColStringToIndex('IV') == 255

I am afraid, this is not what you naively expected.

You need to implement this sophisticated lookup logic for yourself.

7

thank you , it works …

I handled both index and header names in this code and works perfectly

> @Keyword

//###### read data from excel sheet (int row , def col) in files file , fileName should be in filename.xlsx format
def readDataFromExcel(String fileName,int sheetIndex, int rowNum, def celldef){
String pathToApp = RunConfiguration.getProjectDir() + (“\files\”)+(fileName)
FileInputStream file = new FileInputStream (new File(pathToApp))
XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
try {
if (celldef == (int)celldef) {
String Data_fromCell=sheet.getRow(rowNum).getCell(celldef).getStringCellValue();
return Data_fromCell
}
} catch (Exception e) {
Row row = sheet.getRow(0)
//for (Cell cell : row) {
for (short colIx=0; colIx<6; colIx++) {
Cell cell = row.getCell(colIx)
if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
if(cell.getRichStringCellValue().getString().trim().contains(celldef)) {
// If Column_Name is blank, return, else find next.
String Data_fromCell=sheet.getRow(rowNum).getCell(cell.getColumnIndex()).getStringCellValue();
// return cell.getColumnIndex();
return Data_fromCell

  			}
  		}
  	}
  	//}
  	return 0
  }

}

8

I use:

  FileInputStream fis = new FileInputStream("Filename");
  XSSFWorkbook workbook = new XSSFWorkbook(fis);
  XSSFSheet sheet = workbook.getSheet("The_Name_Of_The_Sheet");
  int searchColNum = findColumn(sheet,"The_Name_Of_Column", maxColIx)