For anyone else looking to support this. The following code will find any HYPERLINK formulas, calculate them to grab the Hyperlink Text and Hyperlink Address, and then convert the cell into a true static hyperlink object. This will allow you to remove the formulas in your document but retain calculated hyperlinks as true clickable hyperlink objects.
Cell hyperLinkCell = null;
while (true)
{
// Loop to find the hyperlink formulas on this sheet.
hyperLinkCell = sheet.Cells.Find("hyperlink", hyperLinkCell, new FindOptions()
{
CaseSensitive = false,
LookInType = LookInType.OnlyFormulas,
});
// No more hyperlinks, we're done
if (hyperLinkCell == null)
break;
// Calculate the hyperlink formula, using a custom engine
var result = sheet.CalculateFormula(hyperLinkCell.Formula, new CalculationOptions()
{
CustomEngine = new MyEngine()
}) as List<string>;
// The result of the hyperlink formula with out custom engine will give us the name and address in an array
if (result != null && result.Count == 2)
{
sheet.Hyperlinks.Add(hyperLinkCell.Name, hyperLinkCell.Name, result[1], result[0], result[0]);
}
}
The custom engine is required to leverage Aspose’s calculation engine to obtain the hyperlink text and name from whatever nested formulas (e.g. concat, vlookup, etc.). However we need this formula to return both the name and the address, where normally it returns just the name.
public class MyEngine : AbstractCalculationEngine
{
public override bool ProcessBuiltInFunctions => true;
public override void Calculate(CalculationData data)
{
string funcName = data.FunctionName.ToUpper();
if ("HYPERLINK".Equals(funcName))
{
if (data.ParamCount < 1) return;
string hyperlink = data.GetParamValue(0).ToString();
string name = data.GetParamValue(data.ParamCount == 2 ? 1 : 0).ToString();
data.CalculatedValue = new List<string> { name, hyperlink };
}
}
}