This documentation is work in progress and will be released with the next WSO2 EI version.
Exposing Excel Data as a Data Service - WSO2 Enterprise Integrator 6.x.x - WSO2 Documentation

All docs This doc
||
Skip to end of metadata
Go to start of metadata

This tutorial guides you on how to expose the data in an Excel sheet as a data service. We will create a data service that can search for data on the file and insert data into the file.

WSO2 EI uses the Apache POI library version 3.9.0 to work with Excel datasources, and thereby, supports both XLS and XLSX formats. For more information, see the Apache POI Documentation.

To demonstrate this feature, we will use the Products.xls file that is shipped with WSO2 EI. The Products.xls file is stored in the <EI_HOME>/samples/data-services/resources/ folder and it contains data about products ( cars/motorcycles) that are manufactured in an automobile company. The data table has the following columns: ID, Model, and Classification.

Creating the data service

Now, let's start creating the data service from scratch:

  1. Download the product installer from here, and run the installer.
    Let's call the installation location of your product the <EI_HOME> directory. This is located in a place specific to your OS as shown below:

    OSHome directory
    Mac OS/Library/WSO2/EnterpriseIntegrator/6.5.0
    WindowsC:\Program Files\WSO2\EnterpriseIntegrator\6.5.0\
    Ubuntu/usr/lib/wso2/EnterpriseIntegrator/6.5.0
    CentOS/usr/lib64/EnterpriseIntegrator/6.5.0

  2. Start the ESB profile:

    Open a terminal and execute the following command:

    wso2ei-6.4.0-integrator
    Go to Start Menu -> Programs -> WSO2 -> Enterprise Integrator 6.4.0 Integrator. This will open a terminal and start the ESB profile.
  3. Access the ESB profile's management console using the following URL: https://localhost:9443/carbon/.
  4. Sign in using admin as the username and password.
  5. Click Create under Data Service to open the Create Data Service  window.
  6. Enter the following data service name.

    Data Service NameExcel
  7. Leave the default values for the other fields.
  8. Click  Next  to go to the  Datasources  screen.

Connecting to the datasource

Follow the steps given below to add an Excel file as the datasource.

  1. Click Add New Datasource and enter the following details:

    Datasource IDExcel
    Datasource TypeEXCEL
    Excel URLEnter ./samples/data-services/resources/Products.xls.
    In this tutorial, we are using a sample excel file that is stored in the above location of your product pack.
    Use Query Mode

    Select Use Query Mode to enable it or keep it deselected to disable query mode. You can try out this tutorial using either option.
    The Use Query Mode option allows you to write queries for the datasource in two different ways. 

    • If query mode is enabled, you can write SQL queries for the excel sheet.

    • If query mode is disabled, you can get data from the excel datasource without an SQL query. 

    See the next section on defining queries for more information on how the query mode affects how you query data in the excel sheet.

  2. Save the datasource.
  3. Click Next to go to the Queries screen.

Define a query for the datasource

If Query Mode is disabled for the spreadsheet, you cannot write SQL statements to query the spreadsheet. Instead, you need to specify the query details by filling in the data extraction parameters. This also means that, when query mode is disabled, you cannot use the data service to insert, update, or modify data in the spreadsheet. This data service can only be used to get data that is already stored in the spreadsheet.

Follow the steps given below:

  1. Click Add New Query and add the following details:

    Query IDGetProducts
    Datasource TypeEXCEL
    Workbook NameSheet1
    You want the query to get the data from Sheet1 in the excel spreadsheet.
    Start reading from2
    You enter 2 because the 1st row of the spreadsheet is the header. The data is available from row 2 onwards.
    Rows to read5
    Five rows of data will be fetched by the query.
    Headers availabletrue
    Header row1
    You enter 1 because the 1st row of the spreadsheet includes the header.
  2. Define Output Mapping:
    Now, let's specify how the data fetched from the datasource should be displayed in the output. The Excel datasource we are using contains three columns: ID, Model, and Classification. We will create an output mapping for each of these columns.    
    1. Start by giving the following information:

      Output typeSelect XML.
      You can select XML, JSON, or RDF. This specifies the format in which the query results should be presented. 
      Grouped by elementEnter Products.
      This is the XML element that groups the query result.
      Row NameEnter Product.
      This is the XML element that should group each individual result.
    2. Click Add New Output Mapping to start creating the output mapping. Listed below are the output mappings that should be created.
      Fill in the details and click Add to add each output mapping.

      Mapping TypeElement NameDatasource TypeDatasource Column NameParameter TypeSchema Type
      ElementIDColumnIDSCALARstring
      ElementModelColumnModelSCALARstring
      ElementClassificationColumnClassificationSCALARstring
  3. Once you have created all of the above mappings, click Main Configuration to return to the Query screen.
  4. Save the query.

If Query Mode is enabled  for the datasource, follow the steps given below.

Creating a query to GET data
  1. Click Add New Query and add the following details.

    Query IDGetProductbyID
    DatasourceEXCEL
    SQL
    select ID, Model, Classification from Sheet1 where ID=:ID
  2. Click Generate Input Mapping, and the default input mappings will be generated for all the fields that you have specified in your SQL statement.
    You need to create input mappings for the ID, Model and Classification columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns. 
  3. Click Generate Response to create the output mapping.
    This defines how the employee details retrieved from the datasource will be presented in the result. Note that, by default, the output type is XML. 
  4. Save the query.
Creating a query to POST data

You can query data in the spreadsheet using SQL statements. At the moment, the query mode supports only basic SELECT, INSERT, UPDATE, and DELETE queries. Nested queries will be supported in an upcoming release. The org.wso2.carbon.dataservices.sql.driver.TDriver class is used internally as the SQL Driver. It is a JDBC driver implementation used with tabular data models such as Google spreadsheets, Excel sheets, etc.   

Follow the steps given below.

  1. Click Add New Query and add the following details.

    Query IDAddProducts
    DatasourceEXCEL
    SQL
    INSERT INTO sheet1 (ID, Model, Classification) VALUES(:ID, :Model, :Classification)
  2. Click Generate Input Mapping, and the default input mappings will be generated for all the fields that you have specified in your SQL statement.
    You need to create input mappings for the ID, Model, and Classification columns specified in the above SQL statement. These input mapping parameters will be used for inserting data into the relevant columns.
  3. Save the query.

Create a SOAP operation to invoke the queries

To invoke the query, you need to define an operation.

To Follow the steps given below

  1. Click Add New Operation and enter the following information.

    Operation NameGetProducts
    Query IDGetProducts
  2. Save the operation.

You can now invoke the data service query using SOAP.

To invoke the query, you need to define an operation.

  1. Click Add New Operation and enter the following information.

    Operation NameGetProductsbyIDOp
    Query IDGetProductsbyID
  2. Save the operation.
  3. Click Add New Operation and enter the following information.

    Operation NameAddProductsOp
    Query IDAddProducts
  4. Save the operation.

You can now invoke the data service query using SOAP.

Creating a REST resource to invoke the query

Now, let's create REST resources to invoke the query created above. Alternatively, you can create SOAP operations to invoke the queries. See the previous section or instructions.

Follow the steps given below:

  1. Click Add New Resource and enter the following information.

    Resource PathProducts
    Resource MethodGET
    Query IDGetProducts
  2. Save the resource.

You can now invoke the data service query using REST.

Follow the steps given below:

  1. Click Add New Resource and enter the following information.

    Resource PathProducts/{ID}
    Resource MethodGET
    Query IDGetProductsbyID
  2. Save the resource.
  3. Click Add New Resource and enter the following information.

    Resource PathProducts
    Resource MethodPOST
    Query IDAddProducts
  4. Save the resource.

You can now invoke the data service query using REST.

Finish creating the data service

Once you have defined the operation, click Finish to complete the data service creation process.
You will now be taken to the Deployed Services screen, which shows all the data services deployed on the server.

Invoking your data service using SOAP

You can try the data service you created by using the TryIt tool that is in your product by default.

  1. Go to the Deployed Services screen.
  2. Click the Try this service link for the Excel data service. The TryIt tool will open with the Excel data service.
  3. Run the operation. 

    Select the GetProducts operation your created earlier and click Send.
    You see an output similar to the following:

    Invoking the AddProductsOp operation

    This operation can insert data into the excel sheet, you will need to specify the values that should be inserted. 

    1. Click AddProductsOp.

    2. Provide the product details by copying the code given below.

      <p:AddProductsOp xmlns:p="http://ws.wso2.org/dataservice">
       <!--Exactly 1 occurrence-->
       <xs:ID xmlns:xs="http://ws.wso2.org/dataservice">S310_5686</xs:ID>
       <!--Exactly 1 occurrence-->
       <xs:Model xmlns:xs="http://ws.wso2.org/dataservice">1972 Alfa Romeo GTA</xs:Model>
       <!--Exactly 1 occurrence-->
       <xs:Classification xmlns:xs="http://ws.wso2.org/dataservice">Classic Cars</xs:Classification>
      </p:AddProductsOp>
    3. Click Send.

    Invoking the GetProductsbyIDOp operation

    This operation can get details of a product. You need to define the product ID for the data to be fetched from the excel sheet.

      1. Click GetProductsbyIDOp.

      2. Provide the product ID by copying the code given below. To confirm that the data you added above was included in the spreadsheet, let's use the same ID.

        <p:_getproducts_id xmlns:p="http://ws.wso2.org/dataservice">
           <!--Exactly 1 occurrence-->
           <xs:ID xmlns:xs="http://ws.wso2.org/dataservice">S310_5686</xs:ID>
        </p:_getproducts_id>
      3. Click  Send.

Invoking your data service using REST

You can send an HTTP GET request to invoke the data service using a curl command as shown below.

Run the following curl command to get the product details:

curl -X GET http://localhost:8280/services/Excel.HTTPEndpoint/Products

You get an output similar to the following:

<Products xmlns="http://ws.wso2.org/dataservice"><Product><ID>S10_1678</ID><Model>1969 Harley Davidson Ultimate Chopper</Model>
<Classification>Motorcycles</Classification></Product><Product><ID>S10_1949</ID><Model>1952 Alpine Renault 1300</Model><Classification>Classic Cars</Classification>
</Product><Product><ID>S10_2016</ID><Model>1996 Moto Guzzi 1100i</Model><Classification>Motorcycles</Classification></Product><Product>
<ID>S10_4698</ID><Model>2003 Harley-Davidson Eagle Drag Bike</Model><Classification>Motorcycles</Classification></Product><Product>
<ID>S10_4757</ID><Model>1972 Alfa Romeo GTA</Model><Classification>Classic Cars</Classification></Product></Products>

Get product details

Invoke the following command to get details of a product.

curl -X GET http://localhost:8280/services/Excel.HTTPEndpoint/Products/{PRODUCT_ID}

Example:

curl -X GET http://localhost:8280/services/Excel.HTTPEndpoint/Products/S10_4757

Add a product

Follow the steps given below to insert data to the excel sheet:

  1. Copy code given below to a file, name it product-data.xml, and save it.

    <_putproduct>
          <ID>S410_5443</ID>
          <Model>1972 Alfa Romeo GTA</Model>
          <Classification>Classic Cars</Classification>
    </_putproduct>
  2. Open the terminal, navigate the location where the file was saved and run the following command to insert data to the excel sheet.

    curl -X POST -H 'Accept: application/xml' -H 'Content-Type: application/xml' --data "@product-data.xml" -k -v http://localhost:8280/services/Excel.HTTPEndpoint/Products

    To confirm that the data got added you can run the GET curl command again using the product ID that you used in the product-data.xml file.

These will return the response in XML.

What's next?

Try out the samples under Data Integration Samples.

  • No labels