1

I'm trying to add a formula to my spreadsheet that contains a SUMIF that references a cell. Here's the code for the formula I'm trying to add:

addFormula("(100 * " + dRangeCell + " / 256) * (SUMIF(B" + (dip.start+1) + ":B" + (dip.end+1) + ",\"<\"&" + dbThresholdCell + ")) / " + profile.getImageWidth(), 
            dipAreaPercentageCol, row, sheet, highlightedFormat);

The important part is the condition in the SUMIF, where I'm trying to do a sum if the values are less than the value in another cell.

When the code runs I get this error:

Warning:  Lexical error:   (100 * D18 / 256) * (SUMIF(B1:B2,"<"&D21)) / 332 at char  36 when parsing formula (100 * D18 / 256) * (SUMIF(B1:B2,"<"&D21)) / 332 in cell Profile!N24

The character it's complaining about is the ampersand. However, when I paste that exact formula into the spreadsheet in Excel it works perfectly.

Does JExcel not know how to parse the ampersand properly? Is there a workaround for my situation?

almostkorean
  • 41
  • 2
  • What does `SUMIF(B1:B2,"<"&D21)` actually mean? Is this a valid formula? – Thomas W May 14 '13 at 02:29
  • Im not sure of this but try to use URLEncoder.encode() – Oneb May 14 '13 at 03:19
  • `SUMIF(B1:B2,"<"&D21)` means sum values between B1 and B2 if they are less than the value in cell D21. Yep it is a valid formula, when I put it into Excel manually it works perfectly. You can see an example of it in Microsoft documentation as well: [link](http://office.microsoft.com/en-us/excel-help/sumif-function-HP010062465.aspx) towards the bottom (search for "&") – almostkorean May 14 '13 at 15:38

1 Answers1

0

if you use concatenate(a,b) instead of a&b you can get the desired effect. The example above will only handle single level concatenation like &and&and&and if you want nested ie (and&and)&and you'll have to mess around with the regex –

if (colElement.attributeValue("type").equals(CellType.STRING_FORMULA.toString())) {
  WritableCellFormat wcf = new WritableCellFormat();
  Pattern pattern = Pattern.compile("^([^&]+)&(.*)$");
  for (Matcher m = pattern.matcher(t); m.matches(); m=pattern.matcher(t)) {
    t = "CONCATENATE(" + m.group(1) +  "," + m.group(2) + ")";
  }
  sheet.addCell(new jxl.write.Formula(x, y, t));

}
Qantas 94 Heavy
  • 15,750
  • 31
  • 68
  • 83
user3514323
  • 1
  • if you use concatenate you can re create the string instead of using the & ampersand character. The example above only works for lists of &and&and& formulas, you'll have to work out the regex to do complex nesting in brackets if you need to. – user3514323 Apr 09 '14 at 08:35
  • if you use concatenate(a,b) instead of a&b you can get the desired effect. The example above will only handle single level concatenation like &and&and&and if you want nested ie (and&and)&and you'll have to mess around with the regex – user3514323 Apr 09 '14 at 08:37
  • Welcome to [so]! There's no need to put that as a comment, you can add that straight to the answer. Thanks! – Qantas 94 Heavy Apr 09 '14 at 08:52
  • Did anyone use this solution and resolved the issue? – Shamal Karunarathne Sep 11 '14 at 09:34