Use C# script task in SSIS to make Excel hyperlink clickable

Question

I have an SSIS package which is populating an existing excel (template) with data via a Data Flow Task. The issue I am having is that I am dynamically creating worksheets and want to add a Hyperlink from the main page to the appropriate worksheet in that workbook.

I add the =Hyperlink reference to the appropriate sheet in the data flow task and it carries through just fine but the link (despite the column type being 'General') is not actually showing as a Hyperlink until I actually click in the cell, then it looks good.

I thought I could use a C# script task to flip the column to 'Text' and then back to 'General' and maybe that would make it read properly but no luck. Any ideas how I can simulate a double click in each of the cells in my column?

Here is what I have so far, which works for converting the column to general and wrapping text in another column but I am at a loss as to how to make this formula display properly as a Hyperlink.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;

#endregion

namespace ST_2bdf93d5542441248076f053703d32c9
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        string inputFile = (string)Dts.Variables["RecommendationFileName"].Value;
        Excel.Application ExcelApp = new Excel.Application();
        Excel.Workbook ExcelWorkbook = ExcelApp.Workbooks.Open(inputFile);
        ExcelApp.Visible = true;

        Excel.Worksheet xlWorkSheetFocus = (Excel.Worksheet)ExcelWorkbook.Worksheets.get_Item(2);
        xlWorkSheetFocus.Activate();
        xlWorkSheetFocus.Select(Type.Missing);
        Excel.Range usedRange = xlWorkSheetFocus.UsedRange;


        xlWorkSheetFocus.Columns[5].NumberFormat = ""; //Make column 5 type General
        xlWorkSheetFocus.Columns[4].WrapText = true; //Wrap text in column 4


        ExcelWorkbook.Save();

        GC.Collect();
        GC.WaitForPendingFinalizers();

        ExcelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
        Marshal.FinalReleaseComObject(ExcelWorkbook);

        ExcelApp.Quit();
        Marshal.FinalReleaseComObject(ExcelApp);
    }
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
}
}

Answer 1

Well looks like I found a workable solution using text to columns, picking a delimiter "@" which doesn't exists, and writing the results to the same column/cell. Guessing there might be a better way but it gets me a clickable link.

xlWorkSheetFocus.get_Range("E6", ("E6" +
  xlWorkSheetFocus.UsedRange.Rows.Count)).TextToColumns(Type.Missing,
  Microsoft.Office.Interop.Excel.XlTextParsingType.xlDelimited,
  Microsoft.Office.Interop.Excel.XlTextQualifier.xlTextQualifierNone, true,
  Type.Missing, Type.Missing, false, true, Type.Missing,
  "@", Type.Missing, Type.Missing, Type.Missing, Type.Missing);