Integration

Hello,

I have a requirement where the B2B picks up an Excel file from SFTP server and process in BEPL to Invoke a web service.

I am planning to convert the Excel file to XML using a Java callout at the B2B layer. I have written a Java code using Apache POI classes. The java code does the transform and there is no need of an XSLT. The default Callout "XSLTCalloitImpl.java", uses " String xsltFile = context.getStringProperty("xsltFile");" where the xsltFile is specified in the B2B Callout configuration on B2B console. My code below does not have that. I am not sure how to implement the callout with the below code, Is it possible with out XSLT? Or am I completely in a wrong direction?

I have tested this code outside SOA with an Excel File as a file input and got the expected XML output.

Any Ideas and suggestion are greatly appreciated.

Here is my Java code

package xmlexcel;

import java.io.IOException;
import java.io.InputStream;

import java.util.List;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import oracle.tip.b2b.callout.Callout;
import oracle.tip.b2b.callout.CalloutContext;
import oracle.tip.b2b.callout.CalloutMessage;
import oracle.tip.b2b.callout.exception.CalloutDomainException;
import oracle.tip.b2b.callout.exception.CalloutSystemException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;


public class excelxml implements Callout {


    // public void generateXML(File excelFile) throws InvalidFormatException {

    public void execute(CalloutContext context, List input,
                        List output) throws CalloutDomainException,
                                            CalloutSystemException
                                             {
        try {
            // Get the input callout message
            CalloutMessage cmIn = (CalloutMessage)input.get(0);
            //String inputPayload = cmIn.getBodyAsString();
            InputStream inputPayload = cmIn.getBodyAsInputStream();

            DocumentBuilderFactory factory =
                DocumentBuilderFactory.newInstance();
            DocumentBuilder builder = factory.newDocumentBuilder();
            Document document = builder.newDocument();
            Element orderdetailElement = document.createElement("OrderDetail");
            document.appendChild(orderdetailElement);

            Workbook workbook = WorkbookFactory.create(inputPayload);
            // Workbook workbook = WorkbookFactory.create(excelFile);
            Sheet spreadsheet =
                workbook.getSheetAt(0); // sheet can be used as common for XSSF (xlsx) and HSSF (xls) WorkBook
            for (int i = 1; i <= spreadsheet.getLastRowNum();
                 i++) // Rows in the Sheet

            {
                Row row = spreadsheet.getRow(i);
                Element orderElement =
                    document.createElement("Order"); //Root Element
                orderdetailElement.appendChild(orderElement);
                //Start All columns
                Element fcorderdetailElement =
                    document.createElement("OrderDetail"); //B2B Identifier, can be removed if decided as not required
                orderElement.appendChild(fcorderdetailElement);
                fcorderdetailElement.appendChild(document.createTextNode(""));

                Element vendoridElement = document.createElement("VendorID");
                orderElement.appendChild(vendoridElement);
                vendoridElement.appendChild(document.createTextNode(row.getCell(1).getRichStringCellValue().getString()));

                Element ordernumberElement =
                    document.createElement("PurchaseOrderNumber");
                orderElement.appendChild(ordernumberElement);
                ordernumberElement.appendChild(document.createTextNode(row.getCell(2).getRichStringCellValue().getString()));

                Element invoicenumberElement =
                    document.createElement("InvoiceNumber");
                orderElement.appendChild(invoicenumberElement);
                invoicenumberElement.appendChild(document.createTextNode(row.getCell(5).getRichStringCellValue().getString()));

                Element invoiceamountElement =
                    document.createElement("InvoiceAmount");
                orderElement.appendChild(invoiceamountElement);
                invoiceamountElement.appendChild(document.createTextNode(row.getCell(6).getRichStringCellValue().getString()));
                //End All columns
            }


            //Transform the document
            TransformerFactory tFactory = TransformerFactory.newInstance();

            Transformer transformer = tFactory.newTransformer();

            DOMSource source = new DOMSource(document);
            //StreamResult result = new StreamResult(new File("orderdetail2.xml"));
            StreamResult result = new StreamResult();
            transformer.transform(source, result);
            //Create Callout output Message
            CalloutMessage cmOut = new CalloutMessage(result.toString());
            output.add(cmOut);

        } catch (IOException e) {
            System.err.println(e.getMessage());
        } catch (ParserConfigurationException e) {
            System.err.println(e.getMessage());

        } catch (TransformerConfigurationException e) {
            System.err.println(e.getMessage());

        } catch (TransformerException e) {
            System.err.println(e.getMessage());

        } catch (InvalidFormatException e) {
          System.err.println(e.getMessage());
        }
    }


    /* public static void main(String[] argv) throws InvalidFormatException {

        excelxml excel = new excelxml();
          File input = new File("c:/orderdetail.xls");
          excel.generateXML(input);
       }*/


}

Thanks,
Venkatesh