Tuesday, August 23, 2011

Example: Excel Parsing & Processing with SOA Suite 11g

Naturally, processing of native Excel documents is not a built-in functionality of SOA Suite. Nevertheless, this question arises frequently. The good news is, that this can be easily accomplished by utilizing the Apache POI framework in a Spring Bean. Here's how to do it:

The full sample project can be downloaded here: ExcelIntegration.zip
(In the archive, the POI library is removed to reduce the file size. Just copy it back to the ".../SCA-INF/lib" folder as decribed further down)

First, the scenario:







  1. A webservice receives the name of an Excel file.
  2. A BPEL process is invoked.
  3. This process reads the binary file from the file system.
  4. The opaque binary is handed over to a Spring bean, which utilizes the Apache POI library to parse the Excel file into a XML string.
  5. Back in BPEL, the XML String is parsed into an elemnt-based BPEL variable representing the Excel content.
Let's have a closer look at the BPEL process:























The first Invoke "InvokeReadExcel" triggers the reading of the Excel file from the file system. It uses the "Synchronous Read File" operation to read the file into an opaque schema. The file name is set to a dummy value at design time, because its overwritten in each process instance by the file name received in the service call. This is done by assigning the dynamic file name to the "jca.file.FileName" property in the invoke action:











The adapter setting defined by using the wizards can later be found in corresponding ".jca" files in your project. Here, properties can be changed manually. Refer to the documentation to see what's possible in addition to what can be defined in the wizards. (To give you an example: The file/ftp adapter is e.g. able to do remote copies without reading a file into the process) I manually set the property "DeleteFile" to "false" to make testing easier later:









Now the core piece: Apache POI is a library for processing of Excel documents. To use it in a SOA composite application, we just have to copy all ".jar" files of the library flat into ".../SCA-INF/lib" folder of the SOA project, here for version 3.7:













Don't forget to refresh the JDeveloper project to detect the changes and to select the ".jar" files in the deployment descriptor for deployment. Now we're ready to build the core parsing functionality as a simple Java POJO:



















I use a byte[] as the input parameter in my Java classe's "decodeExcel" method, which fits nicely the return type of the file adapter: The file adapter's opaque element contains the Excel file in Base64 encoding. Published as a Spring bean, the mapped byte[] input parameter also needes to be Base64-encoded in BPEL. No additional (de-)coding efforts needed here! The return type is a simple String which will contain the content of the Excel sheet as a Sting. I#ll parse it later in the BPEL process to keep the bean's interfaces simple.

A class to be publiched as Spring bean needs to implement the methods to be accessible via interface. With JDeveloper, extracting an interface is vary easy, just a mouse click away:















Now we're ready to publish the class as a Spring bean: Drag the "Spring Context" service component to the "Components" section of your composite designer and open it. Now, the bean and the SCA service interface have to be added to the Spring context:











The Spring bean can now be wired to the BPEL process. All interfaces and mappings are generated automatically. Last step in BPEL is an assign activity to parse the String retuned by the Spring bean into a proper BPEL variable, based on an element:












That's it! Lets give it a try by invoking the composite from em console, handing over the name of an Excel document. Here's my trace:


25 comments:

  1. Really helpful

    ReplyDelete
  2. Good one, would you mind to send me sample .xlsx file to dilipg@gmail.com ?? when I gave my own it's not reading the rows.

    ReplyDelete
  3. Hi... you would have been used <= in for loop, it was missing last row in excel as you missed out equal in loop

    for(int row=0;row<=sheet.getLastRowNum();row++){

    ReplyDelete
  4. can u pls let me know,where to download apache poi version 3.7..?

    ReplyDelete
    Replies
    1. Download from Apache archives: http://archive.apache.org/dist/poi/release/bin/

      Delete
    2. i have tried opening the aboe link to download the jars but they are not available there. can you please provde some other link.
      Thanks
      kuldeep

      Delete
    3. This comment has been removed by the author.

      Delete
    4. They are available in the above mentioned link

      Delete
  5. Incredible, Thank you so much.

    ReplyDelete
  6. not able to download the file ExcelIntegration.zip

    ReplyDelete
  7. Please i need the source, i cannot download the ExcelIntegration.zip

    ReplyDelete
  8. I can't download it. Could you please share the zip file of the project to me (dassrinanda@gmail.com) Thnaks.

    ReplyDelete
    Replies
    1. Thanks, i followed your blog and its worked fine for me though i faced a lot of challenges during this.

      Delete
    2. Dear Srinanda,

      Could you please share a sample working project for this. I have the same type of requirement.

      Delete
  9. The shared link isn't accessible. Could you please share the zip file of the project to me (anjani.monu88@gmail.com) Thanks.

    ReplyDelete
  10. Unable to download Sample Project. Can you please share zip file to me sivamodem@outlook.com

    Thanks

    ReplyDelete
  11. Please share the sample project to "jayanthidevadoss@yahoo.com"

    ReplyDelete
  12. I can't download full sample project. Could you please sent the zip file of the project to me (thuy.mmt@gmail.com) Thanks so much.

    ReplyDelete
  13. Could you please share the sample project to "krlosyop@gmail.com"? Thanks.

    ReplyDelete
  14. Hi, Could you please share the sample project to sudheer.kondichetty@icloud.com

    ReplyDelete
  15. Hi,

    Could you please share the project with me in pravatranjangiri@gmail.com

    ReplyDelete
  16. Hi,

    Could you please share the project with me in pravatranjangiri@gmail.com

    ReplyDelete