How to add a hyperlink in Excel DNA with SetValue?

Question

One of +60 columns I pass to Excel is a hyperlink

   private object GetApplicationUrl(int id)
   {
      var environmentUri = _configurationManager.Default.AppSettings["EnvironmentUri"];
      return $"=HYPERLINK(\"{environmentUri}://TradingObject={id}/\", \"{id}\");";
   }

this resolves external protocol and opens particular deal in another application. This works, but initially, the hyperlink formula is unevaluated, forcing the users to evaluate it first. I use object array:

   protected void SetRange(object[,] range, int rows, int columns, ExcelCell start = null)
   {
      start = start ?? GetCurrentCell();
      ExcelAsyncUtil.QueueAsMacro(
          () =>
          {
             var data = new ExcelReference(start.Row, start.Row + (rows - 1), start.Column, start.Column + (columns - 1));
             data.SetValue(range);
          });
   }

How can I force the excel to evaluate it?

---

I tried several things, but they did not work. For example:

return XlCall.Excel(
   XlCall.xlcCalculateNow, 
   $"HYPERLINK(\"{environmentUri}://TradingObject={id}/\", \"{id}\")");

maybe I am using an incorrect flag or missing something.

Answer 1

I found out several ways to fix it, but not all of them worked as I expected. In short, I came up with calling the cell formula value instead.

   private void SetFormulaColumn(List<int> ids, ExcelCell cell)
   {
      var wSheet = ((Application)ExcelDnaUtil.Application).ActiveWorkbook.ActiveSheet;
      for (var i = 0; i < ids.Count; i++)
      {
         wSheet.Cells(cell.Row + i, cell.Column).Formula = GetApplicationUrl(ids[i]);
      }
   }

It is probably slightly less performant but works well enough to be considered as a valid solution.