HYPERLINK function

Aspose.Cells Product Family
1

Hi.


Help me please with HYPERLINK function. How i can retrieve a html-URL form cell, that contains (Excel’s native function) HYPERLINK function.

@Test
public void hyperlinkTest() throws Exception {
    Workbook wb = new Workbook(“D://in.xlsx”);

    Cells cells = wb.getWorksheets().get(0).getCells();

    System.out.println(cells.get(“A1”).getStringValue());

    wb.save(“D://out.xlsx”);
}
2

Hi,


Thanks for the template file and sample code.

Well, as your hyperlink is obtained by HYPERLINK formula/function, so there is no better way to extract the exact URL (considering the fact that those parts are again coming from different cells to be merged via formulas etc.). I am afraid, you have to devise your own method and write your own code to accomplish the task via using Cell’s precedents. I have written a sample code that extracts different parts including URL, please refer to it and you may write your own codes accordingly.
e.g
Sample code:

Workbook wb = new Workbook(“in.xlsx”);
Cells cells = wb.getWorksheets().get(0).getCells();
System.out.println(cells.get(“A1”).getStringValue());
Cell cell = cells.get(“A1”);

if(cell.isFormula())
{

//Tracing precedents of the cell A1.
ReferredAreaCollection ret = cell.getPrecedents();

//Printing all the precedents with their data
if(ret != null)
{
for(int m = 0 ; m < ret.getCount(); m++)
{
ReferredArea area = ret.get(m);
StringBuilder stringBuilder = new StringBuilder();
String cellname = CellsHelper.cellIndexToName(area.getStartRow(), area.getStartColumn());
wb.calculateFormula();
System.out.println("Cell name: " + cellname + ", value: " +cells.get(cellname).getStringValue());

}

}
}


wb.save(“out1.xlsx”);

Also, see the document on Tracing Precedents and Dependents for your reference:


Thanks.

3

Hi,


Thanks for sample code, but i afraid, that it is unacceptable way, beacuse there is many ways to declare HYPERLINK function (concatenaiting a several strings, strings and cells, cells, nested formulas, etc). And i can’t predict a right way for each case.

Probably, a right way for this was to extend Worksheet’s hyperlinks collection? Can you please deliberate this functionality with developers? Or add some additional cell’s information field (for example “Attributes”), where i could find out reference value.

UPD: Probably could help me, if you provide a way for override calculation of this function in ICustomFunction (At this moment i unable to do this)

Best regards. Alexey
4

I think it is required for you too, beacuse method 


cells.get(cellname).getHtmlString()
returns for hyperlink cell, html without tag.


5

Hi,


Thanks for providing further details.

Do you want us to extend the Worksheet’s Hyperlink collection to get this type of hyperlink object (rendered from HYPERLINK formula/function) or you need us to enhance Cell.HtmlString attribute so it could retrieve Hyperlink (via ?

Once you confirm, we may log a ticket appropriately into our database for investigation if we could implement the feature.

Thank you.
6
Hi.

I prefer a first way - extend hyperlinks collection by HYPERLINK function arguments.

Best regards. Alexey
7

Hi,


Thanks for your feedback.

I have logged a ticket with an id “CELLSJAVA-41334” for your requested feature. We will check if we could include this enhancement, i.e., extend the Worksheet’s Hyperlink collection to get this type of hyperlink object (rendered from HYPERLINK formula/function) as well. The product team will investigate and look into it soon.

Once we have any update on it, we will let you know here.

Thank you.


8
Hi,

Please try our latest version/fix: Aspose.Cells for Java v8.4.2.1 (attached).

We will call ICustomFunction.CalculateCustomFunction when calculating HYPERLINK function.

Thank you.


9

Hi. You have broke this feature for version 8.4.2.5


Can you fix it?

Best regards. Alexey
10
Hi,

makarovalv:
Hi. You have broke this feature for version 8.4.2.5

Can you fix it?

Best regards. Alexey

Could you elaborate and provide sample code (runnable) and template file on the broken feature while using v8.4.2.5 (JAVA) to show the issue, we will check it soon.

Thank you.
11
New feature assumes, that custom calculation will be called for HYPERLINK function. But for version 8.4.2.5 (java) it not happens



@Test
public void hyperlinkTest() throws Exception {
ClassPathResource resource = new ClassPathResource("hyperlink.xlsx", this.getClass());
Workbook workbook = new Workbook(resource.getInputStream());

ICustomFunction customFunction = new ICustomFunction() {

@Override
public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList arrayList1) {

System.out.println(s);

return null;
}
};

workbook.calculateFormula(true, customFunction);

}
12

Hi,


Thanks for the template file and sample code:

You are right. When using our latest version/fix: Aspose.Cells for Java v8.4.2.5, this is not working anymore, it was working fine though using one the previous fix v8.4.2.1 (in which which actually supported this enhancement). I used the following sample code with your newly attached file:
e.g
Sample code:

Workbook workbook = new Workbook(“hyperlink.xlsx”);

ICustomFunction customFunction = new ICustomFunction() {

@Override
public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList arrayList1) {

System.out.println(s);

return null;
}
};

workbook.calculateFormula(true, customFunction); //null

I have reopened your issue “CELLSJAVA-41334” again. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.
13

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for Java v8.4.2.8 and let us know your feedback.

14

Hi. Now it works correctly


Best regards
15

Hi,


Thanks for your feedback.

Good to know that your issue is sorted out by the new fix, we have closed your ticket. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.
16

The issues you have found earlier (filed as CELLSJAVA-41334) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
17

Hi,


Just FYI:
From the version/fix v8.7.1.4 and later versions, to support your special requirement, your code should be changed to:
e.g
Sample code:

ICustomFunction customFunction = new AbstractCustomFunction(){
@Override
public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList arrayList1) {

System.out.println(s);

return null;
}
@Override
public boolean isSupported(String funcName)
{
return “HYPERLINK”.equalsIgnoreCase(funcName);
}
};

Please ignore this if you don’t upgrade to latest versions/fixes.

Thank you.

18

Hi.


Thanks for information. Should i use a method “isSupported” only for default functions, that i want override, either i should use this method for all custom functions?

Best regards. Alexey
19

Hi Alexey,


In order to simplify the implementation of AbstractCustomFunction, you may use this method only for those special default functions that you want to override the calculation logic. Of course, list all supported functions (including default as well as user custom) should also work fine.
20

Hi. Today i have updated up to version 8.7.1.4, but ICustomFunction does not contains a method isSupported.


Is it correct?

Best regards. Alexey