Purpose
You want to read an Excel file

Solution
Use a JavaScript function that writes the Excel file to the Windows clipboard. Then read the clipboard with CopyText.

There are alternative ways, for example to read each cell value in JavaScript via Excel automation and to fill a GuiXT variable for each cell. This is appropriate if you only need a small subset of the columns or rows. For reading all data, the solution described here is a relatively simple and fast approach. It also has the advantage that you can use the same JavaScript function for each Excel file.

JavaScript function excel_to_clipboard()

The function essentially consists of the following three operations:

// create Excel automation object
var XL = guixt.CreateObject("Excel.Application");

// open file
var wBook = XL.Workbooks.Open(path)

// write file to clipboard
 wBook.ActiveSheet.UsedRange.Copy

Our complete function (below) adds error handling (file not found) and  removes empty rows which are often present at the end of an Excel file. It also frees all objects correctly, otherwise an Excel instance keeps running in the background.

function excel_to_clipboard(path) {
    var XL = guixt.CreateObject("Excel.Application");
    XL.Visible = false;
    XL.DisplayAlerts = false;

    var wBook = null; 

    try
    {
        wBook = XL.Workbooks.Open(path)
    }
    catch (e)
    {
        return "";
    }
        
    // copy to clipboard
    wBook.ActiveSheet.UsedRange.Copy;

    // we remove empty rows via the "PasteSpecial" function
    var tempSheet = XL.ActiveWorkbook.Sheets.Add;
    tempSheet.UsedRange.PasteSpecial(null, null, true);
    tempSheet.UsedRange.Copy;
    tempSheet = null;

    wBook.Close(false);
    wBook = null;
    XL.Quit;
    XL = null;

    return "X";

}


Example

We read the following Excel file and display it in a SAP GUI table:



The InputScript needs about one second to read the demo file, consisting of 1675 rows and 6 columns, and to build up the table:





GuiXT script

Pushbutton (toolbar) "Load operations" _

      process="load_operations.txt"

 

if V[opcount>0]

  Table (2,2) (30,135) name="operations" rows="&V[opcount]" _

      fixedcolumns=6 title="Orders and Operations"

   Column "Order" name="aufnr" size=8

   Column "Description" name="ktext" size=40

   Column "Operation" name="vornr" size=8

   Column "Short text" name="ltxa1" size=40

   Column "Workplace" name="arbpl" size=10

   Column "Syst.State" name="sttxt" size=20

endif

 


Input script

CallJS found = excel_to_clipboard _
         "C:\temp\operations.xlsx"

 

if not V[found]

  return "E: Excel file not found" -statusline

endif

 

// read data from clipboard

CopyText toText="tmp" -fromCLipboard

 

// we need a TAB character (Hexcode 09)

Set V[TAB] "&V[_tabcharacter]"

 

// clear all cell values and table status

Clear V[operations.*]

 

// row index

Set V[k] 1

 

label next_row

 CopyText fromText="tmp" toString="s"  line="&V[k]"

 if Q[ok]

   CopyText fromString="s" toText="cells"

  

   // order number

   CopyText fromText="cells" line=1  _

     toString="operations.cell.aufnr.&V[k]" _

     delimiter="&V[TAB]"

   // description

   CopyText fromText="cells" line=2  _
     toString="operations.cell.ktext.&V[k]" _
     delimiter="&V[TAB]"

   // operation number
    CopyText fromText="cells" line=3 _
     toString="operations.cell.vornr.&V[k]"  _

     delimiter="&V[TAB]"

  

   // operation text
    CopyText fromText="cells" line=4 _

     toString="operations.cell.ltxa1.&V[k]"  _
     delimiter="&V[TAB]"

 

   // workplace
    CopyText fromText="cells" line=5 _
      toString="operations.cell.arbpl.&V[k]"  _
      delimiter="&V[TAB]"

 

   // system status

   CopyText fromText="cells" line=6  _
     toString="operations.cell.sttxt.&V[k]" _ 
     delimiter="&V[TAB]"

 

   // next row

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

   goto next_row

 

 endif

 

 Set V[opcount] &V[k] - 1

 

 return "S: &V[opcount] operations loaded" -statusline

Remarks

  •  The InputScript assumes a fixed file name. Add SelectFIle if you want to let the user choose the Excel file.
      

  • If you do not want to overwrite the current clipboard content, save the clipboard when the InputScript starts and restore its content at the end:
    CopyText toText="clip" -fromClipboard 
     ....
    CopyText fromText="clip" -toCLipboard      
     

  • The Excel process started in the JavaScript function may be running for a certain period of time after the JavaScript call, visible in WIndows Task Manager, since the JavaScript Garbage Collector does not stop the process immediately. To avoid this, call the Garbage Collector at the end of your JavaScript function:

     ..
    XL = null;

    // Optionally: call up JavaScript Garbage Collector to free Excel immediately
    if (typeof (CollectGarbage) == "function") CollectGarbage();

    return "X";

Components
InputAssistant + Controls