SUM with more than 255 parameters

Issue #59 resolved
Tom Hombergs
created an issue

We have an Excel template that contains a $[SUM(...)] expression that creates the sum over cells that are not connected (e.g the SUM is created over A1, A3, A5 and so forth and not continuously over A1, A2, A3 ...).

JXLS puts an Excel SUM function into the cell like "SUM(A1, A3, A5...)" (as opposed to "SUM(A:A)" when the sum is over continuous cells).

As long as there are not more than 255 Parameters within the SUM-function, all works properly. As soon as there are more than 255 cells to sum up, Excel fails with an error.

It would be nice if JXLS checks if there are more than 255 parameters and creates multiple SUM-calls (each with max 255 parameters) and adds them (e.g "SUM(A1, A3, A5...) + SUM(A256, A258, A260...)".

Comments (5)

  1. leonate NA repo owner
    1. If you have a sum for all values in a column why don't you specify for example SUM(A:A) in your template file?
    2. Have you tried it with a StandardFormulaProcessor instead of the default one?
  2. Tom Hombergs reporter

    Thanks for the answer.

    1. In my use case, the cells that should be added are not adjacent, e.g. every second cell should be summed up. JXLS transforms this into "SUM(A1, A3, A5, ...)", which breaks with more than 255 parameters.

    2. It's the same result with the StandardFormularProcessor as with the FastFormularProcessor. POI throws the following exception:

    23:18:37.539 [main] ERROR o.jxls.transform.poi.PoiTransformer - Failed to set formula = SUM(B4,B6,B8,B10,B12,B14,B16,B18,B20,B22,B24,B26,B28,B30,B32,B34,B36,B38,B40,B42,B44,B46,B48,B50,B52,B54,B56,B58,B60,B62,B64,B66,B68,B70,B72,B74,B76,B78,B80,B82,B84,B86,B88,B90,B92,B94,B96,B98,B100,B102,B104,B106,B108,B110,B112,B114,B116,B118,B120,B122,B124,B126,B128,B130,B132,B134,B136,B138,B140,B142,B144,B146,B148,B150,B152,B154,B156,B158,B160,B162,B164,B166,B168,B170,B172,B174,B176,B178,B180,B182,B184,B186,B188,B190,B192,B194,B196,B198,B200,B202,B204,B206,B208,B210,B212,B214,B216,B218,B220,B222,B224,B226,B228,B230,B232,B234,B236,B238,B240,B242,B244,B246,B248,B250,B252,B254,B256,B258,B260,B262,B264,B266,B268,B270,B272,B274,B276,B278,B280,B282,B284,B286,B288,B290,B292,B294,B296,B298,B300,B302,B304,B306,B308,B310,B312,B314,B316,B318,B320,B322,B324,B326,B328,B330,B332,B334,B336,B338,B340,B342,B344,B346,B348,B350,B352,B354,B356,B358,B360,B362,B364,B366,B368,B370,B372,B374,B376,B378,B380,B382,B384,B386,B388,B390,B392,B394,B396,B398,B400,B402,B404,B406,B408,B410,B412,B414,B416,B418,B420,B422,B424,B426,B428,B430,B432,B434,B436,B438,B440,B442,B444,B446,B448,B450,B452,B454,B456,B458,B460,B462,B464,B466,B468,B470,B472,B474,B476,B478,B480,B482,B484,B486,B488,B490,B492,B494,B496,B498,B500,B502,B504,B506,B508,B510,B512,B514,B516,B518,B520,B522,B524,B526,B528,B530,B532,B534,B536,B538,B540,B542,B544,B546,B548,B550,B552,B554,B556,B558,B560,B562,B564,B566,B568,B570,B572,B574,B576,B578,B580,B582,B584,B586,B588,B590,B592,B594,B596,B598,B600,B602,B604,B606,B608,B610,B612,B614,B616,B618,B620,B622,B624,B626,B628,B630,B632,B634,B636,B638,B640,B642,B644,B646,B648,B650,B652,B654,B656,B658,B660,B662,B664,B666,B668,B670,B672,B674,B676,B678,B680,B682,B684,B686,B688,B690,B692,B694,B696,B698,B700,B702,B704,B706,B708,B710,B712,B714,B716,B718,B720,B722,B724,B726,B728,B730,B732,B734,B736,B738,B740,B742,B744,B746,B748,B750,B752,B754,B756,B758,B760,B762,B764,B766,B768,B770,B772,B774,B776,B778,B780,B782,B784,B786,B788,B790,B792,B794,B796,B798,B800,B802,B804,B806,B808,B810,B812,B814,B816,B818,B820,B822,B824,B826,B828,B830,B832,B834,B836,B838,B840,B842,B844,B846,B848,B850,B852,B854,B856,B858,B860,B862,B864,B866,B868,B870,B872,B874,B876,B878,B880,B882,B884,B886,B888,B890,B892,B894,B896,B898,B900,B902,B904,B906,B908,B910,B912,B914,B916,B918,B920,B922,B924,B926,B928,B930,B932,B934,B936,B938,B940,B942,B944,B946,B948,B950,B952,B954,B956,B958,B960,B962,B964,B966,B968,B970,B972,B974,B976,B978,B980,B982,B984,B986,B988,B990,B992,B994,B996,B998,B1000,B1002,B1004,B1006,B1008,B1010,B1012,B1014,B1016,B1018,B1020,B1022,B1024,B1026,B1028,B1030,B1032,B1034,B1036,B1038,B1040,B1042,B1044,B1046,B1048,B1050,B1052,B1054,B1056,B1058,B1060,B1062,B1064,B1066,B1068,B1070,B1072,B1074,B1076,B1078,B1080,B1082,B1084,B1086,B1088,B1090,B1092,B1094,B1096,B1098,B1100,B1102,B1104,B1106,B1108,B1110,B1112,B1114,B1116,B1118,B1120,B1122,B1124,B1126,B1128,B1130,B1132,B1134,B1136,B1138,B1140,B1142,B1144,B1146,B1148,B1150,B1152,B1154,B1156,B1158,B1160,B1162,B1164,B1166,B1168,B1170,B1172,B1174,B1176,B1178,B1180,B1182,B1184,B1186,B1188,B1190,B1192,B1194,B1196,B1198,B1200,B1202,B1204,B1206,B1208,B1210,B1212,B1214,B1216,B1218,B1220,B1222,B1224,B1226,B1228,B1230,B1232,B1234,B1236,B1238,B1240,B1242,B1244,B1246,B1248,B1250,B1252,B1254,B1256,B1258,B1260,B1262,B1264,B1266,B1268,B1270,B1272,B1274,B1276,B1278,B1280,B1282,B1284,B1286,B1288,B1290,B1292,B1294,B1296,B1298,B1300,B1302,B1304,B1306,B1308,B1310,B1312,B1314,B1316,B1318,B1320,B1322,B1324,B1326,B1328,B1330,B1332,B1334,B1336,B1338,B1340,B1342,B1344,B1346,B1348,B1350,B1352,B1354,B1356,B1358,B1360,B1362,B1364,B1366,B1368,B1370,B1372,B1374,B1376,B1378,B1380,B1382,B1384,B1386,B1388,B1390,B1392,B1394,B1396,B1398,B1400,B1402,B1404,B1406,B1408,B1410,B1412,B1414,B1416,B1418,B1420,B1422,B1424,B1426,B1428,B1430,B1432,B1434,B1436,B1438,B1440,B1442,B1444,B1446,B1448,B1450,B1452,B1454,B1456,B1458,B1460,B1462,B1464,B1466,B1468,B1470,B1472,B1474,B1476,B1478,B1480,B1482,B1484,B1486,B1488,B1490,B1492,B1494,B1496,B1498,B1500,B1502,B1504,B1506,B1508,B1510,B1512,B1514,B1516,B1518,B1520,B1522,B1524,B1526,B1528,B1530,B1532,B1534,B1536,B1538,B1540,B1542,B1544,B1546,B1548,B1550,B1552,B1554,B1556,B1558,B1560,B1562,B1564,B1566,B1568,B1570,B1572,B1574,B1576,B1578,B1580,B1582,B1584,B1586,B1588,B1590,B1592,B1594,B1596,B1598,B1600,B1602,B1604,B1606,B1608,B1610,B1612,B1614,B1616,B1618,B1620,B1622,B1624,B1626,B1628,B1630,B1632,B1634,B1636,B1638,B1640,B1642,B1644,B1646,B1648,B1650,B1652,B1654,B1656,B1658,B1660,B1662,B1664,B1666,B1668,B1670,B1672,B1674,B1676,B1678,B1680,B1682,B1684,B1686,B1688,B1690,B1692,B1694,B1696,B1698,B1700,B1702,B1704,B1706,B1708,B1710,B1712,B1714,B1716,B1718,B1720,B1722,B1724,B1726,B1728,B1730,B1732,B1734,B1736,B1738,B1740,B1742,B1744,B1746,B1748,B1750,B1752,B1754,B1756,B1758,B1760,B1762,B1764,B1766,B1768,B1770,B1772,B1774,B1776,B1778,B1780,B1782,B1784,B1786,B1788,B1790,B1792,B1794,B1796,B1798,B1800,B1802,B1804,B1806,B1808,B1810,B1812,B1814,B1816,B1818,B1820,B1822,B1824,B1826,B1828,B1830,B1832,B1834,B1836,B1838,B1840,B1842,B1844,B1846,B1848,B1850,B1852,B1854,B1856,B1858,B1860,B1862,B1864,B1866,B1868,B1870,B1872,B1874,B1876,B1878,B1880,B1882,B1884,B1886,B1888,B1890,B1892,B1894,B1896,B1898,B1900,B1902,B1904,B1906,B1908,B1910,B1912,B1914,B1916,B1918,B1920,B1922,B1924,B1926,B1928,B1930,B1932,B1934,B1936,B1938,B1940,B1942,B1944,B1946,B1948,B1950,B1952,B1954,B1956,B1958,B1960,B1962,B1964,B1966,B1968,B1970,B1972,B1974,B1976,B1978,B1980,B1982,B1984,B1986,B1988,B1990,B1992,B1994,B1996,B1998,B2000,B2002) into cell = Tabelle1!B2005
    org.apache.poi.ss.formula.FormulaParseException: Too many arguments to function 'SUM'. At most 255 were expected but got 1000.
        at org.apache.poi.ss.formula.FormulaParser.validateNumArgs(FormulaParser.java:1025)
        at org.apache.poi.ss.formula.FormulaParser.getFunction(FormulaParser.java:982)
        at org.apache.poi.ss.formula.FormulaParser.function(FormulaParser.java:946)
        at org.apache.poi.ss.formula.FormulaParser.parseNonRange(FormulaParser.java:536)
        at org.apache.poi.ss.formula.FormulaParser.parseRangeable(FormulaParser.java:504)
        at org.apache.poi.ss.formula.FormulaParser.parseRangeExpression(FormulaParser.java:237)
        at org.apache.poi.ss.formula.FormulaParser.parseSimpleFactor(FormulaParser.java:1123)
        at org.apache.poi.ss.formula.FormulaParser.percentFactor(FormulaParser.java:1083)
        at org.apache.poi.ss.formula.FormulaParser.powerFactor(FormulaParser.java:1070)
        at org.apache.poi.ss.formula.FormulaParser.Term(FormulaParser.java:1430)
        at org.apache.poi.ss.formula.FormulaParser.additiveExpression(FormulaParser.java:1530)
        at org.apache.poi.ss.formula.FormulaParser.concatExpression(FormulaParser.java:1514)
        at org.apache.poi.ss.formula.FormulaParser.comparisonExpression(FormulaParser.java:1471)
        at org.apache.poi.ss.formula.FormulaParser.unionExpression(FormulaParser.java:1451)
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:1572)
        at org.apache.poi.ss.formula.FormulaParser.parse(FormulaParser.java:145)
        at org.apache.poi.xssf.usermodel.XSSFCell.setFormula(XSSFCell.java:464)
        at org.apache.poi.xssf.usermodel.XSSFCell.setCellFormula(XSSFCell.java:444)
        at org.jxls.transform.poi.PoiTransformer.setFormula(PoiTransformer.java:202)
        at org.jxls.formula.FastFormulaProcessor.processAreaFormulas(FastFormulaProcessor.java:106)
        at org.jxls.area.XlsArea.processFormulas(XlsArea.java:509)
        at org.jxls.util.JxlsHelper.processTemplate(JxlsHelper.java:106)
        at org.jxls.util.JxlsHelper.processTemplate(JxlsHelper.java:94)
        at org.wickedsource.jxls.SumWithManyParametersTest.test(SumWithManyParametersTest.java:32)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
        at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
        at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
        at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
        at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
        at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
        at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
        at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
        at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
        at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
        at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
        at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
        at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
        at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:119)
        at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
        at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
        at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
    

    Now, this problem could be addressed within POI or within JXLs FormularProcessor. The latter wis easier, I think :).

    I solved it rather uglyly by creating a copy of FastFormularProcessor in which i replaced the following line

    targetFormulaString = targetFormulaString.replaceAll(Util.regexJointedLookBehind + Util.sheetNameRegex(cellRefEntry) + Pattern.quote(cellRefEntry.getKey().getCellName()), Matcher.quoteReplacement(replacementString));
    

    with

    if (targetCells.size() > 255 && targetFormulaString.startsWith("SUM")) {
      // Excel doesn't support more than 255 arguments in functions.
      // Thus, we just concatenate all cells with "+" to have the same effect.
      targetFormulaString = join(targetCells, "+");
    } else {
      targetFormulaString =   targetFormulaString.replaceAll(Util.regexJointedLookBehind + Util.sheetNameRegex(cellRefEntry) + Pattern.quote(cellRefEntry.getKey().getCellName()), Matcher.quoteReplacement(replacementString));
    }
    

    where the new method "join()" looks as follows:

    private String join(List<CellRef> cellRefs, String separator) {
    List<String> cellStrings = new ArrayList<>();
      for (CellRef cellRef : cellRefs) {
        cellStrings.add(cellRef.getCellName());
      }
      return Util.joinStrings(cellStrings, separator);
    }
    

    Basically, instead of resolving to "SUM(A1, A3, A5, ...)" the result now is "A1 + A3 + A5 + ..." which allows more than 255 values to be summed up. The solution only works for the SUM function, obviously.

    I then created a copy of JxlsHelper and changed the method setFormularProcessor so that it uses the custom FormularProcessor. The sources can be viewed at https://github.com/thombergs/jxls-playground/tree/master/src/test/java/org/wickedsource/jxls.

    Is there any chance you would include this special treatment (in this or similar fashion) into the FormularProcessors shipped with JXLS?

    By the way, it would be nice if JxlsHelper would allow to pass in a custom implementation of FormularProcessor instead of just allowing to switch between the Standard- and FastFormularProcessor implementations. This way, I wouldn't have to copy the whole JxlsHelper class.

  3. Log in to comment