Jxls - Transformer class is org.jxls.transform.poi.PoiTransformer

Question

I'm working on exporting the results of a Java program to an excel sheet. I'm using Jxls for this. It works when I use XLSTransformer, but the calculation of the excel template are not performed. To have these calulations performed, I then try to use the Transformer class, but I got a debug message : DEBUG org.jxls.util.TransformerFactory - Transformer class is org.jxls.transform.poi.PoiTransformer , while my imports are the same as in the examples on Jxls sourceforge page.

private void exportData(File file) throws ParseException, IOException {

    AreaBuilder areaBuilder = new XlsCommentAreaBuilder();

    int o = 0;
    List<ListData2> rs2 = new ArrayList<ListData2>();
    ListData2 s2 = new ListData2(item[o], rep[o], justefaux[o], tempsrep[o]);
    for(int i=0; i<tableau.getRowCount(); i++){
        item[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 2).toString();
        rep[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 3).toString();
        justefaux[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 4).toString();
        tempsrep[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 5).toString();
        ListData2 temp = new ListData2(item[i], rep[i], justefaux[i], tempsrep[i]);
        s2.add(temp);
        rs2.add(s2);
    }



    InputStream in = IdGenre.class.getResourceAsStream("/xlsTemplates/IdGenre/IdGenreTemplate.xlsx");
 try
    {OutputStream out = new FileOutputStream("d:/IdGenre.xlsx");

     Context context = new Context();
     context.putVar("rs2", rs2);

     Transformer transformer = TransformerFactory.createTransformer(in, out);
     JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig().getExpressionEvaluator();
     Map<String, Object> functionMap = new HashMap<>();
     functionMap.put("joiner", new ListUtil());
     evaluator.getJexlEngine().setFunctions(functionMap);

     areaBuilder.setTransformer(transformer);
     List xlsAreaList = areaBuilder.build();
     Iterator iterator = xlsAreaList.iterator();

     while (iterator.hasNext()) {
         Area xlsArea = (Area) iterator.next();
         xlsArea.applyAt(new CellRef(xlsArea.getStartCellRef().getCellName()), context);
     }
     transformer.write();

    }
    finally
    {
        in.close();
    }
}


public static class ListUtil {

    public String join(List list) {
        StringBuilder builder = new StringBuilder();
        for (Object o : list) {
            if (builder.length() != 0) {
                builder.append(" / ");
            }
            builder.append(o);
        }
        return builder.toString();
    }
}

Stack trace :

15:44:44.975 [AWT-EventQueue-0] DEBUG org.jxls.util.TransformerFactory - Transformer class is org.jxls.transform.poi.PoiTransformer
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at sun.font.FontDesignMetrics$MetricsKey.init(Unknown Source)
at sun.font.FontDesignMetrics.getMetrics(Unknown Source)
...

I got the same stack trace when I try with the JxlsHelper class :

private void exportData(File file) throws ParseException, IOException {

    int o = 0;
    List<ListData2> rs2 = new ArrayList<ListData2>();
    ListData2 s2 = new ListData2(item[o], rep[o], justefaux[o], tempsrep[o]);
    for(int i=0; i<tableau.getRowCount(); i++){
        item[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 2).toString();
        rep[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 3).toString();
        justefaux[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 4).toString();
        tempsrep[i]=((DataIdGenre) tableau.getModel()).getValueAt(i, 5).toString();
        ListData2 temp = new ListData2(item[i], rep[i], justefaux[i], tempsrep[i]);
        s2.add(temp);
        rs2.add(s2);
    }



    InputStream in = IdGenre.class.getResourceAsStream("/xlsTemplates/IdGenre/IdGenreTemplate.xlsx");
 try
    {OutputStream out = new FileOutputStream("d:/IdGenreYOLO.xlsx");

     Context context = new Context();
     context.putVar("rs2", rs2);
     JxlsHelper.getInstance().processTemplate(in, out, context);

    }
    finally
    {
        in.close();
    }
}

Any help will be much appreciate.

Answer 1

The debug message you get

15:44:44.975 [AWT-EventQueue-0] DEBUG org.jxls.util.TransformerFactory - Transformer class is org.jxls.transform.poi.PoiTransformer

is just an information message indicating that Jxls PoiTransformer will be used to produce an Excel output.

The real error has nothing to do with Jxls but is connected with the NullPointerException you are having next

Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
at sun.font.FontDesignMetrics$MetricsKey.init(Unknown Source)
at sun.font.FontDesignMetrics.getMetrics(Unknown Source)
...

So you should look further into the stacktrace and see why do you get NullPointerException at sun.font.FontDesignMetrics.getMetrics()

Update:

If you have a problem with Excel not calculating formulas you can use techniques explained at POI Formula Evaluation. For example you can force Excel to recalculate formulas using workbook.setForceFormulaRecalculation(true) after Jxls processing is done. You can get a workbook instance by calling PoiTransformergetWorkbook() method.