Variable Binding with several sheets

Hi,

I use several sheets in Excel to maintain variables.

I have a payment flow in Katalon in different countries with different payment methods.

So I have the following sheets:

Service Matrix: Here it is defined in which countries we offer which kind of services and payment types.
Payment: Here the login data to the different payment methods are saved
Account: Here mock data for Login, Addresses etc are saved

I use Account and Payment with binding type many to test all countries with all payment types. With service matrix I check if service should be visible and tested or if it should be absent.

Now the problem is:

Account and Payment should iterate n to n what they are doing. 12 countries and 5 payment types are 60 test runs = correct.

But in Service Matrix where I need to stay in same line as in account (both are set up according to the countries) to know which service should be tested.

For example in test run 13 the binding is landing in service matrix line 13 which doesn’t exist anymore as there are only 12 countries.

Does anybody know how to manage this complicated case?

Regards

hi,

read all excel sheet rows to the arrays (memory) and use them in a loops
are you able to share something in plain text maybe this all what you are shared with images?
ok, here is something where to start

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

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

import java.io.*;
import java.util.ArrayList;
import java.util.List;


public class ExcelUtil {

    Workbook wb;

    public static List<String> ExcelHelper(int colCount, int start, int end, String path, String sheetname) throws IOException, InvalidFormatException {

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

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

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

        // 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;
        //System.out.println("row start "+rowStart);
        int rowEnd = end;
        //System.out.println("row end "+rowEnd);

        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.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));
                }
            }
        }
        return excelValues;
    }

    public static 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){
            System.out.println(ex);
        }

        return totalRows;
    }

    public static List<String> getSheetNames(String path) throws IOException, InvalidFormatException {

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

    public static int getColumnCount(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));
            Sheet sheet = book.getSheet(sheetName);
            noOfColumns = sheet.getRow(0).getLastCellNum();
        }
        catch(Exception ex){
            System.out.println(ex);
        }

        return noOfColumns;
    }


    public static void main(String[] args) throws Exception {

        String path = "C:\\Users\\xxx\\IdeaProjects\\com.excelutil\\src\\main\\resources\\payments.xlsx";
        List<String> headerValues = new ArrayList<String>();
        List<String> excelValues = new ArrayList<String>();
        List<String> sheetNames = new ArrayList<>();

        sheetNames = getSheetNames(path);

        int start = 0;
        int end = 1;

        for (String sheet : sheetNames) {

            int rowCnt = getRowCount(path, sheet);
            int colCnt = getColumnCount(path, sheet);
            int processRows = rowCnt - 1;

            headerValues = ExcelHelper(colCnt, 0, 1, path, sheet);
            start = 1;
            end = 2;
        /*excelValues = eu.ExcelHelper(start, end, path);

        start = 2;
        end = 3;
        excelValues = eu.ExcelHelper(start, end, path);*/

            //print out all Account values
            for (int x = 0; x < processRows; x++) {
                excelValues = ExcelHelper(headerValues.size(), start, end, path, sheet);
                int i = 0;

                for (String s : headerValues) {
                    //System.out.println(headerValues.get(i));
                    //System.out.println(excelValues.get(i));
                    if (excelValues.get(i).equals("**No Value**")) {
                        System.out.println("Row " + start + " Header " + headerValues.get(i) + " has not value ");
                    } else {
                        System.out.println("Row " + start + " Header " + headerValues.get(i) + " has value " + excelValues.get(i));
                    }
                    i++;
                }
                start++;
                end++;
            }
        }
    }
}

Row 1 Header: Site has value: AMER
Row 1 Header: Domain has value: com/shop
Row 1 Header: lang has value: en_USA
Row 1 Header: first name has value: Mat
Row 1 Header: last name has value: Pat
Row 1 Header: phone has value: +1234
Row 2 Header: Site has value: AMER
Row 2 Header: Domain has value: ca/shop/en
Row 2 Header: lang has value: en_CA
Row 2 Header: first name has value: Kat
Row 2 Header: last name has value: Lat
Row 2 Header: phone has value: +4321
Row 3 Header: Site has value: AMER
Row 3 Header: Domain has value: ca/shop/fr
Row 3 Header: lang has value: fr_CA
Row 3 Header: first name has value: Sat
Row 3 Header: last name has value: Lot
Row 3 Header: phone has value: +2345
Row 4 Header: Site has value: EU
Row 4 Header: Domain has value: ca/shop/de
Row 4 Header: lang has value: de_DE
Row 4 Header: first name has value: Dis
Row 4 Header: last name has value: Des
Row 4 Header: phone has value: +5432
Row 1 Header: lang has value: en_USA
Row 1 Header: paypal has value: x
Row 1 Header: paypal_express has value: x
Row 1 Header: payflow has value: x
Row 2 Header: lang has value: en_CA
Row 2 Header: paypal has not value 
Row 2 Header: paypal_express has not value 
Row 2 Header: payflow has value: x
Row 3 Header: lang has value: fr_CA
Row 3 Header: paypal has value: x
Row 3 Header: paypal_express has not value 
Row 3 Header: payflow has value: x
Row 4 Header: lang has value: de_DE
Row 4 Header: paypal has value: x
Row 4 Header: paypal_express has value: x
Row 4 Header: payflow has value: x
Row 1 Header: payment_type has value: pt_payflow
Row 1 Header: company has value: visa
Row 1 Header: card_number has value: 4111111111111110
Row 1 Header: card_exp_mont has value: 12
Row 1 Header: card_exp_year has value: 2030
Row 2 Header: payment_type has value: pt_paypal
Row 2 Header: company has value: vesa
Row 2 Header: card_number has value: 4211111111111110
Row 2 Header: card_exp_mont has value: 12
Row 2 Header: card_exp_year has value: 2030
Row 3 Header: payment_type has value: pt_paypal_express
Row 3 Header: company has value: vosu
Row 3 Header: card_number has value: 4311111111111110
Row 3 Header: card_exp_mont has value: 12
Row 3 Header: card_exp_year has value: 2030

excelfile what used as testdata
payments.7z (7.7 KB)

1 Like

Hi @Timo_Kuisma1:

thank you for your suggestion how to solve it.
As I need a developer for this I will remember your post but have to wait until one is free.
In the meantime I merged service matrix into the account sheet and grouped it in excel for a better overview.

But I hope your explanation will help also other users in this forum :slight_smile:

hi,

nb, i am here to support, that’s my hobby :slight_smile: