Update SAP from Excel

[Benjamin Dasari]

Purpose:
To read from user selected Excel file and update in standard SAP fields.


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@Update SAP from Excel", "/nMM01", {"process":testUpdateSAPFromExcel});

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

// Check if a control exists or not
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 testUpdateSAPFromExcel(){
   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;
      }
   }
   
   onscreen 'SAPLMGMM.0060'   
      updateSAPFromExcel(excelSheet,2,1,4);   // Updates Industry sector, Material Type and Copy from Material
      enter();                        // Select Views pop-up is displayed   
}

// Applicable to only edit field and check box
// For Table and Radio button different logic applies
function updateSAPFromExcel(excelActiveSheet, nCurrentRow, nStartColumn, nEndColumn) {
   excelColumnSAPHeadingsRow = 1;
   var scrElementName = '';
   var scrElementValue = '';
      
   for(var currentCol = nStartColumn; currentCol < nEndColumn; currentCol++) {
      scrElementName = excelActiveSheet.Cells(excelColumnSAPHeadingsRow,currentCol).Value;
      scrElementValue = excelActiveSheet.Cells(nCurrentRow,currentCol).Value;

      println('\n------------ scrElementName:'+scrElementName+':');
      println('\n------------ scrElementValue1:'+scrElementValue+':\n');

      if(scrElementValue == undefined || typeof(scrElementValue) == 'undefined' || isBlank(scrElementValue)) {
         scrElementValue = '';
         continue;         // Next Element within for loop
      }
      
      if(isControl('F['+scrElementName+']')){
         // if(scrElementValue.trim() == 'NULL'){
         if(scrElementValue.toString().trim().toUpperCase() == 'NULL'){
            set('F['+scrElementName+']','');                           //set the value to blank
         } else {
            set('F['+scrElementName+']','&V[scrElementValue]');               //set the value to user entered value in excel
         }
      } else if(isControl('C['+scrElementName+']')){
         // if(scrElementValue.trim() == 'NULL'){
         if(scrElementValue.toString().trim().toUpperCase() == 'NULL'){
            set('C['+scrElementName+']',' ');                           //set the value to blank
         } else {
            set('C['+scrElementName+']','X');                           //set the value
         }
      } 
   }
}


See attachments for code samples!