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 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
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
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
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?