Wrong sum in multisheet

Issue #85 resolved
Marcus W.
created an issue

We have a production problem with wrong sums.

Symptom: The sums are wrong. The sum formula contains cells for all sheets and all cells concatted with "+". This only happens if the collection (that contains data for all the sheets) has many items (326).

Target: The sum formula should contain only an area (e.g. "E2:E260") of the current sheet to produce a correct sum.

Our Hotfix: We found out that issue #59 causes this problem. We guess it does not work with multisheets. We subclassed the StandardFormulaProcessor and took back the change https://bitbucket.org/leonate/jxls/commits/70f3a70f2e21 . After that the sums are correct. However, this is not the final solution. But now our client can work again.

What are your plans to release a fixed version? :-)

Comments (11)

  1. Marcus W. reporter

    How to reproduce:

    • take jxls-demo > MultiSheetMarkupDemo

    • replace line 95 of EachIfCommandDemo.java to

            while (department.getHeadcount() < 300) {
                department.addEmployee(new Employee("Cat", 34, 1900, 0.15));
            }
    

    to get more rows.

    • change cell F9 of multisheet_markup_demo.xls to =SUMME(F8)

    • run MultiSheetMarkupDemo and look at target/multisheet_markup_output.xls

    • sheet "IT": D13 (using $[SUM...]) is okay, but F13 has a wrong value and this wrong formula

    =F8+F11+HR!F8+HR!F10+HR!F11+HR!F12+HR!F13+HR!F14+HR!F15+HR!F16+HR!F17+HR!F18+HR!F19+HR!F20+HR!F21+HR!F22+HR!F23+HR!F24+HR!F25+HR!F26+HR!F27+HR!F28+HR!F29+HR!F30+HR!F31+HR!F32+HR!F33+HR!F34+HR!F35+HR!F36+HR!F37+HR!F38+HR!F39+HR!F40+HR!F41+HR!F42+HR!F43+HR!F44+HR!F45+HR!F46+HR!F47+HR!F48+HR!F49+HR!F50+HR!F51+HR!F52+HR!F53+HR!F54+HR!F55+HR!F56+HR!F57+HR!F58+HR!F59+HR!F60+HR!F61+HR!F62+HR!F63+HR!F64+HR!F65+HR!F66+HR!F67+HR!F68+HR!F69+HR!F70+HR!F71+HR!F72+HR!F73+HR!F74+HR!F75+HR!F76+HR!F77+HR!F78+HR!F79+HR!F80+HR!F81+HR!F82+HR!F83+HR!F84+HR!F85+HR!F86+HR!F87+HR!F88+HR!F89+HR!F90+HR!F91+HR!F92+HR!F93+HR!F94+HR!F95+HR!F96+HR!F97+HR!F98+HR!F99+HR!F100+HR!F101+HR!F102+HR!F103+HR!F104+HR!F105+HR!F106+HR!F107+HR!F108+HR!F109+HR!F110+HR!F111+HR!F112+HR!F113+HR!F114+HR!F115+HR!F116+HR!F117+HR!F118+HR!F119+HR!F120+HR!F121+HR!F122+HR!F123+HR!F124+HR!F125+HR!F126+HR!F127+HR!F128+HR!F129+HR!F130+HR!F131+HR!F132+HR!F133+HR!F134+HR!F135+HR!F136+HR!F137+HR!F138+HR!F139+HR!F140+HR!F141+HR!F142+HR!F143+HR!F144+HR!F145+HR!F146+HR!F147+HR!F148+HR!F149+HR!F150+HR!F151+HR!F152+HR!F153+HR!F154+HR!F155+HR!F156+HR!F157+HR!F158+HR!F159+HR!F160+HR!F161+HR!F162+HR!F163+HR!F164+HR!F165+HR!F166+HR!F167+HR!F168+HR!F169+HR!F170+HR!F171+HR!F172+HR!F173+HR!F174+HR!F175+HR!F176+HR!F177+HR!F178+HR!F179+HR!F180+HR!F181+HR!F182+HR!F183+HR!F184+HR!F185+HR!F186+HR!F187+HR!F188+HR!F189+HR!F190+HR!F191+HR!F192+HR!F193+HR!F194+HR!F195+HR!F196+HR!F197+HR!F198+HR!F199+HR!F200+HR!F201+HR!F202+HR!F203+HR!F204+HR!F205+HR!F206+HR!F207+HR!F208+HR!F209+HR!F210+HR!F211+HR!F212+HR!F213+HR!F214+HR!F215+HR!F216+HR!F217+HR!F218+HR!F219+HR!F220+HR!F221+HR!F222+HR!F223+HR!F224+HR!F225+HR!F226+HR!F227+HR!F228+HR!F229+HR!F230+HR!F231+HR!F232+HR!F233+HR!F234+HR!F235+HR!F236+HR!F237+HR!F238+HR!F239+HR!F240+HR!F241+HR!F242+HR!F243+HR!F244+HR!F245+HR!F246+HR!F247+HR!F248+HR!F249+HR!F250+HR!F251+HR!F252+HR!F253+HR!F254+HR!F255+HR!F256+HR!F257+HR!F258+HR!F259+HR!F260+HR!F261+HR!F262+HR!F263+HR!F264+HR!F265+HR!F266+HR!F267+HR!F268+HR!F269+HR!F270+HR!F271+HR!F272+HR!F273+HR!F274+HR!F275+HR!F276+HR!F277+HR!F278+HR!F279+HR!F280+HR!F281+HR!F282+HR!F283+HR!F284+HR!F285+HR!F286+HR!F287+HR!F288+HR!F289+HR!F290+HR!F291+HR!F292+HR!F293+HR!F294+HR!F295+HR!F296+HR!F297+HR!F298+HR!F299+HR!F300+HR!F301+HR!F302+HR!F303+HR!F304+HR!F305+HR!F306+HR!F307

  2. leonate NA repo owner

    In your example you are using a simple formula when processing two different areas of source cells as defined in the if-command in the template:

    jx:if(condition="employee.payment <= 2000", lastCell="F8", areas=["A8:F8","A13:F13"])
    

    It will not work because the formula processor does not know how to join two different sets of cells. So in this case you should use a special formula syntax which will join the cells from two different source areas.

    So in this case you should use $[SUM(U_(F8,F13))] instead of =SUM(F8)

  3. leonate NA repo owner

    If you see an issue with processing regular SUM formula with a collection of many items please try to find another example which does not involve $[SUM(U_...] formulas so that I can clearly see the issue.

  4. Marcus W. reporter

    I built 2.4.2-SNAPSHOT and replaced jxls-2.4.0 with it. We've tested manually and over night automatically. It was successful. Please release.

    The error of version 2.4.0 occured not only with multisheets. It occured with more than 255 items in the collection. I added MultipleSheetDemoIssue85Simple to https://github.com/gastendonk/jxls-demo .

  5. Log in to comment