Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Hyperlinks created in xls file are invalid
Hyperlinks created in xls file are invalid [message #778831] Fri, 13 January 2012 18:00 Go to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Hi, I have a report design that contains several tables. And I have created bookmarks and hyperlinks so that the user can navigate from one cell of a table to another cell in another table.

This works fine for html, pdf, and doc outputs. However, for the xls (Excel) output, when the user clicks on the link, it says the link is invalid.

Is this supported?

If required, I can post a design and a data source.

Please help!

Thanks,
Joseph
Re: Hyperlinks created in xls file are invalid [message #780375 is a reply to message #778831] Tue, 17 January 2012 12:20 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Joseph,

Can you open a bugzilla entry for this? As a work around try using the
bookmark and table of contents expressions for the target. I have
created and example that works in xls and attached it. The grey
background data item has the hyperlink and the target data item is in
red and uses a bookmark and table of contents expression.

Jason

On 1/13/2012 6:00 PM, Joseph Yeh wrote:
> Hi, I have a report design that contains several tables. And I have
> created bookmarks and hyperlinks so that the user can navigate from one
> cell of a table to another cell in another table.
> This works fine for html, pdf, and doc outputs. However, for the xls
> (Excel) output, when the user clicks on the link, it says the link is
> invalid.
>
> Is this supported?
> If required, I can post a design and a data source.
>
> Please help!
>
> Thanks,
> Joseph
Re: Hyperlinks created in xls file are invalid [message #780377 is a reply to message #778831] Tue, 17 January 2012 12:20 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Joseph,

Can you open a bugzilla entry for this? As a work around try using the
bookmark and table of contents expressions for the target. I have
created and example that works in xls and attached it. The grey
background data item has the hyperlink and the target data item is in
red and uses a bookmark and table of contents expression.

Jason

On 1/13/2012 6:00 PM, Joseph Yeh wrote:
> Hi, I have a report design that contains several tables. And I have
> created bookmarks and hyperlinks so that the user can navigate from one
> cell of a table to another cell in another table.
> This works fine for html, pdf, and doc outputs. However, for the xls
> (Excel) output, when the user clicks on the link, it says the link is
> invalid.
>
> Is this supported?
> If required, I can post a design and a data source.
>
> Please help!
>
> Thanks,
> Joseph
Re: Hyperlinks created in xls file are invalid [message #780423 is a reply to message #780375] Tue, 17 January 2012 14:58 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Jason,

Thanks for your reply.
I have tried your workaround suggestion, but the xls output does not work in my excel. I have a 2002 and a 2003 excel. It still says the reference is not valid when I click on the link. Am I making some mistakes somewhere?

Joseph
Re: Hyperlinks created in xls file are invalid [message #780424 is a reply to message #780377] Tue, 17 January 2012 15:01 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Jason,

I did run your example and the references in the XLS output are invalid still.

Thanks,
Joseph
Re: Hyperlinks created in xls file are invalid [message #780432 is a reply to message #780424] Tue, 17 January 2012 15:34 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

What version of BIRT are you using?

Jason

On 1/17/2012 3:01 PM, Joseph Yeh wrote:
> Jason,
> I did run your example and the references in the XLS output are invalid
> still.
>
> Thanks,
> Joseph
Re: Hyperlinks created in xls file are invalid [message #780433 is a reply to message #780432] Tue, 17 January 2012 16:04 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Here are the first few lines in a new report design I created:

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.6.2.r262_v20110209 Build &lt;2.6.2.v20110214-1523></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
Re: Hyperlinks created in xls file are invalid [message #780435 is a reply to message #780433] Tue, 17 January 2012 16:08 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
I don't know how to determine the version of the BIRT runtime, so I just created a BIRT design and open it in text mode to find the version string.
Re: Hyperlinks created in xls file are invalid [message #780468 is a reply to message #780432] Wed, 18 January 2012 02:29 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Hi Jason, it looks that the workaround does not work on the version of BIRT we are using. I tried it on BIRT 3.7.1 and it worked. In fact, we did use the table-of-contents workaround to make the links work for the DOC files on our current BIRT version. It is just the XLS file that the links don't work. I have also tried our design on 3.7.1 and now I can see the links also working in the XLS file.

Now the question is, will it be possible to make the workaround work for XLS for our versions of BIRT?

Thanks,
Joseph
Re: Hyperlinks created in xls file are invalid [message #780596 is a reply to message #780468] Wed, 18 January 2012 12:51 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Joseph,

That was an issue in the 2.6 series xls emitter. The only way I know to
work around this issue is to change the xls emitter code. The issue is
the way the range tag is generated. In 2.6 this was emitted.

<Names>
<NamedRange ss:Name="mybookmark10104" ss:RefersTo="=Sheet1!R43C1"/>
<NamedRange ss:Name="mybookmark10103" ss:RefersTo="=Sheet1!R25C1"/>
<NamedRange ss:Name="mybookmark10102" ss:RefersTo="Sheet1!R21C1"/>
<NamedRange ss:Name="mybookmark10101" ss:RefersTo="=Sheet1!R15C1"/>
<NamedRange ss:Name="mybookmark10100" ss:RefersTo="=Sheet1!R9C1"/>
</Names>

Where in 3.7 this is emitted.

<Names>
<NamedRange ss:Name="mybookmark10104" ss:RefersTo="Report!R43C1"/>
<NamedRange ss:Name="mybookmark10103" ss:RefersTo="Report!R25C1"/>
<NamedRange ss:Name="mybookmark10102" ss:RefersTo="Report!R21C1"/>
<NamedRange ss:Name="mybookmark10101" ss:RefersTo="Report!R15C1"/>
<NamedRange ss:Name="mybookmark10100" ss:RefersTo="Report!R9C1"/>
</Names>

Jason

On 1/18/2012 2:29 AM, Joseph Yeh wrote:
> Hi Jason, it looks that the workaround does not work on the version of
> BIRT we are using. I tried it on BIRT 3.7.1 and it worked. In fact, we
> did use the table-of-contents workaround to make the links work for the
> DOC files on our current BIRT version. It is just the XLS file that the
> links don't work. I have also tried our design on 3.7.1 and now I can
> see the links also working in the XLS file.
>
> Now the question is, will it be possible to make the workaround work for
> XLS for our versions of BIRT?
>
> Thanks,
> Joseph
Re: Hyperlinks created in xls file are invalid [message #780620 is a reply to message #780596] Wed, 18 January 2012 14:34 Go to previous messageGo to next message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Hi Jason,

Thanks for the answers. Do you suggest that I open a bugzilla entry against BIRT 2.6.2 to change the xls emitter as you pointed out? Or will it be a small change to some code in BIRT 2.6.2 that a patch can be created for? Would it be possible that you send me such a patch including the source code? I guess there is no other way to work around this issue, right?

Thanks,
Joseph
Re: Hyperlinks created in xls file are invalid [message #780869 is a reply to message #780620] Thu, 19 January 2012 13:34 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

You could see if the 3.7 xls emitter will work in 2.6.2. The changes
appear to all be in ExcelXmlWriter.java

Version 2.6.2 code in ExcelXmlWriter.java

private void outputBookmarks( HashMap<String, BookmarkDef> bookmarkList )
{
if ( !bookmarkList.isEmpty( ) )
{
writer.openTag( "Names" );
Set<Entry<String, BookmarkDef>> bookmarkEntry = bookmarkList
.entrySet( );
for ( Entry<String, BookmarkDef> bookmark : bookmarkEntry )
defineNames( bookmark );
writer.closeTag( "Names" );
}
}


private void defineNames( Entry<String, BookmarkDef> bookmarkEntry )
{
BookmarkDef bookmark = bookmarkEntry.getValue( );
String name = bookmark.getValidName( );
String refer = getRefer( bookmark.getSheetIndex( ), bookmark );
defineName( name, refer );
}

private String getRefer( int sheetIndex, BookmarkDef bookmark )
{
StringBuffer sb = new StringBuffer( "=Sheet" );
sb.append( sheetIndex );
sb.append( "!R" );
sb.append( bookmark.getRowNo( ) );
sb.append( "C" );
sb.append( bookmark.getColumnNo( ) );
return sb.toString( );
}

private void defineName( String name, String refer )
{
writer.openTag( "NamedRange" );
writer.attribute( "ss:Name", name );
writer.attribute( "ss:RefersTo", refer );
writer.closeTag( "NamedRange" );
}


Version 3.7.1 code in ExcelXmlWriter.java

private void outputBookmarks( HashMap<String, BookmarkDef> bookmarkList )
{
if ( !bookmarkList.isEmpty( ) )
{
writer.openTag( "Names" );
for ( Entry<String, BookmarkDef> entry : bookmarkList.entrySet( ) )
{
BookmarkDef bookmark = entry.getValue( );
defineName( bookmark.getValidName( ), getRefer( bookmark ) );
}
writer.closeTag( "Names" );
}
}

private String getRefer( BookmarkDef bookmark )
{
StringBuffer buffer = new StringBuffer( '=' );
buffer.append( bookmark.getSheetName( ) );
buffer.append( "!" );
int startColumn = bookmark.getStartColumn( );
int startRow = bookmark.getStartRow( );
addCellPosition( buffer, startColumn, startRow );
int endColumn = bookmark.getEndColumn( );
int endRow = bookmark.getEndRow( );
if ( endRow != -1 && endColumn != -1 && startRow != endRow
&& startColumn != endColumn )
{
buffer.append( ':' );
addCellPosition( buffer, endColumn, endRow );
}
return buffer.toString( );
}




private void defineName( String name, String refer )
{
writer.openTag( "NamedRange" );
writer.attribute( "ss:Name", name );
writer.attribute( "ss:RefersTo", refer );
writer.closeTag( "NamedRange" );
}


Jason

On 1/18/2012 2:34 PM, Joseph Yeh wrote:
> Hi Jason,
>
> Thanks for the answers. Do you suggest that I open a bugzilla entry
> against BIRT 2.6.2 to change the xls emitter as you pointed out? Or will
> it be a small change to some code in BIRT 2.6.2 that a patch can be
> created for? Would it be possible that you send me such a patch
> including the source code? I guess there is no other way to work around
> this issue, right?
>
> Thanks,
> Joseph
Re: Hyperlinks created in xls file are invalid [message #783477 is a reply to message #780869] Wed, 25 January 2012 14:41 Go to previous message
Joseph Yeh is currently offline Joseph Yeh
Messages: 12
Registered: July 2009
Junior Member
Hi Jason, thanks for your help.
Joseph
Previous Topic:Date Format
Next Topic:The viewing session is not available or has expired
Goto Forum:
  


Current Time: Tue Sep 02 12:53:46 EDT 2014

Powered by FUDForum. Page generated in 0.02301 seconds