Formula value becomes null after changing formula of some other cell (#1278)

Aspose.Cells Product Family
1

Hi,

In excel file, when I set formula in some cell, formula of some other cell (in given example it’s B13) unexpectedly change its value to null. Can you help me with this?
I’m using aspose.cells 16.11.0 for Java.

String path = "tachshiv_trachelium_2015.xlsx";
Workbook workbook = new Workbook(path);
Cells cells = workbook.getWorksheets().get(0).getCells();
Cell cellB12 = cells.get("B12");
Cell cellB13 = cells.get("B13");
System.out.println("Before: " + cellB13.getFormula());
cellB12.setFormula("=VLOOKUP(A12:A204,'C:\\Users\\haname\\Downloads\\index14.xlsm'!index,2,0)");
System.out.println("After: " + cellB13.getFormula());

tachshiv_trachelium_2015.xlsx.zip (52.2 KB)

Regards,
Zeljko

2

@Zeljko

Thanks for using Aspose APIs.

We were able to observe this issue and logged it in our database for investigation and for a fix. Once, the issue is resolved or we have some other news for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-42421 - Formula value becomes null after changing formula of some other cell
3

@Zeljko

Thanks for considering Aspose APIs.

Please change your code as the following:

Java

String path = "D:\\Filetemp\\2015.xlsx"; 

Workbook workbook = new Workbook(path); 

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

Cell cellB12 = cells.get("B12"); 
Cell cellB13 = cells.get("B13"); 

System.out.println("Before: " + cellB13.getFormula()); 

if(cellB12.isInArray()) 
{ 
	CellArea ca = cellB12.getArrayRange(); 

	cells.get(ca.StartRow, ca.StartColumn).setArrayFormula("=VLOOKUP(A12:A204,'C:\\Users\\haname\\Downloads\\index14.xlsm'!index,2,0)", ca.EndRow - ca.StartRow + 1, ca.EndColumn - ca.StartColumn + 1); 
}
else 
{ 
	cellB12.setFormula("=VLOOKUP(A12:A204,'C:\\Users\\haname\\Downloads\\index14.xlsm'!index,2,0)"); 
} 

System.out.println("After: " + cellB13.getFormula());

The Cell B12 is inside Array Formula. So if you want to change the formula of the cell, please change the whole array formula.

We will throw an exception if changing the part of the array formula later.

4

@Zeljko

Thanks for using Aspose APIs.

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

5

@shakeel.faiz

I tried it with 18.3.9 and there is an adequate exception now, thank you.

Regards

6

@Zeljko

Thanks for your feedback and using Aspose APIs.

It is good to know that the new version throws appropriate exception now. Hopefully, it will be helpful for you. Let us know if you encounter any other issue, we will be glad to help you further.