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)