Conditional Formatting and Transition Calc issue

Aspose.Cells Product Family
1

Hi.


I have found, that when i change transition evaluation flag, contditional formatting for my workbook checks incorrectly:

@Test
public void testTransitionCalc() throws Exception {
    Workbook wb = new Workbook(“D://in.xlsx”);
    wb.getWorksheets().get(0).setTransitionEvaluation(true);
    wb.save(“D://out.xlsx”);
}
For example:
Cell G44 contains a two conditional formatting formulas:
=$B9=“t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19”
=$B29="t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19"
Value of B9 = t1:org_00:org_01:upsng_01:shop_63:nsp_11:rvs_11_14
Value of B29 = t1:org_00:org_01:upsng_01:shop_63:nsp_11:rvs_11_15
So neither conditions are TRUE,
But out.xlsx show this cell as conditional formatting is TRUE (cyan background fill)
Best regards. Alexey
2

Hi Alexey,


Thank you for contacting Aspose support.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for Java 8.6.0.2, and we have noticed that setting the Worksheet.TransitionEvaluation property to true causes the change in the conditional formatting rules set for G44 from =$B29=“t1:org_00:org_01+$A$1:$V$209:upsng_00:shop_64:nsp_07_02:rvs_07_02_19” to =$B29=“t1:org_00:org_01:upsng_00:shop_64:nsp_07_02:rvs_07_02_19” and consequently changes the outcome. We have logged this incident in our bug tracking system under the ticket CELLSJAVA-41490 for further investigation. Please allow us some time to properly analyze the scenario and get back to you with updates in this regard.
3

Hi.


You incorrectly describe an error:
conditional formatting rules are not changes. There is two conditional formatting in “in.xlsx” file and same conditional formatting rules in “out.xlsx”, but in “in.xlsx” rules not applied to cell, because condition are FALSE, but in “out.xlsx” rules are applies to cell.

Best regards. Alexey
4

Hi Alexey,


Thank you for the correction. You are right therefore I have updated the ticket details and attached your comments to it for better understanding. As soon as we receive updates in this regard, we will post here for your reference.
5

Hi. Have you any news for this issue? It is very important for us, and we needed to fix it ASAP.


Best regards. Alexey
6

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We are afraid, there is no update for you at this moment. However, we have logged your comment in our database against this issue and requested the product team to provide some ETA or fix for this issue. Once there is some news for you, we will let you know asap.

7

Hi Alexey,


Thank you for your patience with us.

This is to update you that we have investigated the matter logged earlier as CELLSJAVA-41490 and have found the behavior to be correct as per Excel standards. Please consider the following justification for better understanding.

  • Please set Transition Evaluation as true manually in Excel application for your spreadsheet and save the file. Then re-open the saved file in Excel and compare it against the file generated with Aspose.Cells APIs. You will notice that the result matches with Aspose.Cells’ generated spreadsheet while setting the Transition Evaluation to true.
  • The result is caused due to the relative row index in the formula. Please check the attached in2.xls in Excel 97-2003, the formula will change as =$B25="…". Since Excel 2007, the formula will not show correct formula according to the cell if the row/column index is relative
  • If you wish to compare with “B9”, please use the formula as =$B$9="…"
8

Ok. Thanks. I will check another cause, why my cells are blue.


Best regards. Alexey