xlsx recipe: Error when evaluating with new version
-
We are using jsreportonline to send periodical reports to our customers. One of our monthly reports has started to fail just this new month.
The error reported is:
Error while executing xlsx recipe
(because) error when evaluating engine handlebars for template anonymous
(because) "xlsxSData" helper call failed
(because) "xlsxSData" helper call failed
(because) "xlsxSData" helper call failed
(because) xlsxSData type="formula" helper only support cell refs that are above the current cell ref. cell ref: "D6", formula: "IF(ISNUMBER(VLOOKUP($B6,GEN!$A$2:$N$150,COLUMN()-1,FALSE)),VLOOKUP($B6,GEN!$A$2:$N$150,COLUMN()-1,FALSE),"")"This issue didn't exist with the previous version and it seems to be reported becase the software (xlsxSData function) doesn't take into account that the referred cells come from a different and previous sheet.
Has anyone else had a similar problem?
-
Thank you for reporting this. We will check on it and update you.
-
as an FYI, we ran in to the same issue and had to code around it. It seems more checking was added to make sure things didn't go out of bounds and changing the order to make sure the cells were created before this was done was our only solution. Sort of a pain, but it did work for us
-
@rjaimel i have checked the template and the issue was that our formula validation was not considering cell references from other sheets, and you have a lot of formulas using this, so we have fixed this but it is not available yet, it will be published soon.
@zewar96 do you remember the exact case? perhaps you can share an example of this, i just want to be sure that we are not validating something that should work.
-
For our stuff, it was because the SUM() formula that was in our template file referenced a cell that was below it. We actually didn't modify anything via code in the xlsx recipe and just put in the xslxPrint command and it threw that error, so it's validating things that aren't even touching. Excel had no problem with the forumla using a cell below what was being summed, but JSReport did. So we had to rip out the formula and manually calculate it ourself and then put the Replace command in there to write the value instead of the formula
-
@zewar96 interesting, i found the validation was not working as expected and was preventing some valid cases to succeed (like the SUM you were using). i will continue checking and improving the validation, it was mostly added to prevent some bad usage with loops and formulas, i will continue testing more cases and fine-tuned it. thanks
-
@bjrmatos Hi, When are you planning to publish the solution to jsreport online?
Meanwhile we are generating some reports manually
-
It should be done now.
Please report if you have further problems. Thank you and sorry for the delay.
