Liquid UI - Documentation - 16.02 Determine data rows and columns in excel

16.02 Determine data rows and columns in excel


Prerequisites


Purpose

In this article, you will learn how to determine the data rows and columns from the user-selected Excel file and display the value in the respective input field.

To determine the data rows and columns from the selected file, follow the sequence.

  1. load wsoffice.dll file.
  2. Add input fields and push buttons.
  3. Add a function to determine and display the data from rows and columns.

User Interface

//Create this file inside your script folder for customizing the SAP Easy Access screen: SAPLSMTR_NAVIGATION.E0100.sjs

//Now, let's start adding the Liquid UI script to the above file and save it.

  1. Load the wsoffice

    Firstly, place the wsoffice file inside your script folder. Now, call the wsoffice.dll file using the load() command in the ESESSION.sjs file.

    load('wsoffice');    // Need to load this file to display File Selection pop-up
  2. Clear the screen using clearscreen()
    //clear the SAP Easy Access screen 
    clearscreen();
  3. Add two input fields “Excel Rows” and “Excel Columns” to get the data from the excel sheet.
    //create two input fields “Excel Rows" and “Excel Columns”
    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"});
     
  4. Add a pushbutton "Read Excel" to execute the process excelRowColumnCount on click.
     
    //create a pushbutton "Read Excel"
    pushbutton([4,0],"@0V@Read Excel", "?", {"process":excelRowColumnCount});
     
  5. Add a function to remove blank space and validate the blank or null value of the variable.
     
    // 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;
    }
     
  6. Add a function to show File Open Dialog.
     
    // 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;
    }
     
  7. Add a function to open Excel File from the File Selection Dialog.
     
    // 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;
    }

  8. Add code to identify the total number of columns and rows with data in the selected excel spreadsheet.
     
    // 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;
    }
     
  9. Add a function to set the values of rows and columns in input fields.
     
    // 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]');
    }

SAP Process

  1. Logon to SAP. On SAP Easy Access, you can see “Excel Rows”, “Excel Columns” input fields, and “Read Excel” push button. Clicking on the Read Excel push button displays a pop-up to select an excel file. Select the file and click on the open button option, as shown below.

     
  2. After identifying the rows and columns, the data from the selected file will be displayed in the input fields, as shown below.


Can't find the answers you're looking for?