This no longer seems to be working in Aspose 21.7.6. A cell with the contents =HYPERLINK(“https://www.google.com”,“Google”) does not export as a hyperlink when saving to HTML.
I tested your scenario/case using the following sample code with a simple template Excel file (that contains the “=HYPERLINK("https://www.google.com","Google")” formula in a cell). The output HTML (attached) is fine tuned with the hyperlink in tact:
e.g. Sample code:
Workbook book = new Workbook("e:\\test2\\Bk_hyperlinks1.xlsx");
HtmlSaveOptions hs = new HtmlSaveOptions();
hs.ExcludeUnusedStyles = true;
hs.ExportDocumentProperties = false;
hs.ExportPrintAreaOnly = true;
hs.ExportWorksheetProperties = false;
hs.ExportSimilarBorderStyle = true;
hs.ExportImagesAsBase64 = true;
hs.PresentationPreference = false;
//hs.WarningCallback = new WarningCallback(NullLogger.Instance);
book.Save("e:\\test2\\outhyperlinks1.html", hs);
I’ll give the latest dll’s a try. Can you correct the permissions on the files please, I’m seeing:
Sorry, this file is private. Only visible to topic owner and staff members.
Thanks,
-Andy
My apologies. There was something I missed. We are removing formulas before exporting to HTML. The sheet.Cells.RemoveFormulas() function does not convert the HYPERLINK formula into a text based Hyperlink. Is it possible for RemoveFormulas to treat HYPERLINK formulas this way?
@Amjad_Sahi,
Right I would expect it to remove the formula. I’m asking if it can retain the hyperlink as static text, similar to how a textual hyperlink shows up in the Hyperlinks Collection today. This would allow us to dynamically generate hyperlinks and export them to HTML without retaining the formulas.
Yes, it will retain the hyperlink as static text only. When using Worksheet.Cells.RemoveFormulas() method, it will just retain the resultant (static) text in that cell. Since it is not hyperlinked text now, so you cannot click on the “Google” (static) text to navigate to Google site anymore.
Hyperlink will only work if you do not remove the formula (behind the scene). So, if you remove the formula, this will be just a static text and nothing more than this. Maybe you could add hyperlink via Hyperlink class.
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 };
}
}
}