(Pressed send before finishing...) So it seems that there is an issue with putting this formula into a cell in memory.
On 8 April 2012 09:27, Mark Coleman <[email protected]> wrote: > 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/> > > -- 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/>
