How to read excel files using Java Compute Node

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.

XML/JSON Beautifier

There are lot of online tools available in the internet for this but they say, do not put sensitive information as they’re cached on the server side. So, obviously it is not a safe place to beautify. And not all companies allow admin access to developers to install plugins for this. What’s the solution for this? Let us develop something on our own and add what ever we want.

Here is a solution developed in-house keeping in mind all IIB/ACE developers.

You can not only beautify xml/json messages but also do the following

  • Blob to string
  • String to blob
  • Base64 Encode/Decode
  • XML to JSON
  • JSON to XML
  • XPath evaluation
  • XSLT Transformation
  • Validate XML against XSD
  • XML Decoding
  • Change themes

Above all this is developed as web-application and no external dependencies. Just deploy the war file to a web-server and start using it. By default it runs on port 8080 and so you can try http://hostname:8080

Here are some screenprints to please your eyes.

XML PrettyPrint
XML to JSON conversion
Blog to String

You can download this for free for personal use. For commercial use, please contact us at support@vaithu.com.

We also do full-stack development. If you’re looking for modernizing your UI, please contact us.