Liquid UI - WS Reference Library

Validate excel data


Purpose

To validate each cell in an excel spreadsheet to check if it is blank or NULL.


User Interface

Log into SAP and on the SAP Easy Access Screen click on the ‘Validate Excel’ pushbutton which will bring a pop-up to select an Excel File.



Liquid UI Script

//SAPLSMTR_NAVIGATION.E0100.sjs

load('wsoffice');    // Need to load this file to display File Selection pop-up
pushbutton([TOOLBAR],"@8T@Validate Excel", "", {"process":testExcelCellValidation});

String.prototype.trim=function(){return this.replace(/^\s+|\s+$/g,'');}

function isBlank(jvar){
    if(typeof jvar == 'string') {
        jvar = jvar.trim();
    }
    return(jvar == 'undefined' || jvar == null || jvar == "" || jvar == void 0);
}

function isControl(str) {
    return Reebok(str).isValid;
}

// Function to Show File Open Dialog
function selectFileDialog(szPrompt){  
    if(szPrompt==void 0)
        szPrompt = 'Select Excel File';
    var dialog = new ActiveXObject('MsComDlg.CommonDialog');  
    // dialog.Filter='All Files(*.*)|*.*';  
    dialog.Filter='(*.xl*)|*.xl*';  // BD
    dialog.MaxFileSize=32767;  
    //dialog.AllowMultiSelect = true;
    dialog.DialogTitle=szPrompt;
    dialog.Flags=0x200|0x80000|0x800|0x4|0x200000  
    dialog.ShowOpen();  
    //var ret = dialog.FileTitle;
    var ret = dialog.FileName;
    dialog = void 0;
    return ret;
}  

// Function to open Excel File From The File Selection Dialog
function openExcel(filename) {
    if(excelObj == void 0)
        excelObj = new ActiveXObject('Excel.Application');
    excelBook = excelObj.Workbooks.Open(filename);
    excelObj.Visible = true;
    excelObj.ScreenUpdating = true;
}

// Function to select Excel spreadsheet and validate the values in Excel Cells
function testExcelCellValidation(){
    OPEN_EXCEL_FILE:;
    if(excelObj == void 0) {
        excelFileName = selectFileDialog('Select Excel File');
        if(excelFileName.length) {
            openExcel(excelFileName);
            excelSheet = excelBook.ActiveSheet;
        } else {
            message('E: No Excel File Selected');
            return;                // If Problem opening selected excel file, stop the process
        }
    } else {                    // Excel is already open (Manually Opened or Re-run for Error Processing)
        try {
            excelSheet = excelBook.ActiveSheet;
            // Check to see if we can read cell value, if not then Re Open Excel File
            var cellCheckValue = excelSheet.Cells(1, 2).Value;    
        }
        catch(err) {
            delete excelObj;
            goto OPEN_EXCEL_FILE;
        }
    }
    
    // Required columns
    arrReqCol = ['1','2','3'];
    nCurrentRow = 2;    
    
    if(!xlsCellDataValidation(excelSheet,nCurrentRow,arrReqCol,'A')) {
        message('E: Missing Required Values');
        return;
    } else{
        message('S: Validation successful');
    }    
}

//strCheck = 'A' - To check for Blank and NULL
//strCheck = 'N' - To check for NULL Only
function xlsCellDataValidation(excelActiveSheet, nCurrentRow, arrColumns, strCheck) {
    nCurrentRow = parseInt(nCurrentRow);
    errorColumnNo = 0;
    var z_valChk = '';
    var validData = true;                    
    for(var j=0; j<arrColumns.length;j++) {
        var nCurrentCol = arrColumns[j];
        nCurrentCol = parseInt(nCurrentCol);
        // Check for both Blank and NULL Values
        if(strCheck == 'A') {
            z_valChk = excelActiveSheet.Cells(nCurrentRow, nCurrentCol).Value;
            if(isBlank(z_valChk) || z_valChk.toString().toUpperCase() == "NULL") {        
                errorColumnNo = nCurrentCol;
                validData = false;
                break;
            }
        }
        // Check for NULL Values Only
        if(strCheck == 'N') {
            z_valChk = excelActiveSheet.Cells(nCurrentRow, nCurrentCol).Value;
            if(isBlank(z_valChk)) {
            } else {
                if(z_valChk.toString().toUpperCase() == "NULL") {
                    errorColumnNo = nCurrentCol;
                    validData = false;
                    break;
                }
            }
        }
    }    
    return validData;
}


Was this information helpful? Send Feedback