Read Excel horizontally with TestNG

Question

I can read an Excel sheet vertically and the pass the values to TestNG dataprovide. This makes TestNG executes each row.

public Object[][] getData(ITestContext context) throws IOException {
        Object[][] obj = null;
        try {
            File file = new File(context.getCurrentXmlTest().getParameter("resource"));
            FileInputStream fis = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet sheet = wb.getSheetAt(0);
            wb.close();

            int rowCount= sheet.getLastRowNum();
            int colCount = sheet.getRow(0).getLastCellNum();

            obj = new Object[rowCount][1];
            DataFormatter df = new DataFormatter();
            for (int i = 0; i < rowCount; i++) {
                Map<String, String> datamap= new HashMap<String, String>();
                for (int j = 0; j < colCount; j++) {
                    String a = df.formatCellValue(sheet.getRow(0).getCell(j));
                    String b = df.formatCellValue(sheet.getRow(i+1).getCell(j));

                    if(!b.equals(""))
                        datamap.put(a, b);  

                    if(b.equals("[PWD]")) {
                        b=PropertiesFile.getProperty("psw");
                        byte[] byteArray = Base64.decodeBase64(b.getBytes());   
                        String decodedString = new String(byteArray);
                        b=decodedString;
                        datamap.put(a, b);                      
                    }
                }
                obj[i][0] =datamap;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return obj;
    }

I'd like to read it horizontally, so that TestNG executes each column (the first column would be the keys column), how can i do?

Thanks

score 0 · Accepted Answer · answered Jun 23 '20 at 09:21

0

You can transpose your sheet and work with former columns which are now the rows: How to transpose sheet with POI SS/XSSF?

Or just change your loop nesting so that you go by columns in the outer loops. Not by rows.

answered Jun 23 '20 at 09:21

Alexey R.

8,057
2
11
27

I'll be honest, I have no idea how to do it. I found that code online, sorry – CCC Jun 23 '20 at 10:29

score 0 · Answer 2 · answered Jun 23 '20 at 11:44

I think i solved it

public Object[][] getData(ITestContext context) throws IOException {
        Object[][] obj = null;
        try {
            File file = new File(context.getCurrentXmlTest().getParameter("resource"));
            FileInputStream fis = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet sheet = wb.getSheetAt(0);
            wb.close();

            int rowCount= sheet.getLastRowNum();
            int colCount = sheet.getRow(0).getLastCellNum();

            obj = new Object[colCount-1][1];
            DataFormatter df = new DataFormatter();
            for (int i = 0; i < colCount-1; i++) {
                Map<String, String> datamap= new HashMap<String, String>();
                for (int j = 0; j < rowCount+1; j++) {
                    String a = df.formatCellValue(sheet.getRow(j).getCell(0));
                    String b = df.formatCellValue(sheet.getRow(j).getCell(i+1));
                    System.out.println(a+ " "+b);
                    if(!b.equals(""))
                        datamap.put(a, b);  

                    if(b.equals("[PWD]")) {
                        b=PropertiesFile.getProperty("psw");
                        byte[] byteArray = Base64.decodeBase64(b.getBytes());   
                        String decodedString = new String(byteArray);
                        b=decodedString;
                        datamap.put(a, b);                      
                    }
                }
                obj[i][0] =datamap;
            }
        }catch(Exception e){
            e.printStackTrace();
        }
        return obj;
    }

Read Excel horizontally with TestNG

2 Answers2