Adding links to a range in Aspose.Cells

Aspose.Cells Product Family
1

Howdy!
I have no problems with adding links (or any other Html string) to a cell. I just use:
cell.HtmlString = myHtmlString
syntax.
However, doing the same on a cell range seems to be more complex.
Say, I want to add this string to a range: “Please go to Google ”.
First off, range does not have “HtmlString” property so “cell” syntax as listed above does not work.
I tried to add value to the first cell in range or to all cells within range and when I do this I lose link, that is I just see text “Please go to Google” value. I could use worksheet.Hyperlinks.Add method - this one works but presents problems since the “Add” method accepts link url and display text only. In words, I would have to add “manually” first part of text to the cell(s) on the left. This would be cumbersome at best.
Anyhow, is there a better way of doing this?
How does one sets html string value to a range?
May be one should use “PutValue” method on a range with some specific flags?
Or, who knows, it is done via HtmlSaveOptions class?
Thank you for your prompt answer.

3

@bogdanw
We noticed that the problem you mentioned does exist. Thank you for your suggestion, we will optimize it in the future. We have created ticket CELLSNET-54317 to track this issue and will notify you as soon as we make progress.

4

@bogdanw
When calling worksheet.Hyperlinks.Add() method, the first cell will be automatically updated.
Can you explain your need with same codes and excepted file? We will check it soon.

5

Below is code snipped illustrating the problem.
It uses Aspose.Cells v. 23.9.0.0.
Note please that the text we want to add to range contains link.
This link is not displayed in correct format.
The language is C# v. 7.0.

        var myDir = @"YourDirHere";
        var textWithLink = @"Please click <a href=""https://www.google.com""> Google </a> to do search";
        Workbook wb = new Workbook();
        Worksheet ws = wb.Worksheets[0];
        
        var firstRange = ws.Cells.CreateRange(1, 0, 1, 3);
        firstRange.Name = "firstRange";
        firstRange.PutValue(textWithLink, false, false); // Link displayed as text

        ws.Cells[2, 0].HtmlString = textWithLink;
        var secondRange = ws.Cells.CreateRange(2, 0, 1, 3);
        secondRange.Name = "secondRange";   //No link at all though its display text shows up.

        wb.Save(myDir + "YourFileNameHere", SaveFormat.Xlsx);
        wb.Dispose();
6

@bogdanw,

Aspose.Cells follows MS Excel standards/rules. We have found that MS Excel does not support setting a hyperlink to partial text within a cell. MS Excel only supports applying a hyperlink to the whole cell, so the hyperlink cannot be a part of the cell’s value. You can confirm this behavior in MS Excel by manually adding a hyperlink to cell text. In short, Aspose.Cells supports adding a hyperlink to the whole cell instead of a part of the text.

So, your hyperlink ("Please click <a href=""https://www.google.com""> Google </a> to do search") is not right. That’s why you are getting ordinary text when setting it via HtmlString in code.

For your code segment:

var firstRange = ws.Cells.CreateRange(1, 0, 1, 3);
firstRange.Name = "firstRange";
firstRange.PutValue(textWithLink, false, false); // Link displayed as text

Please note, Cell.PutValue method is used to set or insert data/content/strings into the cell, so it won’t parse HTML tags, rather HTML tags would be pasted as string/text. This is correct behavior for Cell.PutValue method.

For your second part to set HTML string to a cell will work if your HTML string is valid. Please use the following sample code segment, it works fine as I tested:

var myDir = "g:\\test2\\";
var textWithLink = @"<html><a href=""https://www.google.com"">Please click Google to do search</a></html>";
Workbook wb = new Workbook();
Worksheet ws = wb.Worksheets[0];

ws.Cells[2, 0].HtmlString = textWithLink;
var secondRange = ws.Cells.CreateRange(2, 0, 1, 3);
secondRange.Name = "secondRange";   

wb.Save(myDir + "out1.xlsx", SaveFormat.Xlsx);
wb.Dispose();
7

The issues you have found earlier (filed as CELLSNET-54317) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi