POI Formula Update

Now, I'm not entirely sure about this, but you might want to try : WorksheetFunction.TEXT($J$129,"0.0%")

In the actual Excel file??    ="Lesser of Actual or "&WorksheetFunction.TEXT($J$129,"0.0%")&" of Construction Fee Base" doesn't work.

Afaik, 'TEXT' is initialcapped in Excel i.e. 'Text' - making 'WorksheetFunction.Text( arg, arg )'. But this is from my Excel experience, nothing else.

Excel changes is to Worksheetfunction.TEXT and I get a name issue...

>  I just get a "Error evaluating cell" error message.

In CF? What about the stack trace and log files ? They usually contain more details

>  Is it because it linked to a formula too?

Any difference if you remove the formula and test it with a hard coded value?

Works great except for those that contain text and fields.

Can you give us an example of how it looks when it does work?

Also,  forget to ask what version of POI are you running?  Traditionally CF is bundled with an older version.  If you haven't replaced it, might try it with a newer version to see if you get the same result (be sure to backup jars first).

The regular formulas that just reference another field like

=+F50

Select allOpen in new window

.  We are running CF11 so whatever ships with that - it is on a hosted server (Hostek).  

The error log

Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: TEXT
	at org.apache.poi.hssf.record.formula.functions.NotImplementedFunction.evaluate(NotImplementedFunction.java:42)
	at org.apache.poi.ss.formula.OperationEvaluatorFactory.evaluate(OperationEvaluatorFactory.java:132)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateFormula(WorkbookEvaluator.java:437)
	at org.apache.poi.ss.formula.WorkbookEvaluator.evaluateAny(WorkbookEvaluator.java:260)

Select allOpen in new window

Well it might be a small and useful cost to expend on this :

>As of Feb 2012, POI supports about 140 built-in functions, see Appendix A for the full list. You can programmatically list supported / unsuported functions using the following helper methods:

    // list of functions that POI can evaluate
    Collection<String> supportedFuncs = WorkbookEvaluator.getSupportedFunctionNames();

    // list of functions that are not supported by POI
    Collection<String> unsupportedFuncs = WorkbookEvaluator.getNotSupportedFunctionNames();<

from here.

So it appears that TEXT is an approved function...

> Caused by: org.apache.poi.ss.formula.eval.NotImplementedException: TEXT

According to the error message, it is not supported in the version you are using.  The best option is to upgrade the jars, though that's probably out if you're on a shared host.  You could also try loading a newer version using the dynamic class loader feature in CF10+ - but I doubt it'll be compatible w/cfspreadsheet because it would be mixing objects created by different class loaders.

The CF jars are notoriously behind the latest.  If you're curious and have CF11 installed locally, use 7-zip to peak inside the POI jar.  The version number is listed in the manifest:  META-INF/MANIFEST.MF

Ugh.   Any other thoughts on this?   I found a solution to use CONCATENATE which the original developer of the Excel didn't do but I still can't use TEXT.    Any other options other than POI to update the formulas?   That's the whole reason for this exercise.

I ended up just writing VB to force a recalculation on open no matter what the users settings but you both sent me down the right path (and my disappointment that CF isn't using the latest POI!)

In some ways then, a reasonable outcome, given the circumstances. Thanks for sharing your determination.  ;)

> a reasonable outcome, given the circumstances

Yep. Would have been nice if it it could be done with the built in tools only, but in this specific case it can't.

@_agx_

:)