Hyperlinks with encoded symbols decode after re-saving

Hello. I have founded that Aspose.Cells breaks hyperlinks.
Latest Tomcat’s version don’t allow to use several symbols in URL. For example, symbol “”, that used in domain names for example. So we encode that symbols, but after re-saving workbook, that symbols decode automatically. Please check example below

@Test
public void linksTest() throws Exception {
    Workbook workbook = new Workbook();
    Worksheet worksheet = workbook.getWorksheets().get(0);

    String sourceLink = "http://someaddress.ru?user=OIS%5cavmakarov";

    worksheet.getHyperlinks().add("A1", 1, 1, sourceLink);
    assertEquals("First check", sourceLink, worksheet.getHyperlinks().get(0).getAddress());

    workbook.save("D://in.xlsx");

    workbook = new Workbook("D://in.xlsx");
    worksheet = workbook.getWorksheets().get(0);
    assertEquals("Second check", sourceLink, worksheet.getHyperlinks().get(0).getAddress());

}

@makarovalv,

Thanks for the sample code and details.

After an initial test, I observed the issue as you mentioned by using your sample code. I found that Hyperlinks with encoded symbols (such as, “%5c”) decode after re-saving:
e.g
Sample code:

Workbook workbook = new Workbook(); 
Worksheet worksheet = workbook.getWorksheets().get(0); 

String sourceLink = "http://someaddress.ru?user=OIS%5cavmakarov"; 

worksheet.getHyperlinks().add("A1", 1, 1, sourceLink); 
System.out.println("First check : " + sourceLink + " : " + worksheet.getHyperlinks().get(0).getAddress()); 

workbook.save("f://files//out1.xlsx"); 

workbook = new Workbook("f://files//out1.xlsx"); 
worksheet = workbook.getWorksheets().get(0); 
System.out.println("Second check: " + sourceLink + " : " + worksheet.getHyperlinks().get(0).getAddress()); 

I have logged a ticket with an id “CELLSJAVA-42624” for your issue. We will look into it soon.

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

@makarovalv

Thanks for using Aspose APIs.

This is to inform you that we have fixed your issue CELLSJAVA-42624 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

@makarovalv

Please download and try the following fix and let us know your feedback.

Hi. Thank you for update, but now CustomFunction does’t work for XLSX files

@makarovalv,

Kindly provide your sample code (runnable) or preferably a simple console application with template file(s) (you may zip it prior attaching here) to reproduce the issue, we will check it soon.

Hi.

There is some more complex behavior, than i assumed. First time book recalculate, but after it some happens with book, that it became to incalculable state.
I have saved this book and tried to recalculate it by test app, and it does not recalculate too.
in.zip (6.4 KB)
You can see it by test (there is no any request custom function resolver)

public static class CustomFuncImpl implements ICustomFunction {

    private String val;

    public CustomFuncImpl(String val) {
        this.val = val;
    }

    @Override
    public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList arrayList1) {
        System.out.println(s);
        return null;
    }

    public void setVal(String val) {
        this.val = val;
    }
}

@Test
public void recalcTest() throws Exception {
    Workbook workbook = new Workbook("D://in.xlsx");

    CustomFuncImpl impl = new CustomFuncImpl("1");

    CalculationOptions calcOptions = new CalculationOptions();
    calcOptions.setIgnoreError(true);
    calcOptions.setCustomFunction(impl);
    workbook.calculateFormula(calcOptions);

    System.out.println(workbook.getWorksheets().get(0).getCells().get("A1"));

    impl.setVal("2");
    workbook.calculateFormula(calcOptions);

    System.out.println(workbook.getWorksheets().get(0).getCells().get("A1"));

}

@makarovalv,

Thanks for the sample code and template file.

After an initial test, I am able to observe the issue as you mentioned by using your sample code with your template file. I found that CustomFunction does not work for the XLSX file, Does this scenario/case works fine with any older version that you were using before?

I have logged a ticket with an id “CELLSJAVA-42633” for your issue. We will look into it soon.

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

Hi. No, this template now does not recalculated even older versions. Let’s i explain you how i got it:

  • our program get some template and process it;
  • in that process it recalculates book several times with different flags
  • older version works fine - book recalculated every time
  • but when i upgraded library version, i found that now template recalculates only once and it breaks down all our lifecycle
  • i tried to reproduce this issue on simple test, but i failed on that
  • so i took template in the middle of our lifecycle, save it like file and tried to recalculate it. And i failed to do that.

Probably some our actions broke that workbook? Can you provide more information about some flags of that book, because library is obfuscated and i can’t understand why book not recalculated now.

Best regards. Alexey

@makarovalv,

Thanks for providing further details.

Could you try to add the line just after opening/initializing the workbook/file in your actual program/scenario if it makes any difference:
e.g
Sample code:

.........
   workbook.getSettings().setCreateCalcChain(false);

Hi. There is no difference between code with

workbook.getSettings().setCreateCalcChain(false);

and without it.

Best regards. Alexey

@makarovalv,

Well, there is not any formula in in.xlsx. We think maybe you should check yourself whether there are some reasons behind that cause formulas lost. I am afraid, we have no clue why the formulas are not re-calculated in your specific program on your end.

The issues you have found earlier (filed as CELLSJAVA-42624) have been fixed in Aspose.Cells for Java 18.6. Please also see the document for your reference: Installation|Documentation