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.
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.
3.4. Write the script
In the present tutorial, we’re implementing only the getFeed() method.
That’s enough for the topics that we want to showcase.
What are we going to do in the script?
Our tasks can be grouped into the following steps:
1. Get the location of the Excel file
2. Load the Excel file
3. Parse the Excel
4. Store the data in a List of Maps
5. Set the List as response body of our service
OK, let’s start with the coding.
1. Read the destination to retrieve the location of the Excel
Within this section, we’ll learn how to access information from the configured Destination.
Remember:
the Destination is configured in the SMP server and is used to maintain the URL of an ABAP system or the URL of a Database or the persistence unit name of a JPA model.
In our scenario, we assume that the Excel file can be accessed via an HTTP URL.
However, in order to make life easier for testing, we do a little trick:
We store our test-Excel file on our local file system
We create an HTTP destination where we replace the drive symbol C:/ with the http:// protocol
In our script, we obtain the destination and replace the http:// back to C:/
Please note that this is only a workaround that we use in our tutorial. It is of course not an official recommendation.
The information about the destination that is configured at runtime, can be accessed from a header.
Then we can ask the destination configuration for the URL:
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();// URL looks like this: http://temp/obd/OBDCodes.xls
var fileName = destinationURL.replace("http://", "C:/");
return fileName;
}
This code requires the following import statement:
importPackage(com.sap.gateway.core.api.destination.configuration);
2. Load the Excel file
Now that we have the path to the Excel file, we can load it.
We’re using a helper class provided by apache.commons. This is available in SMP 3.0, so we can simply use it.
The required imports are:
importPackage(java.io);
importPackage(org.apache.commons.io);
and the code:
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;
}
3. Parse the Excel
As mentioned above, for parsing the Excel file we’re referencing the POI library, which we have already installed in SMP server.
Now we can use it - and therefore we need the import statement:
importPackage(org.apache.poi.hssf.usermodel);
First, we load the Excel workbook from the input stream that we’ve created in the previous section.
Since our Excel file has only one sheet, we can obtain it easily.
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;
}
The sheet contains the data, so now we can loop over all rows.
rowIterator = sheet.rowIterator();
while (rowIterator.hasNext()) {
var row = rowIterator.next();
We have to care about the first row, which is the header of our table:
if (row.getRowNum() == 0){
continue;
}
Each row can be asked for its cells, according to its order in the Excel worksheet.
var cellObdID = row.getCell(0);
In our Excel, the data type of the rows is String, so we get along with the following code
if(cellObdID.getCellType() == HSSFCell.CELL_TYPE_STRING){
obdId = cellObdID.getRichStringCellValue().getString().trim();
That’s it for the parsing
4. Store the data in a List of Maps
After we’ve parsed one row of the Excel sheet, we have the data for one <entry> in the sense of an atom feed of our OData service.
We've stored the value of each of the 6 cells of the row in a variable.
So now we can convert the data (the 6 variables) into the format that is required by the Integration Gateway Framework (IGW).
You should be already familiar with scripting in IGW, so I don’t need to explain.
Just a reminder:
At the end, each <entry> of our OData service consists of pairs of propertyName – propertyValue
All properties of the Entity Type that is defined in our OData model are put into one instance of java.util.HashMap
One HashMap is created for every entry of the OData collection
All instances of HashMaps are put into a java.util.ArrayList
As such, a map represents an <entry> and the list represents the <feed>
This ArrayList is at the end returned to the IGW framework by setting it as response body of the message object in the callback method getFeed()
One more point to consider.
We’ve defined a Complex Type in our OData model. The Complex Type as well consists of pairs of propertyName – propertyValue.
So we create a second HashMap which represents the Complex Type.
This instance of HashMap is then set as value to the property of the OData model that has the data type “Complex Type”:
obdCodeMap.put("Description", complexTypeObdDescriptionMap);
As a reminder, the details about our OData model.
We can see that the type of the "Description" property is specified as Complex Type:
(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
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: |
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:
Tutorial for Integration Gateway:
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
- SAP Managed Tags:
2 Comments
