DataProviderManager.cfc

component  hint="Encapsulates access to the test data in Excel spreadsheets using the JExcel API.  Wraps a Java access layer to that API." output="false"
{
    property name="dpm" type="DataproviderMgr" hint="An instance of a Java object";

    public void function setDataProvider(required string argXlFilePath, required string argSheetName, required string argTableName)
     hint="Sets the excel file, sheet name, and table name for access to desired data" output="false"
    {
        local.dpm = createObject("java","DataproviderMgr");
        local.dpm.setDataprovider(argXlFilePath,argSheetName,argTableName);
        this.dpm = local.dpm;
    }

    public Query function getData()
    hint="Returns an query object of all data in the desired table from the Excel sheet"  output="false"
    {
        local.result = TwoDimArrayWithHeadersToQuery(stripEmptyRows(this.dpm.getData()));
        return local.result;
    }

    public Query function getDataAtRow(required numeric rowNum)
    hint="Returns an query object of one row of data  in the desired table from the Excel sheet"  output="false"
    {
        local.qryDpm = getData();
        if (isQuery(local.qryDpm)){
            local.qryService = new Query();
            qryService.setDbtype("query");
            qryService.setAttributes(sourceQuery=local.qryDpm);
            qryService.setSQL("SELECT * FROM sourceQuery WHERE id = #rowNum#");
            qryResult = qryService.execute();
            if(isQuery(qryResult.getResult())){
                return qryResult.getResult();
            }
            return QueryNew("");
        }
        return QueryNew("");
    }

    public Query function getDataByRange(required numeric startRowNum, required numeric endRowNum)
    hint="Returns an array of data between the specified rows inclusively from the desired table from the Excel sheet"  output="false"
    {
        local.qryDpm = getData();
        if (isQuery(local.qryDpm)){
            local.qryService = new Query();
            qryService.setDbtype("query");
            qryService.setAttributes(sourceQuery=local.qryDpm);
            qryService.setSQL("SELECT * FROM sourceQuery WHERE id >= #startRowNum# AND id <= #endRowNum#");
            qryResult = qryService.execute();
            if(isQuery(qryResult.getResult())){
                return qryResult.getResult();
            }
            return QueryNew("");
        }
        return QueryNew("");
    }

    private Array function stripEmptyRows(required Array dp) {
        returnDP = ArrayNew(2);
        // 2 dimensional array.  Loop over the inner array which represents a data row
        for(local.i = 1; i <= arraylen(dp); local.i++){
            local.bIsEmptyRow = true;
            for(local.j = 1; j <= arraylen(dp[local.i]); local.j++){
                if (local.j != 1 && dp[local.i][local.j] IS NOT ""){
                    local.bIsEmptyRow = false;  // if any row other than the id column has a value, then this is not an empty row, don't delete
                }
            }
            if (!local.bIsEmptyRow) {
                for(local.j = 1; j <= arraylen(dp[local.i]); local.j++){
                    returnDP[i][j]  = dp[i][j];
                }
            }
        }
        return returnDP;
    }

    private Query function TwoDimArrayWithHeadersToQuery(required Array argArray){
        local.result = "";
        for(local.i=1;i<=arraylen(argArray);i++){

            if(i EQ 1){  // First row is headers, so use to set query column names
                local.result = QueryNew("");
                local.seed = arrayNew(1);
                // all queries must have "id" column, and "id" must be first column in query
                QueryAddColumn(local.result,"id","integer",local.seed);
                for(local.k=1;k<=arraylen(argArray[i]);k++){
                    if (argArray[i][k] IS NOT "id") {
                        QueryAddColumn(local.result,argArray[i][k],"varchar",local.seed);
                    }
                }
            } else {
                QueryAddRow(local.result,1);
            }

            for(local.j=1;j<=arraylen(argArray[i]);j++){
                if(i GT 1) {
                    QuerySetCell(local.result,argArray[1][j],argArray[i][j],i-1); // on all other rows for inner array, values are row values
                }
            }
            if(i GT 1){
                QuerySetCell(local.result,"id",i-1,i-1);
            }
        }
        return local.result;
    }

}