You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2015/03/01 21:50:02 UTC
[Bug 57651] New: Not able to use a Excel 97 file as template when it
containes used User-Define Functions
https://bz.apache.org/bugzilla/show_bug.cgi?id=57651
Bug ID: 57651
Summary: Not able to use a Excel 97 file as template when it
containes used User-Define Functions
Product: POI
Version: 3.11-FINAL
Hardware: PC
Status: NEW
Severity: blocker
Priority: P2
Component: HSSF
Assignee: dev@poi.apache.org
Reporter: v.thoule@alkaes.fr
Created attachment 32538
--> https://bz.apache.org/bugzilla/attachment.cgi?id=32538&action=edit
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
--
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
[Bug 57651] Not able to use a Excel 97 file as template when it
containes used User-Define Functions
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57651
--- Comment #3 from Javen O'Neal <on...@apache.org> ---
Test your problem with POI 3.14, which contains a fix for big 58452, which
might also fix your problem.
--
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
[Bug 57651] Not able to use a Excel 97 file as template when it
containes used User-Define Functions
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57651
Nick Burch <ap...@gagravarr.org> changed:
What |Removed |Added
----------------------------------------------------------------------------
OS| |All
Status|NEW |NEEDINFO
--- Comment #1 from Nick Burch <ap...@gagravarr.org> ---
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?
--
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
[Bug 57651] Not able to use a Excel 97 file as template when it
containes used User-Define Functions
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57651
Dominik Stadler <do...@gmx.at> changed:
What |Removed |Added
----------------------------------------------------------------------------
Resolution|--- |INVALID
Status|NEEDINFO |RESOLVED
--- Comment #2 from Dominik Stadler <do...@gmx.at> ---
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.
--
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
[Bug 57651] Not able to use a Excel 97 file as template when it
containes used User-Define Functions
Posted by bu...@apache.org.
https://bz.apache.org/bugzilla/show_bug.cgi?id=57651
--- Comment #4 from v.thoule@alkaes.fr ---
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.
V.
--
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