You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Cross, David A." <Cr...@llnl.gov> on 2012/01/30 19:50:44 UTC
XSSF & Hyperlink to Directory
Hello,
I am working on a project in which I am adding Hyperlinks to Cells in a xlsx file. I attempted to use the ss.usermodel hyperlink functionality, however I was unable to achieve the desired results. I was able to create a hyperlink to a file in the current directory, or even in any directory (as long as path does not contain special characters like spaces). However I would like to create a hyperlink to a specific directory or network location (ie "\\My network\Group A\User C\Folder F\" etc...) just as you can manually in Excel. I was unable to get the hyperlink setAddress function to work under these circumstances. I have been able to work around this by setting the cells formula using the Excel hyperlink formula. This work around provides the functionality, however its somewhat messy to have the long hyperlink formula displayed in the function bar whenever the cell is selected (this does not happen when hyperlinks are added correctly). Is there anyway to achieve this desired behavior for hyperlinks in Excel (xlsx) fils?
David
RE: XSSF & Hyperlink to Directory
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 30 Jan 2012, Cross, David A. wrote:
> It hadn't occurred to me to unzip and read through the xml. By doing so
> I was able to find what was expected, and I can write a method that will
> translate the file path into the appropriate URL like form such as
> ("file:////network%20place/) and it works . It is unfortunate that it
> will not just accept a java file path but this will work for me and I
> like it better than the excel function method
Java should be able to do that style of translation for you, that looks
like a regular file URL
Are you able to come up with some new wording for the javadocs to make it
clear to future users what they need to do?
Thanks
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSF & Hyperlink to Directory
Posted by "Cross, David A." <Cr...@llnl.gov>.
Thanks Yegor that is exactly right, I didn't realize setAddress() was expecting strings from URI's. After switching to URI the code performs outstandingly, just as I've come to expect from Apache projects.
Thanks again to Yegor & Nick
David
-----Original Message-----
From: Yegor Kozlov [mailto:yegor.kozlov@dinom.ru]
Sent: Tuesday, January 31, 2012 4:57 AM
To: POI Users List
Subject: Re: XSSF & Hyperlink to Directory
Current implementation is too forgiving. The setAddress function
writes the raw string in the OPC relationship table and passing an
invalid value will corrupt the output file.
The setAddress function should accept strings that are valid URIs. If
you are passing a file then instead of passing File.getPath() pass
File.toURI().toString()
I'm going to improve it and add validation. If you pass a wrong string
then setAddress should throw IllegalArgumentException
Yegor
On Tue, Jan 31, 2012 at 2:28 AM, Cross, David A. <Cr...@llnl.gov> wrote:
> Thanks Nick,
>
> It hadn't occurred to me to unzip and read through the xml. By doing so I was able to find what was expected, and I can write a method that will translate the file path into the appropriate URL like form such as ("file:////network%20place/) and it works . It is unfortunate that it will not just accept a java file path but this will work for me and I like it better than the excel function method
>
> Thanks again
> David
>
> -----Original Message-----
> From: Nick Burch [mailto:nick.burch@alfresco.com]
> Sent: Monday, January 30, 2012 11:34 AM
> To: POI Users List
> Subject: RE: XSSF & Hyperlink to Directory
>
> On Mon, 30 Jan 2012, Cross, David A. wrote:
>> In Excel you can add a hyperlink to any cell and point it to any of the
>> example directories above
>
> Can you try unzipping the .xlsx files from POI and Excel, and seeing how
> they differ in their hyperlinks? If memory serves, they get stored as
> relations, but a grep (or similar) should let you find which file(s)
> they're in
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: XSSF & Hyperlink to Directory
Posted by Yegor Kozlov <ye...@dinom.ru>.
Current implementation is too forgiving. The setAddress function
writes the raw string in the OPC relationship table and passing an
invalid value will corrupt the output file.
The setAddress function should accept strings that are valid URIs. If
you are passing a file then instead of passing File.getPath() pass
File.toURI().toString()
I'm going to improve it and add validation. If you pass a wrong string
then setAddress should throw IllegalArgumentException
Yegor
On Tue, Jan 31, 2012 at 2:28 AM, Cross, David A. <Cr...@llnl.gov> wrote:
> Thanks Nick,
>
> It hadn't occurred to me to unzip and read through the xml. By doing so I was able to find what was expected, and I can write a method that will translate the file path into the appropriate URL like form such as ("file:////network%20place/) and it works . It is unfortunate that it will not just accept a java file path but this will work for me and I like it better than the excel function method
>
> Thanks again
> David
>
> -----Original Message-----
> From: Nick Burch [mailto:nick.burch@alfresco.com]
> Sent: Monday, January 30, 2012 11:34 AM
> To: POI Users List
> Subject: RE: XSSF & Hyperlink to Directory
>
> On Mon, 30 Jan 2012, Cross, David A. wrote:
>> In Excel you can add a hyperlink to any cell and point it to any of the
>> example directories above
>
> Can you try unzipping the .xlsx files from POI and Excel, and seeing how
> they differ in their hyperlinks? If memory serves, they get stored as
> relations, but a grep (or similar) should let you find which file(s)
> they're in
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSF & Hyperlink to Directory
Posted by "Cross, David A." <Cr...@llnl.gov>.
Thanks Nick,
It hadn't occurred to me to unzip and read through the xml. By doing so I was able to find what was expected, and I can write a method that will translate the file path into the appropriate URL like form such as ("file:////network%20place/) and it works . It is unfortunate that it will not just accept a java file path but this will work for me and I like it better than the excel function method
Thanks again
David
-----Original Message-----
From: Nick Burch [mailto:nick.burch@alfresco.com]
Sent: Monday, January 30, 2012 11:34 AM
To: POI Users List
Subject: RE: XSSF & Hyperlink to Directory
On Mon, 30 Jan 2012, Cross, David A. wrote:
> In Excel you can add a hyperlink to any cell and point it to any of the
> example directories above
Can you try unzipping the .xlsx files from POI and Excel, and seeing how
they differ in their hyperlinks? If memory serves, they get stored as
relations, but a grep (or similar) should let you find which file(s)
they're in
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSF & Hyperlink to Directory
Posted by "Chandrasekhar, Teja" <Te...@xerox.com>.
Hi Team,
Please remove my name from the mailing list. I am getting continous
emails.
Regards,
teja
-----Original Message-----
From: Nick Burch [mailto:nick.burch@alfresco.com]
Sent: Monday, January 30, 2012 2:34 PM
To: POI Users List
Subject: RE: XSSF & Hyperlink to Directory
On Mon, 30 Jan 2012, Cross, David A. wrote:
> In Excel you can add a hyperlink to any cell and point it to any of
the
> example directories above
Can you try unzipping the .xlsx files from POI and Excel, and seeing how
they differ in their hyperlinks? If memory serves, they get stored as
relations, but a grep (or similar) should let you find which file(s)
they're in
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSF & Hyperlink to Directory
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 30 Jan 2012, Cross, David A. wrote:
> In Excel you can add a hyperlink to any cell and point it to any of the
> example directories above
Can you try unzipping the .xlsx files from POI and Excel, and seeing how
they differ in their hyperlinks? If memory serves, they get stored as
relations, but a grep (or similar) should let you find which file(s)
they're in
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSF & Hyperlink to Directory
Posted by "Cross, David A." <Cr...@llnl.gov>.
Attempting to create a hyperlink of type file and calling setAddress("C:\\test dir\\"); creates code that will not execute. No exception is thrown, the code simply stalls
In fact similar results for:
"C:\\Test.xls"
"C:\\TestDir\\"
This can be made to work by writing the path as a URL like link ie:
"C:/TestDir/"
This works fine and has desired results, however due to the nature of URL links
"C:/Test Dir/" fails to execute again
In Excel you can add a hyperlink to any cell and point it to any of the example directories above
David
-----Original Message-----
From: Nick Burch [mailto:nick.burch@alfresco.com]
Sent: Monday, January 30, 2012 10:54 AM
To: POI Users List
Subject: Re: XSSF & Hyperlink to Directory
On Mon, 30 Jan 2012, Cross, David A. wrote:
> However I would like to create a hyperlink to a specific directory or
> network location (ie "\\My network\Group A\User C\Folder F\" etc...)
> just as you can manually in Excel. I was unable to get the hyperlink
> setAddress function to work under these circumstances.
What are you getting, and how does it differ from if you create the same
thing in Excel?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: XSSF & Hyperlink to Directory
Posted by Nick Burch <ni...@alfresco.com>.
On Mon, 30 Jan 2012, Cross, David A. wrote:
> However I would like to create a hyperlink to a specific directory or
> network location (ie "\\My network\Group A\User C\Folder F\" etc...)
> just as you can manually in Excel. I was unable to get the hyperlink
> setAddress function to work under these circumstances.
What are you getting, and how does it differ from if you create the same
thing in Excel?
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org