(no subject)

[Giu Fus]

Hi,

I need an hand to find the better approach to solve my requirement:

Attached you find the template i'm working on, the result and a video to explain my problem:

How to use a formula in a section of the xls that reference to cells populated by a foreach of another section.

I hope you can give me some tips.

Thanks

[Giu Fus]

I resolved using :
- a style tag to mark a cell with a class name,
- then implement a custom cell listener that keep updated a list in beans map adding the rowCol indexes of the cell i want to keep track
- set cellFormula in the workbook using the list values

[Giu Fus]

[Giu Fus]

[Giu Fus]

[Etienne Canaud]

Hi Giu,

I can't find any attachment in your posts - did you already address all your requirements or do you need further help?

[Giu Fus]

Hi,
I think I solved, don't know if is a workaround or the better approach, but solved.
If you want to take a look this is the template.
D10 in sheet #2 should be the sum of all generated Q21.

At the moment what I've done is:
- tagged Q21 cell with a css class using <jt: style=""> tag
- implemented a CellListener that update a beanMap item with the addresses ot the tagged cells (in a string like "Q21,Q31,Q41 etc...")
- </jt:>

class MyCellListener implements CellListener {

        @Override
        public boolean beforeCellProcessed(CellEvent event) {
            if(event.getCell().toString().contains("classRicavoTot")) {
                final Cell cell = event.getCell();
                final String address = cell.getAddress().toString();
                final Map<String, Object> beans = event.getBeans();

                String oldRicavoTotalString = (String) beans.get(RICAVO_TOTAL_KEY);
                String cellSeparator = StringUtils.isBlank(oldRicavoTotalString) ? "" : CELL_SEPARATOR;
                String newRicavoTotalString = oldRicavoTotalString.concat(cellSeparator).concat(address);

                beans.put(RICAVO_TOTAL_KEY, newRicavoTotalString);
            }

            return true;
        }

        @Override
        public void cellProcessed(CellEvent event) {

        }
    }

• after getting the workbook (jettTransformer.transform) I use directly apache poi to do workbook.getD10.setCellFormula ("SUM(" + beanMap.get(RICAVO_TOTAL_KEY) + ")")

Surely there is a more elegant approach butat the moment I have not found it.
Thanks for your help :)

[Etienne Canaud]

I'm not too familiar with how JETT can keep track of $[] formulas and update the references within as template get filled, so I don't know whether there's a better way to generate the list of cells that you use in the D10 formula.

However, you can skip the step where you edit with POI at the end by adding similar code at the bottom of your sheet #2 enclosed in a ${{ ...your code here... ; return ''; }} block. Just make sure to use JEXL syntax, which may be a bit different from the Java one. Keep in mind that you can access the current sheet with the sheet variable that's automatically available to the script code. Also, concatenation of strings in JEXL can sometimes yield unpredictable results when you use the "+" operator, I found it more reliable to use "First string".concat("the other string");

If you're fine with doing some extra POI processing after filling the template then you don't really need this, but for maintenance purposes I find that it's easier to have a pure JETT solution that doesn't involve Java POI processing.

[Giu Fus]

I had not yet discovered this feature but I think that it can just improve
maintainability of what I've done, as you said, so thank you, I really appreciate it :)

have a great week,
bye