The latest release of SAP NetWeaver Gateway productivity accelerator for Microsoft (GWPAM) works on Visual Studio 2013, 2012, and 2010.
Oh! By the way, at the time of writing, the name of the product was changed to SAP Gateway for Microsoft (GWM). In order to get around the name, and avoid confusing ourselves, we will use the name GWM for Visual Studio throughout this article.
If you want to see what’s in the latest release, SP02, I recommend you take a quick look at the blog, SAP Gateway for Microsoft Service Pack 02 -- Now available!
GWM for Visual Studio exposes SAP data through OData services in SAP NetWeaver Gateway. It allows you to create add-in templates for your Outlook or Excel application that binds an OData service in SAP NetWeaver Gateway.
SAP NetWeaver Gateway provides sample OData services that we can use. For this article, we will use the GWDEMO service.
To complete the steps in this article, we assume that you have already installed GWM for Visual Studio, and you have access to an SAP NetWeaver Gateway system. If you have not done that, so download the GWM for Visual Studio from the SAP Software Download Center at: http://service.sap.com/swdc, go to Support Packages and Patches –> G, and find GWM (A.K.A GWPAM).
Install it so that you can create GWM projects in Visual Studio. The complete documentation, including the installation guide is at: http://help.sap.com/nwgwpam
The version of the Microsoft Office application in which you want to run the add-in determines which GWM project to use. If you use the GWM projects for Visual Studio 2010, your add-in will function only in the Microsoft Office 2010 applications, and not in 2013.
Find detailed information about the product matrix at: https://service.sap.com/sap/support/pam, and then go to GWM 1.0 (A.K.A GWPAM).
Once you have the installation and access to SAP Gateway out of the way, you are ready to create a new SAP business solution in Visual Studio using GWM (formerly GWPAM).
This article is in two parts, just to make it easier to follow. The first part shows how to quickly generate code for an Excel add-in that presents SAP data in Microsoft Excel, and how to modify the generated code to change the content that is presented.
For the purposes of this article, we used Microsoft Office 2013, and Visual Studio 2012.
I’d like us to thank Alla Cherkes and Alex Avizov for the original content which has been added to the Developer Guide.
In addition, I acknowledge the contributions and guidance of many others, including, the CPS team, Itay Assraf, Roy Fishman, Sagi Lefler, Zahi Libfeld, Shimon Tal, Dalit Tzur, and Nachshon Vagmayster.
Starting the GWPAM Excel Add-In Wizard
In Visual Studio, choose GWPAM under New Project -> Installed -> Templates -> Visual C# node, and select GWPAM Excel 2013 Add-In
Choose OK to accept the default options for your project. The wizard for creating the Excel add-in displays.
Next, we will follow the instructions in the wizard pages to quickly generate the code for our Excel add-in.
Binding the OData Service to Microsoft Excel
We know the name of the OData service, GWDEMO, we want to use, but we do not know the URL for service. From the wizard, we can connect to the SAP Gateway system to find the service.
In the Excel Binding Wizard, choose the BROWSE.
In the Browse Services window that opens, enter the hostname of the SAP Gateway system or the IP address and the port number.
Enter your user name and password for logging onto to the SAP Gateway system, and select the Use Default Client and Use SSL Connection.
Note: If you do not have access to SAP Gateway, you can request for a demo system.
In the Connection to Service Server window, enter the credentials that you received after signing up for access to SAP Gateway.
SAP feeds require authentication, however, GWPAM Excel add-in supports using basic authentication.
Enter the SAP Gateway system hostname or the IP address and the port number. In addition, provide your user name and password for logging onto to the Gateway system, and select the Use Default Client and Use SSL Connection.
Note: If you do not have access to SAP NetWeaver Gateway, I recommend that you request for a demo system before proceeding.
In the Connection to Service Server window, enter the credentials that you received after signing up for access to SAP Gateway. SAP feeds require authentication, however, the GWPAM Excel Add-in supports using basic authentication
.
Then choose CONNECT, and type the name of the service, GWDEMO, or find and select it, and then choose SELECT SERVICE.
Choose NEXT, and select the service collection you want to use, SalesOrderCollection. The properties belonging to the selected collection are displayed. The key property, SalesOrderKey, is identified as a key in the key column.
Above the list of properties, there are checkmark indicators showing the CUD operations that have been implemented in the service. We can see that our service has Createable and Updatable capabilities.
Choose NEXT. The list of properties display with up and down arrows on the right.
Remove the checkmark in the heading for the selection of all the properties. Find and select CustomerID, and then move it to the top of the list using the up arrow so that it will be the heading for the starting cell in Excel.
Next, select and move SalesOrderID to the second position, and then locate TotalSum after the key, SalesOrderKey. Locate and arrange the properties ChangedAt, BillingStatus, BillingStatusDescription and StatusDescription in the order as shown in the screenshot above.
Make sure that all the arranged properties are selected except the property, StatusDescription. Later, we will enable it using code.
The desired order of the properties determines the order of the records to be presented later when the Excel data table is created, the selected fields will automatically be added in the same order.
Choose NEXT, leave the default selections for Starting Reference Cell and User Operations, and then choose GENERATE PROJECT.
When Visual Studio finishes generating the Excel add-in, a summary of the generated information is presented in your default browser, and the project tree is created in the Solution Explorer.
From the Solution Explorer, right click your project name, for example GWPAMExcelAddin01, and choose Build.
Testing with Basic Authentication
From the Solution Explorer, open the class, BusinessConnectivityHelper.cs, double click on the method, HandleSAPConnectivity to open the code.
Go the line as shown below, and add the user name and password for logging into the SAP Gateway system:
switch(authenticationType)
{
case AuthenticationType.BASIC:
webRequest.Credentials = new System.Net.NetworkCredential( "<USERNAME>", "<PASSWORD>" );
break;
}
Save your changes, rebuild the project, and then choose Start to run the Excel add-in.
Notice that the column H is empty. Next, we will get data from the SAP system into that column.
Inserting Content into the Next Column
You will extend the generated code for your Excel add-in application as follows:
- Define the next column (in our case, the column H) in the data series in Excel for the property, StatusDescription
- Bind the property, StatusDescription, to the column H in Excel.
Remember that we did not select the property, StatusDescription in the wizard. Also note that, we have already generated the classes we need to call the OData service, GWDEMO. The service itself has not changed; we want to map an existing property, StatusDescription, by modifying the generated code.
The code snippets show the following:
- Editing the generated class, ExcelDataManager.cs, by determining the next column in the Excel data table, and then binding the applicable response data to it. This maps and binds the property to a column.
- Editing the generated class, SubmitChangesViewModel.cs, to perform an update.
To define the next column H and bind the property, StatusDescription, to it:
Go to the Solution Explorer of the generated project, GWPAMExcelAddin01, and open the class from Excel -> ExcelManager -> ExcelDataManager.cs
Find the method, CreateExcelBindingTable, and insert the code as shown below for determining the next column in the Excel data table.
this.ExcelDataTable.Columns.Add(GetDataColumn("StatusDescription", "System.String"));
Notice that you are expected to provide a property name, and the equivalent Visual C# data type formatting to the EdmType of the property.
Find the method, BindExcelTableWithRecords, and then add the relevant service response data binding to the column you added in Excel data table.
dataRow["StatusDescription"] = entity.StatusDescription;
Go to the method, FillEntityWithData, and then insert the code as shown below, to get data per row for the column.
if (!dataRow.IsNull("StatusDescription"))
{
entity.StatusDescription = dataRow["StatusDescription"].ToString();
}
Determining the View
Open the class UIScreens -> SubmitReport -> ViewModel -> SubmitChangesViewModel.cs, and then go to the method, GetSAPData, and insert the code snippet as shown below:
sapDataRow["StatusDescription"] = recordFromBE.StatusDescription;
Go to the method, UpdateRowValues, and insert code snippet as shown below:
rowToUpdate["StatusDescription"] = recordFromBE.StatusDescription;
Re-build and test your project for the Excel add-in application.
Notice that the column H, has the new heading, StatusDescription, and has data, New, for each row.
Next, we will remove the heading and the content for the current column F, BillingStatus.
Remove a Column
In this section, we will extend the generated code for the Excel add-in application as follows:
- Remove the existing column F, with the heading, BillingStatus, from our Excel
- Unbind the property, BillingStatus, of the OData service, GWDEMO, in our Excel
When we remove the existing column, the SAP data that is presented in that column is removed from our Excel.
Caution: Deleting columns can affect the Excel add-in application, where the deleted column is relevant for some functionality such as the Create, Update, and Delete capabilities implemented in the service.
Note that, the OData service has not changed, we want to unmap an existing property in the generated code for the Excel add-in. The code snippets show how to modify the generated class, ExcelDataManager.cs, to delete a column in the Excel data table.
From the Solution Explorer, open the class, from Excel -> ExcelManager -> ExcelDataManager.cs, and then go to the method, CreateExcelBindingTable, and delete (comment out) the line for the column.
this.ExcelDataTable.Columns.Add(GetDataColumn("BillingStatus", "System.String"));
Notice that you are expected to provide a property name, and the equivalent C# data type formatting to the EdmType of the property.
Go the method, BindExcelTableWithRecords, and delete (comment out) the line for binding the data to the column.
dataRow["BillingStatus"] = entity.BillingStatus;
Rebuild and test your generated project for the Excel add-in application.
Notice that, the column F has the new heading, BillingStatusDescription. Next, we will modify the filtering capabilities in part 2 of this article.
- SAP Managed Tags:
1 Comment
