DataTable binding to Listobject with Hyperlinks RRS feed

  • Question

  • I am using a DataTable as the source of a VSTO ListObject in an excel plug-in, my issue is that I have not found a way to tell the ListObject that a column in my DataTable are hyperlinks. The ListObject see them as text,  in order for my to use them as hyperlinks in the ListObject I must force excel to calculate the cell value. Which is not optimal because I dont get the chance to set the link to a clean value like "Click Me" instead of ""blalblablab"&moreinfo="Blalalalal"


    I have tried setting the datacolumn in the data table to Hyperlink Columns, with hyperlinks , I have set the column to URI's I'm just not sure how to do this and maintain the simplicity of ListObject.itemsource = datatable;

    Any Ideas?

    Wednesday, November 9, 2011 7:47 PM


  • Hi,

    Thanks for your post.

    Excel is unable to detect whether or not the cell value is hyperlink when adding ListObject, you can active each cell of hyperlink column to invoke recalculate method to change the cell to hyperlink.

    Also you can change the cell value to hyperlink programmatically by using the following code:

                Excel.Worksheet sheet1 = Application.ActiveWorkbook.Worksheets[1];
                Excel.ListObject list1 = sheet1.ListObjects[1];
                Excel.ListColumn LinkColumn = list1.ListColumns["Link"];
                foreach (Excel.Range linkCell in LinkColumn.Range)
                    //check if the range is header of the listobject
                    if (linkCell.Row == list1.HeaderRowRange.Row)
                    //add hyperlinks
                    sheet1.Hyperlinks.Add(Anchor: linkCell, Address: linkCell.Value, TextToDisplay: linkCell.Value);

    The procedure above assume that you have added a listobject which contain a hyerplink column named "Link". The code will itreate over all the cells in the hyperlink column and change it to hyperlink.

    I hope this helps.

    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, November 10, 2011 9:12 AM