This project has moved. For the latest updates, please go here.

Hyperlink from Database

Feb 28, 2013 at 6:05 AM
Edited Feb 28, 2013 at 7:20 AM
Cutting my teeth in C# and appreciating what EPPlus can do. I am able to manually generate hyperlinks for the excel file output but, of course, all of the links are to the same file. Here is the code I'm using:
string FileRootPath = @".\folder\013.jpg";
string DisplayText = "Text";
ws.Cells[2, 4, 1 + tbl.Rows.Count, 4].Formula = "HYPERLINK(\"" + FileRootPath + "\",\"" + DisplayText + "\")";
I would like the FileRootPath to be dynamic; based on the output from the database. The datatable already has the imgname (such as "013.jpg" or "mew.jpg"), each of which is unique, so I would like to have something like:
string FileRootPath = @".\expense\" + imgname;
The result being that each cell has a link to the image for its specific imgname. Any help would be greatly appreciated.

Edit
I think I am a little closer. I can loop through the imgnames but in the end all of the hyperlinks get set to the last imgname in the list. I also haven't added back in the \folder\ part of the path.
string DisplayText = "Text";
int rows = tbl.Rows.Count + 1;
for (int i = 1; i < rows; i++)
{
ws.Cells[2, 4, 1 + (i), 4].Formula = "HYPERLINK(\"" + ws.Cells[1 + (i), 4].GetValue<string>() + "\",\"" + DisplayText + "\")";
}
Feb 28, 2013 at 7:58 PM
Edited Feb 28, 2013 at 11:29 PM
It seems like you are not referencing the datatable tbl when assigning a hyperlink value to a cell within a for loop.

Try starting the program in debug mode (F5) and set breakpoints on lines of code with F9. You can right click on a variable or some type of array and inspect its values at a certain point during the codes execution.

Myself I am not a good programmer but I can get by with using the debugger a lot.
Mar 1, 2013 at 2:12 AM
Edited Mar 1, 2013 at 2:22 AM
Hi xav2075, thanks for taking the time to reply. Here is the code where I select and bind the data to the datatable ("tbl"):
SqlCommand cmd = new SqlCommand("SELECT date, desc, imgname FROM users WHERE email=@userstr", conn);
cmd.Parameters.AddWithValue("@userstr", Session["user"].ToString());

DataTable tbl = new DataTable();
cmd.CommandType = CommandType.Text;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(tbl);
I have used the debug tool on the for statement, and I can see it iterating through, so for example the first time through (where i=1) it returns:

ws.Cells[2, 4, 1 + (i), 4].Formula = HYPERLINK("013.jpg","Text")

The second, and final, time it iterates (where i=2) it returns:

ws.Cells[2, 4, 1 + (i), 4].Formula = HYPERLINK("mew.jpg","Text")

When the excel file is created ALL of the cells in column 4 (rows 2 and 3) are hyperlinked to mew.jpg, instead of row 2 hyperlinking to 013.jpg and row 3 hyperlinking to mew.jpg.