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 Thu, 05 Apr 2012 12:25:50 GMT
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 <yegor.kozlov@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.coleman@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/>

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