add-in function in a POI sheet

[Ben Mckenzie <bm...@highlineFI.com>]

Hi all,
I apologize if this is a repost, I originally tried to post from nabble, and not sure if that worked or not.   I am rewriting the question a bit, so it doesn't seem to repetitive.  :)

If anyone has any ideas on how to do this, I would be very grateful.   I am creating an HSSF excel spreadsheet using POI.  Currently, I use cell.setCellValue to set the information in the a cell, however, when I open the spreadsheet, it is impossible for me to get the cells to recalculate with the standard excel functions.  No matter what I try F9, Ctrl-F9, Ctrl->Alt-F9, the cells don't calculate.  The only thing that seems to get them to calculate is to either click on the cell, and hit enter, or do a replace for = with =.

So, I have tried to use setCellFormula, but the problem is, I want to set the cell formula in the spreadsheet I am writing to an addin function, so that the addin will be able to pull the values in.  But, the cell parser keeps saying that the formula is not registered, since of course it is not an add-in function.  I have read the instructions on how to create a new function, but I would rather be ignored by the parser, as it requires pulling information through an addin, so POI would not be able to accurately calculate it.

I also tried adding "setForceFormulaReclaculation(true)" to the spreadsheet, but that didn't seem to do anything.

I have also tried POI versions 3.4, 3.7, and 3.8 beta 3, but they all seem to do the exact same thing.

Any ideas?



Thanks for your help in advance!


-       Ben McKenzie

Re: add-in function in a POI sheet

[ben <bm...@highlinefi.com>]

Yes, a dummy UDF fixed it.  I was only able to use dummy UDF in 3.8 beta 3,
but that worked.  Thanks for the help!

As an FYI, it was a .NET function, not a VB one.

--
View this message in context: http://apache-poi.1045710.n5.nabble.com/add-in-function-in-a-POI-sheet-tp4717201p4724425.html
Sent from the POI - User mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org

Re: add-in function in a POI sheet

[Dave Fisher <da...@comcast.net>]

On Aug 19, 2011, at 3:34 PM, Ben Mckenzie wrote:

> Hi all,
> I apologize if this is a repost, I originally tried to post from nabble, and not sure if that worked or not.   I am rewriting the question a bit, so it doesn't seem to repetitive.  :)
> 
> If anyone has any ideas on how to do this, I would be very grateful.   I am creating an HSSF excel spreadsheet using POI.  Currently, I use cell.setCellValue to set the information in the a cell, however, when I open the spreadsheet, it is impossible for me to get the cells to recalculate with the standard excel functions.  No matter what I try F9, Ctrl-F9, Ctrl->Alt-F9, the cells don't calculate.  The only thing that seems to get them to calculate is to either click on the cell, and hit enter, or do a replace for = with =.
> 
> So, I have tried to use setCellFormula, but the problem is, I want to set the cell formula in the spreadsheet I am writing to an addin function, so that the addin will be able to pull the values in.  But, the cell parser keeps saying that the formula is not registered, since of course it is not an add-in function.  I have read the instructions on how to create a new function, but I would rather be ignored by the parser, as it requires pulling information through an addin, so POI would not be able to accurately calculate it.

Create a dummy "miscalculating" UDF.

> 
> I also tried adding "setForceFormulaReclaculation(true)" to the spreadsheet, but that didn't seem to do anything.

Did you try with a dummy UDF? I think that the behavior will improve.


> 
> I have also tried POI versions 3.4, 3.7, and 3.8 beta 3, but they all seem to do the exact same thing.
> 
> Any ideas?


What type of Add-in function are you calling is it in a VBA, a .NET assembly, or what? It could make a difference.

Regards,
Dave


> 
> 
> 
> Thanks for your help in advance!
> 
> 
> -       Ben McKenzie


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org