Connecting Apps for Office with SAP - Part II: Excel App
Juergen Grebe, Microsoft
Holger Bruchelt, SAP
This document is part of series of articles and code samples on the topic of Connecting Apps for Office with SAP.
- Connecting Apps for Office with SAP - Part I: Word App
- Connecting Apps for Office with SAP - Part II: Excel App
- Connecting Apps for Office with SAP - Part III: Mail App
The app described in this document is available as a free download from the Office Store. Look for the 'SAP Material Info' app.
1.1 Apps for Office
With Office 2013, Microsoft has introduced the apps for Office platform which enables the user to experience the power of the web within the Office 2013 applications (Word, Excel, Outlook, PowerPoint, and Project).
An app for Office is basically a webpage that is hosted inside the Office client application. You can use apps to extend the functionality of a document, workbook, email message, or appointment. Apps can run in multiple environments and clients, including rich Office desktop clients, Office Web Apps, mobile browsers, and also on-premises and in the cloud. After you develop your apps and publish them to the Office Store or to an onsite catalog, they will be available to consumers within their Office 2013 and Office 365 applications.
These are document-centric apps that can assist the user when working with one or more documents in Word, Excel, PowerPoint and Project.
These are document-centric apps that add embedded content/functionality into a document in Excel and Excel Web Application.
These are mailbox-centric apps in the Outlook client or Outlook Web App (OWA) client and they extend Outlook items with custom UI and behaviors.
An app for Office can provide the user experience of any modern web application. What makes the platform really powerful is the ability of the
app to interact with a document through an API. The app can read and write to a document, workbook, presentation, project plan, message or appointment and handle application and user events.
This document focuses on the specific aspects of developing an app for Office that connects with an SAP business system. The solution outlined in this document is a simple solution which illustrates the capabilities of the new app for Office platform and the types of enterprise productivity scenarios that it enables.
1.2 SAP Material Info App
The app described in this document is the SAP Material Info app, a task pane app in Excel. The scenario for the sample app is a user in Excel who needs to add or update material (product) information in a spreadsheet, for example when creating a price list. Material data, including the material number, description, dimensions or component information is maintained in the material master module of the SAP ERP system. With the SAP Material Info app, the user is able to easily find a material record from the SAP system, verify the material numbers on the price list, view an image of the material or insert the component list (Bill of Materials) into the spreadsheet. The user has access to SAP information directly from within the Excel application.
Using the standard Apps for Office button on the Office 2013 ribbon, the user can browse an app catalog, find the app and insert it into the application.
An app for Office can be published to the public Office Store or to an organization’s internal app catalog on a SharePoint server. For this sample app, a SharePoint Online site in Office 365 was provisioned as the app catalog, making the app available under the MY ORGANIZATION category.
Click on any of the embedded images below for a better rendering.
With the app inserted in Excel, the user can find material information from a connected SAP ERP system, either by selecting a cell with the material number, or by entering the material number in the task pane. As the user starts typing in the SAP Material input field, the app will provide autocomplete functionality to assist in identifying a matching material record.
The user can get the details for a material, including the description, the material type, unit and weight.
The user can select the Image tab to view an image of the material. The image of the material is stored in a SharePoint library and displayed by the app based on the selected material number.
With the functionality provided on the Bill of Materials tab, the user is able to get the components of the material from SAP and insert them in form of a table or text in the current worksheet.
2.1 App Hosting
The web application files of your app for Office have to be hosted on a web server. You have the choice between two approaches for how your app for Office will be hosted.
Provider hosted – the web app is hosted in a web site hosting infrastructure of your choice, for example in a Windows Azure web site.
Autohosted – the web app is hosted in a Windows Azure web site that is auto-created behind the scenes and associated with a SharePoint Online account. For this hosting option you will need to use Visual Studio 2012 to package the app and an Office 365 account. Note that this approach only works for task pane and content apps, not for mail apps.
For the sample SAP Material Info app, the app was autohosted to illustrate how the app can work together with an Office 365 environment.
2.2 SAP Data Access
2.2.1 Material Data in SAP ERP
Material data in an SAP ERP system is accessible through BAPIs. The business object RetailMaterial with its methods GetDetail (function module BAPI_MATERIAL_GETDETAIL) and GetList (function module BAPI_MATERIAL_GETLIST) can be used to read basic material information from SAP.
For Bill of Material data, two custom function modules Z_MATERIALBOM_GETITEM and Z_MATERIALBOM_GETLIST were developed. Both function modules internally call the standard SAP function module CSAP_MAT_BOM_READ which can be used to read Bill of Material information.
Using the approach of writing a custom ABAP wrapper function module has the benefit of allowing you to optimize for performance for larger data sets by only returning the data that you need to display in the app. It also allows you to return the data in a structure that can simplify the data model definition in NetWeaver Gateway.
Using SAP NetWeaver Gateway, access to business data in SAP ERP can be enabled for a variety of consumers and devices in a standardized format based on the REST/OData protocols. NetWeaver Gateway provides a number of different configuration and integration options.
For this sample application, a data model was developed using the built-in Service Builder tool. The data model defines the entities (Material, MaterialBOM), the entity sets MaterialCollection, MaterialBOMCollection) as well as the basic read operations for the entities (Query and Read). The data provider interfaces and backend operation proxies are auto-generated by the Service Builder tool in NetWeaver Gateway and do not require any additional coding effort.
2.3 Security and Authentication
2.3.1 Same-origin Policy
There are a number of ways of overcoming same-origin policy enforcement when you develop an app for Office, however some of them require support on the resource server. Among the techniques that are often referenced as ways to deal with the same-origin policy issue are JSON/P and CORS (cross-origin resource sharing), however they are currently not supported on SAP NetWeaver Gateway.
Another technique is to set up a reverse proxy on the web server. With a reverse proxy, the web app can forward a request to the resource server, without the browser knowing that another remote server is involved. Because our sample app was autohosted in an Office 365 environment, a reverse proxy could not be configured.
The approach used for the sample app was to use a server-side request handler (server-side proxy). The request handler is part of the web app and invoked by the client-side script with a relative URI. The request handler does the work of making the resource request to the SAP NetWeaver Gateway server and passes the result set back to the client script.
2.3.2 App Permissions
The developer can specify the level of permissions that the app will require in the app manifest file, ranging from Restricted to ReadWriteDocument. The end user has visibility to the requested permission of the app before the app is installed and loaded for the first time. For the sample app, the permission level is ReadWriteDocument.
2.3.3 SAP NetWeaver Gateway Security
For incoming requests from a client application, the SAP NetWeaver system supports several authentication mechanisms. In addition to basic authentication, X.509 client certificates and SAML assertions are supported for single sign-on (SSO) scenarios.
For the sample SAP Material Info app, basic authentication over HTTPS was implemented. Note that for solutions that modify SAP data (e.g. POST requests), SAP NetWeaver Gateway requires a CSRF (Cross-Site Request Forgery) token to be retrieved and then submitted with the modifying request. For the connection to the SAP ERP system, a trusted RFC connection was configured between the SAP NetWeaver Gateway system and the SAP ERP system.
To optimize for performance, the sample solution only retrieves the data from the SAP ERP system that the user requires in the app. There are two possible approaches to this, both were utilized in the sample solution.
In the first approach, a standard function module in the SAP ERP system is utilized. In the model on the NetWeaver Gateway, only the fields that the consuming app requires are mapped to the entity and its operations. This approach was used for the Read and Query operation of the Material entity. The BAPI methods return additional material information that is not mapped in the data model.
In the second approach, a custom function module was developed in the SAP ERP system which is optimized for the consuming app, i.e. it only returns the data that the user requires. This approach was used for the Read and Query operation of the MaterialBOM entity. The advantage of the first approach is that no ABAP development has to be done in the SAP ERP system and depending on the complexity of the data structures it may not require any adaptation coding in the model in NetWeaver Gateway either. The second approach has the advantage that the optimization can occur directly at the source of the data. Often a small wrapper can be very useful, does not require much effort to implement and greatly simplifies the development of the model.
3 Sample Solution Details
This section describes the main elements and code that was developed for the SAP Material Info app.
The complete solution with source code is available on MSDN: Connecting Apps for Office with SAP - Part II: Sample Material Info App. You will need Visual Studio 2012, the Microsoft Office Developer Tools for Visual Studio 2012 and Word 2013 to compile and run the sample solution.
3.1 App Manifest
The XML manifest file of an app for Office enables the developer to declaratively describe properties that identify and describe the app for deployment. This includes the display name, version, provider name, icon, source location of the app. The manifest also specifies the capabilities of the app, i.e. the Office documents (Document, Workbook, Project Plan, Presentation) that the app can be used with.
3.2 App Loading and Initialization
The Home.js file contains the initialize function that must be executed when a page is loaded. It will make the Office.context property available for other functions that follow. Common app functionality, such as notifications, are defined in the App.js file of the project and are initialized with the app.initialize()function call. The event handlers for the UI controls are registered using JQuery methods.
An event handler is registered for the documentSelectionChanged event which is triggered when the user selects a different cell or range in the worksheet.
Finally, the getMaterialList() function is called to get a list of material records from SAP.
3.3 Task Pane Content
When the app is loaded, the Home.html page is displayed in the task pane. The page is divided into a header and a content area.
The header area displays the SAP logo with a background image that is defined in App.css. The content area displays an accordion control with four sections: SAP Material (default), Image, Bill of Materials and Settings.
In the SAP Material section, the user can enter a material number or simply select a cell with a material number. Using the Get button, the material details will be displayed in the content area.
In the Image section, the app will display an image of the selected material. The app will look for an image with the file name <material>.jpg in a location that can be specified in Settings, for example a folder or SharePoint library.
In the Bill of Materials section, the user is able to insert the Bill of Materials list for the selected material in the current worksheet. The list can be inserted in a table format or as plain text.
In the Settings section, the user can specify the location for the material images, for example a folder or a SharePoint library. To display an image in the Image section, the app will look for a file with the name <material>.jpg in the specified location.
Please refer to the source code for the complete version of this file.
3.3.2 selectionChangedHandler (eventArgs)
The user can either enter a material number in the task pane (with autocomplete support) or select a cell in the current worksheet which contains a material number. The selectionChangedHandler function is invoked when the user selects a new cell or range and the content of the selection is copied to the material input field in the task pane.
3.4 Reading Material Records and Autocomplete
When the app is loaded, a list of material records is retrieved from SAP to enable the autocomplete function of the material input field.
Reading the material records involves two requests. The first request is a JQuery AJAX request to an ASP.NET request handler. The request handler uses the .NET WebClient.DownloadString()method to get the material records from the NetWeaver Gateway OData resource.
This function makes a JQuery AJAX request to the .ashx request handler to retrieve a list of materials. After the successful return of material records from SAP, the data is formatted into a label/value array and the formatMaterialList() function is called to enable the autocomplete functionality in the input field.
This function makes a JQuery AJAX request to the .ashx request handler to retrieve the details for a specified material. After the successful return of the material details from SAP, the information is displayed in the UI fields.
The request handler performs the actual work of making the OData request to the SAP NetWeaver Gateway server. The request handler adds the credentials to the request and invokes the Query or the Read operation for the Material or the MaterialBOM entities, depending on “entity” and “id” parameters in the request URL. The certificate validation callback function was added to handle situations where a name mismatch can occur between the domain name in the certificate and the domain name in the request URL (e.g. when the NetWeaver Gateway system is cloud-hosted).
The JSON formatted response data is sent back to the client script.
This function enables the autocomplete function on the material input. The material list that was generated by the getMaterialList() function is used as the data source for the JQuery-UI autocomplete widget.
3.5 Reading Bill of Materials and Insert into Worksheet
When the user selects the Insert button in the Bill of Materials section, the function insertBOM() is called which will verify the input parameters and then invoke the request handler (described above) to make the OData request to the SAP NetWeaver Gateway server.
This function makes a JQuery AJAX request to the .ashx request handler to retrieve the bill of materials for a material. After the successful execution, the bill of material data is formatted into an Office.TableData object, either as a table with headers or as plain text.
This function inserts an Office.TableData object into the current worksheet. If the worksheet area is not empty, an error is displayed.
3.6 Material Data in SAP ERP
The app uses the standard SAP BAPI methods BAPI_MATERIAL_GETDETAIL and BAPI_MATERIAL_GETLIST to read material data from SAP ERP.
For Bill of Material information, two custom function modules Z_MATERIALBOM_GETITEM and Z_MATERIALBOM_GETLIST were developed.
3.6.1 Structure ZBOMRECORD
A custom structure ZBOMRECORD is used to return the bill of material records as table with function module Z_MATERIALBOM_GETLIST. The structure only contains the fields that are required by the app, i.e. the material number, plant, BOM usage, item category, BOM item number, BOM component, component quantity and component unit of measure.
Click on any of the images below for a better rendering.
3.6.2 Function Z_MATERIALBOM_GETLIST
The remote enabled custom function Z_MATERIALBOM_GETLIST is used to return the bill of materials records for a given material, plant and usage type. Internally, the standard function CSAP_MAT_BOM_READ is called to read the BOM data. The data is returned as a table of structure ZBOMRECORD.
The source code for this function is included in the sample code for this app.
3.6.3 Function Z_MATERIALBOM_GETITEM
The remote enabled custom function Z_MATERIALBOM_GETITEM is used to return a single bill of materials record for a given material, plant and usage type. Internally, the standard function CSAP_MAT_BOM_READ is called to read the BOM data. This function was implemented for consistency, to enable both the Query and Read operations on the MaterialBOM entity via the OData protocol. The function is not actually used by the app, only Z_MATERIALBOM_GETLIST described above.
The source code for this function is included in the sample code for this app.
3.7 NetWeaver Gateway Data Model
3.7.1 Entity Type Material
You use the SAP NetWeaver Gateway Service Builder (transaction SEGW) to define your data model. The Material entity was defined using the menu option Import -> RFC/BOR interface and is based on the method BAPI_MATERIAL_GETDETAIL.
3.7.2 Entity Type MaterialBOM
The Material entity was defined using the menu option Import -> RFC/BOR interface and is based on the method Z_MATERIALBOM_GETITEM.
3.7.3 Entity Sets MaterialCollection and MaterialBOMCollection
You define the MaterialCollectionand MaterialBOMCollection entity sets based on the two entities.
3.7.4 MaterialCollection - GetEntity (Read) Mapping
The GetEntity operation is mapped to the GetDetail method of business object RetailMaterial. The property MaterialNo is used as input parameter.
3.7.5 MaterialCollection - GetEntitySet (Query) Mapping
The GetEntitySet operation is mapped to the GetList method of business object RetailMaterial. A constant value of ‘*’ is used as input parameter and the material number and description are used as output parameters.
3.7.6 MaterialBOMCollection - GetEntity (Read) Mapping
The GetEntity operation is mapped to data source method Z_MATERIALBOM_GETITEM. The properties Material, Plant, BomUsage and ItemNo are used as input parameters.
3.7.7 MaterialBOMCollection - GetEntitySet (Query) Mapping
The GetEntitySet operation is mapped to data source method Z_MATERIALBOM_GETLIST. The properties Material, Plant and BomUsage are used as input parameters.
3.7.8 Service Maintenance
Using transaction /IWFND/MAINT_SERVICE you can maintain the OData service in NetWeaver Gateway. Ensure that your service is created, that you have an active OData service (ICF) node and that a system alias for the SAP ERP system is assigned.
3.7.9 Testing the OData service
Using the SAP NetWeaver Gateway Client (transaction /IWFND/GW_CLIENT), you can test the service. For example, to test the Query operation for the material entity, specify as Request URI: /sap/opu/odata/sap/Z_OFFICE_APPS_DEMOS_SRV/MaterialCollection.
3.8 Publishing the app
In your Visual Studio project, in Solution Explorer, select the project for your app to display its properties. To deploy and host your app in a SharePoint online server, select the Autohosted app for SharePoint option in the project properties.
After you publish the app, a deployable .app file is created that you can import to SharePoint.
As a perquisite you need to have an app catalog configured on your SharePoint Online server. Go to the Apps for SharePoint library and upload the .app file for the SAP Material Info app. Note that even though you have developed an App for Office, the deployment procedure for autohosted apps is to upload the .app file to the Apps for SharePoint library. During this step, you can specify details about your app that the user will see while browsing the app catalog, including an icon for the app, a category, the publisher or screenshots of the app.
Switch to Site Contents and select add an app. Select the MaterialInfo app and when prompted select to Trust it.
3.9 Using the app
For the user to be able to browse the SharePoint app catalog, the catalog has to be added to the list of Trusted Catalogs in Office. In your Office application (e.g. Word), go to File -> Options -> Trust Center -> Trust Center Settings -> Trusted App Catalogs and add the Url of the app catalog to the list.
The apps in the catalog are now available in the Insert -> Apps for Office -> See All dialog, in the My Organization category.
The sample SAP Material Info solution outlined in this document illustrates how an app for Office can be connected with an SAP ERP system and enable new types of enterprise productivity solutions. The app provides an easy-to-use and responsive user experience that consumers are expecting from today’s web applications. Further, the app illustrates how SAP data can be integrated in to a productivity scenario in a way that was previously not possible.
The solution showcases SAP NetWeaver Gateway, a technology from SAP to connect consumers and devices to data inside an SAP business system. Using the Service Modeler in SAP NetWeaver Gateway, data can be exposed in a standardized OData format and does not require the app developer to have any specific SAP knowledge. Although outside of the scope of this document, single sign-on and write-back scenarios are enabled by SAP NetWeaver Gateway.
5 For more Information
Apps for Office and SharePoint Dev Center: http://dev.office.com
Apps for Office and SharePoint Blog: http://blogs.msdn.com/b/officeapps
SAP NetWeaver Gateway Community: http://scn.sap.com/community/netweaver-gateway
SAP NetWeaver Gateway Online Help: http://help.sap.com/nwgateway
6 About the Author
Christoph Schuler has been involved in the design and development of integrated SAP solutions for over 15 years. He has been leading the design and development of commercial software products for Microsoft/SAP interoperability, including the .NET Data Provider and Duet Enterprise. Christoph works as a consultant, architect and instructor for Microsoft/SAP solutions and is the principal consultant at Connected ERP. You can reach him at email@example.com.