Data in Microsoft Excel 97-2007 can be exposed as services using the 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,
Follow the instructions below to create a data service using this excel sheet.
1. Log on to the Data Services Server Management Console.
2. Click the "Main" tab and select the "Create" link under "Data Services."
3. The "Create Data Service" page appears. Fill in the fields and click "Next." The Data Service name is mandatory.
Info:
For more information on Advanced Configurations in creating data services, refer to the Transaction Handling section.
4. 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,
5. After saving, the added data source page appears. You can edit or delete the Data Source. To proceed click "Next."
6. 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 input mappings in the query, if any, refer to section Input Validators.
7. Once a new query is added, it will be listed as follows:
You can edit or delete your query. To continue, click "Next."
8. The "Operations" page appears. Click the "Add new operation" link to create an operation. Select the query created above to be executed when this operation is called.
Click "Save" once done. For example,
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 can also continue to add resources by clicking the Next button).
11. 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 service.
For a demonstration on the usage of a Excel sheets as a data source in the WSO2 Data Services Server, refer to the Excel Sample.
.