Hyperlinks not Clickable when Exported to excel

Question

When I run a query, the results of it are exported to the Excel. This data has few hyperlinks which get displayed in random (I don't know which cell or column).

My issue is that the hyperlinks are getting displayed as normal word and not as clickable hyperlinks. Unless, I click on the particular cell and click outside, the hyperlink doesn't become blue. How can I resolve this issue? I want the hyperlinks exported to excel as clickable hyperlinks.

Answer 1

There is a =HYPERLINK() formula in ms's excel and apple's numbers programs. Two ways to apply this:

In your source data, have that column's output wrapped in the formula. As you didn't give an example query or mention the language. here's a sql example:

select products_id, products_name, concat("=HYPERLINK(\"http://www.site.com/product_info.php?products_id=",products_id,"\")"), products_description from products p;

so the resulting excel/xml column will be =HYPERLINK("http://www.site.com/product_info.php?products_id=1223") and it will be clickable when you open the file in excel.

The other way to handle this is after you open it in excel: Let's say the links are in column C. Make a new column D, in cell D2 insert the formula =HYPERLINK(C2) then press enter, click on cell D2, copy, then select the entire column D, paste. Now you have a column of clickable links.

-Z

Answer 2

One of the ways is to show the result of the query in a grid like GridView control and then export the Grid. This will causes the underlying HTML to get exported as well (i.e GridView HTML and all the controls inside the GridView).

After that when you open the Excel file you will see the links intact. Here is an article which talks about exporting GridView to excel.

http://www.highoncoding.com/Articles/197_Extensive_Study_of_GridView_Export_to_Excel.aspx