POI Formula Update

We are using Coldfusion CFSPREADSHEET to update an excel file and then using

Open in new window

to update all of the formulas.  Works great except for those that contain text and fields.  

="Lesser of Actual or "&TEXT($J$129,"0.0%")&" of Construction Fee Base"

Open in new window

Anything I can do to change that formula out and have it update properly?   I just get a "Error evaluating cell" error message.     Is it because it linked to a formula too?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Now, I'm not entirely sure about this, but you might want to try : WorksheetFunction.TEXT($J$129,"0.0%")
digitalwiseAuthor Commented:
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.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

digitalwiseAuthor Commented:
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).
digitalwiseAuthor Commented:
The regular formulas that just reference another field like

Open 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)

Open 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.
digitalwiseAuthor Commented:
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
digitalwiseAuthor Commented:
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.
Silly question, but you're positive you need to recalculate? ie That this particular formula it doesn't update correctly when the workbook is opened in Excel.  Edit: Because if the function is not supported in your version, there's really not much you can do other than upgrade or find another function.

Unfortunately if the best option (upgrading) is out, the only possibilities I can think of are to a) find an alternate function that is supported or b) try adding all functions except TEXT, recalculate, then add the TEXT functions (poor option, very hackish).
You could of course consider writing the formulae to a file from Java, and pull them in via an Excel VB routine.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
digitalwiseAuthor Commented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.