transformer produces empty cells after update

[codexx666]

Hello,

I'm using the JXLS library to generate some specific Excel based reports within the PTC PDMLink 12.x environment.

After a minor patch/update of the PDMLink application server, the report generation fails with this error:

Caused by: java.lang.NoSuchMethodError: 'org.apache.poi.ss.usermodel.CellType org.apache.poi.ss.usermodel.Cell.getCellTypeEnum()'
at org.jxls.transform.poi.PoiCellData.readCellContents(PoiCellData.java:88)
at org.jxls.transform.poi.PoiCellData.readCell(PoiCellData.java:54)

In the production environment are these jxls jars deployed within the active classpath:
commons-jexl-2.1.1.jar
jxls-2.4.7.jar
jxls-jexcel-1.0.7.jar
jxls-poi-1.0.16.jar
jxls-reader-2.0.5.jar

My XLSX template looks like this:

My conclusion was that PTC has updated with the patch the org.apache.poi.* packages and as a result I have to update the JXLS jars too.
(Apache POI before update: v4.1.2 / after update: v5.2.1)

In my DEV/test environment I've updated the JXLS jars to
jxls-2.14.0.jar
jxls-poi-2.14.0.jar
jxls-reader-2.1.0.jar

But now I get empty cells as a result

JXLS seems to recognize the data an generates rows accordingly, but all cells are empty?!

the very unspectacular code looks like this
File tmplFile = new File(params.get("template"));
File outFile = new File(params.get("output"));

        is = new FileInputStream(tmplFile);
        OutputStream os = new FileOutputStream(outFile);

        JxlsHelper.getInstance().processTemplate(is, os, context);

        os.flush();
        os.close();
        is.close();

Any idea what I'm doing wrong?
Is my jx:each command incorrect?

thanks in advance!

Best regards!

[SoltauFintel]

jxls-2.4.7 to 2.14 is a big jump. I have no idea what change it is. I guess your data objects won't be called. Please debug the JEXL (expression evaluation) code.

[codexx666]

Hello @SoltauFintel

thanks for your reply!

Regarding versions
Yes, it's a big jump. But I also tried nearly all other versions in between. Every version after 2.4.7 behaves the same.

Anyhow... I enabled the logging for org.apache.commons.jexl3 and this is reported in the log
2024-08-23 08:19:53,634 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.jxls.area.XlsArea wcadmin - Applying XlsArea at Template!A4
2024-08-23 08:19:53,665 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:18![7,18]: 'matInfo.materialNr' unsolvable property 'materialNr'
2024-08-23 08:19:53,668 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:19![7,19]: 'matInfo.description' unsolvable property 'description'
2024-08-23 08:19:53,670 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:19![7,19]: 'matInfo.placeholder' unsolvable property 'placeholder'
2024-08-23 08:19:53,672 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:20![7,20]: 'matInfo.changeAction' unsolvable property 'changeAction'
2024-08-23 08:19:53,672 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.jxls.area.XlsArea wcadmin - Applying XlsArea at Template!A5
2024-08-23 08:19:53,673 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:18![7,18]: 'matInfo.materialNr' unsolvable property 'materialNr'
2024-08-23 08:19:53,674 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:19![7,19]: 'matInfo.description' unsolvable property 'description'
2024-08-23 08:19:53,676 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:19![7,19]: 'matInfo.placeholder' unsolvable property 'placeholder'
2024-08-23 08:19:53,677 DEBUG [ajp-nio-127.0.0.1-8010-exec-3] org.apache.commons.jexl3.JexlEngine wcadmin - org.jxls.expression.JexlExpressionEvaluator.evaluate:65@1:20![7,20]: 'matInfo.changeAction' unsolvable property 'changeAction'
...

the MatInfo class looks like this

public class MatInfo {
   public String materialNr;
   public String description;
   public String placeholder = "";
   public String changeAction;

   /**
    * @return the materialNr
    */
   public String getMaterialNr() {
      return materialNr;
   }

   /**
    * @param materialNr the materialNr to set
    */
   public void setMaterialNr(String materialNr) {
      this.materialNr = materialNr;
   }

   /**
    * @return the description
    */
   public String getDescription() {
      return description;
   }

   /**
    * @param description the description to set
    */
   public void setDescription(String description) {
      this.description = description;
   }

   /**
    * @return the placeholder
    */
   public String getPlaceholder() {
      return placeholder;
   }

   /**
    * @param placeholder the placeholder to set
    */
   public void setPlaceholder(String placeholder) {
      this.placeholder = placeholder;
   }

   public String getChangeAction() {
      return changeAction;
   }

I made the class properties public, the getters are public and were generated with "standard" naming.

I also have moved all classes for the JXLS report into the same package. But that had no effect so far.

Regards

[SoltauFintel]

You have to debug.

[codexx666]

Ok, I did a debug session today,

The reason for my problem is that the JexlPermissions in the expression evaluator are not allowed to access my data classes.

Per default allowed classes are:
org.apache.commons.jexl3.introspection.JexlPermissions

->

By invoking the template processing through the suggested way there seems to be no way to configure the JexlPermissions :
JxlsHelper.getInstance().processTemplate(is, os, context);

For now I relocated my data holding classes to "org.apache.commons.jexl3"
But this solution seems not ideal to me.

Any suggestions? :-) Thanks in advance.

[SoltauFintel]

Use Jxls 3.0.

https://jxls.sourceforge.net/builder.html -> permissions

[codexx666]

jxls 3.0 would require to compile it myself.
the downloadable jars are compiled for Java 17 - my environment is still 11.

for now I use the allowed package name.
data is now retrieved again and placed within the cells.

but I now face the strange issue that some columns widths are massively reduced compared to the template in the output, which makes some of the data unreadable without manual interaction.

[SoltauFintel]

Just like Jxls 3.0 sets the JEXL permissions, you can probably program it for 2.14 as well.

As for column widths, you'll have to do your own research.