Free Support Forum - aspose.com

Upgrade issue with Aspose Cells v17.11

I’m trying to provide a hyperlink url to the excel cell value, Same piece of code working fine for aspose cells 8.3.0 and now upgraded to 17.11 version.

Sharing the sample code for your reference.

cellValue = cellValue.replaceAll("\"", "\"\"");
cellValue = ExcelConstants.linkUrlFormula.replace("cellValue", cellValue);
cellValue = cellValue.replace("linkUrl", url);
              //cellValue =HYPERLINK("https://www.google.co.in","Search Engine")
cell.setFormula(cellValue);

Getting the ArrayIndexOutOfBoundsException: -1 while setting the hyperlink formula to excel cell.

Can somebody help me out of this ?

@prasad4aspose,

Thanks for providing us some details.

Could you try our latest version/fix: Aspose.Cells for Java v18.3 if it makes any difference.

If you still find the issue with v18.3.x, kindly provide us your sample Java program (runnable) with template file (if any) to reproduce the issue on our end, we will check it soon.

Hi Amjad,

Thank you for the quick response :slight_smile:

I will try it out the suggested version and update whether it will solve the issue or not.

@prasad4aspose

Thanks for using Aspose APIs.

We tested this issue with the following sample code. It works fine. Please see the output Excel file generated by the code and screenshot for a reference.

Download Link:
Output Excel File.zip (6.3 KB)

Java

Workbook wb = new Workbook();

Worksheet ws = wb.getWorksheets().get(0);

Cell cell = ws.getCells().get("B4");

String cellValue = "=HYPERLINK(\"https://www.google.co.in\",\"Search Engine\")";
cell.setFormula(cellValue);

wb.save(dirPath + "output.xlsx"); 

Screenshot:

Hi @shakeel.faiz @Amjad_Sahi,

Upgrading to 18.3 solved the issue.

I have had many questions answered, The community of Aspose Forum is what makes it such a helpful and powerful resource.

Thank you :smile:

1 Like

@prasad4aspose

Thanks for your feedback and appreciating Aspose Team.

Please feel free to ask any other question, we will be glad to help you further. Have a good day. :slight_smile:

Hi Team,

Can you conform that you tested the sample code with Aspose.Cells 17.11 or 18.3 ?

Thank you.

@prasad4aspose

The code we shared in previous posts works fine on both versions as we have tested on both versions. Please see the output Excel files generated with both versions for a reference.

Download Link:
Output Excel Files with 17.11 and 18.3.zip (12.5 KB)


Please also add this line so that Aspose.Cells performs formula calculation before saving the workbook.

wb.calculateFormula();

So the correct code will be like this.

Java

Workbook wb = new Workbook();

Worksheet ws = wb.getWorksheets().get(0);

Cell cell = ws.getCells().get("B4");

String cellValue = "=HYPERLINK(\"https://www.google.co.in\",\"Search Engine\")";
cell.setFormula(cellValue);

wb.calculateFormula();

wb.save(dirPath + CellsHelper.getVersion() + "-output.xlsx");

Hi @shakeel.faiz :raised_hand:,

For us 8.3 and 18.3 worked fine without any code changes, 17.11 giving the issue.

We will test with suggested formula calculation before saving the workbook.

Thank you.

@prasad4aspose

Please look into it why 17.11 is not working fine at your end. It should work fine as we have tested the code with 17.11 also. If you still find any issue, please isolate your code segment and provide us the runnable sample code that we could run at our end and replicate the issue. We will then look into it further and help you asap.

Hi @shakeel.faiz,

Currently we are working on that, I will update the status once i have done.

Thank you :+1:

Hi @shakeel.faiz,

This issue is with the 17.11, I tried your suggestion but we are getting ArrayIndexOutOfBoundsException: -1 when we are setting the Cell Value.

cellValue = cellValue.replaceAll("\"", "\"\"");
cellValue = ExcelConstants.linkUrlFormula.replace("cellValue", cellValue);
cellValue = cellValue.replace("linkUrl", url);
              //cellValue =HYPERLINK("https://www.google.co.in","Search Engine")
cell.setFormula(cellValue);

Note:- we are overriding public void startCell(Cell cell) Aspose Api method from this method we are getting cell.

@kvishnu

We are afraid, you provided us the code snippet not the runnable code. When we try to run your code, it gives compile time errors and we are unable to replicate your issue.

However, we tested this issue with the following code. Please check the output Excel file i.e.

17.11.0-output.xlsx

generated by the 17.11 for your reference. If you open it in Microsoft Excel, you will see, it is a correct file. It means, the error is inside your code. You must debug your code and remove the error by yourself.

Java

String cellValue = "=HYPERLINK(\"https://www.google.co.in\",\"Search Engine\")";
//cellValue = cellValue.replaceAll("\"", "\"\"");
//cellValue = ExcelConstants.linkUrlFormula.replace("cellValue", cellValue);
//cellValue = cellValue.replace("linkUrl", url);
			  //cellValue =HYPERLINK("https://www.google.co.in","Search Engine")

System.out.println(cellValue);

//Aspose.Cells code starts from here.
//----------------------------------------
Workbook wb = new Workbook();

Worksheet ws  = wb.getWorksheets().get(0);

Cell cell = ws.getCells().get("B4");
cell.setFormula(cellValue);

wb.calculateFormula();

wb.save(dirPath + CellsHelper.getVersion() +  "-output.xlsx");
//----------------------------------------

Download Link:
17.11.0-output.zip (6.2 KB)

Hey @shakeel.faiz and @Amjad_Sahi ,

Issues is in code that we found and fixed not with the 17.11 API.

Thank you so much guys for your immediate response. 

Love to use Aspose cells :heart_eyes:

@prasad4aspose

Thanks for loving Aspose APIs and Aspose Support. Regards. :slight_smile: