poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Coleman <m.cole...@uglyduckling.nl>
Subject Re: Full paths removed from formulas in SS spreadsheet
Date Sun, 08 Apr 2012 07:27:52 GMT
(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 <m.coleman@uglyduckling.nl> 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 <markbrdsly@tiscali.co.uk> 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: user-unsubscribe@poi.apache.org
>> For additional commands, e-mail: user-help@poi.apache.org
>>
>>
>
>
> --
>
>
>  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: m.coleman@uglyduckling.nl
> 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: m.coleman@uglyduckling.nl
M: +31 (0) 646347972
W: http://uGlyDuckling.nl <http://www.uglyduckling.nl/>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message