0

I want to convert some spreadsheets which contain references to a plugin-defined function which we are trying to remove. There is a method to convert this which works in VBA by using SUMIFS and generating a table

I am at the step where I have parsed the formulas and extracted the parameters used for the function (done by bracket counting). I have hit a snag. How do I get NPOI/POI Apache to evaluate the things. It seems to demand everything be done inside a cell. This isn't possible in this scenario (since these are parameters not cell formulas, so they don't have a cell in which to evaluate them).

There is the OperandResolver class which seems to be along the right lines, but it wants a ValueEval type as its input which I can't figure out at all. Currently I can only get the parameters as strings. Like "A1", "0.9", "SomeOtherFunction(...)" etc. Those are what I have.

Basically I need something like

pseudocode:    
var result = Evaluate_Formula_String(string formula, var Contextual_Information_eg_current_Worksheet)

Where the result would contain either a string or something easily converted into a string.

Community
  • 1
  • 1
Camderman106
  • 1
  • Create a new dummy cell, put the formula in it, and evaluate that? – Gagravarr May 05 '20 at 00:15
  • 1
    I cannot tell something about `NPOI`. But even if this question would be about `Java` and `apache poi`. it would be too vague and inconcrete. You should show a concrete formula you have and want evaluating. But see https://stackoverflow.com/questions/57324522/library-to-evaluate-excel-other-language-expression-evaluation-in-java-without-u/57327555#57327555. There a `Java` method `Object evaluateExcelFormula(String formula, Workbook workbookWithVariables)` is shown. Maybe this helps. – Axel Richter May 05 '20 at 06:19
  • @Gagravarr At the moment this is what im trying to do. But its so clearly terrible practice. I am creating a cell on the very end of the first row and just hoping its empty. Its also limited as not all parameters can be evaluated this way. for example, OFFSET would break since the cell move position. – Camderman106 May 05 '20 at 10:09
  • @AxelRichter The question is quite specific in its scope. NPOI and POI Apache are (practically) the same thing. Just in different languages. (C# vs Java). So a solution in one can be almost verbatim translated to the other The short version is, I can evaluate entire cells fine, but I need a way to evaluate strings as formulas directly, without that being in a cell object. For example, to evaluate parameters inside a cell formula string from a function that isn't in standard excel I'll have a look at the other thread. It looks promising. – Camderman106 May 05 '20 at 10:18

1 Answers1

0

The function you need seems to be simply Application.Evaluate. It takes a string and evaluates it.

Reference page: https://learn.microsoft.com/en-us/office/vba/api/excel.application.evaluate

Matthias B
  • 404
  • 4
  • 11
  • 2
    As stated in the question, I'm doing this within the C# library NPOI. Which is a copy of POI apache (java). I am not doing this within VBA. Hence this isn't available – Camderman106 May 04 '20 at 15:24
  • Ah, sorry - I browsed the VBA tag and thought you had access to VBA, even though you mainly use a different framework. Still, most of Excel is available through OLE automation, so from practically any language you should be able to do (pseudo-code): App := CreateOleObject('Excel.Application'); App.Evaluate(formulaString); – Matthias B May 05 '20 at 19:37