webMethods

Hello,

I tried to make the Java Class work with the xlsx format.

Bellow the begging of code:

package spreedSheet;

import com.wm.data.*;
import com.wm.util.Values;
import com.wm.app.b2b.server.Service;
import com.wm.app.b2b.server.ServiceException;
import java.text.SimpleDateFormat;
import java.text.ParseException;
import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public final class isValidDate_SVC

{

public static final void isValidDate(IData pipeline) throws ServiceException {
IDataCursor pipelineCursor = pipeline.getCursor();

String fileName = IDataUtil.getString(pipelineCursor, "file_name");
convertExcelToCSV(fileName);		 		
pipelineCursor.destroy();
}

// --- <<IS-BEGIN-SHARED-SOURCE-AREA>> ---



public static void convertExcelToCSV(String fileName) {
File inputFile = new File(fileName);
File outputFile = new File(fileName.substring(0, fileName.lastIndexOf(".")) + ".csv");
StringBuffer data = new StringBuffer();

try {

FileInputStream fis = new FileInputStream(inputFile);

if (inputFile.getName().endsWith(".xlsx")) {
workbook = new XSSFWorkbook(fis);//PROBLEMATIC LINE
} else {
fis.close();
throw new Exception("File not supported!");
} 
} catch (Exception e) {
e.printStackTrace();
}
}

I did a local project in IntelliJ and it worked. I added all JARs to the:

path: \code\jars inside the package where I have class.

Eroor:

java.lang.reflect.InvocationTargetException: Could not initialize class org.openxmlformats.schemas.drawingml.x2006.main.ThemeDocumen

IS version: 10.15
Java version: 11

Of course, I reload the package after adding the jars.

Any idea?

My best guess is that the package classloader isn’t being utilized.
Please check the manifest.v3 file of the package for this entry

<value name='classloader'>package</value>

Please refer Using a Package Class Loader in the documentation for more details.

-NP

The problem was resolved. Bellow solution. It looks like the Integration Server is not compatible with the newest version of Apache POI>5.x

And

import org.apache.poi.ss.usermodel.Workbook;

doesn’t work and couldn’t be initialized.

I changed the version of Apache POI to 4.1.2 and changed way to read Excel file like below:

        File inputFile = new File(fileName);
StringBuffer data = new StringBuffer();

try {
FileOutputStream fos = new FileOutputStream(outputFile);
XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile));
XSSFSheet sheet = wBook.getSheetAt(0);
Row row;
Cell cell;
Iterator<Row> rowIterator = sheet.iterator();


while (rowIterator.hasNext()) {
// For each row, iterate through each columns
}

Glad to know it was resolved.
But Integration Server functions as a platform and by itself isn’t likely to have compatibility issues with specific libraries. Might make sense to confirm the java versions are same in intellij and the Integration Server , the incompatibility could be with versions of java.

An InvocationTargetException is a wrapper to another exception, you can also check the underlying exception by expanding the stacktrace in the IntegrationServer Error Logs, if you would like to pursue this.

-NP

I want to emphasize the points that @Nagendra_Prasad is making.

@Mark_Kpc , did you test the new POI library with the package class loader setting? Because the symptoms you describe are extremely typical for such a scenario.

Hi Mark,

According to the Apache POI website, the POI lib should be working with Java 8 or newer.

Is there a more detailed error message available, why the class could not be initialized?

Regards,
Holger

All the other posts aside, I would encourage you that if at all possible that you DO NOT use .xlsx as a transport format. Using POI is okay and all but experience has shown that using Excel formats in this manner is fragile. Relatively easy to break the integration just because a user edited a workbook and to them looks “correct.”

You may find Excel FF to CSV FF comments to be of interest.