Mailing-List: contact dev-help@poi.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "POI Developers List" <dev@poi.apache.org>
From: bugzilla@apache.org
To: dev@poi.apache.org
Subject: [Bug 54125] New: Setting an external API formula in a cell
Date: Fri, 09 Nov 2012 14:37:44 +0000
Message-ID: <bug-54125-47293@https.issues.apache.org/bugzilla/>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit
Auto-Submitted: auto-generated
MIME-Version: 1.0

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