What-if Analysis function with Data Table in Excel in .NET

jbijsusteen · March 9, 2020, 6:08pm

Hello

On our current version of Aspose (7?) the function of What-if analysis with the Data Table function does not work properly. Calculations are made, but outcomes are incorrect. We would like to know if updating our Aspose version would resolve this issue. So the question is, is the “Data Table…” function of the “What-If Analysis” fully functional in the latest version of Aspose.Cells?

Background: We use Aspose to create a webform from an Excel file. This file has about 100 sheets and a total of about 200.000 cells with formulas. Right now we use 9 exactly similar sheets with each sheet having almost 20.000 cells with formulas. Only the input for each of these sheet is different. If we could remove 8 of these ‘calculation core’-sheets by using the Data Table function, the speed of our webtool will probably increase a lot.

ahsaniqbalsidiqui · July 26, 2018, 5:06pm

@jbijsusteen,

Thanks for your query.

We have tested the scenario using available sample Excel files and observed no issue in the function of What-if analysis. Please provide us a runnable program, sample XLSX file and exact issues which you observed in the output. We will analyze them and provide our feedback.

jbijsusteen · August 1, 2018, 12:44pm

Could you please be more specific about the Data Table-function? Looking at older posts on this forum it seems that the Data Table-function was not fully functional and implemented in the Aspose feature in the past. This is fine. I just need to know when it was implemented. / I need to be sure if the most current version of Aspose can deal with it. So the question is:

From what version of Aspose is the Data Table function fully functional?

If it is not yet 100% reliable, that is also fine but please tell me so I dont waste my time on making a comprehensive excel-file that in the end doesnt work as intended.

About our model. I cannot provide it, only describe. In the basics the next alinea is saying: Besides not being allowed to provide the file I think it will be hard to trace down mistakes as our wished Data Table function will use a very large calculation chains multiple times.

The use of the Data Table function as we have it in mind now contains one input sheet, one calculation sheet and one for the Data Table.

The Data Table sheet has just a small table with numbers 1 to 9 and an output column. These numbers are used to determine which input column needs to be used in the Inputsheet. The ouputcolumn gives results from the third sheet, the calculation sheet.
The input sheet has 1 column (column A) of cells that go to the calculation sheet + 9 columns (column B:J) with different data input for the calculation sheet. Each column has 600 rows. The Data Table numbers control which column of inputdata is sent to the calculation core. If Data Table is calculating nr 2, then the offset function also has argument 2 and column C is the result of the offset function. The values of column C are then the same as column A, so the data of column C is sent to the calculation sheet.
The third sheet is the calculation sheet. This contains a very big formula chain which calculates (in this case) only one output number. That number is result of the Data Table function. The formula chain in the calculation sheet has about 5.000 rows with at least 18.000 cells with formulas.

ahsaniqbalsidiqui · August 1, 2018, 4:14pm

@jbijsusteen,

I prepared a worksheet containing a table and two fields year and rate on the same sheet using Excel 2016. Then What-If Analysis for DataTable is performed by setting row input cell and column input cell for 10s of years and rates. This file is saved on disc and Aspose.Cells is used to load and change the rate. When CalculateFormula() is called, it updates the table properly. This shows that this feature is working and no restriction is there.

Regarding your question about the exact version, when this feature was implemented, I will try to search this information and provide you if possible, however in the current version it is working fine.

No issue is there in our database about this functionality, therefore we assume it to be 100% reliable. However if you can provide sample Excel file and exact values which needs to be changed for updating the datatable, we can check it and provide assistance if some issue is observed. It is not possible to create file and check scenario as per your description provided above. Please create some simple Excel file and test it using Aspose.Cells latest version. If some issue is there, provide us details along with the sample file.

jbijsusteen · August 1, 2018, 5:05pm

Thank you. I will create an example file on short notice.

ahsaniqbalsidiqui · August 1, 2018, 5:36pm

@jbijsusteen,

You are welcome.

jbijsusteen · June 25, 2023, 6:19pm

Hi see attachement for an example file, with values calculated with our webtool (by Aspose vs7). As you will know most likely if the file is opened in Excel ánd ‘enabled’ it will not show Aspose values anymore but it will show only Excel values. Test DataTable 2x.zip (32.9 KB) Also the (Data Table in the) file created with Aspose v7 does not function properly anymore. See original Excel file in the attachement (original file is the file without the long number in the filename).

Also, I noticed that without influencing the (input of the) of the calculation core, values are correctly calculated with Aspose. However this is not enough, input for calculation is required in the webform. This is why input was added on the interface tab (see cell interface!D16).

Could you please test if cell interface!D17 shows the text “correct” if you input values of 1, 2 and 3 in cell interface!D16 in the latest version of Aspose?

ahsaniqbalsidiqui · August 2, 2018, 1:27am

@jbijsusteen,

In the first place I opened the sample file “1533146383579_Test Tool DataTable.xlsx” in Excel 2016 and changed values in interface!D16 to 1,2 and then 3 but no change is observed in interface!D7 i.e. “correct” string is not displayed.

Same behavior is observed while using Aspose.Cells for .NET 18.7.4. Please check this source Excel file by opening it in Excel and changing the interface!D16 values. Provide your feedback whether it changes interface!D17 value to “correct” or not. If it does not change value, provide us this source file after updating it.

Following sample code is used for testing:

Workbook wb = new Workbook(path + "1533146383579_Test Tool DataTable.xlsx");
wb.Worksheets["interface"].Cells["D16"].Value = 1;
wb.CalculateFormula();
wb.Save(path + @"output1.xlsx", Aspose.Cells.SaveFormat.Xlsx);
wb.Worksheets["interface"].Cells["D16"].Value = 2;
wb.CalculateFormula();
wb.Save(path + @"output2.xlsx", Aspose.Cells.SaveFormat.Xlsx);
wb.Worksheets["interface"].Cells["D16"].Value = 3;
wb.CalculateFormula();
wb.Save(path + @"output3.xlsx", Aspose.Cells.SaveFormat.Xlsx);

jbijsusteen · August 2, 2018, 11:17am

In the first place I opened the sample file “1533146383579_Test Tool DataTable.xlsx” in Excel 2016 and changed values in interface!D16 to 1,2 and then 3 but no change is observed in interface!D7 i.e. “correct” string is not displayed.

Note that this file is the one created by (an older version of) Aspose. When I open that in Excel 2016 it is indeed not functioning correctly.

Same behavior is observed while using Aspose.Cells for .NET 18.7.4.

Do you mean that you used the file “1533146383579_Test Tool DataTable.xlsx” or did you test with the file “Test Tool DataTable.xlsx”?

Please check this source Excel file by opening it in Excel and changing the interface!D16 values. Provide your feedback whether it changes interface!D17 value to “correct” or not. If it does not change value, provide us this source file after updating it.

The source file is also included in the zip file, it is the file without the long number, so the file called “Test Tool DataTable.xlsx”. It is the file untouched by Aspose, only created with Excel. When I download the zip above and open the file “Test Tool DataTable.xlsx” in Excel 2016 (and enable it) it does show the value “correct” in cell interface!D17 for input of values 1, 2 and 3 in cell interface!D16.

When you open the source file, does it do that same in your Excel version as I describe here?

Same behavior is observed while using Aspose.Cells for .NET 18.7.4.

Could you please tell me if this also happens when you use the source file “Test Tool DataTable.xlsx” ?

ahsaniqbalsidiqui · August 2, 2018, 4:19pm

@jbijsusteen,

I used “1533146383579_Test Tool DataTable.xlsx” for testing as suggested by you earlier. When this file is opened it does not show “correct” when value is changed in interface!D16. As this file was faulty and not updated properly when opened in Excel, so same behavior was shown using Aspose.Cells.

When we use “Test Tool DataTable.xlsx” it works fine in Excel and with Aspose.Cells as well.

Summary of observations is as follows:

Aspose.Cells CalculateFormula works fine with the file “Test Tool DataTable.xlsx” which is created by Excel.
Aspose.Cells CalculateFormula does not work for the file “1533146383579_Test Tool DataTable.xlsx” which is also not working with Excel. Hence here the same behavior i.e. no "correct " is displayed, is observed with Aspose.Cells and Excel.

If we focus on the issue of working of “CalculateFormula”, it has no issue yet. However issue is observed with the file “1533146383579_Test Tool DataTable.xlsx” which is created by an older version of Aspose.Cells. You may try to create this file using latest version and try the scenario again.

If issue still persists, please create a separate thread for the issue of creating a faulty file with Aspose.Cells. Provide us a simplified runnable code along with any input/output files.

jbijsusteen · August 2, 2018, 6:23pm

Hi

I used “1533146383579_Test Tool DataTable.xlsx” for testing as suggested by you earlier.

Sorry I meant to sent that as kind of a debug file/to show you it is not working (in our current version).

When we use “Test Tool DataTable.xlsx” it works fine in Excel and with Aspose.Cells as well.

Aspose.Cells CalculateFormula works fine with the file “Test Tool DataTable.xlsx” which is created by Excel.

Very nice to hear. To be sure, do you mean that interface!D17 is showing the text “correct” with every input of 1 2 and 3 in cell interface!D16 if “Test Tool DataTable.xlsx” is calculated with Aspose 18?

ahsaniqbalsidiqui · August 2, 2018, 6:25pm

@jbijsusteen,

Yes you are right.

jbijsusteen · August 3, 2018, 10:01am

Alright thank you very much.