calculateFormula not working for SUM() over 128 cells


#1

I am attempting to create a workbook containing cells that compute a SUM() over more than 127 cells; however, the code fails with a NullPointerException when I call workbook.calculateFormula().

Here is some example code:

(When I run the code without calculating formulas, the file is created and the formula/result shows up correctly in Excel)

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

import org.junit.Test;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.FileFormatType;
import com.aspose.cells.OoxmlSaveOptions;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.WorksheetCollection;

public class AsposeSumLimitTest {
	@Test
	public void testAsposeSum() throws Exception {
		File file = new File("../test.xlsx");
		Workbook wb = new Workbook(FileFormatType.XLSX);
		WorksheetCollection worksheets = wb.getWorksheets();
		Cells cells = worksheets.get(0).getCells();
		
		//formula string
		StringBuilder sb = new StringBuilder();
		sb.append("=SUM(");
		
		//fill cells and add to sum formula. calculateFormula() fails for limit >= 128.
		for (int i = 0; i < 128; i++) {
			Cell c = cells.get(i*2, 1);
			c.setValue(i);

			if (i != 0) {
				sb.append(",");
			} 
			sb.append(c.getName());
		}
		sb.append(")");
		
		//set sum formula
		Cell sumCell = cells.get(0, 0);
		sumCell.setFormula(sb.toString());
		
		//error when trying to calculate formula. If this line is commented out, 
		//code works and formula is calculated as expected in excel
		wb.calculateFormula();
		
		OutputStream output = new FileOutputStream(file);
		wb.save(output, new OoxmlSaveOptions(SaveFormat.XLSX));
	}
}

This is the error I see:
com.aspose.cells.CellsException: java.lang.NullPointerException: null("Based on cell "Sheet1!A1)


#2

@syedihuda,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSJAVA-42997 – calculateFormula() fails with large formula strings


#3

@syedihuda,

This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancement and fixes.


#4

@syedihuda,

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

Your issue should be fixed in it.

Let us know your feedback.
aspose-cells-19.8.6-java.zip (6.7 MB)


#5

The issues you have found earlier (filed as CELLSJAVA-42997) have been fixed in Aspose.Cells for Java v19.9. This message was posted using BugNotificationTool from Downloads module by ahsaniqbalsidiqui