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++;
    }

Answer 1

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.

Answer 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");
        } 
    }
}