Unable to pass test data from multiple sheets from various Excel files

Hello Everyone,

I’m trying to input test data from different sheets using multiple excel files.
During mapping of the test data, I’m unable to identify all the sheet names from multiple files ?

How to view all the sheet names of multiple test data files ?

Thanks,
Praveen

Hey,

I think read some time that this works only with the paid version of katalon. In the free version you can only use one excel file …

hello,

with this little snip you are able to read multiple excel sheets /files

TESTCASE:

//excelfile should be format .xlsx !!!
String pathXlsx = System.getProperty("user.dir")+"\\path\\toyour\\excelfile.xlsx";

//get excel file sheet names
List<String> sheetNames = new ArrayList<>()
sheetNames = CustomKeywords.'excelHelper.ExcelUtilForXlsx.getSheetNames'(pathXlsx)
for(String name: sheetNames){
	println("SheetNames in excel file: "+name)
}

int counter = 0
int rowCount = 0
//loop sheets
for(String sheet: sheetNames){
	//header count
	start = 0;
	end = 1;
	//get count of headers
	counter = CustomKeywords.'excelHelper.ExcelUtilForXlsx.ExcelHelperGetColumnCountXlsx'(pathXlsx, sheet)
	println ("DEBUG*******************Columns "+counter)
	//get header values
	headerValuesXlsx = CustomKeywords.'excelHelper.ExcelUtilForXlsx.ReadFile'(counter, start, end, pathXlsx, sheet)
	println ("DEBUG*******************Header Values "+headerValuesXlsx)
	//get used rows / sheet
	rowCount = CustomKeywords.'excelHelper.ExcelUtilForXlsx.getRowCount'(pathXlsx, sheet)
	rowCount--
	println ("DEBUG*******************Rows "+rowCount)
	//row count
	start = 1;
	end = 2;
	//loop rows
	for (int z = 0; z < rowCount; z++) {
		excelValuesXlsx = CustomKeywords.'excelHelper.ExcelUtilForXlsx.ReadFile'(headerValuesXlsx.size(), start, end, pathXlsx, sheet)
		//excelValuesXlsx = CustomKeywords.'excelHelper.UpdateXlsxFile.ExcelHelperReadXlsx'(headerValuesXlsx.size(), start, end, pathXlsx, truefalse)
		int y = 0;
		//loop columns
		for (String s : headerValuesXlsx) {
			if (excelValuesXlsx.get(y).equals("**No Value**")) {
				System.out.println("Row " + start + " Header " + headerValuesXlsx.get(y) + " has not value ");
			} else {
				System.out.println("Row " + start + " Header " + headerValuesXlsx.get(y) + " has value " + excelValuesXlsx.get(y));
			}
			y++;
		}
		start++; //increase start & end to get data rows
		end++;
	}
}

KEYWORDS:

package excelHelper

import java.text.SimpleDateFormat;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.kms.katalon.core.annotation.Keyword
import com.kms.katalon.core.util.KeywordUtil

public class ExcelUtilForXlsx {

	FileInputStream fsIP
	XSSFWorkbook wb
	XSSFSheet worksheet
	FileOutputStream outputFile
	KeywordUtil logger

	public ExcelUtilForXlsx(){
		logger = new KeywordUtil()
	}


	@Keyword
	public List<String> getSheetNames(String path){

		Workbook book = WorkbookFactory.create(new File(path));
		List<String> sheetNames = new ArrayList<String>();

		for (int i=0; i<book.getNumberOfSheets(); i++) {
			sheetNames.add( book.getSheetName(i) );
		}
		//book.close()
		return sheetNames
	}
	
	@Keyword
	public int getRowCount(String path, String sheetName) throws IOException, InvalidFormatException {

		int noOfColumns = 0
		int totalRows = 0
		// Check the file extension
		if (!path.endsWith(".xlsx")) {
			throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
		}

		try
		{
	
		   // Open the Excel file
		   FileInputStream ExcelFile = new FileInputStream(path);
			
		   // Access the required test data sheet
		   XSSFWorkbook ExcelWBook = new XSSFWorkbook(ExcelFile);
		   XSSFSheet ExcelWSheet = ExcelWBook.getSheet(sheetName);
			
		   totalRows = ExcelWSheet.getPhysicalNumberOfRows();
							
		}
		catch(Exception ex){
			logger.logInfo(ex)
		}

		return totalRows;
	}


	@Keyword
	public int ExcelHelperGetColumnCountXlsx(String path, String sheetName) throws IOException, InvalidFormatException {

		int noOfColumns = 0
		// Check the file extension
		if (!path.endsWith(".xlsx")) {
			throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
		}

		try
		{

			Workbook book = WorkbookFactory.create(new File(path));

			//Get first/desired sheet from the workbook
			//Sheet sheet = book.getSheetAt(0); //int sheet 0 1 2 ...
			Sheet sheet = book.getSheet(sheetName);
			noOfColumns = sheet.getRow(0).getLastCellNum();
			//book.close()
		}
		catch(Exception ex){
			logger.logInfo(ex)
		}

		return noOfColumns;
	}
	
	@Keyword
	public List<String> ReadFile(int colCount, int start, int end, String path, String sheetName) throws IOException, InvalidFormatException {


		// Check the file extension
		if (!path.endsWith(".xlsx")) {
			throw new IllegalArgumentException("Unknown file type. Please use .xlsx");
		}

		List<String> excelValues = new ArrayList<String>();
		int MY_MINIMUM_COLUMN_COUNT = colCount;

		try
		{
			Workbook book = WorkbookFactory.create(new File(path));

			//Get first/desired sheet from the workbook
			Sheet sheet = book.getSheet(sheetName);
			//Sheet sheet = book.getSheetAt(1);

			// Create a DataFormatter to format and get each cell's value as String
			DataFormatter dataFormatter = new DataFormatter();

			// Decide which rows to process
			int rowStart = start;
			int rowEnd = end;

			for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
				Row r = sheet.getRow(rowNum);
				if (r == null) {
					// This whole row is empty
					// Handle it as needed
					continue;
				}

				int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);
				//System.out.println("last column "+lastColumn);

				for (int cn = 0; cn < lastColumn; cn++) {
					Cell c = r.getCell(cn, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
					if (c == null) {
						excelValues.add("**No Value**");
						// The spreadsheet is empty in this cell
					} else {
						// Do something useful with the cell's contents
						excelValues.add(dataFormatter.formatCellValue(c));
					}
				}
			}

		}
		catch (Exception ex){
			logger.logInfo(ex)
		}

		return excelValues;
	}
}