dan_0
asked on
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.
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++
};
ASKER
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/spr eadsheet.x ls");
oConn.ConnectionString = @"Provider=Microsoft.Jet.O LEDB.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
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/spr
oConn.ConnectionString = @"Provider=Microsoft.Jet.O
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
Try this....
1) Click on the cell
2) Then hit return
Does the cell change to a hyperlink?
thx.
1) Click on the cell
2) Then hit return
Does the cell change to a hyperlink?
thx.
Also, please check that there are no spaces before the = sign.
thx.
thx.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.
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.
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("https://www.experts-exchange.com","Click to View")
I think thats what you may be after.
regards.