You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Mark Coleman <m....@uglyduckling.nl> on 2012/04/04 13:21:12 UTC
Full paths removed from formulas in SS spreadsheet
Hello,
I have a cell which references another cell in another spreadsheet. The
formula looks like this when I view it in Excel...
*='M:\Improvements\Spreadsheet Scanner\Spreadsheet
Scanner\test_data\[C.xls]Sheet1'!$A$1*
...however when I use getCellFormula() the following is returned...
*[C.xls]Sheet1!$A$1*
Somehow I'm losing the path. Is this the intended behaviour? If so, how can
I get the path? If not, what am I doing wrong?
Kind regards,
--
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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Coleman <m....@uglyduckling.nl>.
Thanks Mark, I appreciate your help with this. I'm implementing a search to
plug the gap for now.
Cheers,
Mark
On 5 April 2012 08:26, Mark Beardsley <ma...@tiscali.co.uk> wrote:
> Sorry about that Mark. Will take the time to have a play today and post if
> I
> find anything.
>
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Full-paths-removed-from-formulas-in-SS-spreadsheet-tp5617482p5619613.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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Sorry about that Mark. Will take the time to have a play today and post if I
find anything.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Full-paths-removed-from-formulas-in-SS-spreadsheet-tp5617482p5619613.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
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Coleman <m....@uglyduckling.nl>.
Morning Mark,
Thanks for the suggestion, I tried it and unfortunately it doesn't work.
Excel flags it as an invalid reference. Not sure where to go with this now.
I could provide a list of possible directories and then search for the file
name, but this seems like a lot of work.
Does anybody know how Excel does this internally?
Cheers,
Mark
On 4 April 2012 18:03, Mark Beardsley <ma...@tiscali.co.uk> wrote:
> Guessing here but have you tried placing the full path and name of the
> workbook between the braces? Something like this
>
> "='[M:\Improvements\Spreadsheet Scanner\Spreadsheet
> Scanner\test_data\C.xls]Sheet1'!$A$1*
>
> That might work - might by the way as I have never tried it myself.
>
>
> --
> View this message in context:
> http://apache-poi.1045710.n5.nabble.com/Full-paths-removed-from-formulas-in-SS-spreadsheet-tp5617482p5618225.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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
Guessing here but have you tried placing the full path and name of the
workbook between the braces? Something like this
"='[M:\Improvements\Spreadsheet Scanner\Spreadsheet
Scanner\test_data\C.xls]Sheet1'!$A$1*
That might work - might by the way as I have never tried it myself.
--
View this message in context: http://apache-poi.1045710.n5.nabble.com/Full-paths-removed-from-formulas-in-SS-spreadsheet-tp5617482p5618225.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
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Coleman <m....@uglyduckling.nl>.
(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....@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 <ma...@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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Coleman <m....@uglyduckling.nl>.
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 <ma...@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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Beardsley <ma...@tiscali.co.uk>.
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
Re: Full paths removed from formulas in SS spreadsheet
Posted by Yegor Kozlov <ye...@dinom.ru>.
What happens if you create the test file in Excel and read it with
POI: does POI read it correctly ?
Yegor
On Thu, Apr 5, 2012 at 4:25 PM, Mark Coleman <m....@uglyduckling.nl> wrote:
> Hello Yegor,
>
> Thanks for your suggestion. I switched to POI 3.8 (3.8-20120326) and
> unfortunately the problem persists. In order to aid the investigation, I
> wrote a simple test which reproduces the issue:
>
> ------------------------------------------------
> @Test
> *public* *void* manualPathFormulaProblemForMailingList()
> {
>
> Workbook workBook = *new* HSSFWorkbook();
> Sheet sheet = workBook.createSheet("Sheet1");
> Row row = sheet.createRow(0);
> Cell cell = row.createCell(0, Cell.*CELL_TYPE_FORMULA*);
> cell.setCellFormula("'M:\\Improvements\\Spreadsheet Scanner\\Spreadsheet
> Scanner\\test_data\\test\\[C.xls]Sheet1'!$A$1");
> *assertThat*(cell.getCellFormula(), *is*("'M:\\Improvements\\Spreadsheet
> Scanner\\Spreadsheet Scanner\\test_data\\test\\[C.xls]Sheet1'!$A$1"));
>
> }
> ------------------------------------------------
>
> When I run the test I get the following assertion:
>
> ------------------------------------------------
> java.lang.AssertionError:
> Expected: is "'M:\Improvements\Spreadsheet Scanner\Spreadsheet
> Scanner\test_data\test\[C.xls]Sheet1'!$A$1"
> got: "#REF!$A$1"
> at org.junit.Assert.assertThat(Assert.java:778)
> at org.junit.Assert.assertThat(Assert.java:736)
> at
> com.uglyduckling.spreadsheetscanner.SpreadsheetLinkParserTest.manualPathFormulaProblemForMailingList(SpreadsheetLinkParserTest.java:72)
> ------------------------------------------------
>
> Clearly POI doesn't like the path but it is definitely valid as
> demonstrated below in cmd.exe:
>
> ------------------------------------------------
>
>>dir "M:\Improvements\Spreadsheet Scanner\Spreadsheet
> Scanner\test_data\test"
> De volumenaam van station M is Home Drives
> Het volumenummer is 228C-BD76
> Map van M:\Improvements\Spreadsheet Scanner\Spreadsheet
> Scanner\test_data\test
> 05-04-2012 12:27 <DIR> .
> 05-04-2012 12:27 <DIR> ..
> 05-04-2012 11:33 23.040 C.xls
> 1 bestand(en) 23.040 bytes
> 2 map(pen) 38.813.650.944 bytes beschikbaar
>
> ------------------------------------------------
>
> At this point I'm not sure how to proceed. Is anyone able to reproduce this
> issue?
>
> Kind regards,
>
> Mark
>
> On 5 April 2012 08:45, Yegor Kozlov <ye...@dinom.ru> wrote:
>
>> Which version of POI? Are you using the latest POI-3.8 ?
>>
>> A similar issue was fixed in r1242701 committed on Feb 10:
>> http://svn.apache.org/viewvc?view=revision&sortby=date&revision=1242701
>>
>> The origin is https://issues.apache.org/bugzilla/show_bug.cgi?id=49896
>>
>> At least, we have a unit test that proves that full path inside
>> VLOOKUP is preserved and POI returns you formulas like this:
>> "VLOOKUP(A2,'[C:Documents and Settings/Yegor/My
>> Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)"
>>
>> Your case looks similar, so I suspect you are using an older version of
>> POI.
>>
>> Yegor
>>
>> On Wed, Apr 4, 2012 at 3:21 PM, Mark Coleman <m....@uglyduckling.nl>
>> wrote:
>> > Hello,
>> >
>> > I have a cell which references another cell in another spreadsheet. The
>> > formula looks like this when I view it in Excel...
>> >
>> > *='M:\Improvements\Spreadsheet Scanner\Spreadsheet
>> > Scanner\test_data\[C.xls]Sheet1'!$A$1*
>> >
>> > ...however when I use getCellFormula() the following is returned...
>> >
>> > *[C.xls]Sheet1!$A$1*
>> >
>> > Somehow I'm losing the path. Is this the intended behaviour? If so, how
>> can
>> > I get the path? If not, what am I doing wrong?
>> >
>> > Kind regards,
>> >
>> > --
>> >
>> >
>> > 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://uglyduckling.nl/> <
>> http://www.uglyduckling.nl/>
>>
>> ---------------------------------------------------------------------
>> 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/>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Full paths removed from formulas in SS spreadsheet
Posted by Mark Coleman <m....@uglyduckling.nl>.
Hello Yegor,
Thanks for your suggestion. I switched to POI 3.8 (3.8-20120326) and
unfortunately the problem persists. In order to aid the investigation, I
wrote a simple test which reproduces the issue:
------------------------------------------------
@Test
*public* *void* manualPathFormulaProblemForMailingList()
{
Workbook workBook = *new* HSSFWorkbook();
Sheet sheet = workBook.createSheet("Sheet1");
Row row = sheet.createRow(0);
Cell cell = row.createCell(0, Cell.*CELL_TYPE_FORMULA*);
cell.setCellFormula("'M:\\Improvements\\Spreadsheet Scanner\\Spreadsheet
Scanner\\test_data\\test\\[C.xls]Sheet1'!$A$1");
*assertThat*(cell.getCellFormula(), *is*("'M:\\Improvements\\Spreadsheet
Scanner\\Spreadsheet Scanner\\test_data\\test\\[C.xls]Sheet1'!$A$1"));
}
------------------------------------------------
When I run the test I get the following assertion:
------------------------------------------------
java.lang.AssertionError:
Expected: is "'M:\Improvements\Spreadsheet Scanner\Spreadsheet
Scanner\test_data\test\[C.xls]Sheet1'!$A$1"
got: "#REF!$A$1"
at org.junit.Assert.assertThat(Assert.java:778)
at org.junit.Assert.assertThat(Assert.java:736)
at
com.uglyduckling.spreadsheetscanner.SpreadsheetLinkParserTest.manualPathFormulaProblemForMailingList(SpreadsheetLinkParserTest.java:72)
------------------------------------------------
Clearly POI doesn't like the path but it is definitely valid as
demonstrated below in cmd.exe:
------------------------------------------------
>dir "M:\Improvements\Spreadsheet Scanner\Spreadsheet
Scanner\test_data\test"
De volumenaam van station M is Home Drives
Het volumenummer is 228C-BD76
Map van M:\Improvements\Spreadsheet Scanner\Spreadsheet
Scanner\test_data\test
05-04-2012 12:27 <DIR> .
05-04-2012 12:27 <DIR> ..
05-04-2012 11:33 23.040 C.xls
1 bestand(en) 23.040 bytes
2 map(pen) 38.813.650.944 bytes beschikbaar
------------------------------------------------
At this point I'm not sure how to proceed. Is anyone able to reproduce this
issue?
Kind regards,
Mark
On 5 April 2012 08:45, Yegor Kozlov <ye...@dinom.ru> wrote:
> Which version of POI? Are you using the latest POI-3.8 ?
>
> A similar issue was fixed in r1242701 committed on Feb 10:
> http://svn.apache.org/viewvc?view=revision&sortby=date&revision=1242701
>
> The origin is https://issues.apache.org/bugzilla/show_bug.cgi?id=49896
>
> At least, we have a unit test that proves that full path inside
> VLOOKUP is preserved and POI returns you formulas like this:
> "VLOOKUP(A2,'[C:Documents and Settings/Yegor/My
> Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)"
>
> Your case looks similar, so I suspect you are using an older version of
> POI.
>
> Yegor
>
> On Wed, Apr 4, 2012 at 3:21 PM, Mark Coleman <m....@uglyduckling.nl>
> wrote:
> > Hello,
> >
> > I have a cell which references another cell in another spreadsheet. The
> > formula looks like this when I view it in Excel...
> >
> > *='M:\Improvements\Spreadsheet Scanner\Spreadsheet
> > Scanner\test_data\[C.xls]Sheet1'!$A$1*
> >
> > ...however when I use getCellFormula() the following is returned...
> >
> > *[C.xls]Sheet1!$A$1*
> >
> > Somehow I'm losing the path. Is this the intended behaviour? If so, how
> can
> > I get the path? If not, what am I doing wrong?
> >
> > Kind regards,
> >
> > --
> >
> >
> > 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://uglyduckling.nl/> <
> http://www.uglyduckling.nl/>
>
> ---------------------------------------------------------------------
> 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/>
Re: Full paths removed from formulas in SS spreadsheet
Posted by Yegor Kozlov <ye...@dinom.ru>.
Which version of POI? Are you using the latest POI-3.8 ?
A similar issue was fixed in r1242701 committed on Feb 10:
http://svn.apache.org/viewvc?view=revision&sortby=date&revision=1242701
The origin is https://issues.apache.org/bugzilla/show_bug.cgi?id=49896
At least, we have a unit test that proves that full path inside
VLOOKUP is preserved and POI returns you formulas like this:
"VLOOKUP(A2,'[C:Documents and Settings/Yegor/My
Documents/csco.xls]Sheet1'!$A$2:$B$3,2,FALSE)"
Your case looks similar, so I suspect you are using an older version of POI.
Yegor
On Wed, Apr 4, 2012 at 3:21 PM, Mark Coleman <m....@uglyduckling.nl> wrote:
> Hello,
>
> I have a cell which references another cell in another spreadsheet. The
> formula looks like this when I view it in Excel...
>
> *='M:\Improvements\Spreadsheet Scanner\Spreadsheet
> Scanner\test_data\[C.xls]Sheet1'!$A$1*
>
> ...however when I use getCellFormula() the following is returned...
>
> *[C.xls]Sheet1!$A$1*
>
> Somehow I'm losing the path. Is this the intended behaviour? If so, how can
> I get the path? If not, what am I doing wrong?
>
> Kind regards,
>
> --
>
>
> 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/>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org