Introduction

With the introduction of BPM 12c comes the long-awaited migration tool to migrate BPM 10g projects to BPM 12c.

The A-Team have been heavily involved with the effort to create collateral around this tool - patterns, approaches, samples, tutorials, labs etc.

One of the common patterns in BPM 10g is using an Excel spreadsheet as input to a process which led me to investigate how this could be replicated in 12c. What follows is a step-by-step guide to achieving an example of this. Note that this blog will not deliver an enterprise production solution but will at least provide a working example which can be built upon as required.

Approach

Handling files in SOA Suite 11g & 12c is standard functionality with the file and ftp adapters... so we'll use the file adapter for this example.

Handling CSV files is also straightforward, they can be specified as input in the file adapter wizard... so we can use a CSV file as input to the process.

Apache POI is a standard open source approach to converting Excel to a.n.other file format.... so we can use this to convert the Excel to CSV.

The file adapter and FTP adapter in 12c (and 11g) provide a feature known as "pipelines and valves" for pre-processing (and post-processing) of files prior to delivery to the composite.... so we can use this as the point of conversion for our file.

Given we now know the approach we can begin to build the example....

The Example Project

Examine the Input Spreadsheet / CSV File

We'll be using a simple excel spreadsheet of orders....

...which we'll convert into CSV in a "valve"....

...and then feed into the process via the file adapter.

Create the Application/Project

We'll create a simple application/project as a basis for testing our "Excel to CSV" valve....

Create a new "BPM Application"....

....leave it as "asynchronous" for now and click "Finish"....

Create the BPM Process

Add a human activity and change the "End" to "none"....

Add a File Adapter to the Composite

In the composite view add a "File Adapter" (if you don't see the "Components" window click on the "Window" tab and select it)....

...or whatever location is appropriate for you....

Here we specify the CSV file as it will be after conversion from Excel....

Now we return to the file adapter wizard....

...and we're done....

 Complete the Process

In the properties of the "Start" node choose "Use Interface"....

....click on the magnifying glass for the "Reference" and choose the file adapter we've just created....

...remove the unwanted WSDL....

...notice the composite reflects the change....

Create a data object to hold the orders....

...click on "browse" in the dropdown and notice in the subsequent dropdown that the "Orders" type created as part of the file adapter is available....

...choose it....

Do the data association for the "Start" activity....

Create the human task for the user activity....

Open the human task and auto-generate the task form....

...accept defaults for creating the table in the form.

Deploy the UI....

 Create the "Excel to CSV" Valve

For this we'll need a Java project... I'll simply add this to my existing application however it can be wherever makes sense....

Create a new Java class....

Add Required JARs

For this class we will need three JARs...

BPM-Infra.jar - which has the classes necessary to implement the valve - this can be found in <middleware home>/soa/soa/modules/oracle.soa.fabric_11.1.1

poi-ooxml-3.10.1-20140818.jar - which is the Apache POI JAR for converting Excel 2007- spreadsheets - here you will need to download the JAR relevant to the kind of spreadsheet you are using.

poi-3.10.1-20140818.jar - the generic Apache POI JAR

...let's add these JARs to the project....

Complete the Java Class

We'll need the following code to implement the Valve....

package excelvalves; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import oracle.tip.pc.services.pipeline.AbstractValve; import oracle.tip.pc.services.pipeline.InputStreamContext; import oracle.tip.pc.services.pipeline.PipelineException; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.ss.usermodel.*; import java.util.Iterator; public class ExcelToCsv extends AbstractValve {         public InputStreamContext execute(InputStreamContext inputStreamContext) throws IOException,                                                                                         PipelineException {             System.out.println("The valve will begin executing the inputstream");             // Get the input stream that is passed to the Valve             InputStream originalInputStream = inputStreamContext.getInputStream();             ByteArrayOutputStream bos = new ByteArrayOutputStream();             // Read workbook into HSSFWorkbook             XSSFWorkbook my_xls_workbook = new XSSFWorkbook(originalInputStream);             // Read worksheet into HSSFSheet             XSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);             // To iterate over the rows             Iterator<Row> rowIterator = my_worksheet.iterator();             //store the csv string             StringBuffer data = new StringBuffer();             //Loop through rows.             while(rowIterator.hasNext()) {                 Row row = rowIterator.next();                 Cell cell;                 // For each row, iterate through each columns                 Iterator<Cell> cellIterator = row.cellIterator();                 while (cellIterator.hasNext()) {                     cell = cellIterator.next();                     switch (cell.getCellType()) {                         //not a complete list                         case Cell.CELL_TYPE_BOOLEAN:                             data.append(cell.getBooleanCellValue() + ",");                             break;                         case Cell.CELL_TYPE_NUMERIC:                             data.append(cell.getNumericCellValue() + ",");                             break;                         case Cell.CELL_TYPE_STRING:                             data.append(cell.getStringCellValue() + ",");                             break;                         case Cell.CELL_TYPE_BLANK:                             data.append("" + ",");                             break;                         default:                             data.append(cell + ",");                     }                 }                 //end of line, remove trailing ","                 data.deleteCharAt(data.length()-1);                 data.append("\r\n");             }             System.out.println("snippet from stream: " + data.toString());             ByteArrayInputStream bin = new ByteArrayInputStream(data.toString().getBytes());             inputStreamContext.setInputStream(bin);             System.out.println("done processing the stream in the valve");             return inputStreamContext;         }     @Override     public void finalize(InputStreamContext inputStreamContext) {         // TODO Implement this method     }     @Override     public void cleanup() throws PipelineException, IOException {         // TODO Implement this method     } }

...we can see by looking at the code that we simply extend the "AbstractValve" interface, step through the Excel spreadsheet using the POI classes building up the StringBuffer as we go and finally return the CSV file to the file adapter.

 Package the Valve

We can now package the Valve as a JAR file for use by the BPM project....

...now build it....

Use the ExcelToCSV Valve in the BPM Project

Make the JAR Accessible

The first thing we need to do is make the Valve JAR we have just built available to the BPM project... there are a number of ways to do this, some more appropriate than others in a production system, but for the sake of this blog we'll just add it to the SCA-INF/lib directory of the BPM project....

Create a Pipeline in the BPM Project

We will need an xml file to define the pipeline/valve in the BPM project at the level of the "SOA" directory...

...in it we just need to reference the fully qualified class we created for the valve earlier....

Modify the File Adapter to Use the Pipeline/Valve

Now we can change the properties of the file adapter to use the pipeline/valve....

Open the ".jca" file associated with the file adapter....

...and add a property referencing the XML file we've just created....

Deploy the BPM Project

Test the Deployed Project

First let us allocate a user to the process owner role so that the human task can be assigned....

...now we can copy the excel file to the input directory....

...and let's see what has happened by first looking at the server log (we had some println in our Java code)....

...looking good, what about the human task in the workspace....

...excellent !

Summary

We have seen in this project how to reproduce a common BPM 10g pattern in BPM 12c.... using an Excel file as input to a process.

Now this may not be production ready, we'd probably need to determine the best way of packaging and referencing the JAR files, we'd probably need to complete the "valve" code to handle more complex cell types in Excel and to handle any potential errors, we may need to handle the Excel rows one-at-a-time, i.e. each row creates one process instance.... etc. What we do have here is a simple example of how to handle Excel files which we can build upon.

The packaged project can be found here....AppExcelInput

The Excel and CSV files can be found here....ExcelAndCSV