Bug 31661 - HSSFCell.getCellFormula() fails with references to external workbooks
Summary: HSSFCell.getCellFormula() fails with references to external workbooks
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: All All
: P3 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL: http://www.mozilla.org
Keywords:
Depends on:
Blocks:
 
Reported: 2004-10-12 04:22 UTC by Gerry Matte
Modified: 2008-12-29 08:49 UTC (History)
1 user (show)



Attachments
spreadsheet referring to another sheet (15.00 KB, application/octet-stream)
2004-10-12 04:39 UTC, Gerry Matte
Details
the workbook referred to (16.50 KB, application/octet-stream)
2004-10-12 04:40 UTC, Gerry Matte
Details
screenshot of IT2005shortError.xls workbook with erroneous formula (25.05 KB, image/gif)
2004-10-12 04:44 UTC, Gerry Matte
Details
screenshot of GMBudget.xls workbook referred to (23.26 KB, image/gif)
2004-10-12 04:45 UTC, Gerry Matte
Details
Test case (2.59 KB, text/plain)
2004-10-12 06:05 UTC, Gerry Matte
Details

Note You need to log in before you can comment on or make changes to this bug.
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