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;
}
}