You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Faraz Rozi <fr...@gmail.com> on 2008/03/14 19:53:48 UTC
Problem setting hyperlink to files outside of current directory
Hey everyone,
I'm having issues with hyper links. I am trying to set a link in excel
using the new setHyperlink method from HSSFCell, which I got from SVN
checkout. I'm following online template for doing this but instead of simply
putting in the file name in String format I'm doing file.getAbsolutePath()
which also returns a String. For those wondering, using .getCanonicalPath()
produces the same results. This file variable holds the path to the file I
wish to link to. The problem is that while it looks like it creates the
hyper link in the excel sheet and displays the file name as the link label,
clicking on the link produces the following excel error message:
The address of this site is not valid. Check the address and try again.
Here's where it gets interesting. If I select the hyper link and choose
EDIT, it correctly displays the path in the new dialog window. If I choose
OK, without changing ANYTHING, it fixes the link and I can now click it and
it will take me to the file! This almost feels like my previous problem with
evaluating formulas, where I had to re-evaluate formulas to get appropriate
answers. Is there a similar method for hyper links? Thanks guys.
Here is the code:
public void setFileLinkValue(File file, int row, int col)
{
try
{
HSSFCellStyle hlink_style = workBook.createCellStyle();
HSSFFont hlink_font = workBook.createFont();
hlink_font.setUnderline(HSSFFont.U_SINGLE);
hlink_font.setColor(HSSFColor.BLUE.index);
hlink_style.setFont(hlink_font);
HSSFRow sheetRow;
HSSFCell cell;
sheetRow = sheet.getRow(row);
cell = sheetRow.getCell((short) col);
HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_FILE);
//link to a file corresponding to row entry
cell.setCellValue(new HSSFRichTextString(file.getName()));
link.setAddress(file.getAbsolutePath());
cell.setHyperlink(link);
cell.setCellStyle(hlink_style);
outputStream = new FileOutputStream("C:\\test.xls");
workBook.write(outputStream);
outputStream.close();
}
catch (FileNotFoundException e)
{
System.out.println("file not found\n");
e.printStackTrace();
}
catch (IOException e)
{
System.out.println("IO Exception\n");
}
}
Re: Problem setting hyperlink to files outside of current directory
Posted by Faraz Rozi <fr...@gmail.com>.
Hi Dave,
Your assumption was absolutely correct. However, it was the setAddress
line that should have been changed, not the setCellValue. The setCellValue
is basically a label or the "value" to be shown within Excel. The actual
hyper link lives in the link variable. So, the only modification to the
previous code posted is the replacement of the original setAddress line
with:
link.setAddress("file:"+(file.getAbsolutePath()));
All is well after this.
Thanks again,
Faraz
On Fri, Mar 14, 2008 at 3:17 PM, David Fisher <df...@jmlafferty.com>
wrote:
> Hi -
>
> I'm not sure, but it sounds like Excel is interpreting your first
> call as an http: URL link. Then when you use the dialog Excel gets
> smart enough to know that you are providing a Pathname.
>
> Try:
>
> cell.setCellValue(new HSSFRichTextString
> ("file:"+file.getName()));
>
> Regards,
> Dave
>
> On Mar 14, 2008, at 1:53 PM, Faraz Rozi wrote:
>
> > Hey everyone,
> >
> > I'm having issues with hyper links. I am trying to set a link in
> > excel
> > using the new setHyperlink method from HSSFCell, which I got from SVN
> > checkout. I'm following online template for doing this but instead
> > of simply
> > putting in the file name in String format I'm doing
> > file.getAbsolutePath()
> > which also returns a String. For those wondering,
> > using .getCanonicalPath()
> > produces the same results. This file variable holds the path to the
> > file I
> > wish to link to. The problem is that while it looks like it creates
> > the
> > hyper link in the excel sheet and displays the file name as the
> > link label,
> > clicking on the link produces the following excel error message:
> >
> > The address of this site is not valid. Check the address and try
> > again.
> >
> > Here's where it gets interesting. If I select the hyper link and
> > choose
> > EDIT, it correctly displays the path in the new dialog window. If I
> > choose
> > OK, without changing ANYTHING, it fixes the link and I can now
> > click it and
> > it will take me to the file! This almost feels like my previous
> > problem with
> > evaluating formulas, where I had to re-evaluate formulas to get
> > appropriate
> > answers. Is there a similar method for hyper links? Thanks guys.
> >
> >
> > Here is the code:
> >
> > public void setFileLinkValue(File file, int row, int col)
> > {
> > try
> > {
> > HSSFCellStyle hlink_style = workBook.createCellStyle();
> > HSSFFont hlink_font = workBook.createFont();
> > hlink_font.setUnderline(HSSFFont.U_SINGLE);
> > hlink_font.setColor(HSSFColor.BLUE.index);
> > hlink_style.setFont(hlink_font);
> > HSSFRow sheetRow;
> > HSSFCell cell;
> > sheetRow = sheet.getRow(row);
> > cell = sheetRow.getCell((short) col);
> > HSSFHyperlink link = new HSSFHyperlink
> > (HSSFHyperlink.LINK_FILE);
> >
> > //link to a file corresponding to row entry
> >
> > cell.setCellValue(new HSSFRichTextString(file.getName()));
> > link.setAddress(file.getAbsolutePath());
> > cell.setHyperlink(link);
> > cell.setCellStyle(hlink_style);
> >
> > outputStream = new FileOutputStream("C:\\test.xls");
> > workBook.write(outputStream);
> > outputStream.close();
> > }
> > catch (FileNotFoundException e)
> > {
> > System.out.println("file not found\n");
> > e.printStackTrace();
> > }
> > catch (IOException e)
> > {
> > System.out.println("IO Exception\n");
> > }
> > }
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Problem setting hyperlink to files outside of current directory
Posted by David Fisher <df...@jmlafferty.com>.
Hi -
I'm not sure, but it sounds like Excel is interpreting your first
call as an http: URL link. Then when you use the dialog Excel gets
smart enough to know that you are providing a Pathname.
Try:
cell.setCellValue(new HSSFRichTextString
("file:"+file.getName()));
Regards,
Dave
On Mar 14, 2008, at 1:53 PM, Faraz Rozi wrote:
> Hey everyone,
>
> I'm having issues with hyper links. I am trying to set a link in
> excel
> using the new setHyperlink method from HSSFCell, which I got from SVN
> checkout. I'm following online template for doing this but instead
> of simply
> putting in the file name in String format I'm doing
> file.getAbsolutePath()
> which also returns a String. For those wondering,
> using .getCanonicalPath()
> produces the same results. This file variable holds the path to the
> file I
> wish to link to. The problem is that while it looks like it creates
> the
> hyper link in the excel sheet and displays the file name as the
> link label,
> clicking on the link produces the following excel error message:
>
> The address of this site is not valid. Check the address and try
> again.
>
> Here's where it gets interesting. If I select the hyper link and
> choose
> EDIT, it correctly displays the path in the new dialog window. If I
> choose
> OK, without changing ANYTHING, it fixes the link and I can now
> click it and
> it will take me to the file! This almost feels like my previous
> problem with
> evaluating formulas, where I had to re-evaluate formulas to get
> appropriate
> answers. Is there a similar method for hyper links? Thanks guys.
>
>
> Here is the code:
>
> public void setFileLinkValue(File file, int row, int col)
> {
> try
> {
> HSSFCellStyle hlink_style = workBook.createCellStyle();
> HSSFFont hlink_font = workBook.createFont();
> hlink_font.setUnderline(HSSFFont.U_SINGLE);
> hlink_font.setColor(HSSFColor.BLUE.index);
> hlink_style.setFont(hlink_font);
> HSSFRow sheetRow;
> HSSFCell cell;
> sheetRow = sheet.getRow(row);
> cell = sheetRow.getCell((short) col);
> HSSFHyperlink link = new HSSFHyperlink
> (HSSFHyperlink.LINK_FILE);
>
> //link to a file corresponding to row entry
>
> cell.setCellValue(new HSSFRichTextString(file.getName()));
> link.setAddress(file.getAbsolutePath());
> cell.setHyperlink(link);
> cell.setCellStyle(hlink_style);
>
> outputStream = new FileOutputStream("C:\\test.xls");
> workBook.write(outputStream);
> outputStream.close();
> }
> catch (FileNotFoundException e)
> {
> System.out.println("file not found\n");
> e.printStackTrace();
> }
> catch (IOException e)
> {
> System.out.println("IO Exception\n");
> }
> }
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org