Lets say i create a foreach statement in a cell and output an attribute of a bean. In Excel I name this cell (in the top left corner where it says B5, to MyData). Next i use an excel function: Sum(MyData) so that it follows the dynamic growth of the rows created from the foreach loop.
When the excel output file is generated, the sum(MyData) only references the original Cell (B5) and does not grow as rows are added by JETT. Normally in Excel when you insert rows it grows names ranges that are affected in the range. Since this does not work in JETT is there another way to capture the data cell range generated in something like a formula or a chart?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
It seems named ranges don't get expanded during transform. However, you could define a named ranges formula to be something like:
=OFFSET(Sheet1!$A$3, 0, 0, COUNTA(Sheet1!$A$3:$A$99999), 1)
and it would include all cells from a3-a999999 stopping at the first cell that no longer has data. [see excel info about counta()]
Hope this helps someone.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yes, using an OFFSET Excel formula can include all cells in the expected range. That can be useful for other things, such as graphing data populated by JETT in Excel charts. Apache POI doesn't have good support for manipulating charts yet, so for that case that's a good solution.
But JETT doesn't currently pay attention to named ranges. It will ignore them. That is why MyData still refers only to B5. However, JETT Formulas will track the translations and expansions of the original cell reference. In the cell that you currently have the formula Sum(MyData), use a JETT Formula instead.
$[SUM(B5)]
JETT recognizes this and, at the end of transformation, replaces this with an Excel formula, with the original cell reference replaced with the translated/expanded cell reference range.
=SUM(B5:B14)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Lets say i create a foreach statement in a cell and output an attribute of a bean. In Excel I name this cell (in the top left corner where it says B5, to MyData). Next i use an excel function: Sum(MyData) so that it follows the dynamic growth of the rows created from the foreach loop.
When the excel output file is generated, the sum(MyData) only references the original Cell (B5) and does not grow as rows are added by JETT. Normally in Excel when you insert rows it grows names ranges that are affected in the range. Since this does not work in JETT is there another way to capture the data cell range generated in something like a formula or a chart?
It seems named ranges don't get expanded during transform. However, you could define a named ranges formula to be something like:
=OFFSET(Sheet1!$A$3, 0, 0, COUNTA(Sheet1!$A$3:$A$99999), 1)
and it would include all cells from a3-a999999 stopping at the first cell that no longer has data. [see excel info about counta()]
Hope this helps someone.
Yes, using an
OFFSETExcel formula can include all cells in the expected range. That can be useful for other things, such as graphing data populated by JETT in Excel charts. Apache POI doesn't have good support for manipulating charts yet, so for that case that's a good solution.But JETT doesn't currently pay attention to named ranges. It will ignore them. That is why
MyDatastill refers only toB5. However, JETT Formulas will track the translations and expansions of the original cell reference. In the cell that you currently have the formulaSum(MyData), use a JETT Formula instead.JETT recognizes this and, at the end of transformation, replaces this with an Excel formula, with the original cell reference replaced with the translated/expanded cell reference range.