Page History

...

Step 6: Initial Work on Customer 3 Input (Excel over e-mail)

To get a start on Customer 3, we'll create a POJO to convert the Customer 3 sample Excel data into the JAXB POJOs representing the canonical XML format, write a small Camel route to test it, and build that into a unit test. If we get through this, we can be pretty sure that the Excel conversion and JAXB handling is valid and can be run safely in Camel.

Create an Excel-handling POJO

Camel does not have a data format handler for Excel by default. We have two options – create an Excel DataFormat (so Camel can convert Excel spreadsheets to something like the CSV List<List<String>> automatically), or create a POJO that can translate Excel data manually. For now, the second approach is easier (if we go the DataFormat route, we need code to both read and write Excel files, whereas otherwise read-only will do).

So, we need a POJO with a method that takes something like an InputStream or byte[] as an argument, and returns in Invoice as before. The process should look something like this:

1. Update the Maven POM to include POI support:

   Code Block
   xml xml
   <dependency> <artifactId>poi</artifactId> <groupId>org.apache.poi</groupId> <version>3.1-FINAL</version> </dependency>

2. Create a new class under src/main/java, perhaps called ExcelConverterBean.
3. Add a method, with one argument of type InputStream and the return type Invoice
   • You may annotate the argument with @Body to specifically designate it as the body of the incoming message
4. In the method, the logic should look roughly like this:
   1. Create a new Invoice, using the method on the generated ObjectFactory class
   2. Create a new HSSFWorkbook from the InputStream, and get the first sheet from it
   3. Loop through all the rows in the sheet
   4. Skip the first row, which contains headers (column names)
   5. For the other rows:
      1. Create a new LineItemType (using the ObjectFactory again)
      2. Pick out all the cell values and put them into the correct fields of the LineItemType (you'll need some data type conversion logic)
         • Not all of the values will actually go into the line item in this example
         • You may hardcode the column ordering based on the sample data file, or else try to read it dynamically from the headers in the first line
         • Note that you'll need to use a JAXB DatatypeFactory to create the XMLGregorianCalendar values that JAXB uses for the date fields in the XML – which probably means setting the date from a date cell on a GregorianCalendar
      3. Add the line item to the invoice
   6. Populate the partner ID, date of receipt, and order total on the Invoice
   7. Throw any exceptions out of the method, so Camel knows something went wrong
   8. Return the finished Invoice

Solution: Here's an example of what the ExcelConverterBean might look like.

Create a unit test

Step 7: Put this all together into Camel routes for the Customer Input

...