Bug 57651 - Not able to use a Excel 97 file as template when it containes used User-Define Functions
Summary: Not able to use a Excel 97 file as template when it containes used User-Defin...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.11-FINAL
Hardware: PC All
: P2 blocker (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2015-03-01 20:50 UTC by v.thoule
Modified: 2016-04-05 16:27 UTC (History)
0 users

Excel Template with a predefined VBA Function (30.00 KB, application/vnd.ms-excel)
2015-03-01 20:50 UTC, v.thoule

Note You need to log in before you can comment on or make changes to this bug.
Description v.thoule 2015-03-01 20:50:02 UTC
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 {
	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);

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
Comment 1 Nick Burch 2015-03-02 04:56:19 UTC
What happens if you change your FreeRefFunction to return something sensible looking? The null might be confusing things

Are you opening the HSSFWorkbook with preserve nodes set to true?

Did you try with POI 3.12 beta 1?
Comment 2 Dominik Stadler 2016-04-05 12:22:00 UTC
No answer on the questions for a long time, thus i am closing this until we get the required information. Feel free to reopen if you can provide more information.
Comment 3 Javen O'Neal 2016-04-05 16:11:38 UTC
Test your problem with POI 3.14, which contains a fix for big 58452, which might also fix your problem.
Comment 4 v.thoule 2016-04-05 16:27:07 UTC
Thanks for your return.
The initial need has been aborted due to the initial bug.
I will have an improvement to develop in few weeks ... I will try to re-test it.