1

Is there a way to generate a report for a hierarchical tree using JXLS (or JETT)? For example the model is something like:

-node1
--node11
--node12
---node121
---node1211
--node13
-node2

I need to render the result with two columns:

| node name | node value
| node1     | =sum(node11, node12, node13)
| node11    | 5
| node12    | =sum(node121)
| node121   | =sum(node1211)
| node1211  | 10
| node13    | 15
| node2     | 20

....

I don't know the number of nested levels in the tree. My problem is the fact that for every parent node the rendered value in excel must be a formula with sum of the direct children ... and so on. Only leaf nodes must be rendered with the actual node value.

Thank you

Fanfy
  • 11
  • 2

1 Answers1

0

Taking the code from here: Traversing a tree recursively in depth first problems

public void traverseTree(Tree tree) {

    // print, increment counter, whatever
    System.out.println(tree.toString());
    // Do your code to build your sum formula string here and store it on your tree node.

    // traverse children
    int childCount = tree.getChildCount();
    if (childCount == 0) {
        // Overwrite the "forumla" you were building with something denoting that it is a leaf node here.
    } else {
        for (int i = 0; i < childCount; i++) {
            Tree child = tree.getChild(i);
            traverseTree(child);
        }
    }
}

After you've stored the built formulas on your nodes, traverse it again, but this time in place of the System.out.println(tree.toString()); line - actually write it to Excel using the JXLS API.

(Note that in place of a "Tree" object, you may need to define your own Tree class to wrap the Tree node and allow you to store the formula information as well.)

Community
  • 1
  • 1
user681574
  • 553
  • 2
  • 15
  • Thank you for your answer. I hoped that a more template oriented solution exists to actually traverse the tree. What I understand is that the only way is to build the tree in Java with JXLS Api. Also with the formulas defined in Java the I will have to know about row indexes in code – Fanfy Mar 21 '17 at 20:37
  • I have not seen anything in the JXLS library that would suggest a tree based template for the creation of an Excel sheet, but we could both learn something if someone else has something to add. – user681574 Mar 21 '17 at 21:58