Calculations formulas

Aspose.Cells Product Family
1

Hi.


I have found, that Aspose.Cells will not fire recalc event in your model, until i will not call Workbook.recalc() method. May be there is exists method for recalc last changed chains (without book-wild recalculation)?

public void autoRecalcTest() {
try {
Workbook workbook = new Workbook(“D:\book1.xls”);
Worksheet sheet = workbook.getWorksheets().get(0);

Cell b2 = sheet.getCells().get(“B2”);
Cell d2 = sheet.getCells().get(“D2”);

b2.putValue(“50”, true);

System.out.print(d2.getDisplayStringValue());

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

I want to retrieve value “70”, but now it print value "30"

P.S. Can you please explain purpose of following settings, if i should call recalc method every time?
workbook.getSettings().setCalcMode(CalcModeType.AUTOMATIC);

P.P.S May be you have some method for recalc some range or cell separate?


Best regards. Alexey

2

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

Workbook.getSettings().setCalcMode() actually sets the calculation mode of MS-Excel for the given file. Please see the screenshot for your reference. It does not make Aspose.Cells to calculate the formulas automatically. In order to make Aspose.Cells calculate the formula, you need to use Workbook.calculateFormula() method.

You can also try Cell.calculate() method to calculate the formula of one cell only if you do not want to use Workbook.calculateFormula() method.

3

Hi Alexey,


Also, we recommend you to kindly see the documents/articles for your further reference:


Thank you.
4
Hi

Do you have a plan for refactor your calculation engine? We have a very complex engine, that based on Excel books. Earlier we have used Actuate Spreadsheet Engine, but it is freezed and not evolved. Now we choosed Aspose.Cells, but your engine don't recalculated values on fly. We should to call calculateFormulas method for recalculation values.

Now we ready to do some steps for develop inhouse way for recalculation formulas on the fly, but we have found that Cell's hashCode are not identical after operations with sheet:

@Test
public void calculationChainTest() {
try {
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();

Cell a5 = cells.get("A5");
Cell a15 = cells.get("A15");

cells.insertRows(5,1);

Cell a5_1 = cells.get("A5");
Cell a16_1 = cells.get("A16");

assertEquals("A5", true, a5.equals(a5_1));
assertEquals("A15", true, a15.equals(a16_1));

assertEquals("A5 hash", true, a5.hashCode() == a5_1.hashCode());
assertEquals("A15 hash", true, a15.hashCode() == a16_1.hashCode());

} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}

Can you please fix hashCode calculation method (equals works now)
5

Hi,


Thanks for using Aspose.Cells.

Well, surely, you need to call calculateFormula() that will calculate all the formulas in Workbook. If you have reservations with the method, you may adopt an approach i.e., Call Workbook.calculateFormula() method with WorkbookSettings.createCalcChain() API to set it to true ( default it is false), now it will only calculate/recalculate those formulas for the cells that have not been calculated or need to be recalculated because their precedents have been changed for values etc.

Moreover, you may try to use Worksheet.calculateFormula() that would calculate a specific worksheet formulas only. Also, you may try even Cell.calculate() method to calculate the formula for the single cell only.

Now regarding the comparisons of hashCode calculations of the cells, we will look into it and get back to you soon. Apparently the hash codes are not equal for the cells.

Thank you.



6

Hi guys. Do you have some method for recalc range?


Something like this

public void calculationChainTest() {
try {
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cells cells = ws.getCells();

Range range = cells.createRange(0, 0, 10, 10);
//TODO
range.calculate(true, MyCustomResolver);


} catch (Exception e) {
e.printStackTrace();
assertNull(e);
}
}
7

Hi,


Well, I am afraid, there is no such method to calculate the range in one go. You may either use Worksheet.calculateFormula() to calculate formulas in the sheet or loop through the cells in the range and call Cell.calculate() method for the cells in the area.

And, we have evaluated your issue regarding hash codes which are not equal for the cells. It looks like an issue with the product. I have logged a ticket with an id “CELLSJAVA-40796” for your issue. We will look into it to figure it out soon.

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

Thank you.



8

The issues you have found earlier (filed as CELLSJAVA-40796) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.