Unable to refresh formulas in excel workbook

[tylerjthomas9]

I am trying to refresh an excel workbook from within Julia (instead of having to open the document in excel). I have used Taro to update the cell's value, but the API for refreshing formulas is not available. I am trying to gain access to it, but I can not seem to correctly import it into JavaCall.jl. Here is the method I am trying to call: https://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFFormulaEvaluator.html#create-org.apache.poi.hssf.[…]i.ss.formula.udf.UDFFinder-

@mkitti thinks that Taro.jl may be using an older version https://github.com/aviks/Taro.jl/blob/master/deps/build.jl

using Taro
using JavaCall
Taro.init()

# update cell
w = Workbook("test.xlsx")
s = getSheet(w, "Display")
r = getRow(s, 1)
c = getCell(r, 2)
setCellValue(c, "V")

@jimport org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator
jcall(w, "evaluateAllFormulaCells", Workbook, ())

Exception in thread "main" java.lang.NoSuchMethodError: evaluateAllFormulaCells
ERROR: JavaCall.JavaCallError("Error calling Java: java.lang.NoSuchMethodError: evaluateAllFormulaCells")
Stacktrace:
 [1] geterror(allow::Bool)
   @ JavaCall C:\Users\tyler\.julia\packages\JavaCall\tjlYt\src\core.jl:418
 [2] jcall(typ::Type{JavaObject{Symbol("org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator")}}, method::String, rettype::Type, argtypes::Tuple{}, args::JavaObject{Symbol("org.apache.poi.ss.usermodel.Workbook")})
   @ JavaCall C:\Users\tyler\.julia\packages\JavaCall\tjlYt\src\core.jl:226
 [3] top-level scope
   @ REPL[18]:1

[aviks]

Taro is using Tika 1.23. Can you check and confirm that the version of POI included in that version of Tika has the functionality you are looking for? I am not sure what to expect for this.

In terms of the Julia code above, you seem to be trying to execute

workbook.evaluateAllFormulaCells()

Is that method you want to run? Looking at the API, you probably want something like HSSFFormulaEvaluator.create(w, null, null).evaluateAllFormulaCells(w) . So maybe try to call the equivalent in Julia and see if that works?

I will also say, why do you care to change the result value? Unless the workbook has auto-recalculation set to off, the workbook should recalculate all values when opened in Excel. The reason to be sceptical is that formula evaluation in POI/java will try to replicate the calculations that Excel would have done, but it is not guaranteed to be the same result, since it's not the same code. Of course this will work in many, or most, cases, but just wanted to mention the possibility of failures here.

Finally, if you do know the answer to a formula evaluation, you can set the value in the formula cell directly using cell.setCellValue(...). The formula will be retained as is, but the result value will change.

[tylerjthomas9]

I will also say, why do you care to change the result value? Unless the workbook has auto-recalculation set to off, the workbook should recalculate all values when opened in Excel. The reason to be sceptical is that formula evaluation in POI/java will try to replicate the calculations that Excel would have done, but it is not guaranteed to be the same result, since it's not the same code. Of course this will work in many, or most, cases, but just wanted to mention the possibility of failures here.

A data feed that I am using has certain data points that are only available via their excel plugin, and not everything is available via SQL as of right now. For the time being, I am going to have to automate refreshing excel workbooks to pull the data.

In terms of the Julia code above, you seem to be trying to execute

workbook.evaluateAllFormulaCells()

Ah, I was using JavaCall incorrectly. From what I can see, Taro is not causing any issues, it was my poor understanding of JavaCall. Thank you for the help. Tika 1.23 has all the functionality that I need.