How to evaluate a excel formula through XSSF event

问题:

I am reading XLSX files using event API of Apache POI, i.e I read the contents of the XLSX sheet through a SAX Parser. I want to know how can we get the computed value of a formula by using XSSF event API.

The way I know to do this is by using the FormulaEvaluator class. But since formulaEvaluator takes an instance of Workbook class I don't want to use this approach. (I am reading Excel files containing a million rows and 100 columns so if I create a Workbook object of that Excel my app server goes out of memory and hence I am using Event API)

How can I do the evaluation in event parsing, without a Workbook instance?

回答1:

Generally the cached value will be written with the cell, so you can just read that (no need to evaluate)

Taking this formula cell as an example:

  <c r="B10">
    <f>SUM(B1:B9)</f>
    <v>4995</v>
  </c>

You read the f value to get the formula itself, or just read the v value to see what the evaluation of the formula was when excel last touched the file. No need to evaluate!