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:
- A webservice receives the name of an Excel file.
- A BPEL process is invoked.
- This process reads the binary file from the file system.
- 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.
- Back in BPEL, the XML String is parsed into an elemnt-based BPEL variable representing the Excel content.
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: