I have a task where I need to process a bunch of data to fill an Excel file using a provided template. In webmethods.io, I haven’t found sufficient means to do that, so the proposed solution was to create a Javaservice that would do the task and pass back the result. I did it, using Apache POI, and the service performed well, while debugged from respective TestHarness, but it crashes when called as an actual service. At first I suspected that it has something to do with the need to read a file from HD which might not work, but even if I read the file into memory and try to pass it from there, it still does not work:
File templateFile = new File(path);
if (!templateFile.exists()){
result = "File not found";
}
else
try (InputStream inp = new FileInputStream(templateFile)) {
int fileSize = inp.available();
byte[] fileInMemory = new byte[fileSize];
inp.read(fileInMemory);
inp.close();
InputStream inp2 = new ByteArrayInputStream(fileInMemory);
^======= Works until this point =======^
XSSFWorkbook workbook = new XSSFWorkbook(inp2); <=== this line causes the crash
As I said, everything works as long as I run it as Java, it’s the invoking as a service where it fails:
This post won’t be of direct help but I would suggest that if at all possible to not use Excel as a data format.
Having worked with Excel files before using POI as the tool within IS many moons ago – it worked okay (in Integration Server, not wM.io). The end result was sufficient at the time for what was needed but left me with this rule-of-thumb that I follow to this day – never process Excel files in automated integration scenarios. Far too fragile because people manually edit them. “Why did it fail? All I did was insert a column.”
A guess is that there is a library missing on the classpath or potentially a class conflict used by different libraries.
Oh, I fully share your sentiment about using externally formed Excels, but sometimes you just can’t Jedi the end user into “This is not the format you want your data in”. Fortunately, in this case it’s just filling a safely stashed template.
Hi @kirill.medvedev ,
I would agree with @reamon that it is probably a class conflict since you are able to run this code as a standalone but not as a java service.
you can try to check where the class CTNumFmtsImpl is loaded from by using this code in a java service
IDataMap map = new IDataMap(pipeline);
map.put("result",org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTNumFmtsImpl.class.getProtectionDomain().getCodeSource().getLocation() );
The variable result should point to the location of the jar where the class CTNumFmtsImpl is loaded from .
Edit: If there is a conflict, you can explore using a package classloader to use a specific jar in the package.
If this doesn’t help, please provide the full stacktrace of the error you are receiving, InvocationTargetExceptions are usually wrappers around the actual exception so the next few lines should have more information.
Typical Invocation Target Exceptions occur when there is another error that is happening underneath the layers. From the error it seems to be looking for “xmlbeans-x.x.x.jar” import.
Do you have all the imports in the code/jars folder? When you run using test harness, it uses your local workspace.
While I haven’t managed to solve the jar dependency problem directly, at least it seems that using libraries for non-XML based Excel 2003 XLS format works, so that’s good enough for me.
Far too fragile because people manually edit them. “Why did it fail? All I did was insert a column.”