Hyperlink with multiple cells / range

Aspose.Cells Product Family
1

I am using the Aspose Cells for .net of version (21.5.0.0).
I am facing and Issue when I create a hyperlink in a worksheet with the range
“\\FileShare\Data\Query Excel\Excel.xlsx#‘Sheet3’!A1:B6”

if its able to create the range for the single cell such as
“\\FileShare\Data\Query Excel\Excel.xlsx#‘Sheet3’!A10”

I am using the method objWS.Hyperlinks.Add(strCellName, 1, 1, objLinkInfo.Address) to add the hyper link in the worksheet.

Is there any other method or way for a range becuase while getting the list of links from the worksheet it is able to get those range hyper links.

2

@harshCIPL22,

Please try our latest version/fix: Aspose.Cells for .NET v24.9.

I tested your scenario/case with latest version/fix using the following sample code and it works fine and as expected.
e.g.,
Sample code:

Workbook workbook = new Workbook();
//Obtaining the reference of the first worksheet.
WorksheetCollection worksheets = workbook.Worksheets;
Worksheet worksheet = worksheets[0];

HyperlinkCollection hyperLinks = worksheet.Hyperlinks;

//Adding a link to the external file
hyperLinks.Add("A5", 1, 1, "Book1.xlsx#'Sheet1'!A1:B6");

//Saving the Excel file
workbook.Save("e:\\test2\\test_hyperlinks1.xlsx");

Please find attached the Book1.xlsx and output test_hyperlinks1.xlsx in the zipped archive.

Please try the above code (just update the file path accordingly). Please place Book1.xlsx in the same folder where output test_hyperlinks1.xlsx will be generated.
files1.zip (13.2 KB)

Please provide more details, sample code and sample files if you still find any issue with latest version/fix (Aspose.Cells for .NET v24.9). We will check your issue soon.

3

Thank you for your quick response.
it is working pretty well in the latest version (24.9.0.0).
Is that a bug in the version that I am using?
can I have the fix or some work around for the version 21.5.0.0.
As I cant upgrade to the latest version for now.

4

@harshCIPL22,

Thanks for your feedback.

It’s good to know that your issue was resolved in the newer version of Aspose.Cells for .NET. The older version you’re using may have had a bug or limitations. Over the years, the APIs have been enhanced to be more robust and feature-rich, so it’s worth trying or upgrading to the latest versions. Unfortunately, there may not be a better way or workaround to cope with it using the older versions. We will still check if there is one. The recommendation is to upgrade to the latest APIs, as any issues found will be addressed in a timely manner.

5

I understand that the latest version of Aspose may come with several improvements. However, I am currently working with large spreadsheets, and upgrading to the latest version is not feasible for me. I have noticed that some new properties have been introduced, while certain older properties have been removed. Updating my code to accommodate these changes would require significant effort, which I cannot afford at the moment. It would be very helpful if there is any workaround or patch available for the version I am currently using.

6

@harshCIPL22

We re-evaluated the code with 21.5 and found the hyperlink works too. Would you please describe what kind of problem you have with hyperlinks and provide us the file you generated with issue so we can look into it?

8

Thank you for drawing attention towards that it also works fine in the 21.5.0.0.
I tried few more test cases on that.

Here is a sample code of mine that works fine for the direct file with the range and not working for the shared path with the range.
My all links will have the shared file paths. if i create the same link with excel it shelf that works pretty fine but not with the Aspose.

If Not IsNothing(objWorkbook) Then
    Me.Cursor = Cursors.WaitCursor
    Dim intHYIndex As Integer = 0
    For Each objWS As Worksheet In objWorkbook.Worksheets
        intHYIndex = objWS.Hyperlinks.Add("H2", 1, 1, "\\harsh\File Share\DEMO Files\Acty_Results_Extractor_VA_to_Moses_v1.21.xlsm#'Control'!A1:B6")
        objWS.Hyperlinks(intHYIndex).TextToDisplay = "A1:B6"
        intHYIndex = objWS.Hyperlinks.Add("H3", 1, 1, "F:\Users\File Share\DEMO Files\Acty_Results_Extractor_VA_to_Moses_v1.21.xlsm#'Control'!A1:B6")
        objWS.Hyperlinks(intHYIndex).TextToDisplay = "A1:B6"
    Next
End If

Also attaching the screen shot that will show you that the what’s the actual issue is.
Screen Shot.png (56.1 KB)

I also tested this scenario with the latest version of Aspose cells too and the same out out come is happening with it too.
One more thing I want to mention that its not based on the extension of any excel file in hyperlink, I also tested with xlsx file to and the behaviour is same that the direct links works fine but not the shared one.
is there any other way for shared path for link creation or i have to set any parameter for that?

9

@harshCIPL22
By creating a sample file and testing it on the latest version v24.9 using the following sample code, we can obtain the correct results. Please refer to the attachment. result.zip (62.6 KB)

The sample code as follows:

Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.Worksheets;
Worksheet worksheet = worksheets[0];
HyperlinkCollection hyperLinks = worksheet.Hyperlinks;


int index = hyperLinks.Add("H2", 1, 1, "\\harsh\\File Share\\DEMO Files\\Acty_Results_Extractor_VA_to_Moses_v1.21.xlsm#'Control'!A1:B6");
hyperLinks[index].TextToDisplay = "A1:B6";

index = hyperLinks.Add("H3", 1, 1, "E:\\VSCellsForm\\sample.xlsx#'Sheet1'!A1:B6");
hyperLinks[index].TextToDisplay = "A1:B6";

workbook.Save(filePath + "out_net.xlsx");

We recommend you to kindly try using our latest release version:
Aspose.Cells for .NET v24.9.

10

@harshCIPL22
For using shared path as hyperlink, would you please try the code:

intHYIndex = objWS.Hyperlinks.Add("H2", 1, 1, "file://harsh\File Share\DEMO Files\Acty_Results_Extractor_VA_to_Moses_v1.21.xlsm#'Control'!A1:B6")
11

Thank you for this solution. It is working pretty well.

12

@harshCIPL22,

You are welcome. I’m glad the suggested solution meets your requirements. Please don’t hesitate to reach out to us if you have any additional questions or feedback.

13

@amjad.sahi
Could you please explain me that why its necessary to specify that “file://” before the path for shared path?

14

@harshCIPL22
“file://” indicates that the path requires the use of the File protocol, which is a local file transfer protocol.

15

@harshCIPL22

In fact when you specifing a shared path for the hyperlink in ms excel, it will use file protocol automatically. In our apis when setting such kind of hyperlink, we do not perform the refactor. We will investigate whether there are some other exceptions for such kind of situation, or such kind of automatic operation may cause any other issue. If not, we will perform this operation automatically too in our later versions. Currently, we think you may do it in this way(specify the file protocol explicitly) as solution for your issue.

16

@harshCIPL22
As an improvement requirement for setting hyperlink with address of shared file, 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-56793

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.

17

@johnson.shi @John.He
Thank you for the detailed explanation for the issue and workaround too.

18

@harshCIPL22
You are welcome. If you have any questions, please feel free to contact us at any time.