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 2012/11/09 15:37:44 UTC
[Bug 54125] New: Setting an external API formula in a cell
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
Priority: P2
Bug ID: 54125
Assignee: dev@poi.apache.org
Summary: Setting an external API formula in a cell
Severity: normal
Classification: Unclassified
Reporter: aurimas.sabalys@outlook.com
Hardware: All
Status: NEW
Version: 3.8
Component: XSSF
Product: POI
I'm building an excel workbook that contains some formulas provided by
Bloomberg Excel Add-in. Specifically =BDP(security, field)
In order to set the cell formula I have to define a Name:
workbook = new XSSFWorkbook();
Name name = workbook.createName();
name.setNameName("BDP");
name.setFunction(true);
Further in the code some cell values are set as follows:
excelCell.setCellFormula(cellValue); // here cellValue is a formula string
built elsewhere in the code, e.g. =BDP("GOOG Equity","CHG_PCT_YTD")/100
The problem appears appears when opening the generated Excel file. First a
message appears "Excel found unreadable content in 'filename.xlsx'. Do you want
to recover the contents of this workbook. If you trust the source of this
workbook, click Yes"
Clicking YES opens the excel successfully, and a message is displayed "Removed
Records: Named range from /xl/workbook.xml part (Workbook)". The cells with
bloomberg formulas work OK.
It seems that the only way to bypass the message during the file opening is to
call name.setRefersToFormula(String) - but I cannot do this, since the name is
not really a reference, it's simply an external formula.
Not setting the name on a workbook doesn't work either, since
excelCell.setCellFormula throws an exception
"org.apache.poi.ss.formula.FormulaParseException: Name 'BDP' is completely
unknown in the current workbook"
I think Cell.setCellFormula should contain some parameter that would allow
setting an external formula in it without throwing an exception.
--
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 54125] Setting an external API formula in a cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
aurimas.sabalys@outlook.com changed:
What |Removed |Added
----------------------------------------------------------------------------
CC| |aurimas.sabalys@outlook.com
OS| |All
--
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 54125] Setting an external API formula in a cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
--- Comment #4 from kuba.v <ja...@gmail.com> ---
The workaround is handy however I don't think the issue was invalid. In Excel
it is possible to set an invalid function name to a cell so why it should not
be possible in POI? For example if one wants to make an exact copy of a cell
which contained an invalid function token. Is there another function to do that
or an attribute to suppress this validation at the time of setting the formula
value?
--
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 54125] Setting an external API formula in a cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution|--- |INVALID
--- Comment #1 from Yegor Kozlov <ye...@dinom.ru> ---
You are doing it wrong, you need to register external functions in a toolpack
instead of using a named range:
Workbook wb = new XSSFWorkbook();
String[] functionNames = {"BDP"};
FreeRefFunction[] functionImpls = {new FreeRefFunction() {
public ValueEval evaluate(ValueEval[] args,
OperationEvaluationContext ec) {
// don't care about the returned result. we are not going to
evaluate BDP
return ErrorEval.NA;
}
}};
UDFFinder udfToolpack = new DefaultUDFFinder(functionNames,
functionImpls);
// register the user-defined function in the workbook
wb.addToolPack(udfToolpack);
Sheet sheet = wb.createSheet();
Cell cell = sheet.createRow(0).createCell(0);
cell.setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
this way it should work.
Yegor
--
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 54125] Setting an external API formula in a cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
--- Comment #3 from Yegor Kozlov <ye...@dinom.ru> ---
I added an example in svn:
https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/formula/SettingExternalFunction.java
(In reply to comment #2)
> Thanks a lot! It worked as you said
>
> (In reply to comment #1)
> > You are doing it wrong, you need to register external functions in a
> > toolpack instead of using a named range:
> >
> >
> > Workbook wb = new XSSFWorkbook();
> >
> > String[] functionNames = {"BDP"};
> > FreeRefFunction[] functionImpls = {new FreeRefFunction() {
> > public ValueEval evaluate(ValueEval[] args,
> > OperationEvaluationContext ec) {
> > // don't care about the returned result. we are not going to
> > evaluate BDP
> > return ErrorEval.NA;
> > }
> > }};
> >
> > UDFFinder udfToolpack = new DefaultUDFFinder(functionNames,
> > functionImpls);
> >
> > // register the user-defined function in the workbook
> > wb.addToolPack(udfToolpack);
> >
> > Sheet sheet = wb.createSheet();
> > Cell cell = sheet.createRow(0).createCell(0);
> > cell.setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
> >
> > this way it should work.
> >
> > Yegor
--
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 54125] Setting an external API formula in a cell
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=54125
--- Comment #2 from aurimas.sabalys@outlook.com ---
Thanks a lot! It worked as you said
(In reply to comment #1)
> You are doing it wrong, you need to register external functions in a
> toolpack instead of using a named range:
>
>
> Workbook wb = new XSSFWorkbook();
>
> String[] functionNames = {"BDP"};
> FreeRefFunction[] functionImpls = {new FreeRefFunction() {
> public ValueEval evaluate(ValueEval[] args,
> OperationEvaluationContext ec) {
> // don't care about the returned result. we are not going to
> evaluate BDP
> return ErrorEval.NA;
> }
> }};
>
> UDFFinder udfToolpack = new DefaultUDFFinder(functionNames,
> functionImpls);
>
> // register the user-defined function in the workbook
> wb.addToolPack(udfToolpack);
>
> Sheet sheet = wb.createSheet();
> Cell cell = sheet.createRow(0).createCell(0);
> cell.setCellFormula("BDP(\"GOOG Equity\",\"CHG_PCT_YTD\")/100");
>
> this way it should work.
>
> Yegor
--
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