BPM 10g-12c Migration: Handling Excel Files as Input

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….

E2C26

…which we’ll convert into CSV in a “valve”….

E2C27

…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”….

E2C01

E2C02

E2C03

E2C04

….leave it as “asynchronous” for now and click “Finish”….

E2C05

Create the BPM Process

Add a human activity and change the “End” to “none”….

E2C06

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)….

E2C07

E2C08

E2C09

E2C10

E2C11

E2C12

…or whatever location is appropriate for you….

E2C13

E2C14

E2C15

E2C16

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

E2C17

E2C18

E2C19

E2C20

E2C21

E2C22

E2C23

Now we return to the file adapter wizard….

E2C24

…and we’re done….

E2C25

 Complete the Process

In the properties of the “Start” node choose “Use Interface”….

E2C28

….click on the magnifying glass for the “Reference” and choose the file adapter we’ve just created….

E2C29

…remove the unwanted WSDL….

E2C30

…notice the composite reflects the change….

E2C31

Create a data object to hold the orders….

E2C32

…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….

E2C33

…choose it….

E2C34

Do the data association for the “Start” activity….

E2C35

Create the human task for the user activity….

E2C36

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

E2C38

E2C39

…accept defaults for creating the table in the form.

Deploy the UI….

E2C40

 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….

E2C41

E2C42E2C43

Create a new Java class….

E2C44

E2C45

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….

E2C46

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….

E2C47

E2C48

E2C49

…now build it….

E2C50

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….

E2C51

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…

E2C52

E2C53

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

E2C54

E2C55

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….

E2C56

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

E2C57

Deploy the BPM Project

E2C58

Test the Deployed Project

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

E2C59

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

E2C60

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

E2C61

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

E2C62

…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

Add Your Comment