WSO2 DSS - Exposing Excel Data in Query Mode

In Query mode you can query data in the spreadsheet using SQL statements. The query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. The org.wso2.carbon.dataservices.sql.driver.TDriver class is used internally as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google spreadsheets, Excel sheets etc. Internally it use the Apache POI - the Java API for Microsoft Documents to read and modify documents [1].

The below sample use DSS 3.5.1 with DSSTest.xls excel file. Download the DSSTest.xls from [2] and update the file system location in the URL field.

Data Service

<data name="ExcelTest" transports="http https local">
   <config enableOData="false" id="ExcelDS">
      <property name="driverClassName">org.wso2.carbon.dataservices.sql.driver.TDriver</property>
      <property name="url">jdbc:wso2:excel:filePath=/home/Anupama/DSSTest.xls</property>
   </config>
   <query id="QueryData" useConfig="ExcelDS">
      <sql>Select AlertID, Owner, AlertType from Alerts where AlertType &gt; 3</sql>
      <result element="Entries" rowName="Entry">
         <element column="AlertID" name="AlertID" xsdType="string"/>
         <element column="Owner" name="Owner" xsdType="string"/>
         <element column="AlertType" name="AlertType" xsdType="string"/>
      </result>
   </query>
   <query id="InsertData" useConfig="ExcelDS">
      <sql>Insert into Alerts(AlertID, Owner, AlertType) values (?,?,?)</sql>
      <param name="ID" sqlType="INTEGER"/>
      <param name="Owner" sqlType="STRING"/>
      <param name="Type" sqlType="INTEGER"/>
   </query>
   <operation name="GetData">
      <call-query href="QueryData"/>
   </operation>
   <operation name="InsertData" returnRequestStatus="true">
      <call-query href="InsertData">
         <with-param name="ID" query-param="ID"/>
         <with-param name="Owner" query-param="Owner"/>
         <with-param name="Type" query-param="Type"/>
      </call-query>
   </operation>
</data>

Request and Response

http://localhost:9763/services/ExcelTest.SOAP11Endpoint/

For Get Data

Request :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://ws.wso2.org/dataservice">
   <soapenv:Header/>
   <soapenv:Body>
      <dat:GetData/>
   </soapenv:Body>
</soapenv:Envelope>

Response

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <Entries xmlns="http://ws.wso2.org/dataservice">
         <Entry>
            <AlertID>2.0</AlertID>
            <Owner>James</Owner>
            <AlertType>4.0</AlertType>
         </Entry>
         <Entry>
            <AlertID>4.0</AlertID>
            <Owner>Jane</Owner>
            <AlertType>11.0</AlertType>
         </Entry>
      </Entries>
   </soapenv:Body>
</soapenv:Envelope>

For Insert Data:

Request:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:dat="http://ws.wso2.org/dataservice">
   <soapenv:Header/>
   <soapenv:Body>
      <dat:InsertData>
         <dat:ID>30</dat:ID>
         <dat:Owner>Smith</dat:Owner>
         <dat:Type>1</dat:Type>
      </dat:InsertData>
   </soapenv:Body>
</soapenv:Envelope>

Response :

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <axis2ns3:REQUEST_STATUS xmlns:axis2ns3="http://ws.wso2.org/dataservice">SUCCESSFUL</axis2ns3:REQUEST_STATUS>
   </soapenv:Body>
</soapenv:Envelope>

References : 

[1] https://poi.apache.org/spreadsheet/index.html
[2] https://github.com/anupama-pathirage/DemoFiles/raw/master/Blog/Excel/DSSTest.xls