Excel

Data in Microsoft Excel 97-2007 can be exposed as services .

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,

Excel data sources can be created in two different modes as follows:

• Excel Data Sources in Non-Query Mode : The 'Non-Query' mode allows to simply expose the content of an Excel sheet as a service.
• Excel Data Sources in Query Mode : The 'Query Mode' allows users to query an Excel sheet in a familiar, SQL-like manner, and expose it as a service.

Excel Data Sources in Non-Query Mode 

Follow the instructions below to create a data service using this excel sheet in non-query mode.

1. Log on to the product's management console and select   "Data Service -> Create" under the "Main" menu.

2. The "Create Data Service" page appears. Fill in the fields and click "Next." The Data Service name is mandatory.

3. The "Data Sources" page appears. Click on the " Add New Data Source" link and add your Data Source details.

Select the data source type as EXCEL. For example,

4. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next."

5. The "Queries" page appears.

Initially your service does not include any queries. Click on 'Add New Query' to add a new query, input/output mappings, events etc. to your Data Service.

Enter query details using which values will be extracted. For example,

Query ID : sheet1Query
Data Source : default (This is the data source created in previous step)

Grouped by element : Products
Row name : Product
Row namespace : http://ws.wso2.org/dataservice/sample

Click on "Add Output Mapping" button to define how the output looks like. We have created ID as an element and "Name" and "Classification" as attributes.

For information on adding validations to your input mappings in the query, refer to section Input Validators . Also, detailed information on writing queries can be found in section Data Service Queries.

6. Once a new query is added, it will be listed as follows:

You can edit or delete your query. To continue, click "Next."

7. The "Operations" page appear s. Click the "Add new operation" link to c reate an operation. Select the query created above to be executed when this operation is called.

Click "Save" once done. For example,

8. The operation will be listed in the "operations" page. You can edit or delete your operation.

9. The Data Service configuration is done. Click "Finish" . ( You can also continue to add resources by clicking the "Next" button).

10. Once the "Finish" button is clicked, you will be navigated to the "Deployed Services" page where the newly added data service is listed .

From here, you can manage your data service. For instructions, refer to section Service Management .

For a demonstration of service-enabling an Excel sheets, refer to page   Excel Sample in Data Services Server 3.0.0 Documentation. 

Excel Data Sources in Query Mode

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

Follow the instructions below to create a data service using this excel sheet in query mode.

1. Log on to the product's management console and select   "Data Service -> Create" under the "Main" menu.

2. The "Create Data Service" page appears. Fill in the fields and click "Next." The Data Service name is mandatory.

3. The "Data Sources" page appears. Click on the " Add New Data Source" link and add your Data Source details. The data source type should be EXCEL and select the "Use Query Mode" option. For example,

4. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next."

5. The "Queries" page appears. Initially your service does not include any queries. Click on "Add New Query" to add a new query, input/output mappings, events etc. to your Data Service.

Currently, the Query Mode supports only basic SELECT, INSERT, UPDATE and DELETE queries. Nested queries are not supported at the moment but will be facilitated in an upcoming release. For more information, refer to supported queries.

6. Enter the query and click "Generate Input Mappings" to auto generate the input mapping parameters. For example,

7. Once a new query is added, click "Next".

8. The "Operations" page appear s. Add the operation details. In the following example, operation name is "insertProducts". Select the created query from the drop down list and click on "Add Query Params as Operation Params" to add the parameters to operation. Click "Save" once done.

9. The operation will be listed in the "operations" page. You can edit or delete your operation.

10. The Data Service configuration is done. Click "Finish" . You will be navigated to the "Deployed Services" page where the newly added data service is listed .

From there, you can manage your data service. For instructions, refer to section Service Management .

Queries Supported in the 'Query' Mode 

Currently, only basic SELECT, INSERT, UPDATE and DELETE queries are supported by the 'Query' mode. Shown below are few example queries.

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