Issue #8 resolved

Bug at using nested formulas

Alexander Komarov
created an issue

Test-case project attached. Look at the formula "City Total" and "Municipality Total". There is 4 nested loops in cells A4, B4, C4, D4. There is values on 4-level-loop in cell E4. In the cell E5 there is formula, that calc sum of values in house (4-level-loop) In the cell E6 there is formula, that must calc sum of values in city (only one city, 3-level-loop), but it's calcs sum of all houses

Comments (7)

  1. leonate NA repo owner

    It was required to completely rewrite the formula processing code to resolve this bug.

    But the real problem is that the new (correct) formula processing works about 10 times slower than the original one because it has to perform complex analysis of all formula cell transformations.

    Because of this I made it possible for a user to choose the formula processing strategy.

    There is now a FormulaProcessor interface which has 2 implementations

    • FastFormulaProcessor - works in the same way as before but for some particular cases (like the one attached to the ticket) may produce incorrect results
    • StandardFormulaProcessor - performs thorough formula analysis and eliminates incorrect results but works about 10 times slower

    By default XlsArea uses FastFormulaProcessor because it works fine in the most cases and performs really well.

    But if a user has multi-level nested formulas then he can choose to use StandardFormulaProcessor with a single line of code

    area.setFormulaProcessor(new StandardFormulaProcessor());
    

    JxlsHelper class now also contains a boolean flag to specify if it should use fast formula processing during area transformations. By default it is set to true.

  2. Log in to comment