How do I return a table from a table function using groovy?

advertisements

How do I return an array from an array function using Groovy? I am currently trying it with the following code but I am getting errors when I execute the code! I want to be able to get values from an array function where I am extracting the data from excel and I want to be able to use arbitrary values retrieved from the function. Can anyone help?

Code:

package Check

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class Second {

    static main(args) {
        //def alpo = new String[2][3]
        def alpo = getTableArray("KSuite");
        println "£34." + alpo[1][2]
    }

    public static String[][] getTableArray(String xlSheet) throws Exception {
        FileInputStream input = new FileInputStream(new File("C:\\Temp\\KSuite.xlsx"));
        //System.out.println(input.path);
        XSSFWorkbook workbook = new XSSFWorkbook(input);
        //System.out.println(xlSheet);
        XSSFSheet sheet = workbook.getSheet(xlSheet);
        int ci;//,cj;
        XSSFRow r = sheet.getRow(sheet.getFirstRowNum());
        int jMax=Math.max(r.getLastCellNum()-r.getFirstCellNum(),2);
        int iMax=sheet.getLastRowNum()-sheet.getFirstRowNum();
        def tabArray= new String[sheet.getLastRowNum()-sheet.getFirstRowNum()][jMax];
        //System.out.println(sheet.getLastRowNum()-sheet.getFirstRowNum());
        //System.out.println(jMax);

        for (int i = sheet.getFirstRowNum()+1; i <= sheet.getLastRowNum(); i++) {
            ci = i - 1;
            r = sheet.getRow(i);
            for (int j = 0; j < jMax; j++) {
                XSSFCell c = r.getCell(j, r.CREATE_NULL_AS_BLANK);
                if(c.getCellType() == 1) {
                    tabArray[ci][j]=c.getStringCellValue();
                } else {
                    tabArray[ci][j]=((float)(c.getNumericCellValue()) + "");
                }
            }
        }
        input.close();
        // return(tabArray);
        // return(cj);

        return(tabArray);
        // return(cj);
    }
}

Error:

   Caught: java.lang.ArrayIndexOutOfBoundsException: 2
   java.lang.ArrayIndexOutOfBoundsException: 2
at Check.Second.main(Second.groovy:15)

Table KSuite in KSuite.xlsx:

Script  Run
Login,  Yes
Workspaces  No
Users,  Yes


It's hard to say as I don't know your input file, and your code is a bit confusing, but as a guess, on this line here:

    int jMax=Math.max(r.getLastCellNum()-r.getFirstCellNum(),2);

You set the minimum number for the secondary dimension to 2

then here:

    println "£34." + alpo[1][2]

You get the 3rd element of the returned array. I assume it only has length 2

I also had a go at Groovifying your getTableArray method, and came up with:

String[][] getTableArray( File xls, String sheetName, int skip=1 ) {
    xls.withInputStream { input ->
        new XSSFWorkbook( input ).with { workbook ->
            getSheet( sheetName ).with { sheet ->
                (sheet.firstRowNum+skip..sheet.lastRowNum).collect { rowIdx ->
                    getRow( rowIdx ).with { row ->
                        (row.firstCellNum..<row.lastCellNum).collect { cellIdx ->
                            row.getCell( cellIdx, CREATE_NULL_AS_BLANK ).stringCellValue
                        }
                    }
                }
            }
        }
    }
}

def results = getTableArray( new File( '/tmp/test.xlsx' ), 'Sheet1' )

assert results[ 1 ] == [ 'Workspaces', 'No' ]