im new in ACE. I want to create a flow that reads xlsx excel file using java and prints it to the database. Which nodes should I use for this task and is it correct to use only this java compute code as java code?
/*
JAR files to be added to shared-classes folder under server or integration node dir:
dom4j-1.6.1.jar
names.txt
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar
*/
import com.ibm.broker.javacompute.MbJavaComputeNode;
import com.ibm.broker.plugin.MbElement;
import com.ibm.broker.plugin.MbException;
import com.ibm.broker.plugin.MbMessage;
import com.ibm.broker.plugin.MbMessageAssembly;
import com.ibm.broker.plugin.MbOutputTerminal;
import com.ibm.broker.plugin.MbUserException;
import com.ibm.broker.plugin.MbXMLNSC;
import java.io.InputStream;
import java.io.ByteArrayInputStream;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ParseExcel extends MbJavaComputeNode {
public void evaluate(MbMessageAssembly inAssembly) throws MbException {
MbOutputTerminal out = getOutputTerminal("out");
// MbOutputTerminal alt = getOutputTerminal("alternate");
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
// get InputBody
MbElement inputBlob = inAssembly.getMessage().getRootElement().getLastChild();
byte[] originalMsgByteArray = (byte[])inputBlob.getLastChild().getValue();
InputStream stream = new ByteArrayInputStream(originalMsgByteArray);
parseXLSX(stream, outMessage);
// 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);
}
public void parseXLSX(InputStream fis, MbMessage outMessage){
try {
MbElement outRoot = outMessage.getRootElement();
// create XMNLSC parser
MbElement outBody = outRoot.createElementAsLastChild(MbXMLNSC.PARSER_NAME);
// Create root element.
MbElement excelRoot = outBody.createElementAsLastChild(MbElement.TYPE_NAME, "excel", null);
DataFormatter formatter = new DataFormatter();
XSSFWorkbook wb = new XSSFWorkbook(fis);
XSSFSheet sheet1 = wb.getSheetAt(0);
for (Row row : sheet1) {
// create a row element for current excel row.
MbElement rowMsgElement = excelRoot.createElementAsLastChild(MbElement.TYPE_NAME, "row", null);
for (Cell cell : row) {
// get cell value as text
String text = formatter.formatCellValue(cell);
// create an element called cell in output message with value as cell value
rowMsgElement.createElementAsLastChild(MbElement.TYPE_NAME,"cell",text);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
I tried to add 2 java project in independent resources which reads the excel correctly but i dont know how to add them into flow.First code introduces the table and writes excel to console:
public class GFKTable {
private String reportingProductGroup;
private String region;
private String year;
private String month;
private String brand;
private int salesUnits;
private float salesUnitPercentage;
public GFKTable(){}
public GFKTable(String reportingProductGroup, String region, String year, String month,
String brand,int salesUnits,float salesUnitPercentage) {
super();
this.reportingProductGroup = reportingProductGroup;
this.region = region;
this.year = year;
this.month = month;
this.brand = brand;
this.salesUnits = salesUnits;
this.salesUnitPercentage =salesUnitPercentage;
}
@Override
public String toString() {
return "GFK Table[reportingProductGroup=" + reportingProductGroup + ", region=" +region+", year=" + year + ", month=" + month
+ ", brand=" + brand + ", salesUnits="+salesUnits+", salesUnitPercentage="+salesUnitPercentage+"]";
}
public String getReportingProductGroup() {
return reportingProductGroup;
}
public void setReportingProductGroup(String reportingProductGroup) {
this.reportingProductGroup = reportingProductGroup;
}
public String getRegion() {
return region;
}
public void setRegion(String region) {
this.region= region;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
public String getMonth() {
return month;
}
public void setMonth(String month) {
this.month = month;
}
public String getBrand(String brand) {
return brand;
}
public void setBrand(String brand) {
this.brand = brand;
}
public int getSalesUnits(int salesUnits) {
return salesUnits;
}
public void setSalesUnits(int salesUnits) {
this.salesUnits = salesUnits;
}
public float getSalesUnitPercentage(float salesUnitPercentage) {
return salesUnitPercentage;
}
public void setSalesUnitPercentage(float salesUnitPercentage) {
this.salesUnitPercentage = salesUnitPercentage;
}
}
second code reads the xlsx excel
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadXLSX {
public static void main(String[] args) {
List<GFKTable> gfkList = readXLSXFile("C:\\Users\\Y9GAMF862\\Desktop\\GFKbosluksuz.xlsx");
for(GFKTable gfk : gfkList){
System.out.println(gfk);
}
}
private static List<GFKTable> readXLSXFile(String file) {
List<GFKTable> listGfk = new ArrayList<GFKTable>();
try {
XSSFWorkbook work = new XSSFWorkbook(new FileInputStream(file));
XSSFSheet sheet = work.getSheet("Sheet1");
XSSFRow row;
int i=1;
int salesUnit;
String reportingProductGroup,region,month,brand,year;
float salesUnitPercentage;
while((row = sheet.getRow(i))!=null){
try{
reportingProductGroup = row.getCell(0).getStringCellValue();
}
catch(Exception e){reportingProductGroup = null;}
try{
region = row.getCell(1).getStringCellValue();
}
catch(Exception e){region = null;}
try{
year = row.getCell(2).getStringCellValue();
}
catch(Exception e){year = null;}
try{
month = row.getCell(3).getStringCellValue();
}
catch(Exception e){month = null;}
try{
brand = row.getCell(4).getStringCellValue();
}
catch(Exception e){brand= null;}
try{
salesUnit = (int) row.getCell(5).getNumericCellValue();
}
catch(Exception e){salesUnit = 0;}
try{
salesUnitPercentage = (float) row.getCell(6).getNumericCellValue();
}
catch(Exception e){salesUnitPercentage = 0;}
GFKTable gfk = new GFKTable(reportingProductGroup,region,year,month,brand,salesUnit,salesUnitPercentage);
listGfk.add(gfk);
i++;
}
work.close();
} catch (IOException e) {
System.out.println("Exception is GFKTable fetch data :: "+e.getMessage());
e.printStackTrace();
}
return listGfk;
}
}
also i used these JAR Files for them: commons-codec 1.15 commons-collections4-4.4 commons-compress-1.21 commons-io-2.11.0 commons-math3-3.6.1 curvesapi-1.07 log4j-api-2.18.0 log4j-to-slf4j-2.8.2 poi-5.2.3 poi-ooxml-5.2.3 poi-ooxml-lite-5.2.3 slf4j-api-1.7.5 slf4j-simple-1.6.4 SparseBitSet-1.2 xmlbeans-5.1.1
