Proper way to apply long hyperlinks to Template?

Aspose.Cells Product Family
1

I have a template with a column that is supposed to contain a long (>1024 characters) hyperlink, which is pulled from a dataset in C# code.

The problem with using a smart marker and injecting the value as an argument in =HYPERLINK() is that the URL size will always blow the limit this function imposes in Excel (no longer than a few hundred characters).

So far the only way to create a proper hyperlink that worked is an embedded (Insert > Link), which has no character limit. But so far I haven’t found a way to use this properly in the template that is populated by the dataset containing different links.

I am aware that it is possible to create hyperlinks via code with:
cell.PutValue(“Click me”);
worksheet.Hyperlinks.Add(cell.Name, 1, 1, url);

But this approach involves iterating all rows, which is having a serious performance cost when compared to just using a regular template.

Is there any other approach that I am not considering here?

2

@jfdowsley

Can you please clarify if you are looking for a solution that avoids iterating through all rows while still allowing for long hyperlinks in your template? Additionally, please specify if you are using Aspose.Cells for Java or .NET.

3

I am using Aspose.Cells for .NET

Am looking for a solution that avoids iterating through all rows (if necessary for performance), as long as it allows me to create a clickable hyperlink from a very long URL, which right now Excel is not accepting through =HYPERLINK.

4

@jfdowsley,

Aspose.Cells follows MS Excel standards and specifications for hyperlinks. I am afraid, generally, you have to first process the smart markers to get hyperlinks to fill the data into the respective cells of the column(s) in the sheet and then manually add hyperlinks looping through the individual cells using the Aspose.Cells APIs. Please see the document for your reference on how to add hyperlinks to the cells:
https://docs.aspose.com/cells/net/insert-hyperlinks-to-excel/

If you find any performance issue, kindly do provide a sample app and resource files, we will check it soon.

5

@jfdowsley
We think maybe you want to create hyperlinks while importing data by smart marker feature of Aspose.Cells. If so, HYPERLINK function cannot work because it is limit of ms excel. But we may consider to provide a new marker for creating hyperlink for smart marker feature. Please confirm whether it can fit your requirement so we can make further investigation.

1 Like
6

If you mean using Smart Markers in a way that bypasses URL limit (maybe creating an embedded hyperlink, which I know its possible via manual editing of the XLSL), yes, that would be an amazing feature and would fit our case.

We have dozens of thousands of rows that need to contain a clickable AWS S3 Pre-signed link. Their token always make them longer than =HYPERLINK() can take, and shortening them would incur costs in one way or another.

Our only solution right now is populating the dataset with the URLs, then using it to populate the template with clear text links. After that, we iterate of the workbook and create embedded hyperlinks (via hyperlink.Add) from these cells, but this has severely decreased the function performance to the point where its simply unfeasible.

7

@jfdowsley,

Thanks for your confirmation and providing further details.

We will evaluate your requirements and may provide a new marker or relevant parameter for adding/creating hyperlinks in smart markers. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57087

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

8

@jfdowsley,

We are pleased to inform that your issue (Ticket: “CELLSNET-57087”) has been resolved. The enhancement will be included in our upcoming release (Aspose.Cells v24.11) that we plan to release in the first half of November 2024. You will be notified when the next version is released.

1 Like
9

Appreciate it!

10

@jfdowsley,

You are welcome. We will keep you posted with updates on your issue (once available).

11

@jfdowsley
We added a hyperlink smart marker and also supported dynabic display text. Please check the following codes:

public void CELLSNET53816()
{
var dt = new DataTable("dt");
dt.Columns.Add("[test.]", typeof(string));
dt.Columns.Add("[a/b]", typeof(string));
dt.Columns.Add("links", typeof(string));
dt.Columns.Add("linkslabel", typeof(string));

dt.Rows.Add(new object[] { "a", "b" ,"www.aspose.com", "aspose" });
dt.Rows.Add(new object[] { "10", "20", "www.google.com", "google" });

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells["A1"].PutValue("&=dt.[test.]");
worksheet.Cells["B1"].PutValue("&=dt.[a/b]");
worksheet.Cells["C1"].PutValue("&=dt.links(hyperlink)");//CELLSNET-57087
worksheet.Cells["d1"].PutValue("&=dt.links(hyperlink:&=dt.linkslabel)");//CELLSNET-57087
WorkbookDesigner wd = new WorkbookDesigner();
wd.Workbook = workbook;
wd.SetDataSource(dt);
wd.Process(true);
Assert.AreEqual("20", worksheet.Cells["B2"].StringValue);
Assert.AreEqual("www.google.com", worksheet.Cells["C2"].StringValue);
Assert.AreEqual("google", worksheet.Cells["D2"].StringValue);
workbook.Save(Constants.destPath + "CELLSNET53816.xlsx");
}
1 Like
12

Awesome. Thank you for notifying me.

Very eager for the release.

13

@jfdowsley,

You are welcome.

14

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