Sometimes, we need to read excel files and there is no native connector in IBM Integration Bus. So, the obvious solution is to read them using Java. The most commonly used library is Apache POI.
Here I’ve written a simple program which reads the sheets ( assuming it has a header and multiple rows) and attach them to Environment tree. The java code is
public class ReadExcel_JCN extends MbJavaComputeNode { public void evaluate(MbMessageAssembly inAssembly) throws MbException { MbOutputTerminal out = getOutputTerminal("out"); MbMessage inMessage = inAssembly.getMessage(); MbMessageAssembly outAssembly = null; try { // create new message as a copy of the input MbMessage outMessage = new MbMessage(inMessage); outAssembly = new MbMessageAssembly(inAssembly, outMessage); // ---------------------------------------------------------- // Add user code below MbElement environment = inAssembly.getGlobalEnvironment().getRootElement(); File excelFiles = new File((String) getUserDefinedAttribute("ExcelDir")); Workbook workbook = null; DataFormatter dataFormatter = new DataFormatter(); List<String> headerNames = null; String cellValue = null; MbElement cellElm = null; for (File excel : excelFiles.listFiles()) { workbook = WorkbookFactory.create(excel); for (Sheet sheet : workbook) { MbElement sheetElm = environment.createElementAsLastChild(MbElement.TYPE_NAME, sheet.getSheetName(), null); headerNames = new ArrayList<>(); for (Row row : sheet) { if (row.getRowNum() != 0) { cellElm = sheetElm.createElementAsLastChild(MbElement.TYPE_NAME, "rows", null); } for (Cell cell : row) { cellValue = dataFormatter.formatCellValue(cell); if (row.getRowNum() == 0) { headerNames.add(cellValue); } else { cellElm.createElementAsLastChild(MbElement.TYPE_NAME_VALUE, headerNames.get(cell.getColumnIndex()), cellValue); } } } if (sheetElm.getLastChild() == null) { sheetElm.detach(); } } workbook.close(); } // End of user code // ---------------------------------------------------------- } catch (MbException e) { // Re-throw to allow Broker handling of MbException throw e; } catch (RuntimeException e) { // Re-throw to allow Broker handling of RuntimeException throw e; } catch (Exception e) { // Consider replacing Exception with type(s) thrown by user code // Example handling ensures all exceptions are re-thrown to be handled in the flow throw new MbUserException(this, "evaluate()", "", "", e.toString(), null); } // The following should only be changed // if not propagating message to the 'out' terminal out.propagate(outAssembly); } }
If you’re looking for any support in IIB, please contact us support@vaithu.com.
About the Author
He has worked for various clients and developed more than 800+ message flows. He is an expert in ESQL, Java Compute Node and DFDL. If you’ve any complex code to be written in ESQL, feel free to contact him at +1 6123058684.