Hi Josh,
Vijayakumar raises an interesting question and I was waiting for your
answer.
I am interested in both (a) and (b). This type of facility is
extremely useful. It would be very handy to be able to look at any
cell and determine its utility.
I would define your (a) "apparently used" and (b) "actually resolved"
during evaluation.
(a) full dependencies - cells that might be used, if referenced by
name these may be missing from the workbook.
(b) current dependencies - cells that will be used on evaluation given
the current state of the workbook.
This would be turned into a graph of the cell connections by a
Workbook function - getCalculationGraph? Perhaps the act of evaluating
is required and the CalculationGraph is an optional byproduct.
This graph would then be accessed via cell reference including name
and it could be determined if any cell is in one of several
Calculation States in relation to the graph.
(1) Result. The cell is a terminal value that is the result of a
formula.
(2) Intermediate Result. The cell is the result of a formula and is
also a current dependency of another cell.
(3) Current Input. The cell is not a formula and is a current
dependency of another cell.
(4) Potential Input. The cell is not a formula, is not a current
dependency of another cell, but is a full dependency of another cell.
(5) Label. The cell is not the result of a formula, and is not a
dependency.
I think that this is a good outline of the situation.
Think about the unit tests you could write within an application.
Templates could be translated to other languages or customers and the
completeness of the model confirmed.
Regards,
Dave
Hello Vijayakumar,
Currently there is no officially supported way of tracing formula
dependencies with POI but internally there is quite a lot of logic in
place for dealing with these issues. Before you attack this problem
however, you should establish exactly what it is that you are trying
to find out. For any given formula cell, POI can help you determine
the cell references either (a) apparently used or (b) actually
resolved during evaluation. These (a and b) can be different and you
should understand why. (Many functions like IF, INDEX, INDIRECT
typically have differences between apparent references used versus
actual references used. In general the presence of error values can
cut short the evaluation of just about any operator or function.) If
there are differences between (a) and (b) for your case, then it is
likely that neither is exactly what you want. You can write code to
make your own custom hybrid of (a) and (b), but your solution will
probably be incomplete (many cases not supported).
For (a) you can probably get good use out of
HSSFEvaluationWorkbook.getFormulaTokens(EvaluationCell).
For (b) it might be best to implement IEvaluationListener and pass
that to WorkbookEvaluator
As mentioned above, these solutions involve using POI classes marked
as 'for internal use only' which means that you cannot rely on your
code working properly against future versions of POI. However, if you
are able to contribute your solution (and it has a simple API that is
likely to be useful to other POI users) it can become part of POI and
you won't have to worry about future support.
regards,
Josh
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
