JXLS Multiple sheets fail when using different sized lists newt to eachother

Question

I'm using nearly same code with the Multiple sheets demo, only difference is I have 4 different lists on a sheet. (template4)

• If I run this example just for 1 sheet, that works perfect.
• If I remove the SUM formulas, it works.
• If I modify the template and remove the lists on the same row, It works, (template5)
• If I use same sized lists for each part, it works.

here's the templates

here's the code:

public void excelExport() throws IOException {
    LogManager.getLogger("org.jxls.area.XlsArea").setLevel(Level.ERROR);
    try (FileOutputStream os = new FileOutputStream("c:/Temp/xxx.xls")) {
        try (InputStream is =new FileInputStream("C:\\Works\\workspace-intellij\\nostro-vaadin\\src\\main\\resources\\file_templates\\report5_template.xls")) {
            Transformer transformer = TransformerFactory.createTransformer(is, os);
            XlsArea xlsArea = new XlsArea("template!A1:G20", transformer);

            XlsArea corrsArea = new XlsArea("template!A2:G19", transformer);
            EachCommand corrsEach = new EachCommand("val", "data", corrsArea, new ExcelCellRefGenerator());

            XlsArea bookDebitArea = new XlsArea("template!A8:C8", transformer);
            Command bookDebitEach = new EachCommand("bookDebit", "val.dbooks", bookDebitArea);
            corrsArea.addCommand(new AreaRef("template!A8:C8"), bookDebitEach);

            XlsArea bookCreditArea = new XlsArea("template!E8:G8", transformer);
            Command bookCreditEach = new EachCommand("bookCredit", "val.cbooks", bookCreditArea);
            corrsArea.addCommand(new AreaRef("template!E8:G8"), bookCreditEach);

            XlsArea statDebitArea = new XlsArea("template!A12:C12", transformer);
            Command statDebitEach = new EachCommand("statDebit", "val.dstmts", statDebitArea);
            corrsArea.addCommand(new AreaRef("template!A12:C12"), statDebitEach);

            XlsArea statCreditArea = new XlsArea("template!E12:G12", transformer);
            Command statCreditEach = new EachCommand("statCredit", "val.cstmts", statCreditArea);
            corrsArea.addCommand(new AreaRef("template!E12:G12"), statCreditEach);

            xlsArea.addCommand(corrsArea.getAreaRef(), corrsEach);

            Context context = new Context();
            context.putVar("data", getList(10, 50));
            xlsArea.applyAt(new CellRef("Sheet!A1"), context);
            xlsArea.processFormulas();
            transformer.write();
        } catch (Exception e) {
            log.error("", e);
        }
    }
}

public List<ExcelReportSheet> getList(int bank, int trans) {
    List<ExcelReportSheet> sheets = new ArrayList<>();
    for (int i = 0; i < bank; i++) {
        NsCorrespondents corr = new NsCorrespondents();
        corr.setBankcode(i + "bank");
        corr.setBankname(i + "bankname");
        corr.setBic(i + "bankbic");
        corr.setShdBbalance(new BigDecimal("10000"));
        corr.setShdSbalance(new BigDecimal("20000"));
        ExcelReportSheet reportSheet = new ExcelReportSheet(corr);
        Random random = new Random();
        for (int j = 0; j < random.nextInt(trans); j++) {
            reportSheet.getCbooks().add(ExcelReportSheet.ExcelReportTran.of(Date.valueOf(LocalDate.now()), j + "-" + i + "cbref", new BigDecimal(random.nextInt(trans))));
        }
        for (int j = 0; j < random.nextInt(trans); j++) {
            reportSheet.getDbooks().add(ExcelReportSheet.ExcelReportTran.of(Date.valueOf(LocalDate.now()), j + "-" + i + "cbref", new BigDecimal(random.nextInt(trans))));
        }
        for (int j = 0; j < random.nextInt(trans); j++) {
            reportSheet.getCstmts().add(ExcelReportSheet.ExcelReportTran.of(Date.valueOf(LocalDate.now()), j + "-" + i + "cbref", new BigDecimal(random.nextInt(trans))));
        }
        for (int j = 0; j < random.nextInt(trans); j++) {
            reportSheet.getDstmts().add(ExcelReportSheet.ExcelReportTran.of(Date.valueOf(LocalDate.now()), j + "-" + i + "cbref", new BigDecimal(random.nextInt(trans))));
        }

        sheets.add(reportSheet);
    }
    return sheets;
}

This is the error I'm getting. I believe it mixes the SUM formulas from different sheets.

Any help would be appreciated.

14:57:20.455 [main] ERROR o.jxls.transform.poi.PoiTransformer - Failed to set formula = SUM(C19,C20,Sheet1!C20,Sheet1!C21,Sheet1!C22,Sheet1!C23,Sheet1!C24,Sheet1!C25,Sheet1!C26,Sheet1!C27,Sheet1!C28,Sheet1!C29,Sheet1!C30,Sheet1!C31,Sheet1!C32,Sheet3!C21,Sheet3!C22,Sheet3!C23,Sheet3!C24,Sheet3!C25,Sheet4!C18,Sheet4!C19,Sheet4!C20,Sheet4!C21,Sheet4!C22,Sheet4!C23,Sheet4!C24,Sheet5!C23,Sheet5!C24,Sheet5!C25,Sheet5!C26,Sheet5!C27,Sheet5!C28,Sheet5!C29,Sheet5!C30,Sheet6!C19,Sheet6!C20,Sheet6!C21,Sheet6!C22,Sheet6!C23,Sheet6!C24,Sheet6!C25,Sheet6!C26,Sheet6!C27,Sheet6!C28,Sheet7!C16,Sheet7!C17,Sheet7!C18,Sheet7!C19,Sheet7!C20,Sheet7!C21,Sheet7!C22,Sheet7!C23,Sheet7!C24,Sheet7!C25,Sheet7!C26,Sheet7!C27,Sheet8!C12,Sheet8!C13,Sheet9!C27,Sheet9!C28,Sheet9!C29,Sheet9!C30,Sheet9!C31,Sheet9!C32,Sheet9!C33,Sheet9!C34,Sheet9!C35,Sheet9!C36,Sheet9!C37,Sheet9!C38,Sheet9!C39,Sheet9!C40) into cell = Sheet0!C16

Answer 1

When you have a complex template or a transformation with multiple lists the default Jxls formula processor (named FastFormulaProcessor) may not evaluate the formulas correctly.

In this case you should use StandardFormulaProcessor() which is slower but works reliably in all cases. You can configure the formula processor to use by adding the following code somewhere before processFormulas() method

xlsArea.setFormulaProcessor(new StandardFormulaProcessor());