Data table (What-if analysis) evaluation quirk

Aspose.Cells Product Family
1

Hello,

We have an issue with the evaluation of cells containing a Data table (What-if analysis).

Here is an example
TEST_GRID.zip (580,1 Ko)

Test code:

        Workbook workbook = new Workbook("TEST_GRID.xlsx");
        workbook.CalculateFormula();
        Worksheet worksheet = workbook.Worksheets["2- Corporate tranches"];
        var references = new string[] { "AS60", "AS61", "AS62", "AS63", "AS64", "AS65", "AS66", "AS67", "AS68", "AS69", "AS70", "AS71", "AS72", "AS73", "AS74", "AS75", "AS76" };

        foreach (string reference in references)
        {
            var cell = worksheet.Cells[reference];

            Console.WriteLine("Cells[" + reference + "]");
            Console.WriteLine(cell.Value);
        }
        Console.ReadLine();
2

@liujx83

It seems you are experiencing issues with the evaluation of cells that contain a Data Table in your Excel workbook. To address this, you can ensure that the workbook is properly calculating the formulas, including those in the Data Table.

Here’s a sample code snippet that demonstrates how to calculate formulas in a workbook, which includes handling Data Tables:

using System;
using Aspose.Cells;

namespace DataTableEvaluation
{
    class Program
    {
        static void Main(string[] args)
        {
            // Load the workbook
            Workbook workbook = new Workbook("TEST_GRID.xlsx");
            
            // Calculate all formulas in the workbook
            workbook.CalculateFormula();
            
            // Access the specific worksheet
            Worksheet worksheet = workbook.Worksheets["2- Corporate tranches"];
            var references = new string[] { "AS60", "AS61", "AS62", "AS63", "AS64", "AS65", "AS66", "AS67", "AS68", "AS69", "AS70", "AS71", "AS72", "AS73", "AS74", "AS75", "AS76" };

            // Iterate through the specified cell references and print their values
            foreach (string reference in references)
            {
                var cell = worksheet.Cells[reference];
                Console.WriteLine("Cells[" + reference + "]: " + cell.Value);
            }
            Console.ReadLine();
        }
    }
}

In this code, after loading the workbook, the CalculateFormula() method is called to ensure that all formulas, including those in Data Tables, are evaluated correctly. Make sure that the worksheet name and cell references are accurate as per your workbook.

If you continue to face issues, please check the structure of your Data Table and ensure that the formulas are set up correctly. If the problem persists, consider sharing more details or specific error messages for further assistance.

3

@liujx83,

Thanks for the template Excel file.

I tested using your exact sample code with your template Excel file using latest version of Aspose.Cells for .NET v25.1.x.
e.g.,
Sample code:

        Workbook workbook = new Workbook("e:\\test2\\TEST_GRID.xlsx");
        workbook.CalculateFormula();
        Worksheet worksheet = workbook.Worksheets["2- Corporate tranches"];
        var references = new string[] { "AS60", "AS61", "AS62", "AS63", "AS64", "AS65", "AS66", "AS67", "AS68", "AS69", "AS70", "AS71", "AS72", "AS73", "AS74", "AS75", "AS76" };

        foreach (string reference in references)
        {
            var cell = worksheet.Cells[reference];

            Console.WriteLine("Cells[" + reference + "]");
            Console.WriteLine(cell.Value);
        }
        Console.ReadLine();

Here is the console output:

Cells[AS60]
31083499.517833333
Cells[AS61]
31083499.517833333
Cells[AS62]
31083499.517833333
Cells[AS63]
31083499.517833333
Cells[AS64]
31083499.517833333
Cells[AS65]
31083499.517833333
Cells[AS66]
31083499.517833333
Cells[AS67]
31083499.517833333
Cells[AS68]
31083499.517833333
Cells[AS69]
31083499.517833333
Cells[AS70]
31083499.517833333
Cells[AS71]
31083499.517833333
Cells[AS72]
31083499.517833333
Cells[AS73]
31083499.517833333
Cells[AS74]
31083499.517833333
Cells[AS75]
31083499.517833333
Cells[AS76]
31083499.517833333

Are these not expected results? If so, kindly give us your expected results. We will look into your issue soon.

4

Hello,

The expected result via Interop is as displayed in the Excel table
image.png (6,4 Ko)

Cells[AS60]
31083499.517833333
Cells[AS61]
31083499.517833333
Cells[AS62]
31083499.517833333
Cells[AS63]
31083499.517833333
Cells[AS64]
31083499.517833333
Cells[AS65]
0
Cells[AS66]
0
Cells[AS67]
0
Cells[AS68]
0
Cells[AS69]
0
Cells[AS70]
0
Cells[AS71]
0
Cells[AS72]
0
Cells[AS73]
0
Cells[AS74]
0
Cells[AS75]
0
Cells[AS76]
0

Please advise.

Thank you.

Best regards,
Jixuan LIU

5

@liujx83,

Thanks for the expected (calculated) results.

Apparently, we noticed the issue as you mentioned by using your template Excel file and sample code snippet. We found the data table (What-if analysis) formulas evaluation quirk or odd calculated results for some cells.

We require thorough evaluation of the issue. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-57804

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

Hello,

Thank you for the analysis. We would like to have more information on the time needed to fix this issue (approximately) if we pay the support services.

I appreciate your help.

7

@liujx83
Please note, your issue/ticket is logged under normal (free) support model where issues are resolved on first come first serve basis. The resolution time of the issue depends upon the number of issues or other tasks logged prior to it.

You may check our paid support option where issues are prioritized and posses highest priority if your issue is a real blocker.

8

@liujx83

We are evaluating the issue now. Before we find the cause of it, it is hard for us to provide an eta for solving the issue. But generally the issue of calculating table formulas is more complicated than normal issues because the logic and algorithm are much more complex. Sometimes we need several weeks to solve one issue of calculating table formula. Anyways, for paid support services, we will assign more resources for it and try to solve it as soon as possible with higher priority.

9

Thank you for your advice. We will evaluate different Aspose pricing proposals and decide when to purchase.

10

@liujx83
You are welcome.Feel free to ask if there are any further questions.

11

@liujx83,

We are pleased to inform you that your issue (logged earlier as “CELLSNET-57804”) has been resolved. The fix/enhancement will be included in the next release (Aspose.Cells v25.3) scheduled for release first half of March (next month). You will be notified once the new version is published.

12

This is very good news! Thank you very much!

13

@liujx83
Thank you for your feedback. You are welcome. We will notify you promptly once the version v25.3 is released.

1 Like
14

The issues you have found earlier (filed as CELLSNET-57804) have been fixed in this update. This message was posted using Bugs notification tool by leoluo

15

That was quick! Thank you a lot.

16

@liujx83
You are welcome. If you have any questions, please feel free to contact us at any time.