Bug 57651

Created attachment 32538 [details]
Excel Template with a predefined VBA Function

Hi All,

In my current project, I have to generate an extraction to Excel from an Application.

The extraction contains Parent Object exported in a 1st Sheet, and Child Objects into some other  Sheets, depending on their type.

In order to provide to end users an easy way to navigate from Parent to Child Object, I start to implement a HYPERLINK Function.
It gives something like :
=HYPERLINK("[MyWorkbook.xls]'Sheet2'!C"&MATCH(C4,'Sheet2'!C:C,0),"See Child Details")

In order to make it available even if the file is renamed, I had to replace "MyWorkbook.xls" by a dynamic filename, deduced from current opened Workbook.

A found a first solution based on =Cell("filename"), but it appears complex to implement since only the FileName is required and not the FullFileName returned by =Cell("filename").

Since I know that I will have to use a Template Workbook for future features, I have decided to create a User-Defined Function in VBA. The function looks like :

Public Function getWorkbookName() As String
    getWorkbookName = ActiveWorkbook.Name
End Function

And the formula to define for the Cell is like :

=HYPERLINK("["&getWorkbookName()&"]'Sheet2'!C"&MATCH(C4,'Sheet2'!C:C,0),"See Child Details")

On Excel side, It works.

To generate it from POI HSSF, I have added the getWorkbookName function ...

class getWorkookName implements FreeRefFunction {
	@Override
	public ValueEval evaluate(ValueEval[] args, OperationEvaluationContext ec) {
		return null;
	}
}

and added it in Workbook :

String[] functionNames = { "getWorkbookName"};
FreeRefFunction[] functionImpls = { new getWorkookName() };
UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);
workbook.addToolPack(udfToolpack);

The generated file seems to be generated, but the formula is not assumed as a formula ...
I have to validated (Edit + Enter) each concerned cells.

To understand what was wrong without all data to manage, I have just try to use a simple VBA function 

Public Function getTestValue() As Variant
    getTestValue = 100
End Function

In Sheet1 of a new Excel file, I have used this new function (see attachment).

And when I tried to use this template file with a VBA function used in a cell of Sheet 1, I experienced an Exception on the creation of new Sheet in the Workbook.

java.lang.RuntimeException: Could not find 'internal references' EXTERNALBOOK
	at org.apache.poi.hssf.model.LinkTable.checkExternSheet(LinkTable.java:516)
	at org.apache.poi.hssf.model.LinkTable.checkExternSheet(LinkTable.java:504)
	at org.apache.poi.hssf.model.InternalWorkbook.checkSheets(InternalWorkbook.java:741)
	at org.apache.poi.hssf.model.InternalWorkbook.setSheetName(InternalWorkbook.java:579)
	at org.apache.poi.hssf.usermodel.HSSFWorkbook.createSheet(HSSFWorkbook.java:732)
	at ...

It appears that the exception occurs only if the function is used.

Do we have a limitation that blocks the usage of VBA Function in Template Workbook ?
Or is it required to defined such VBA Function differently ? 

The file is created from Excel 2010 (French settings) and savec in Excel 97 format.


Thanks by advance
Vincent