Adding Hyperlink on Cell with Rich Text

Question

I have a variable with rich text and am trying to write the data in an Excel cell using Aspose.Cells. The text formatting is coming fine but the Hyperlink is not getting rendered, it's coming as plain text I cannot click on the link. Below is the code sample:

//Instantiate a Workbook object that represents Excel file.

using Aspose.Cells;

Workbook wb = new Workbook();

Worksheet sheet = wb.Worksheets[0];

//Access the "A1" cell in the sheet.

Cell cell = sheet.Cells["A1"];

//Input the rich text into the "A1" cell

var formattedVal = "<b>Hey This</b> <i>is <a href='http://Test'>Test  </a></i><a href='http://google.com'>http://google.com</a>";
cell.HtmlString = formattedVal;

wb.Save("c:\\MyBook.xls", SaveFormat.Excel97To2003);

Output :

Here "is Test" is a hyperlink but coming as plain text only with formatting and HTTP://google.com is also coming as text only.

Please suggest.

I am using Aspose.Cell 20.2.0 version.

//Instantiate a Workbook object that represents Excel file. using Aspose.Cells; Workbook wb = new Workbook(); Worksheet sheet = wb.Worksheets[0]; //Access the "A1" cell in the sheet. Cell cell = sheet.Cells["A1"]; //Input the rich text into the "A1" cell var formattedVal = "Hey This is Test http://google.com"; cell.HtmlString = formattedVal; wb.Save("c:\\MyBook.xls", SaveFormat.Excel97To2003); — Pankaj Toshniwal, May 02 '23 at 05:08

score 0 · Answer 1 · answered May 03 '23 at 10:29

As far as I'm aware, links must occupy their own cells in Excel. You can't have HTML text and links in the same cell.

Shapes can take HTML strings, so you could just add those and paste in your HTML - but even then I think this would need to be done via SendKeys as their is no method I know of that will paste HTML. If you record your key strokes when pasting to a shape, you'll see that VBA creates its own formatting calls, based on what you paste in.

Your other option would be to parse the HTML, extract the links and put those in adjacent cells. Rough and ready code (in VBA) would look like this:

Public Sub RunMe()
    Dim rng As Range
    Dim sample As String
    Dim parsed As String
    Dim lnk As String
    Dim pt1 As Long, pt2 As Long
    Dim c As Long
    Dim a() As String
    Dim obj As Object
    
    Set rng = Sheet1.Range("A1")
    sample = "<b>Hey This</b> <i>is <a href='http://Test'>Test  </a></i><a href='http://google.com'>http://google.com</a>"
    parsed = sample
    Do While True
        pt1 = InStr(parsed, "<a href=")
        If pt1 = 0 Then Exit Do
        pt2 = InStr(pt1, parsed, "</a>")
        If pt2 = 0 Then Exit Do
        c = c + 1
        lnk = Mid(parsed, pt1 + 8, pt2 - (pt1 + 8))
        parsed = Left(parsed, pt1 - 1) & "{link " & c & "}" & Right(parsed, Len(parsed) - pt2 - 3)
        a = Split(lnk, ">")
        Sheet1.Hyperlinks.Add Anchor:=rng.Offset(, c), Address:=a(0), TextToDisplay:=a(1)
    Loop
    
    Set obj = CreateObject("htmlfile")
    obj.ParentWindow.ClipboardData.SetData "text", "<html>" & parsed & "</html>"
    Sheet1.Paste Destination:=rng
    
End Sub

and it would give you an output like this:

BTW, I don't use Appose.Cell. I suspect you wouldn't need to paste the HTML as I have done in VBA.

Adding Hyperlink on Cell with Rich Text

1 Answers1