WSO2 DSS - Exposing Excel Data in Non Query Mode

If query mode is disabled for the spreadsheet, you cannot use SQL statements to query data in the excel sheet. Note that in non-query mode, you can only get data from the sheet and you cannot insert, update or modify any data.

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

Data Service

<data name="ExcelTestService" transports="http https local">
   <config enableOData="false" id="ExcelDS">
      <property name="excel_datasource">/home/anupama/DSSTest.xls</property>
   </config>
   <query id="SelectData" useConfig="ExcelDS">
      <excel>
         <workbookname>Alerts</workbookname>
         <hasheader>true</hasheader>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
         <headerrow>1</headerrow>
      </excel>
      <result element="AlertDetails" rowName="Alert">
         <element column="AlertID" name="Alert_ID" xsdType="string"/>
         <element column="Owner" name="OwnerName" xsdType="string"/>
         <element column="AlertType" name="Alert_Type" xsdType="string"/>
      </result>
   </query>
   <operation name="getdata">
      <call-query href="SelectData"/>
   </operation>
</data>

Request

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

<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>
      <AlertDetails xmlns="http://ws.wso2.org/dataservice">
         <Alert>
            <Alert_ID>1</Alert_ID>
            <OwnerName>Peter</OwnerName>
            <Alert_Type>3</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>2</Alert_ID>
            <OwnerName>James</OwnerName>
            <Alert_Type>4</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>3</Alert_ID>
            <OwnerName>Anne</OwnerName>
            <Alert_Type>1</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>4</Alert_ID>
            <OwnerName>Jane</OwnerName>
            <Alert_Type>11</Alert_Type>
         </Alert>
         <Alert>
            <Alert_ID>30</Alert_ID>
            <OwnerName>Smith</OwnerName>
            <Alert_Type>1</Alert_Type>
         </Alert>
      </AlertDetails>
   </soapenv:Body>
</soapenv:Envelope>

References:


[1] https://github.com/anupama-pathirage/DemoFiles/raw/master/Blog/Excel/DSSTest.xls