Calculation Issue

Aspose.Cells Product Family
1

Hi.


I have faced with some trouble when complex calculation chain not working for me.

Our project contains many custom functions for Excel. And our reports contains complex using this functions with native Excel functions. I unable to repeat my issue at simple example, but i think i know what i need:

at some moment i need to call Workbook.calculateFormulas method, that will perform full recalculation of workbook. Now (i know it) Aspose contains some flag (or something like this), that will not permit calculation engine to recalculate value (if Aspose guess, that value will not changed).

At this moment. After complex recalculation of workbook i have asked a cell’s value via Aspose, i have received 0. If i will save workbook into file, and open it via Excel - function recalculate and cells shows right value. Also if perform
workbook.getSettings().setCalcMode(CalcModeType.MANUAL);
and save to file, that Excel will show 0, until i will not edit function for cell.

So. Is it possible to add some method for throw off recalculation flag? Or method, that will be recalulate workbook ignoring this flag.

P.S. Also, i have founded, that if i will call calculateFunction for this cell, it will calculated correctly. But if i will call calculateFunction for all workbook, cell will not recalculate its value

Best regards. Alexey

2

Hi,


Thanks for providing us some details.

As I could understand you, I think you may try to use WorkbookSettings.setReCalculateOnOpen() method for your needs that indicates whether to re-calculate all formulas on opening file into Ms Excel. And, I am not entirely certain about your issue or requirements. Could you elaborate your requirements or issue in details, so we could understand you better. You may provide us a template file and point out which value you need for your desired cells via screen shots (if possible). Also you may provide code snippet if necessary. All this will help us to understand your issue completely and we may assist you better. If there is any need to log a ticket for it, we may log a ticket for your issue/ feature request into our database.

Thank you.
3

Hi.


I have spent a 6 hours and founded issue! If function was calculated with named range once, that if named range will changed, function will not accept this changes.

In following example you can see, that cell “B2” after recalculation should be equals 3 (and shows it by opening out file), but library returns value 1 for this cell.
public static class MyFunctions implements ICustomFunction {

    public int stage = 0;

    @Override
    public Object calculateCustomFunction(String s, ArrayList arrayList, ArrayList contextObject) {
        if (stage > 0) {
            if (“crow”.equalsIgnoreCase(s)) {
                return arrayList.get(0);
            } else if (“crow2”.equalsIgnoreCase(s)) {
                return ((Cell) contextObject.get(2)).getRow() + 1;
            }
        }
        return 0;
    }
}

@Test
public void calculationIssue() throws Exception {
    Workbook workbook = new Workbook(“D://summif_aspose.xlsx”);

    MyFunctions resolver = new MyFunctions();

    Cells cells = workbook.getWorksheets().get(0).getCells();
    Cells cells2 = workbook.getWorksheets().get(1).getCells();

    cells.get(“A2”).putValue(“2”, true);
    cells.get(“A3”).putValue(“3”, true);

    resolver.stage = 0;
    workbook.calculateFormula(true, resolver);

    cells2.insertRows(1, 9, true);

    Range range = cells2.createRange(1, 0, 9, cells2.getMaxColumn() + 1);
    Range source = cells2.createRange(0, 0, 1, cells2.getMaxColumn() + 1);

    PasteOptions options = new PasteOptions();
    options.setPasteType(PasteType.ALL);
    range.copy(source, options);

    resolver.stage = 1;
    workbook.calculateFormula(true, resolver);

    cells2.removeFormulas();

    System.out.println(cells.get(“B2”).getStringValue());

    workbook.save(“D://summif_out.xlsx”);

}

Best regards. Alexey
4

Hi,


Thanks for the template file and sample code.

I have tested your scenario/ case a bit using your template file and sample code. I noticed the issue. Upon processing the lines of code:
e.g

System.out.println(cells.get(“B2”).getStringValue());

    workbook.save(“D://summif_out.xlsx”);

I got “1” on console print but in the output Excel file the B2 cell in the first worksheet has the calculated value i.e., “2” and not “3”. Could you confirm this, so we may log a ticket for it into our database.

Thank you.
5

Hi. Yep, “2” ofcourse.


Thanks
6

Hi,


Thanks for the confirmation.

As I already observed the issue you mentioned. I found using custom function via ICustomFunction interface does not calculate correct value for the cell. The cell involve named ranges. After calculations/ recalculations, the cell “B2” should be equals 2 (and shows it by opening the output file too), but Aspose.Cells returns value 1 for this cell. I used the template file with your sample code. I have logged a ticket with an id “CELLSJAVA-41424” for your issue. We will look into it soon.

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

Thank you.

7

Hi. Have you some update for this task?

8

Hi,


I am afraid, your issue is not resolved yet. We have asked the concerned developer to update on it or share an eta if possible.

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

Sorry for any inconvenience caused!
9

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

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