WorkBook recalculation

Hello,

I have the following scenario:
I am opening an Excel file template copied from a specified file path using a memory stream.
This Excel file has globally 3 sheets: one for INPUTS, one for FUNCTIONS (elaborates inputs sheet data), one for OUTPUTS which exposes the calculated values from functions sheet.
I Inizialize a new ExcelEngine from the memory stream with the intent to fill INPUTS sheet, recalculate the workbook and then read OUTPUTS sheet exporting it as dataTable.  
Currently I'm having problem with recalculating the whole workbook and am running out of options.
Below is reported part of the code, the main issue is that NOT ALL formula values are calculated and then read on the outputs sheet.
To test if it was a problem about INPUTS sheet filling I saved a file version after this first operation but if I open the file in Excel (saved after inputs fill) and click on Formula>>"Calculate Now" the OUTPUTS sheet renders all values correctly calculated.

//new ms for ExcelEngine


                using (MemoryStream inStream = new MemoryStream())


                {

                   using (Stream fs = File.Open(productFileEstimatorPath, FileMode.Open, FileAccess.Read))

                    {

                        await fs.CopyToAsync(inStream);

                       fs.Dispose();

                    }


                    //new excel engine


                    using (ExcelEngine excelEngine = new ExcelEngine())


                    {

                        inStream.Position = 0;

                        //open the existing workbook 

                        IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inStream);

                        workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Manual;

                        workbook.CalculationOptions.IsIterationEnabled = true;

   

                        //inputs

                        InputCalculationWriter(workbook, inputParams, product);


                       //recalc all formulas

                        foreach (var sheetObj in workbook.Worksheets)

                          sheetObj.EnableSheetCalculations();


                       foreach (var sheetObj in workbook.Worksheets)

                          sheetObj.DisableSheetCalculations();


                        //outputs

                         IWorksheet outputSheet = wb.Worksheets[(int)FileSheetNames.OUTPUT];

                       //export the sheet data calculated in a dataTable

                         DataTable outputTable = outputSheet.ExportDataTable(outputSheet.UsedRange,

                         ExcelExportDataTableOptions.ComputedFormulaValues | ExcelExportDataTableOptions.ColumnNames);

//....

                        // Close the instance of IWorkbook

                        workbook.Close();

                        //Dispose the instance of ExcelEngine

                        excelEngine.Dispose();

                    }

                    inStream.Dispose();

                }


6 Replies 1 reply marked as answer

SK Shamini Kiruba Sobers Syncfusion Team October 21, 2020 01:52 PM UTC

Hi Alessio, 

Greetings from Syncfusion support. 

You have mentioned that you are trying to recalculate formulas in a filled worksheet. But the Excel file is missing. We could not predict the underlying reason for the incorrect calculated values without knowing the formulas being used. Kindly share us the issue reproducing sample along with the input Excel file so that we can analyze further and provide prompt solution at the earliest. 

Thanks, 
Shamini 



AD Alessio DC October 21, 2020 02:31 PM UTC

Hi Shamini and thanks for the quick reply.

I try to repost the reworked code for the described issue describing what happens of the input operation.

using (MemoryStream inStream = new MemoryStream())
                {
                   using (Stream fs = File.Open(productFileEstimatorPath, FileMode.Open, FileAccess.Read))
                    {
                        await fs.CopyToAsync(inStream);
                        fs.Dispose();
                    }
                    //new excel engine
                    using (ExcelEngine excelEngine = new ExcelEngine())
                    {
                        inStream.Position = 0;
                        //open the existing workbook 
                        IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inStream);
                        workbook.CalculationOptions.CalculationMode = ExcelCalculationMode.Manual;
                        workbook.CalculationOptions.IsIterationEnabled = true;
   
                        //inputs
IWorksheet inputSheet = wb.Worksheets[(int)FileSheetNames.INPUT];
   var inputSheetCells = inputSheet.UsedRange;
long sezioneId = 1;

for (int i = 1; i < inputSheetCells.Rows.Count(); i++)
{
//prima colonna
var firstColumnCell = inputSheetCells[i, 1];

if (firstColumnCell.Text == "SOME VALUE GOT FROM DB DTO") //ADD INPUT VALUE ON SAME ROW AND NEXT COLUMN
{
inputSheet.Range[firstColumnCell.Row, firstColumnCell.Column + 1].Text = "SI";
}
//...
}
                       
                        //recalc all formulas
                        foreach (var sheetObj in workbook.Worksheets)
                          sheetObj.EnableSheetCalculations();

                        foreach (var sheetObj in workbook.Worksheets)
                          sheetObj.DisableSheetCalculations();


                        //outputs export
                        IWorksheet outputSheet = wb.Worksheets[(int)FileSheetNames.OUTPUT];
                        //export the sheet data calculated in a dataTable
                        DataTable outputTable = outputSheet.ExportDataTable(outputSheet.UsedRange,
                        ExcelExportDataTableOptions.ComputedFormulaValues | ExcelExportDataTableOptions.ColumnNames);

//.... THE DATATABLE EXPORTED DOES NOT HAVE CALCULATED VALUES

                        // Close the instance of IWorkbook
                        workbook.Close();

                        //Dispose the instance of ExcelEngine
                        excelEngine.Dispose();

                    }
                    inStream.Dispose();
                }


AD Alessio DC October 22, 2020 10:35 AM UTC

Even if I use in the recalculation logic the following code (as found in other topics)

 foreach (var sheetObj in wb.Worksheets)
            {
                foreach (var cell in sheetObj.Cells.Where(c => c.HasFormula))
                {
                    var a = cell.CalculatedValue;
                }
            }

Does not change the result unfortunately.


SK Shamini Kiruba Sobers Syncfusion Team October 22, 2020 12:46 PM UTC

Hi Alessio, 

Thanks for the updates. 

But, again you have shared the code used and not the input data. Since you have reported the issue with calculated values, we need to know the formulas that give incorrect calculated values so that we can analyze further. 

Kindly share us the simple issue reproducing sample or the Excel file that you have saved after the first operation of INPUTS sheet filling, which would be helpful for us to use that file and check whether the formulas being used in that workbook are calculated properly. 

Regards, 
Shamini 



AD Alessio DC October 22, 2020 01:21 PM UTC

Thanks Shamini finally I have resolved, the issue was into the excel file formulas so not errors on my code.
Just let me ask which is the best approach to perform recalculations into the sheets between the code shared up to now (prevoius thread calculatedValue) and the method Calculate() exposed by the output sheet instance object. I noted this last is faster than the other.

<code>
foreach (var sheet in wb.Worksheets)
            {
                sheet.EnableSheetCalculations();
            }

            //calculate the output
            wb.Worksheets[(int)FileSheetNames.OUTPUT].Calculate();

            foreach (var sheet in wb.Worksheets)
            {
                sheet.DisableSheetCalculations();
            }

</code>

Marked as answer

SK Shamini Kiruba Sobers Syncfusion Team October 23, 2020 05:55 AM UTC

Hi Alessio, 

Yes, Calculate() method is the best and recommended approach to perform re-calculations if you want to calculate all the formulas in a worksheet. If you need to calculate specific formulas in a particular range, then you can access CalculatedValue for those cells. 

We are glad that you have resolved the issue. Kindly let us know if you need further assistance. 

Regards, 
Shamini 


Loader.
Up arrow icon