Purpose
Upload Excel data to an ALV grid

We want to read an Excel file and insert the values into ALV grid cells.

Solution
We use JavaScript to read the Excel file into a GuiXT table variable and then insert the values into the grid via the GuiXT Native Control Interface function  guinet.gridcontrol.setcellvalue.

Remarks

  • You may use SAP GUI scripting instead of the GuiXT Native Control Interface. See
     SAP GUI Scripting -> Upload Excel to an SAP grid control
    for this approach.
  • Use GuiXT 2020 Q2 3 or above, since in previous GuiXT versions the gridcontrol.setvalue function did not work reliably with large amounts of data
Example
 We want to upload purchase requisition data in transaction ME51N:




Our Excel file contains the following sample data:



The following video shows the uploading process;  see the full screen mode to watch it in detail:

Video

 

Show video in full screen


.zip file with InputScript, JavaScript and sample Excel file

GuiXT Script

 // Excel upload button
 Pushbutton (toolbar) "@J2@Excel upload"   process="me51n_excel_upload.txt"


InputScript "me51n_excel_upload.txt"

// --------------------------------------------------
// Upload purchase requisition items from Excel
// --------------------------------------------------

// select a file
 SelectFile name="xfile" _
  title="Select the Excel file to upload" _
  filter="*.xlsx" _
  directory="C:\js4"

// any file selected? else no action
 if not Q[ok]
  return "S: No file selected" -statusline
 endif

 // create table for Excel data
 CreateTable V[mytab] matnr menge eeind name1 ekgrp ekorg

 // load Excel file via JavaScript
 CallJS me51n_excel_to_guixt "&V[xfile]"

// start with Excel row index 2 (skip header row)
 Set V[k] 2

// start with grid row index 1
 Set V[n] 1

// show statusmessage window
 StatusMEssage title="Entering Excel data" addString="Start"

Enter

label fill_grid_control

 Screen saplmegui.0014
  Title "Uploading Excel data... Please wait."

   // determine total grid rows (synchronously)
   CallVB gridrowcount = guinet.gridcontrol.getrowcount

  // fill grid cells
   label next_row

  // anything left?
   if not V[k>&V[mytab.rowcount]]

     // no free grid row vailable?
     if V[k>&V[gridrowcount]]

      // press Enter to get more grid rows
       Enter
      goto fill_grid_control
     endif

    // add statusmessage line
     StatusMessage addString="&V[n] Material &V[mytab.matnr.&V[k]]"

     // fill grid cells
    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="MATNR" value:="&V[mytab.matnr.&V[k]]"

    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="MENGE" value:="&V[mytab.menge.&V[k]]"

    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="EEIND" value:="&V[mytab.eeind.&V[k]]"

    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="NAME1" value:="&V[mytab.name1.&V[k]]"

    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="EKGRP" value:="&V[mytab.ekgrp.&V[k]]"

    CallVBAsync guinet.gridcontrol.setcellvalue _
       row:=&V[n] column:="EKORG" value:="&V[mytab.ekorg.&V[k]]


    Set V[n] &V[n] + 1
    Set V[k] &V[k] + 1

    goto next_row
  endif

   Enter

Screen saplmegui.0014

   // remove statusmessage line
   StatusMessage -remove

Enter


JavaScript function

  1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
function me51n_excel_to_guixt(filename) {

    // try to attach to a running Excel instance
    var XL = guixt.GetObject("Excel.Application");

    // create a new instance if not found or visible
    if (XL == null || XL.Visible) {
        XL = guixt.CreateObject("Excel.Application");

        if (XL == null) {
            alert("Excel not installed")
        };

        // Do not display Excel window
        XL.Visible = false;

        // Do not display any alerts
        XL.DisplayAlerts = false;

    };



    // Open File
    var wBook = null;

    try {
        wBook = XL.Workbooks.Open(filename, 0, true)
    }
    catch (e) {
        return;
    }

    var cells = wBook.Worksheets(1).Cells;

    var k = 1;

    while (cells(k, 1).Value)
    {

        // fill GuiXT table
        guixt.Set("mytab.matnr." + k, cells(k, 1).Value);
        guixt.Set("mytab.menge." + k, cells(k, 2).Value);
        guixt.Set("mytab.eeind." + k, cells(k, 3).Value);
        guixt.Set("mytab.name1." + k, cells(k, 4).Value);
        guixt.Set("mytab.ekgrp." + k, cells(k, 5).Value);
        guixt.Set("mytab.ekorg." + k, cells(k, 6).Value);

        k++;
    };

    wBook.CLose();
    wBook = null;

    XL.Quit();
    XL = null;

    return;
}

Components
InputAssistant + Controls