Thanks for the feedback guys. I tried your suggestion Yegor and it revealed
that the problem indeed only occurs when I create the workbook in memory.
When I read the formula value from an existing sheet the output is correct.
This is the test I used:
@Test
public void testGetPathFromCreatedWorkbook()
{
Workbook workBook = new HSSFWorkbook();
Sheet sheet = workBook.createSheet("Sheet1");
FormulaEvaluator evaluator =
workBook.getCreationHelper().createFormulaEvaluator();
Row row = sheet.createRow(0);
Cell cell = row.createCell(0, Cell.CELL_TYPE_FORMULA);
cell.setCellFormula(TARGET_FORMULA);
assertEquals(TARGET_FORMULA, cell.getCellFormula());
}
The output is: *org.junit.ComparisonFailure: expected:<['[\Users\Mark
Robert Coleman\Eclipse
Workspace\ExcelLinkFormulas\test_data\source_folder\Source.xls]Sheet1']!$A$1>
but was:<[#REF]!$A$1>*
The TARGET_FORMULA constant is the same value that I use in the test below,
which works correctly.
@Test
public void testGetPathFromExistingWorkbook()
{
CellReference cellReference = new CellReference("A1");
String formula =
target_workbook.getSheet("Sheet1").getRow(cellReference.getRow()).getCell(cellReference.getCol()).getCellFormula();
assertEquals(TARGET_FORMULA, formula);
}
So it seems th
Mark
On 6 April 2012 08:31, Mark Beardsley <[email protected]> wrote:
> Nothing to add to the discussion apart from some information. Yesterday, I
> visited Microsoft's site and found the following regarding references to
> external worksheets;
>
>
> http://office.microsoft.com/en-us/excel-help/create-an-external-reference-link-to-a-cell-range-in-another-workbook-HP010102338.aspx
>
> "What an external reference to another workbook looks like
>
> Formulas with external references to other workbooks are displayed in two
> ways, depending on whether the source workbook — the one workbook that
> supplies data to a formula — is open or closed.
>
> When the source is open, the external reference includes the workbook name
> in square brackets ([ ]), followed by the worksheet name, an exclamation
> point (!), and the cells that the formula depends on. For example, the
> following formula adds the cells C10:C25 from the workbook named
> Budget.xls.
> External reference
> =SUM([Budget.xlsx]Annual!C10:C25)
>
> When the source is not open, the external reference includes the entire
> path.
> External reference
> =SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)
>
> Note If the name of the other worksheet or workbook contains
> nonalphabetical characters, you must enclose the name (or the path) within
> single quotation marks.
>
> Formulas that link to a defined name in another workbook use the workbook
> name followed by an exclamation point (!) and the name. For example, the
> following formula adds the cells in the range named Sales from the workbook
> named Budget.xlsx."
>
> From this, it looks as though Mark's original attempt to form the link
> might
> have been the correct one and m correction was in error.
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Full-paths-removed-from-formulas-in-SS-spreadsheet-tp5617482p5622152.html
> Sent from the POI - User mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
--
Mark Coleman
uGly Duckling B.V.
Burgemeester le Fevre de Montignylaan 30
3055LG Rotterdam, the Netherlands
KvK nummer: 52272125
BTW nummer: NL850371570B.01
Rabobank: 14.68.33.473
E: [email protected]
M: +31 (0) 646347972
W: http://uGlyDuckling.nl <http://www.uglyduckling.nl/>
