How to expose Excel 97-2002 spreadsheet as a Data Service using WSAS - 2.0

Before you begin

If your not familiar with WSAS - 2.0 Data Services, please read my earlier post on getting start with WSAS-2.0 Data Service.(http://wso2.org/blog/sumedha/2573).

Step 1: Updating Data Service Library

The Data Service implementation that shipped with WSAS-2.0 does not support Excel files. Hence we need to update to latest Data service library.

1. Go to WSAS_HOME/lib and delete wso2data-service-2.0.jar.

2. Download the zip file (wso2data-service-2.0.2.jar_.zip) from the bottom of this page.

3. Extract it & you will get a jar file (wso2data-service-2.0.2.jar)

4. Drop this file to WSAS_HOME/lib

Step 2: Creating a sample Excel file

Place following content on a Excel file & save it.

Make sure you have ID,Model & Classification on three columns. (i.e. Do not paste the above content into a single cell)

Step 3 : Creating Data Service Configuration File

Create a file called 'ExcelService.dbs' having following content.

<data baseURI="xs:anyURI" name="ExcelService">
<config>
<property name="excel_datasource">[REPLACE WITH FULL PATH TO YOUR EXCEL FILE]</property>
</config>

<query id="allProducts">
<sql>UseSheet=0:StartReadingFromRow=1</sql>
<result element="Products" rowName="Product">
<element name="Id" column="1"/>
<element name="Name" column="2"/>
<element name="Classification" column="3"/>
</result>
</query>

<operation name="getAllProducts">
<call-query href="allProducts">
</call-query>
</operation>

</data>

Step 4 : Deploying the Service

Drop the file into WSAS_HOME/repository/dataservices folder. After a while, a new service ('ExcelService') will appear on your service listing screen.

Step 5 : Invoking the Service

You can invoke the service using 'Try it' feature that comes with WSAS-2.0 or by compiling & running following java class.

import org.apache.axiom.om.OMAbstractFactory;
import org.apache.axiom.om.OMElement;
import org.apache.axiom.om.OMFactory;
import org.apache.axiom.om.OMNamespace;
import org.apache.axis2.addressing.EndpointReference;
import org.apache.axis2.client.Options;
import org.apache.axis2.client.ServiceClient;
import org.apache.axis2.util.XMLPrettyPrinter;

public class SampleClient {
private static EndpointReference targetEPR = new EndpointReference("http://localhost:8080/axis2/services/ExcelService");
public static void main(String args[]){
try {
OMElement payload = getPayload();
Options options = new Options();
options.setTo(targetEPR);
//options.setProperty(Constants.Configuration.TRANSPORT_URL, url);
options.setAction("urn:getAllProducts");
ServiceClient sender = new ServiceClient();
sender.setOptions(options);
OMElement result = sender.sendReceive(payload);
XMLPrettyPrinter.prettify(result, System.out);
} catch (Exception e) {
e.printStackTrace();
}
}

private static OMElement getPayload() {
OMFactory fac = OMAbstractFactory.getOMFactory();
OMNamespace omNs = fac.createOMNamespace(
"http://example1.org/example1", "example1");
OMElement method = fac.createOMElement("getAllProducts", omNs);
return method;
}
}