[Bug 58452] Copy cell formulas containing unregistered Ptgs

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=58452

--- Comment #1 from Javen ONeal <ja...@gmail.com> ---
No code just yet. Here's what I have figured out so far (mostly a brain dump
for me, a passive request for help/direction from formula/ptg experts, and a
guide for future contributors):

Using testdata/spreadsheet/testNames.xls, UDF's (VBA macros) are saved in the
workbook names (Workbook.getName(String)), along with named ranges.

Converting this to an Excel2007 files, the UDFs are not included in the
workbook names.

This means that the behavior of FormulaParser.parse("MyFunc(\"arg\")", fpb,
FormulaParser.CELL, -1); is different for Excel97 and Excel2007 booms when
MyFunc is a UDF. Parse throws FormulaParseException if the formula contains a
UDF that isn't in the workbook names for both.

For my application, I want to copy formulas and shift the cell references, so I
will not be adding new formulas to the workbook. I'm not sure yet if I want to
implement a solution that could handle new formula names (since that would
create an invalid formula without adding the corresponding UDF to
vbaProject.bin or replacing vbaProject.bin with a vbaProject.bin from another
workbook.

Since XSSFWorkbook.getName() only includes named ranges, I'm assuming adding
UDFs to XSSFWotkbook._names is not desired. Right now I'm looking at creating a
variant of NamePtg that can hold information for XSSFWorkbook UDFs, so that
FormulaParser.parse returns ptgs that could be rendered back to a formula
string. Without registering a UDF with the same  name with the workbook via
XSSFWorkbook.addToolPack(UDFFinder), trying to evaluate a formula containing a
nkn-registered UDF will fail.

I could see where a user may rely on formula parse errors to detect
unregistered UDFs in a formula string, so I could introduce this fix by
requiring the user call Workbook.setAllowParsingUnregisteredUDFs(true), but I'm
thinking there are very few if any scenarios where someone would want to
disallow unregistered UDFs where they couldn't more directly detect it--such as
checking for UnregisteredNamePtg instances in the result of parse or otherwise.

-- 
You are receiving this mail because:
You are the assignee for the bug.

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