使用 Excel 数据更新 SAP 字段

目的

读取用户选定的 Excel 文件并在标准 SAP 字段中进行更新。

用户界面

登录到 SAP 并在 SAP 轻松访问屏幕上单击“从 Excel 更新 SAP”按钮，这将弹出一个弹出窗口以选择 Excel 文件。

 

导航到“创建材质”屏幕，其中包含 Excel 文件中自动填充的值。

 

Liquid  UI 脚本

SAPLSMTR_NAVIGATION。E0100.sjs

<span style="color:#333333"><span style="background-color:#f5f5f5">
load('wsoffice');    // Need to load this file to display File Selection pop-up
pushbutton([TOOLBAR],"@8T@Update SAP from Excel", "/nMM01", {"process":testUpdateSAPFromExcel});

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