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.
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
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
again useful, thanks
glad when it helps you;-)