Bug 31661

Summary: HSSFCell.getCellFormula() fails with references to external workbooks
Product: POI Reporter: Gerry Matte <matteg>
Component: HSSFAssignee: POI Developers List <dev>
Status: RESOLVED FIXED    
Severity: normal CC: enterit
Priority: P3    
Version: 2.5-FINAL   
Target Milestone: ---   
Hardware: All   
OS: All   
URL: http://www.mozilla.org
Attachments: spreadsheet referring to another sheet
the workbook referred to
screenshot of IT2005shortError.xls workbook with erroneous formula
screenshot of GMBudget.xls workbook referred to
Test case

Description Gerry Matte 2004-10-12 04:22:29 UTC 
<html>
<head></head>
<body>
<P>
<p>Description:  This program reads an Excel 2003 spreadsheet that references
 another spreadsheet from cell K12 of sheet Account Number Master.
 This example illustrates two errors in the Jakarta POI 2.5.1 package:<ol>
 <li>The <code>HSSFCell.getCellFormula()</code> returns the name of this cell's
 sheet not the external workbook and sheet name of the referenced cell in the
 external workbook.  No workaround is available.</li>
 <li>The <code>HHSFRow.getPhysicalNumberOfCells()</code> is too small by one
 when a cell referencing another workbook is in the row.  The workaround is
 to not rely on the method and instead iterate over all 256 cells (0-255)
 ignoring nulls.</li></ol>
 In an Excel 2003 workbook <code>IT2005short.xls</code> at sheet
 <code>Account Number Master</code> at
 cell K12 is the formula <br>
 <code>
 =<b>+'C:\d\myPrograms\jbproject\POI1\[GM Budget.xls]8085.4450'</b>!$B$2
 </code><br>
 but the formula returned by HSSFCell.getFormulaString() is <br>
 <code><b>+Account Number Master</b>!$B$2</code>.
 </p>
<P>
</body>
</html>
Comment 1 Gerry Matte 2004-10-12 04:39:51 UTC 
Created attachment 13037 [details]
spreadsheet referring to another sheet
Comment 2 Gerry Matte 2004-10-12 04:40:30 UTC 
Created attachment 13038 [details]
the workbook referred to
Comment 3 Gerry Matte 2004-10-12 04:44:39 UTC 
Created attachment 13039 [details]
screenshot of IT2005shortError.xls workbook with erroneous formula
Comment 4 Gerry Matte 2004-10-12 04:45:33 UTC 
Created attachment 13040 [details]
screenshot of GMBudget.xls workbook referred to
Comment 5 Gerry Matte 2004-10-12 06:05:36 UTC 
Created attachment 13041 [details]
Test case
Comment 6 Gerry Matte 2004-10-12 06:06:17 UTC 
Note: The original bugzilla report # 31661 reported that a second error 
existed -
 * HSSFRow.getPhysicalNumberOfCells() was reported to be one too small.
 * Such is <b>not</b> the case as proven by this test program.
Comment 7 Yegor Kozlov 2008-12-29 08:49:59 UTC 
The reported problem is not reproducible with the latest trunk.
Please try the latest 3.5-beta4 or download daily builds from http://encore.torchbox.com/poi-svn-build/

Yegor