We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Hyperlink created by Excel formula not working when Aspose.Cells converts to HTML

Hello,

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.

My save options are specified below.

		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);
		
		MemoryStream htmlStream = new MemoryStream();
		book.Save(htmlStream, hs); 
		string html = htmlStream.ReadContents();

Thank You,
-Andy

@weissa,

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);

files1.zip (15.4 KB)

I am using latest version/fix: Aspose.Cells for .NET v21.10.1(please try it):
Aspose.Cells21.10.1 For .Net2_AuthenticodeSigned.Zip (5.6 MB)
Aspose.Cells21.10.1 For .Net4.0.Zip (5.6 MB)
Aspose.Cells21.10.1 For .NetStandard20.Zip (5.6 MB)

If you still find the issue with latest version/fix, kindly provide complete sample code and sample file(s) to show the issue, we will check it soon.

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

@weissa,

I have split the thread to create new topic in the forums. Could you try it now?

Amjad_Sahi,

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?

Thank You,
-Andy

@weissa,

No, RemoveFormulas will remove all the formulas in the spreadsheet including Hyperlink formula.

@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.

Thanks,
-Andy

@weissa,

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.

@Amjad_Sahi,
Thanks for the info. We’ll try to do the conversion before the remove formulas.
-Andy

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

@weissa,

Thanks for sharing the code segment. It will help others with similar scenario as yours.