Excel Cell Data Validation

[Benjamin Dasari]

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


Liquid UI Code:

// SAPLSMTR_NAVIGATION.E0100.sjs
// User Interface - SAP Easy Access Screen
load('wsoffice');   // Need to load this file to display File Selection pop-up
pushbutton([TOOLBAR],"@8T@Validate Excel", "/nMM01", {"process":testExcelCellValidation});

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

// Validate if the variable holds blank or null value
function isBlank(jvar){
   if(typeof jvar == 'string') {
      jvar = jvar.trim();
   }
   return(jvar == 'undefined' || jvar == null || jvar == "" || jvar == void 0);
}

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


See attachments for code samples!