Skip to Content
Author's profile photo Carlos Roggan

Integration Gateway: Understanding CUSTOM CODE data source [5]: MS Excel

Or:

How to expose Microsoft Excel as OData service

on SMP 3.0

based on Integration Gateway

This  blog is about Integration Gateway (IGW) in SAP Mobile Platform 3.0 (SMP).

The Integration Gateway component allows to expose backend data as an OData service, where some concrete data sources are supported  more or less automatically

Since SMP SP09, a new data source has been introduced: the “Custom Code” data source, that gives the developer more freedom to access different data sources (see below for more info).

In the present tutorial, I’d like to take the opportunity to show how this data source can be used to expose data from Microsoft Excel.

The goal is to showcase that any data which can be stored anywhere can be exposed via this “Custom Code” data source.

The imaginary scenario:

An office desk of a car manufacturer maintains the list of OBD codes in a Microsoft Excel sheet.

This list of OBD codes should be exposed by an OData service, such that it can be accessed by e.g. a mobile application.

Learnings:

– programmatically parse an excel sheet

– use the SMP toolkit to create an OData service that exposes the Excel data

The aim is:

– to showcase how easy it is to expose an excel sheet as OData service and

– the flexibility that the Custom Code data source gives to integration developers

As usual, the artifacts can be found attached to this blog.

Prerequisies

We will be programmatically reading an excel file. For that purpose we’ll be using the apache POI library, the Java API for Microsoft Documents

In order to make use of it, we need to install it in SMP 3.0 server.

Since the server is based on OSGi, we need the POI library packaged as an OSGi- bundle.

Such a bundle is available in the internet: org.apache.servicemix.bundles.poi-3.6_2.jar

It can be downloaded e.g. from here

http://www.java2s.com/Code/Jar/o/Downloadorgapacheservicemixbundlespoi362jar.htm

More Prerequisites:

  • SAP Mobile Platform 3.0 SP09 (SMP) installation.
  • Eclipse with SAP Mobile Platform Tools installed
    check this blog for help
  • Basic knowledge about SMP and Integration Gateway
    Check this blog for some introduction
  • Introduction to the “Custom Code” data source here.

Overview

1. Create the Excel file containing the data

2. Add POI lib to SMP 3.0 server

3. Create an OData service to expose the excel data

  3.1. Create SMP project

  3.2. Create the OData model

  3.3. Create the binding

  3.4. Write the script

4. Test the OData service

5. Summary

6. Links

1. Create the Excel file containing the data

Attached to this Blog you’ll find the Excel file that I’m using in this tutorial.

It contains a list of OBD codes.

Each OBD code contains some additional information, which I’ve added in some further columns.

They’re meant to showcase a bit more of OData capabilities, as we’ll see later

We have

  • the OBD code itself
  • the category of it
  • the short description
  • the long description
  • the URL of the docu
  • the URL of a corresponding video

The excel sheet looks as follows.

excelfile.JPG

Download the attached Excel file and copy to your local file system.

In our example, we’re using the location:

C:\temp\obd

2. Add POI lib to SMP 3.0 server

Background

In our present tutorial, we’re going to create an “OData service implementation project” in Eclipse, and in this project we’re going to use the apache POI library.

The project will be deployed into SMP server and it will be converted into a bundle.

A bundle can only have dependencies on other bundles, therefore we have to make the POI library available as a bundle in SMP.

We could manually convert the lib into a bundle, but such a bundle is already available in the internet: the apache servicemix bundle

In my tutorial, I’m using org.apache.servicemix.bundles.poi-3.6_2.jar

Install POI in the server

I’ve downloaded it from the internet, e.g. here to my local file system.

Then it has to be installed into SMP.

There are several ways of doing that.

The easiest one is:

Copy the jar file to the pickup folder of your SMP installation.

This is located at

<SMP_installdir>\Server\pickup

For example:

The SMP server supports the so-called “hot deployment”, so the server will notice that you’ve copied a bundle into the pickup folder and it will include it in its OSGi runtime on the fly.

Note:

This way of installing (hot deployment) is used rather for testing purpose. For productive environment, use the command line to really “install” the bundle into OSGi.

For more information about OSGi and installation procedure, you may have a look at my OSGi Blogs: http://scn.sap.com/docs/DOC-52346

and http://scn.sap.com/docs/DOC-52455 and http://scn.sap.com/docs/DOC-52456

After copying the jar file to the pickup folder, you should open the .state folder and check if the deployment has been successful, as shown by the below screenshot:

In case of error, you should check the console and also check the SMP error log and the OSGi log.

The reason might be a dependency issue.

Note: One more way to check the status of the deployed bundle is described in this blog, at section 3.5

3. Create an OData service to expose the excel data

Now that the apache POI library is available in SMP, we can use it from within the code that we’re going to write in the following chapter.

3.1. Create SMP project

In Eclipse, go to New->Project->SAP Mobile Platform->SAP Mobile Platform OData Implementation Project

Enter a project name like “OBDService” and a model name like “obdmodel” and press Finish.

3.2. Create the OData model

We will keep our OData service simple, so we create only one Entity Type.

Furthermore, we create a Complex Type, just for fun – to show that we can distribute the data

from: 1 row in excel

to: 2 artifacts in OData

I mean: alternatively, we could create one Entity Type which has 6 properties. But it is more interesting, to have a Complex Type with 4 properties and use the Complex Type as a complex property in the Entity Type:

3.3. Create the binding

In Eclipse, select the .odatasrv node, choose “Select Data Source” from the context menu and select “Custom Code” as data source.

In our tutorial we’re using Javascript for scripting.

(Note: the dotted line is not an association, it is the usage)

The code for the nested Maps:


// provide data for complex type into Map
var complexTypeObdDescriptionMap = new HashMap();
complexTypeObdDescriptionMap.put("ShortText", shortText);
complexTypeObdDescriptionMap.put("LongText", longText);
complexTypeObdDescriptionMap.put("DocumentationURL", docuUrl);
complexTypeObdDescriptionMap.put("VideoURL", videoUrl);
// provide data for entity type into Map
var obdCodeMap = new HashMap();
obdCodeMap.put("OBDcodeID", obdId);
obdCodeMap.put("Category", category);
obdCodeMap.put("Description", complexTypeObdDescriptionMap);
































And each instance of HashMap that represents an Entity Type is then added to the ArrayList:


var obdCodeMap = getOBDcodeFromRow(row);
obdCodeList.add(obdCodeMap);
































5. Set the List as response body of our service

We’ve looped over each row of the Excel file, converted each row into a map and added each map into an ArrayList

The last step is to send the instance of ArrayList back to the framework.


message.setBody(codes);
return message;
































The complete content of the script


function getFeed(message){
  importPackage(java.util);
  importPackage(java.io);
  importPackage(com.sap.gateway.core.api.destination.configuration);
  importPackage(com.sap.gateway.ip.core.customdev.logging);
  importPackage(org.apache.commons.io); // required for the FileUtils helper method
  importPackage(org.apache.poi.hssf.usermodel); // required for the Excel parser
  // 1st: get the path to the excel file from Destination
  //var fileName = new String("C:/temp/obd/OBDmaintenance_small.xls");
  var fileName = getQualifiedFileNameFromDestination(message);
  // 2nd and 3rd: read the data from the Excel and convert into List of HashMaps
  var codes;
  try {
      codes = readExcel(fileName);
  } catch (e) {
      log.logErrors(LogMessage.TechnicalError, "Failed to get the OBD codes...");
      throw new RuntimeException("Server error. Odata service couldn't get the OBD codes from excel data provider");
  }
  // 4th: set the data as response body and pass it to the framework
  message.setBody(codes);
  return message;
}
/* HELPERS */
function getQualifiedFileNameFromDestination(message){
  var destinationConfiguration = message.getHeaders().get("DestinationConfiguration");
  if (! destinationConfiguration){
      throw new RuntimeException("The service is not configured with a destination.");
  }
  if (! (destinationConfiguration instanceof HttpDestinationConfiguration)){
      throw new RuntimeException("The service is configured with a destination type. Must be type HTTP.");
  }
  var destinationURL = destinationConfiguration.getDestinationUrl();
  // destinationURL will look like this: http://temp/obd/OBDCodes.xls
  var fileName = destinationURL.replace("http://", "C:/");
  log.logErrors(LogMessage.TechnicalError, "\n===> After replace, the final Excel file path:  " + fileName);
  return fileName;
}
function readExcel(filename){
  var obdCodeList = new ArrayList();
  // get the excel sheet
  var sheet = getExcelSheet(filename);
  if(sheet == null){
      throw new Exception("Failed to obtain Excel sheet.");
  }
  // loop over all rows
  rowIterator = sheet.rowIterator();
  while (rowIterator.hasNext()) {
      var row = rowIterator.next();
      // avoid looping empty undefined rows
      if(row.getRowNum() > sheet.getLastRowNum()){
            break;
      }
      // skip the first row as it contains the header
      if (row.getRowNum() == 0){
            continue;
      }
      var obdCodeMap = getOBDcodeFromRow(row);
      if(obdCodeMap != null){
            obdCodeList.add(obdCodeMap);
      }
  }
  return obdCodeList;
}
function getOBDcodeFromRow(row){
  // note that emtpy cells are returned as null
  var cellObdID = row.getCell(0);
  var cellCategory = row.getCell(1);
  var cellShortText = row.getCell(2);
  var cellLongText = row.getCell(3);
  var cellDocuUrl = row.getCell(4);
  var cellVideoUrl = row.getCell(5);
  var obdId = "";
  if(cellObdID != null){
      if(cellObdID.getCellType() == HSSFCell.CELL_TYPE_STRING){
            obdId = cellObdID.getRichStringCellValue().getString().trim();
      }
  }
  var category = "";
  if(cellCategory != null){
      if(cellCategory.getCellType() == HSSFCell.CELL_TYPE_STRING){
            category = cellCategory.getRichStringCellValue().getString().trim();
      }
  }
  var shortText = "";
  if(cellShortText != null){
  if(cellShortText.getCellType() == HSSFCell.CELL_TYPE_STRING){
  shortText = cellShortText.getRichStringCellValue().getString().trim();
  }
  }
  var longText = "";
  if(cellLongText != null){
      if(cellLongText.getCellType() == HSSFCell.CELL_TYPE_STRING){
            longText = cellLongText.getRichStringCellValue().getString().trim();
      }
  }
  var docuUrl = "";
  if(cellDocuUrl != null){
  if(cellDocuUrl.getCellType() == HSSFCell.CELL_TYPE_STRING){
  docuUrl = cellDocuUrl.getRichStringCellValue().getString().trim();
  }
  }
  var videoUrl = "";
  if(cellVideoUrl != null){
      if(cellVideoUrl.getCellType() == HSSFCell.CELL_TYPE_STRING){
            videoUrl = cellVideoUrl.getRichStringCellValue().getString().trim();
      }
  }
  // provide data for complex type into Map
  var complexTypeObdDescriptionMap = new HashMap();
  complexTypeObdDescriptionMap.put("ShortText", shortText);
  complexTypeObdDescriptionMap.put("LongText", longText);
  complexTypeObdDescriptionMap.put("DocumentationURL", docuUrl);
  complexTypeObdDescriptionMap.put("VideoURL", videoUrl);
  // provide data for entity type into Map
  var obdCodeMap = new HashMap();
  obdCodeMap.put("OBDcodeID", obdId);
  obdCodeMap.put("Category", category);
  obdCodeMap.put("Description", complexTypeObdDescriptionMap);
  return obdCodeMap;
}
function getExcelSheet(fileName){
  var inputStreamExcelFile = getExcelFileAsStream(fileName);
  var workbook = null;
  try {
      workbook = new HSSFWorkbook(inputStreamExcelFile);
  } catch (e) {
      throw new Exception("Exception occurred while creating HSSFWorkbook from inputStream. Could not read xls file", e);
  }
  var sheet = workbook.getSheetAt(0);
  if (sheet == null) {
      throw new Exception("Excel Sheet not found in ExcelWorkbook");
  }
  return sheet;
}
function getExcelFileAsStream(fileName){
  var file = new File(fileName);
  var inputStreamExcelFile = FileUtils.openInputStream(file);
  if(inputStreamExcelFile == null){
      log.logErrors(LogMessage.TechnicalError, "Failed to read file in the JS script");
      throw new Exception("Excel file not found at " + fileName);
  }
  return inputStreamExcelFile;
}
/*
* Other callback methods that have to be implemented
*/
function getEntry(message) {
  return message;
}
function createEntry(message) {
  return message;
}
function updateEntry(message) {
  return message;
}
function deleteEntry(message) {
  return message;
}
































4. Test the OData service

Deploy the service

After we’ve finished the implementation, we can execute “Generate&Deploy Integration Content”  from the context menu of the project.

Once the deployment of the service has finished, we can check that is has been successfully registered in the Admin page of SMP server

Create Destination

We open the Admin page of our SMP server at

https://localhost:8083/Admin/

Then we navigate to OData services -> Destinations

We press on New to create a new Destination and enter the following details:

Destination Name an arbitrary name, e.g. EXCELFILE
Destination Type HTTP
URL

Here we need to give the path and the name of the excel file as we downloaded from SCN and saved on local file system.

Note that the C:\  has to be replaced by http:// and the backslashes have to be replaced by forward slashes.


In our example, we enter the following path as URL:

http://temp/obd/OBDmaintenance_small.xls

Assign Destination

The newly created destination can now be assigned to the service that we have deployed

Invoke the OData service

Now we’re done and we can invoke the service at

https://localhost:8083/gateway/odata/SAP/OBDSERVICE;v=1

And the list of OBD codes at

https://localhost:8083/gateway/odata/SAP/OBDSERVICE;v=1/OBDcodes

The service accesses the excel sheet each time you execute the request.

You can now go ahead and modify the excel sheet and you’ll see the changes reflected when you call the service again

5. Summary

In the present tutorial we’ve learned to use the “Custom Code” data source to connect to a new and different kind of data storage.

The tutorial is based on the Integration Gateway component which comes along with SMP 3.0 SP09.

Additionally, we’ve learned how to programmatically access the Destination information.

And how to programmatically parse an Excel file.

And we’ve learned how to use ArrayList and nested HashMaps as data structure for providing EntityCollection (getFeed) to Integration Gateway.

6. Links

Installing SMP toolkit:

http://scn.sap.com/community/developer-center/mobility-platform/blog/2014/08/22/how-to-install-sap-mobile-platform-tools-for-integration-gateway-in-eclipse-kepler

Tutorial for Integration Gateway:

http://scn.sap.com/community/developer-center/mobility-platform/blog/2014/06/10/creating-an-odata-service-based-on-sap-gateway-soap-jdbc-and-jpa-data-sources-ba

Understanding Custom Code data source part 1: read operations very simplified

Understanding Custom Code data source part 2: write operations

Understanding Custom Code data source part 3: Function Import

Understanding Custom Code data source part 4: example implementation

The series of tutorials about the REST data source. Many implementation details are also relevant for the “Custom Code” data source:

Integration Gateway: REST data source, overview of Blogs

Homepage Apache POI: http://poi.apache.org/

POI bundle download: http://www.java2s.com/Code/Jar/o/Downloadorgapacheservicemixbundlespoi362jar.htm

The official documentation of Integration Gateway in SMP can be found via the following navigation:

help.sap.com -> Technology Platform -> SAP Mobile Platform -> SAP Mobile Platform Server SP 10 -> Data Integration -> Integration Gateway

Or use the direct link

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin Buschlüter
      Martin Buschlüter

      again useful, thanks

      Author's profile photo Carlos Roggan
      Carlos Roggan
      Blog Post Author

      glad when it helps you;-)