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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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: [email protected] >> > M: +31 (0) 646347972 >> > W: http://uGlyDuckling.nl <http://uglyduckling.nl/> < >> http://www.uglyduckling.nl/> >> >> --------------------------------------------------------------------- >> 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/> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
