Setting array formula to calculate the changed cells' type in Excel worksheet in Java (#1663)

Aspose.Cells Product Family
1

Hi,

when I set array formula on range of cells, cells in that range unexpectedly change theirs type to CellValueType.IS_NULL. Can you help me with this? I am using aspose.cells 17.11.0 for java.

        String path = "LICKAdreslys.xls";
	Workbook workbook = new Workbook(path);
	Worksheet worksheet = workbook.getWorksheets().get(1);
	Cells cells = worksheet.getCells();
        //cells' types before setting array formula
	assertEquals(CellValueType.IS_STRING, cells.get("D93").getType());
	assertEquals(CellValueType.IS_STRING, cells.get("E93").getType());
	assertEquals(CellValueType.IS_DATE_TIME, cells.get("F93").getType());
	Cell cell = cells.get("D93");
	String formula = cell.getFormula();
	CellArea formulaArrayRange = cell.getArrayRange();
	int startRow = formulaArrayRange.StartRow;
	int startColumn = formulaArrayRange.StartColumn;
	int rowCount = formulaArrayRange.EndRow - startRow + 1;
	int columnCount = formulaArrayRange.EndColumn - startColumn + 1;
	cells = cell.getWorksheet().getCells();
	cells.get(startRow, startColumn).setArrayFormula(formula, rowCount, columnCount);
        //cells' types after setting array formula
	assertEquals(CellValueType.IS_STRING, cells.get("D93").getType());  // cell value type becomes CellValueType.IS_NULL
	assertEquals(CellValueType.IS_STRING, cells.get("E93").getType()); // cell value type becomes CellValueType.IS_NULL
	assertEquals(CellValueType.IS_DATE_TIME, cells.get("F93").getType()); // cell value type becomes CellValueType.IS_NULL

There are more array ranges with same behaviour in same worksheet.

LICKAdreslys.xls.zip (83.9 KB)

Regards,
Zeljko

2

@Zeljko,

Thanks for the template file and sample code.

After an initial test, I observed the issue as you mentioned by using your sample code with your template file. I found that by setting array formula causes certain cells’ type to “CellValueType.IS_NULL”:
e.g
Sample code:

String path = "f:\\files\\LICKAdreslys.xls"; 
Workbook workbook = new Workbook(path); 
Worksheet worksheet = workbook.getWorksheets().get(1); 
Cells cells = worksheet.getCells(); 
//cells' types before setting array formula 
System.out.println("CellValueType.IS_STRING: " + cells.get("D93").getType()); 
System.out.println("CellValueType.IS_STRING: " + cells.get("E93").getType()); 
System.out.println("CellValueType.IS_DATE_TIME: " + cells.get("F93").getType()); 
Cell cell = cells.get("D93"); 
String formula = cell.getFormula(); 
CellArea formulaArrayRange = cell.getArrayRange(); 
int startRow = formulaArrayRange.StartRow; 
int startColumn = formulaArrayRange.StartColumn; 
int rowCount = formulaArrayRange.EndRow - startRow + 1; 
int columnCount = formulaArrayRange.EndColumn - startColumn + 1; 
cells = cell.getWorksheet().getCells(); 
cells.get(startRow, startColumn).setArrayFormula(formula, rowCount, columnCount); 
//cells' types after setting array formula 
System.out.println("CellValueType.IS_STRING: " + cells.get("D93").getType()); // cell value type becomes CellValueType.IS_NULL 
System.out.println("CellValueType.IS_STRING: " + cells.get("E93").getType()); // cell value type becomes CellValueType.IS_NULL 
System.out.println("CellValueType.IS_DATE_TIME: " + cells.get("F93").getType());

Output:
_CellValueType.IS_STRING: 5 _
_CellValueType.IS_STRING: 5 _
_CellValueType.IS_DATE_TIME: 1 _
_CellValueType.IS_STRING: 3 //Not Ok _
_CellValueType.IS_STRING: 3 //Not Ok _
CellValueType.IS_DATE_TIME: 3 //Not Ok

I have logged a ticket witha n id “CELLSJAVA-42613” for your issue. We will look into it soon.

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

3

@Zeljko

Thanks for using Aspose APIs.

After setting new formula, old values of cells should be removed. To get the actual value of the cell, you should call calculation APIs such as Workbook.calculateFormula().