This tutorial shows how to read data from an excel worksheet and display the result on the SAP screen.
We have a duty roster that maps malicious codes to the responsible employee. We want to determine an employee according to the code that has been typed in.

We add a new inputfield, a pushbutton and an outputfield to the SAP screen:

inputfield (20,1) "Read user to code from excel-file" (20,35) size="25" name="code"  default="TSC99"  -upperCase
inputfield (20,70) size="15" name="usertocode" -upperCase -noLabel
pushbutton (20,62) "ok" process="read_excel_va03.txt"

In the InputScript "read_excel_va03.txt" we call a VB.NET function and pass to it the name of the file and the code as a parameter:

callvb utilities.exceldotnet.TestReadCellsExcel "D:\Excel\duty_rota.xlsx" "&V[code]"
return

We implement the function "TestReadCellsExcel" in our class "exceldotnet" within the class library "utilities.dll". For interacting with MS Excel we make use of the interface that Microsoft provides, therefore we add a reference to the relevant class library to our project and also import the name space into our class:

 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

Imports Microsoft.Office.Interop
Imports guinet

Public Class exceldotnet

    Public Function TestReadCellsExcel(ByVal filename As String, ByVal code As String) As String

        Dim returncode = "1"
        Dim oXL As New Excel.Application
        Dim g As New guixt

        Try

            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet

            oXL = CreateObject("Excel.Application")
            oXL.Visible = False

            oWB = oXL.Workbooks.Open(filename)
            oSheet = oWB.ActiveSheet

            Dim range As Excel.Range = oSheet.UsedRange
            Dim c As Integer = 1

            For Each r In range.Rows

                If oSheet.Cells(c, 1).Value.ToString = code Then
                    g.SetVariable("usertocode", oSheet.Cells(c, 2).Value.ToString)
                    returncode = "0"
                    Exit For

                End If
                c += 1
            Next

        Catch

            oXL.ActiveWorkbook.Close(False)
            oXL.Quit()
            Return "Exception"

        End Try

        oXL.ActiveWorkbook.Close(False)
        oXL.Quit()

        Return returncode


    End Function

End Class