Excel

You can create datasources to expose data in Microsoft Excel 97-2007 as services using the WSO2 Data Services Server. For demonstration, we use an excel sheet containing three columns, namely, ID, Model and Classification. The sheet is then filled with mock data. Note that the column names should appear in separate cells. For example,

You can create your Excel datasource as you create your data service. First, follow the steps from 1 to 3 in creating a data service using various data sources. When you get to the Add New Data Source screen, select Excel as the data source type. The Excel-specific options will now be available for editing as shown below.

The Use Query Mode option (in the UI above) allows you to create Excel datasources in two different ways as follows:

Excel datasources in Non-Query Mode : Allows to directly expose the content of an Excel sheet as a service
Excel datasources in Query Mode : Allows to query an Excel sheet in a familiar, SQL-like manner, and expose the results set as a service

Also, see a demonstration of service enabling an Excel sheets in Excel Sample.

Excel datasources in non-query mode

To create a datasource in non-query mode, simply uncheck the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI does not provide support to write a query. You can simply add Output Mappings to define how the output looks like. For example,

Excel datasources in query mode

To create a datasource in query mode, check the Use Query Mode check box and save. Then, click Next to add a query. Note that the Query Details UI now provides support to write a query. For example,

Internally, the class org.wso2.carbon.dataservices.sql.driver.TDriver is used as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google Spread Sheets, Excel sheets etc.

At the moment, the query mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries will be supported in an upcoming release. Given below are few examples ofthe supported queries:

Queries supported in the query mode

Sample 1:

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

Sample 2:

INSERT INTO customers (customerNumber, customerName, contactLastName)

VALUES(?,?,?)

Sample 3:

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

Sample 4:

DELETE FROM customers
WHERE customerNumber=?

You can also create new sheets in the Excel or drop existing sheets.

Sample 5:

CREATE SHEET ProductCategories (ProductCode, Category)

Sample 6:

DROP SHEET ProductCategories

To write a query to this datasource, see Step 3: Writing Data Service Queries.

Also, see a demonstration of service enabling an Excel sheet in Excel Sample.