SAP Grid Control: Upload Excel data to an ALV grid

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.

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

GuiXT

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

InputScript "me51n_excel_upload.txt"

GuiXT

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

JavaScript