Liquid UI - WS Reference Library

Determine Data Rows and Columns in Excel


Purpose

To determine the data rows and columns from user selected Excel file.


User Interface

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


Then the number of data rows and columns are displayed as shown below:



Liquid UI Script

// SAPLSMTR_NAVIGATION.E0100.sjs

load('wsoffice');    // Need to load this file to display File Selection pop-up
// User Interface
clearscreen();
inputfield([1,0], "Excel Rows", [1,15], {"size":3, "name":"z_mm01_rows"});
inputfield([2,0], "Excel Columns", [2,15], {"size":3, "name":"z_mm01_cols"});
pushbutton([4,0],"@0V@Read Excel", "?", {"process":excelRowColumnCount});

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

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

// Determine Total Number of Columns with Data in Excel SpreadSheet
function determineNoOfDataColumns(excelActiveSheet, nColumnHeadingRow) {
    var excelColumnCount = excelActiveSheet.Columns.Count;
    for(var i = 1; i<excelColumnCount;i++) {
        if (excelActiveSheet.Cells(nColumnHeadingRow, i).Value == undefined || typeof(excelActiveSheet.Cells(nColumnHeadingRow, i).Value) == 'undefined') {
            break;
        }
    }
    return i;
}

// Determine Total Number of Rows with Data in Excel SpreadSheet
function determineNoOfDataRows(excelActiveSheet, nTotalDataColumns, nStartDataRow) {
    var excelRowCount = excelActiveSheet.Rows.Count;
    for(var i = nStartDataRow; i<excelRowCount;i++) {
        lastRowFound = false;
        for(var j = 2; j<nTotalDataColumns;j++) {
            if (excelActiveSheet.Cells(i, j).Value == undefined || typeof(excelActiveSheet.Cells(i, j).Value) == 'undefined') {
                lastRowFound = true;;
            } else {
                lastRowFound = false;
                break;
            }
        }
        if(lastRowFound == true) {
            // Last Row with Data + 1;
            break;
        }
    }
    return i;
}

// Function to determine rows and columns
function excelRowColumnCount(){
    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;
        }
    }
    
    totalDataColumns = determineNoOfDataColumns(excelSheet,1);
    totalDataRows = determineNoOfDataRows(excelSheet,totalDataColumns,1);
    totalDataColumns = totalDataColumns-1;
    totalDataRows = totalDataRows-1;
    set('V[z_mm01_cols]','&V[totalDataColumns]');
    set('V[z_mm01_rows]','&V[totalDataRows]');
}