Hi,
Attached template has ~1000 data rows that contain formulas. It was created using Aspose Cells and has setting for transition formula evaluation on (Transition Formula Evaluation support).
We use the following code to get calculated values for the cells:
<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”>@Test
public void calcTest() throws Exception {
Workbook wb = new Workbook(“C:/temp/calc_template.xlsx”);
long t = System.currentTimeMillis();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();
int lastRow = Math.max(0, cells.getMaxDataRow());
int lastCol = Math.max(0, cells.getMaxDataColumn());
int formulasCount = 0;
for (int rowIndex = 0; rowIndex < lastRow; rowIndex++) {
for (int colIndex = 0; colIndex < lastCol; colIndex++) {
String formula = cells.get(rowIndex, colIndex).getFormula();
if (formula != null && !formula.isEmpty()) {
// Prepare cell to get calculated value
Cell cell = getCellForRead(wb, cells, rowIndex, colIndex);
// Read cell value
String value = cell.getDisplayStringValue();
formulasCount++;
}
}
}
System.out.println("Total calc time: " + (System.currentTimeMillis() - t) / 1000 + “s”);
System.out.println("Total formulas: " + formulasCount);
}
public void calcTest() throws Exception {
Workbook wb = new Workbook(“C:/temp/calc_template.xlsx”);
long t = System.currentTimeMillis();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();
int lastRow = Math.max(0, cells.getMaxDataRow());
int lastCol = Math.max(0, cells.getMaxDataColumn());
int formulasCount = 0;
for (int rowIndex = 0; rowIndex < lastRow; rowIndex++) {
for (int colIndex = 0; colIndex < lastCol; colIndex++) {
String formula = cells.get(rowIndex, colIndex).getFormula();
if (formula != null && !formula.isEmpty()) {
// Prepare cell to get calculated value
Cell cell = getCellForRead(wb, cells, rowIndex, colIndex);
// Read cell value
String value = cell.getDisplayStringValue();
formulasCount++;
}
}
}
System.out.println("Total calc time: " + (System.currentTimeMillis() - t) / 1000 + “s”);
System.out.println("Total formulas: " + formulasCount);
}
<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”>private Cell getCellForRead(Workbook wb, Cells cells, int row, int col) throws Exception {
Cell cell = cells.get(row, col);
prepareCellToRead(wb, cell);
return cell;
}
private void prepareCellToRead(Workbook wb, Cell cell) throws Exception {
if (wb.getSettings().getCalcMode() == CalcModeType.AUTOMATIC) {
if (cell.getFormula() != null) {
ReferredAreaCollection precedents = cell.getPrecedents();
if (precedents != null) {
for (int i = 0; i < precedents.getCount(); i++) {
ReferredArea area = precedents.get(i);
if (area.getExternalFileName() != null) {
throw new Exception(“Formula refers to an external source”);
}
Cells areaSheetCells = wb.getWorksheets().get(area.getSheetName()).getCells();
int endRow = area.getEndRow() > areaSheetCells.getMaxRow() ? areaSheetCells.getMaxRow() : area.getEndRow();
int endColumn = area.getEndColumn() > areaSheetCells.getMaxColumn() ? areaSheetCells.getMaxColumn() : area.getEndColumn();
if (area.isArea()) {
for (int row = area.getStartRow(); row <= endRow; row++) {
for (int col = area.getStartColumn(); col <= endColumn; col++) {
prepareCellToRead(wb, areaSheetCells.get(row, col));
}
}
} else {
prepareCellToRead(wb, areaSheetCells.get(area.getStartRow(), area.getStartColumn()));
}
}
}
cell.calculate(true, null);
}
}
}
<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”><span style=“font-family: “Times New Roman”; white-space: normal;”>Using Aspose Cells version 8.7.1 output looks as follows:<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”>
Cell cell = cells.get(row, col);
prepareCellToRead(wb, cell);
return cell;
}
private void prepareCellToRead(Workbook wb, Cell cell) throws Exception {
if (wb.getSettings().getCalcMode() == CalcModeType.AUTOMATIC) {
if (cell.getFormula() != null) {
ReferredAreaCollection precedents = cell.getPrecedents();
if (precedents != null) {
for (int i = 0; i < precedents.getCount(); i++) {
ReferredArea area = precedents.get(i);
if (area.getExternalFileName() != null) {
throw new Exception(“Formula refers to an external source”);
}
Cells areaSheetCells = wb.getWorksheets().get(area.getSheetName()).getCells();
int endRow = area.getEndRow() > areaSheetCells.getMaxRow() ? areaSheetCells.getMaxRow() : area.getEndRow();
int endColumn = area.getEndColumn() > areaSheetCells.getMaxColumn() ? areaSheetCells.getMaxColumn() : area.getEndColumn();
if (area.isArea()) {
for (int row = area.getStartRow(); row <= endRow; row++) {
for (int col = area.getStartColumn(); col <= endColumn; col++) {
prepareCellToRead(wb, areaSheetCells.get(row, col));
}
}
} else {
prepareCellToRead(wb, areaSheetCells.get(area.getStartRow(), area.getStartColumn()));
}
}
}
cell.calculate(true, null);
}
}
}
<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”><span style=“font-family: “Times New Roman”; white-space: normal;”>Using Aspose Cells version 8.7.1 output looks as follows:<pre style=“font-family: “Courier New”; background-color: rgb(255, 255, 255);”>
Total calc time: 6s
Total formulas: 2384
<span style=“font-family: “Times New Roman”; white-space: normal;”>
<span style=“font-family: “Times New Roman”; white-space: normal;”>Using Aspose Cells version 8.7.2 and above (including latest 8.9.0) output looks as follows:
<div style=“font-family: “Times New Roman”; white-space: normal;”>Total calc time: 266sTotal formulas: 2384Calculation time is significantly increased. It seems that new custom calculation engine that was added in version <span style=“font-family: “Times New Roman”; white-space: normal;”>8.7.2 is the result of the problem. Release notes of 8.7.2 version state the following:…Adds CalculationOptions.CustomEngine property.
Allow user to use the new custom calculation engine to calculate formulas.
<span style=“font-family: “Times New Roman”; white-space: normal;”>…<span style=“font-family: “Times New Roman”; white-space: normal;”>Could you scrutinize the issue and fix it?Thanks.