We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Calculate formula fails

Aspose.Cells Product Family
#1

HI,

Calculate formula fails for particular file.

Following is the code used.

public static void ChangeInput()
{

        try
        {
            Workbook newInputWB = new Workbook(@"SampleDataInput_WinService.xlsx");
            Workbook OutPutTemplateWB = new Workbook(@"2. Data Output.xlsx");
            Workbook inputTemplateWB = new Workbook(@"1. Data Input and Data cal.xlsx");

            //ReadOutPUT work sheet to verify changes are made.
            Worksheet outPUTWS = OutPutTemplateWB.Worksheets.GetSheetByCodeName("Adjusted_Data");
            var reqCellFormula = outPUTWS.Cells["J2"].Formula;
            var reqCellValue = outPUTWS.Cells["J2"].Value;
            var reqCellStringValue = outPUTWS.Cells["J2"].StringValue;

            var reqCellJ4Formula = outPUTWS.Cells["J4"].Formula;
            var reqCellJ4Value = outPUTWS.Cells["J4"].Value;
            var reqCellJ4StringValue = outPUTWS.Cells["J4"].StringValue;

            Worksheet newInputWS = newInputWB.Worksheets["SampleDataInput_WinService"];
            Range inputRange = newInputWS.Cells.CreateRange("A3", "J3392");

            Worksheet InputWS = inputTemplateWB.Worksheets["0.Data Input"];
            Range outputRange = InputWS.Cells.CreateRange("H3", "Q3392");

            var H3Precopy = InputWS.Cells["H3"].Value;
            //Copy Content
            outputRange.CopyValue(inputRange);

            //Saving the file Can be ignored
            inputTemplateWB.Save(@"C:\zFold\ProjectDetails\PVM Tableau Table Req\TestDataForWinService\JunkChangesDontUse\1. Data Input and Data cal.xlsx");

            OutPutTemplateWB.Settings.CalcMode = CalcModeType.Automatic;
            OutPutTemplateWB.Settings.ReCalculateOnOpen = true;
            OutPutTemplateWB.Save(@"C:\zFold\ProjectDetails\PVM Tableau Table Req\TestDataForWinService\JunkChangesDontUse\2. Data Output.xlsx");

            OutPutTemplateWB = new Workbook(@"C:\zFold\ProjectDetails\PVM Tableau Table Req\TestDataForWinService\JunkChangesDontUse\2. Data Output.xlsx");

            var H3Postcopy = InputWS.Cells["H3"].Value;


            var preCalcFormula = outPUTWS.Cells["J2"].Formula;
            var preCalcValue = outPUTWS.Cells["J2"].Value;
            var preCalcStringValue = outPUTWS.Cells["J2"].StringValue;
                   
            var preCalcJ4Formula = outPUTWS.Cells["J4"].Formula;
            var preCalcJ4Value = outPUTWS.Cells["J4"].Value;
            var preCalcJ4StringValue = outPUTWS.Cells["J4"].StringValue;

            var preCalcH2Cell = outPUTWS.Cells["H2"]; 
            //preCalcH2Cell.Formula;
            //OutPutTemplateWB.Settings.CalcMode = CalcModeType.Automatic;
            //OutPutTemplateWB.Settings.ReCalculateOnOpen = true;

         //Fails here
            OutPutTemplateWB.CalculateFormula(false);

            var postCalcFormula = outPUTWS.Cells["J2"].Formula;
            var postCalcValue = outPUTWS.Cells["J2"].Value;
            var postCalcStringValue = outPUTWS.Cells["J2"].StringValue;
                
            var postCalcJ4Formula = outPUTWS.Cells["J4"].Formula;
            var postCalcJ4Value = outPUTWS.Cells["J4"].Value;
            var postCalcJ4StringValue = outPUTWS.Cells["J4"].StringValue;



        }
        catch (Exception ex)
        {

            throw;
        }

    }

Exception:
Index was outside the bounds of the array.
Error in calculating cell H2 in Worksheet Adjusted_Data

#2

@biswajeet.m,

Thanks for the sample code and details.

Please try our latest version/fix: Aspose.Cells for .NET v18.4.

If you still find the issue, kindly provide us your template file(s), we will check it soon.

#3

It’s not possible to use newer dll since we are locked on previous paid version,
Is there a possibility that i can share my document so that you can verify if that document will not be supported?

#4

Please find the attached files for verification
SamplePart2.zip (756.8 KB)
Sample Part1.zip (2.3 MB)

#5

@biswajeet.m,

Thanks for the template file(s).

I tried your template file with our latest version/fix: Aspose.Cells for .NET v18.4, it works fine and I do not get any exception. I used the simplest lines of code as following:
e.g
Sample code:

var OutPutTemplateWB = new Workbook("e:\\test2\\2. Data Output.xlsx");

           OutPutTemplateWB.CalculateFormula();

Which version of the component you are using? FYI, since you are using some older version of the product, so it might be an issue in your version. Moreover, we cannot evaluate your issue in older version or fix issues (if any). The fixes are based on latest APIs set only. We recommend you to kindly upgrade to latest version/fix: e.g Aspose.Cells for .NET v18.4.x. If you still find the issue with v18.4.x, we will log a ticket for it and try to fix it soon.

#6

Hi,

I tried using your latest DLL, but in this case the calculation did not succeed. There was no exception but the calculated data were incorrect or the calculations did not took place.

In this program I update the data in to a file and store it, then on opening another file I should calculate the formula and fetch the correct calculated result set.

Could you please check why the calculations were incorrect?

#7

@biswajeet.m,

Could you create a sample console application (runnable) with v18.4, zip the project (excluding Aspose.Cells.Dll) and provide us here to reproduce the issue on our end, we will check it soon. Also provide your template files used in the code.

#8

Sample Code:

public static void TestDataCalculation()
{

        try
        {
            Workbook newInputWB = new Workbook(@"SampleDataInput_WinService.xlsx");
            Workbook OutPutTemplateWB = new Workbook(@"2. Data Output.xlsx");
            Workbook inputTemplateWB = new Workbook(@"1. Data Input and Data cal.xlsx");

            //Read Output work sheet to verify changes are made.
            Worksheet outPUTWS = OutPutTemplateWB.Worksheets["Adjusted_Data"];
            var reqCellValue = outPUTWS.Cells["J2"].Value;  // should be 81


            //Fetch Data from new Input data
            Worksheet newInputWS = newInputWB.Worksheets["SampleDataInput_WinService"];
            Range inputRange = newInputWS.Cells.CreateRange("A3", "J3392");

            Worksheet InputWS = inputTemplateWB.Worksheets["0.Data Input"];
            Range outputRange = InputWS.Cells.CreateRange("H3", "Q3392");

            var verifyPreCopyChange = InputWS.Cells["H5"].Value; // should be 81
            //Copy Content
            outputRange.CopyValue(inputRange);
            var verifyPostCopyChange = InputWS.Cells["H5"].Value;  // should be 18

            //Saving the file
            inputTemplateWB.Save(@"C:\zFold\ProjectDetails\PVM Tableau Table Req\TestDataForWinService\JunkChangesDontUse\1. Data Input and Data cal.xlsx");

            //Following line not required
            //OutPutTemplateWB = new Workbook(@"C:\zFold\ProjectDetails\PVM Tableau Table Req\TestDataForWinService\JunkChangesDontUse\2. Data Output.xlsx");
            
            OutPutTemplateWB.CalculateFormula(false);

            var postCalcJ4Value = outPUTWS.Cells["J2"].Value;  // should be 18



        }
        catch (Exception ex)
        {

            throw;
        }

    }

Sample documents with same file names as in code:
Sample Part 2.zip (1.0 MB)
Sample Part 1.zip (2.7 MB)

#9

@biswajeet.m,

Thanks for the sample code and template files.

After an initial test, I am able to observe the issue as you mentioned by using the following sample code (I added a few lines to the code segment) with your template files. I found that formulas (containing linked data sources in other workbooks) are not calculated fine, it still gives older value for some cells even I tried updating linked data sources and calculating relevant workbooks’ formulas. Also it takes time for updating linked sources and calculating formulas since your workbooks has huge list of formulas to be calculated.
e.g
Sample code:

      Workbook newInputWB = new Workbook(@"e:\test2\SampleDataInput_WinService.xlsx"); 
            Workbook OutPutTemplateWB = new Workbook(@"e:\test2\2. Data Output.xlsx"); 
            Workbook inputTemplateWB = new Workbook(@"e:\test2\1. Data Input and Data cal.xlsx"); 

            //Read Output work sheet to verify changes are made. 
            Worksheet outPUTWS = OutPutTemplateWB.Worksheets["Adjusted_Data"]; 
            var reqCellValue = outPUTWS.Cells["J2"].Value; // should be 81 - Ok 


            //Fetch Data from new Input data 
            Worksheet newInputWS = newInputWB.Worksheets["SampleDataInput_WinService"]; 
            Range inputRange = newInputWS.Cells.CreateRange("A3", "J3392"); 

            Worksheet InputWS = inputTemplateWB.Worksheets["0.Data Input"]; 
            Range outputRange = InputWS.Cells.CreateRange("H3", "Q3392"); 

            var verifyPreCopyChange = InputWS.Cells["H5"].Value; // should be 81 -Ok 
            //Copy Content 
            outputRange.CopyValue(inputRange); 
            var verifyPostCopyChange = InputWS.Cells["H5"].Value; // should be 18 -Ok 

            inputTemplateWB.CalculateFormula(); 

            //Saving the file 
            inputTemplateWB.Save(@"e:\test2\1. Data Input and Data cal1.xlsx");//The file is fine. 

            
            OutPutTemplateWB.UpdateLinkedDataSource(new Workbook[] { inputTemplateWB }); 
            
            OutPutTemplateWB.CalculateFormula(); 

            var postCalcJ4Value = outPUTWS.Cells["J2"].Value; // should be 18 but we get 81 - Not Ok

I have logged a ticket with an id “CELLSNET-46069” for your issue. We will look into it soon.

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

#10

@biswajeet.m,

Please try our latest version/fix: Aspose.Cells for .NET v18.4.1:

Please use the following sample code with updated comments, it will work fine:
e.g
Sample code:

Workbook newInputWB = new Workbook(@"e:\test2\SampleDataInput_WinService.xlsx"); 
            Workbook OutPutTemplateWB = new Workbook(@"e:\test2\2. Data Output.xlsx"); 
            Workbook inputTemplateWB = new Workbook(@"e:\test2\1. Data Input and Data cal.xlsx"); 

            //Read Output work sheet to verify changes are made. 
            Worksheet outPUTWS = OutPutTemplateWB.Worksheets["Adjusted_Data"]; 
            var reqCellValue = outPUTWS.Cells["J2"].Value; // should be 81 - Ok 


            //Fetch Data from new Input data 
            Worksheet newInputWS = newInputWB.Worksheets["SampleDataInput_WinService"]; 
            Range inputRange = newInputWS.Cells.CreateRange("A3", "J3392"); 

            Worksheet InputWS = inputTemplateWB.Worksheets["0.Data Input"]; 
            Range outputRange = InputWS.Cells.CreateRange("H3", "Q3392"); 

            var verifyPreCopyChange = InputWS.Cells["H5"].Value; // should be 81 -Ok 
            //Copy Content 
            outputRange.CopyValue(inputRange); 
            var verifyPostCopyChange = InputWS.Cells["H5"].Value; // should be 18 -Ok 

            inputTemplateWB.CalculateFormula(); 

            //Saving the file - Please do not change the name of the file as it will not work.
           //You may also remove this line of code, it is not needed for the last process of calculating formulas.
            inputTemplateWB.Save(@"e:\test2\1. Data Input and Data cal.xlsx");//The file is fine. 

            
            OutPutTemplateWB.UpdateLinkedDataSource(new Workbook[] { inputTemplateWB }); 
            
            OutPutTemplateWB.CalculateFormula(); 

            var postCalcJ4Value = outPUTWS.Cells["J2"].Value; //18 - Ok

Let us know your feedback.

#11

I tried with the new DLL and also added code to update linked data source still the calculation is not correct.

I have uploaded the sample files again. Please verify that the calculation are correct for the given sample.

Files:
SampleDataInput_WinService.zip (200.5 KB)
2. Data Output.zip (845.3 KB)
1. Data Input and Data cal.zip (2.6 MB)

#12

@biswajeet.m,

Which version of the product you are using? It looks like you are not using latest version/fix: i.e., v18.4.x as recommended. Please try our latest version/fix: v18.4.1 (link is already provided in our previous post). I did test your scenario/case using your new files with the following code (it also prints the version number of Aspose.Cells), it works fine and I got expected results with v18.4.1:
e.g
Sample code:

    Console.Writeline(CellsHelper.GetVersion());//Print the version that you are using? 
    Workbook newInputWB = new Workbook(@"e:\test2\SampleDataInput_WinService.xlsx"); 
                Workbook OutPutTemplateWB = new Workbook(@"e:\test2\2. Data Output.xlsx"); 
                Workbook inputTemplateWB = new Workbook(@"e:\test2\1. Data Input and Data cal.xlsx"); 

                //Read Output work sheet to verify changes are made. 
                Worksheet outPUTWS = OutPutTemplateWB.Worksheets["Adjusted_Data"]; 
                var reqCellValue = outPUTWS.Cells["J2"].Value; // should be 81 - Ok 


                //Fetch Data from new Input data 
                Worksheet newInputWS = newInputWB.Worksheets["SampleDataInput_WinService"]; 
                Range inputRange = newInputWS.Cells.CreateRange("A3", "J3392"); 

                Worksheet InputWS = inputTemplateWB.Worksheets["0.Data Input"]; 
                Range outputRange = InputWS.Cells.CreateRange("H3", "Q3392"); 

                var verifyPreCopyChange = InputWS.Cells["H5"].Value; // should be 81 -Ok 
                //Copy Content 
                outputRange.CopyValue(inputRange); 
                var verifyPostCopyChange = InputWS.Cells["H5"].Value; // should be 18 -Ok 

                inputTemplateWB.CalculateFormula(); 

                //Saving the file - Please do not change the name of the file as it will not work.
               //You may also remove this line of code, it is not needed for the last process of calculating formulas.
                inputTemplateWB.Save(@"e:\test2\1. Data Input and Data cal.xlsx");//The file is fine. 

                
                OutPutTemplateWB.UpdateLinkedDataSource(new Workbook[] { inputTemplateWB }); 
                
                OutPutTemplateWB.CalculateFormula(); 

                var postCalcJ4Value = outPUTWS.Cells["J2"].Value; //18 - Ok

Also, see the screenshot for your reference, I set breaks points in my code and you can see value of J2 cell is fine tuned:

I am afraid, if it is not working in older version, we cannot evaluate or fix the issue. If you find the issue using latest version/fix, let us know with exact (runnable) code (similar as above) to reproduce the issue on our end, we will check it soon.

#13

@biswajeet.m,

We recommend you to kindly try our latest version/fix: Aspose.Cells for .NET 18.5.1.
We have improved the performance for refreshing external data sources for formulas in it.
Please also check the document/article for your reference on how to install the package from Nuget repos.: https://docs.aspose.com/display/cellsnet/Installation

#14

The issues you have found earlier (filed as CELLSNET-46069) have been fixed in Aspose.Cells for .NET 18.6. Please also check the document/article for your reference: https://docs.aspose.com/display/cellsnet/Installation