Inserting hyperlink in the DataTable C#

Question

I would like to add the hyperlink in export the dataTable into excel

But the excel cannot show the hyperlink property, only strings.

Is there any limitation for the dataTable input field ?

below is my code for formatting the DATATABLE

       int dt_current_row = 0;
        string[] Days = testDays.ToArray();

        for (int i = 0; i < tableOriginal.Rows.Count; i++)
        {
            string wo_number = handle(tableOriginal.Rows[i]["WO_NUMBER"].ToString());
            string date = handle(tableOriginal.Rows[i]["CREATE_DATE"].ToString());

            if (i == 0)
            {

                // Insert New
                DataRow dr = dt.NewRow();
                dt.Rows.Add(dr);
                dr["WO_NUMBER"] = wo_number;

                for (int x = 1; x < dt.Columns.Count; x++)
                {
                    int z = Convert.ToInt32(Math.Floor(0.5 * (x - 1)));
                    //search Date
                    if (tableOriginal.Rows[i]["CREATE_DATE"].ToString() == Days[z])
                    {
                        //if equal , insert num and ID 
                        object xx = tableOriginal.Rows[i]["NUM"];
                        int num = Convert.ToInt32(xx);
                        dr["PHOTO_" + z.ToString()] = num;

                        object yy = tableOriginal.Rows[i]["PHOTO_ID"];
                        string photo = Convert.ToString(yy);
                        dr["ID_" + z.ToString()] = HttpContext.Current.Server.HtmlEncode(httpLink + photo);

                        break;
                    }
                }
            }

....and the part to export cells in DataTable into cells in excel Document xls I have tried to set the formula but it doesn't work

foreach (DataRow r in result.Rows)
    {
        currentCol = 1;
        foreach (DataColumn c in result.Columns)
        {
            string temp = c.ColumnName.ToString().Substring(0,2);
            if (temp.Equals("ID")) 
            {
                httpLinkForPhoto = r[currentCol - 1].ToString();
                if (!httpLinkForPhoto.Equals(null))
                {
                    string formula = "=HYPERLINK(" + httpLinkForPhoto + "," + httpLinkForPhoto + ")";
                    excelDoc2.SetFormula(1, 1, currentRow, currentCol, currentRow, currentCol, formula);
                }
            }
            else
            { 
                excelDoc2.setCell(1, 1, currentRow, currentCol, r[currentCol - 1].ToString()); 
            }            
            currentCol++;
        }
        currentRow++;
    }

score 2 · Answer 1 · edited Sep 07 '12 at 07:25

2

There is a function in Excel for this. The syntax is

=HYPERLINK(url;friendlyname)

just make sure that text in your cell looks like that, url and friendlyname are of course placeholders and you should substitute them for your URL and text to display.

edited Sep 07 '12 at 07:25

Bridge

29,818
9
60
82

answered Sep 07 '12 at 07:17

Ivan Koshelev

3,830
2
30
50

score 2 · Accepted Answer · edited May 23 '17 at 10:24

2

A .net DataTable may only contain .net Types. So there is no possibility to add a "hyperlink" within a dataTable. But of course you may add a hyperlink to an excel Cell. See this question or MSDN for details on adding a hyperlink to excel. According to your code this may look like

// common syntax to  add a Hyperlink to Excel
object Add(
[In] object Anchor, 
[In] string Address, 
[In, Optional] object SubAddress, 
[In, Optional] object ScreenTip, 
[In, Optional] object TextToDisplay
);

// your code
for (int rowIndex=0; rowIndex<result.Rows.Count; rowIndex++)
{
    for (int columnIndex=0; columnIndex<result.Columns.Count; columnIndex++)
    {
        string yourValue = result.Rows[rowIndex].Item[columnIndex].ToString();
        if (columnIndex!=YOUR_HYPERLINK_COLUMN_INDEX)
            excelDoc2.setCell(1, 1, rowIndex, columnIndex, yourValue);      
        else
        {
             Excel.Range range = (Range) YOUR_SHEET.Cells[rowIndex, columnIndex];

             CURRENT_WORKSHEET.Hyperlinks.Add(
                           range, 
                           yourValue, 
                           Type.Missing,"YOUR_SCREEN_TIP",
                           "YOUR_TEXT_TO_DISPLAY");
        } 
    }
}

edited May 23 '17 at 10:24

Community

1
1

answered Sep 07 '12 at 07:21

Pilgerstorfer Franz

8,303
3
41
54

what does YOUR_HYPERLINK_COLUMN_INDEX mean ? Does it imply I have to hard code with column index to enable such hyperlink-functionality? – Jeff Bootsholz Sep 07 '12 at 07:51
yep! In your code there is only one column that should have hyperlinks (PHOTO_ID - right?) so you might change YOUR_HYPERLINK_COLUMN_INDEX to the index of this column – Pilgerstorfer Franz Sep 07 '12 at 08:03
question modified I have tried to filter out the prefix of the column name as "ID" and then insert the formula but the result does not work. – Jeff Bootsholz Sep 07 '12 at 09:47
How about trying to insert the formula ? – Jeff Bootsholz Sep 08 '12 at 04:30
did you try my solution (without formula?) what were your results? – Pilgerstorfer Franz Sep 09 '12 at 20:07
Cannot, as my ExcelDoc has already initialize these.. if I follow your solution exactly, excel component are overrided .. – Jeff Bootsholz Sep 10 '12 at 02:19
what does these parameter of the API mean ? – Jeff Bootsholz Sep 10 '12 at 02:53
the API call is just a demo code to show you how it works in general. Afterwards (see _//your code_ comment) there is an example of how to integrate it into your code. – Pilgerstorfer Franz Sep 10 '12 at 06:09

Inserting hyperlink in the DataTable C#

2 Answers2