[Download] | [Documentation Home] | [Release Note]

Step-by-Step Guide on Creating Data Services Using Excel Data Sheet


Introduction


WSO2 Data Services Server supports to expose data in Excel 97-2007 as a service. We can create Excel data sources in two different modes.

Sample Excel Data sheet


Before we start on create the data service we will create an excel sheet which contains three columns, namely ID, Model and Classification (i.e. Do not paste the above content into a single cell). Fill the data sheet with some values under those three columns.


DS

Figure 1: Sample excel data sheet


Excel Data Sources in non-query Mode

This functionality allows to expose the content of an Excel sheet as a service.

Step 1: Start by giving a name


Lets start to create the data service by click on "Create" link under Manage/Services/Add/Data Service. We will name the service as ExcelSampleService. Click on "Next" button once you enter the name, and the description if required.


DS

Figure 2: Create excel data service


Step 2: Enter details about your Excel file.


Next step is to provide the details about the excel file. Select the Data Source Type as "EXCEL" from the drop down list after you giving a name to the data source. Then enter the location of the excel file and save.


DS

Figure 3: Configure data source


Created data source will list as below. Click on "Next" to create the query.


DS

Figure 4: View data source


Step 3: Enter excel and query details using which values will be extracted, and configure how the response should look like


Lets name the Query Id as sheet1Query and select the data source which created in previous step amd fill the excel sheet details. Enter a name for Grouped By Element and Row Name. You can also give a namespace if you wish. Click on "Add Output Mapping" button to create how the output should look like. Lets create ID as an element and Name and Classification as attributes. Save the query once you enter the details.


DS

Figure 5: Create query


Once you add a query, it will be listed as follows. Click on "Next" to create the operation.


DS

Figure 6: View querry


Step 4: Create a web service operation


Click on "Add on Operation", and enter a name to the operation. Lets name it as getProducts. Select the created query from the drop down list and save the operaion.


DS

Figure 7: Add operation



Existing operations will be listed as follows. Click on "Finish" button. Service will be created and deployed.



DS

Figure 8: View operations


Step 5: Service deployed


Now we have created a data service. You can go the deployed services list which is under Manage/Service/List. Created service will be list as follows.


DS

Figure 9: Deployed data services


Step 6: Try your service


By click on "Try It" link you can invoke the created service.


DS

Figure 10: Invoke service using Try-it


Click on the deployed service and click on "Edit Data Service(XML Edit)" to view the data service in xml format.


DS

Figure 11: Edit data service


Excel Data Sources in Query Mode

This functionality allows users to query an Excel sheet in a much familiar SQL-like manner, and expose it as a service. Internally org.wso2.carbon.dataservices.sql.driver.TDriver class will be used as the SQL Driver. It is a JDBC driver implemenation to be used with tabular data models such as Google SpreadSheets, Excel sheets.

Step 1: Start by giving a name


As we did earlier lets start by creating a new data service. Let's name it as 'ExcelQueryModeSampleService'.


DS

Figure 12: Create excel data service


Step 2: Enter details about your Excel file.


Now let's provide the details about the excel file. Select the Data Source Type as "EXCEL" from the drop down list after you giving a name to the data source. Then select "Use Query Mode" check box. Then enter the location of the excel file and Save.


DS

Figure 13: Configure data source


As we did earlier click on "Next" to create the query.

Step 3: Define the Query, input parameters and output parameters.


Lets name the Query Id as excelQuery and select the data source which created in previous step. You can see that UI will allow you to define the query, which was not supported earlier. Enter the query as given in the below UI and click on 'Generate Input Mappings' to auto generate the input mapping parameters.


DS

Figure 14: Create query


Once you add a query, click on "Next" to create the operation.


Step 4: Create a web service operation


Click on "Add on Operation", and enter a name to the operation. Lets name it as insertProducts. Select the created query from the drop down list and click on 'Add Query Params as Operation Params' to add the parmeters to operation. Now save the operation.


DS

Figure 15: Add operation


Now Try-it the service and check the excel file for the newly added records.

Supported query types

Currently, this feature provides support for basic SELECT, INSERT, UPDATE and DELETE functionalities.

Note : Nested quries are currently not supported


Eg:

SELECT customerNumber,customerName,phone,state,country FROM customers

Sample SELECT query

INSERT INTO customers(customerNumber,customerName,contactLastName) VALUES(?,?,?)

Sample INSERT query

UPDATE customers SET contactFirstName=?, contactLastName=? WHERE customerNumber=?

Sample UPDATE query

DELETE FROM customers WHERE customerNumber=?

Sample DELETE query

Also we can create new Sheets and drop an existing sheet from a ExcelSheet.


Eg:

CREATE SHEET ProductCategories (ProductCode, Category)

Sample SELECT query

DROP SHEET ProductCategories

Sample INSERT query