Can ADO be used to insert hyperlinks into Excel?

Hi,

I am exporting data from an SQL database into an existing excel file using C# and ADO.
I need to export a datatable where one of the columns is a hyperlink. I am using an OleDb update command to get the data in.


Can this be done?

So far I have tried writing the whole thing using interop but  i find it very 'clunky' and anyway I cannot get Excel installed on the server so thats out the question.

Code snippet attached.


int i=6;
            foreach (DataRow Row in BookiesData.Tables[0].Rows)
            {
               //I have an OleDB update command in here like:
 
  UPDATE [Sheet1$] SET [F5] = Row[0], F6 = Row[1]
 
           //but obviously in a C# string.
           //Say for example that i want F6 to contain a hyperlink with the URL stored in Row[1]
 
i++
 
            };

Open in new window

dan_0Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ramesh SrinivasTechnical ConsultantCommented:
To create a hyperlink field insert the following string and dynamic data:

For example in vb.net  (shouldnt be hard in c#) assign the cell the value of:

"=HYPERLINK(""" & dr("Link") & """, ""Click to View"")"

You basically want to insert:

=HYPERLINK("http://www.experts-exchange.com","Click to View")

I think thats what you may be after.

regards.
0
dan_0Author Commented:
Hi, thanks for the reply. Thats exactly what I am doing currently and it doesn't work. Maybe there is some setting in the Excel file or connection string needed to make it work?

Here's my query (the / character escapes the next character)

SET [F7] = '" + "=hyperlink(\"" + Row["URL"].ToString() + "\",\"" + Row["BookieName"].ToString() + "\")"

Here is my connection string:

OleDbConnection oConn = new OleDbConnection();
            string filename = Server.MapPath("~/auto/spreadsheet.xls");
            oConn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=" + filename + @";Extended Properties=
    ""Excel 8.0;IMEX=1;HDR=YES;""";




When I open the excel file, the following string is in the cells:

=HYPERLINK("http://www.theurl.com","click here")

Any more help appreciated
0
Ramesh SrinivasTechnical ConsultantCommented:
Try this....

1) Click on the cell
2) Then hit return

Does the cell change to a hyperlink?

thx.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ramesh SrinivasTechnical ConsultantCommented:
Also, please check that there are no spaces before the = sign.

thx.
0
dan_0Author Commented:
Hi,

thanks for the replies

In answer to:

Try this....
1) Click on the cell
2) Then hit return
Does the cell change to a hyperlink?

The answer is YES but obviously this is not acceptable ultimately, it just shows that the outputted string is correct.

HOWEVER I HAVE FOUND A SOLUTION THAT SUITS ME

Output the URL and link text as a string to another hidden cell (e.g. F100 and F101)

And use this formula in the cell where you want the hyperlink

=hyperlink(F100,F101)

Works for me.

Cheers
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ramesh SrinivasTechnical ConsultantCommented:
"The answer is YES but obviously this is not acceptable ultimately"

The reason I asked this was because I have had in the past, the same issue and I am trying to remember how I solved it.

But you seem to have worked something out for yourself.

ALl the best.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.