Added range with formulas doesn't get caclulated

[Paul Meems]

My method is opening an Excel file. This sheet has 6 rows with header data and then 1 row with values and formulas.

I add data from an SQL Query to this file and copy the formula to all other rows.

When I open this file in LibreOffice I can see the formulas are correct, but the values are '0'.

When I select the cell and hit F9 (recalculate), the formula is executed and the proper value is shown.

Of course, I need to recalculate in my code. I've read several posts already try to get this to work, so far without any luck.

Here's my code:

public MemoryStream ExportLiv(List<LivExportDto> data, DateTime startPeriod, DateTime endPeriod)

{

    var stream = new MemoryStream();

    //Create an instance of ExcelEngine.

    using (var excelEngine = new ExcelEngine())

    {

        var application = excelEngine.Excel;

        //Set the default application version as Excel 2013.

        application.DefaultVersion = ExcelVersion.Excel2013;

        application.EnableIncrementalFormula = true;

        excelEngine.ThrowNotSavedOnDestroy = true;

        //Open a workbook with a worksheet.

        var workbook = application.Workbooks.Open(new MemoryStream(Resource.LIV2017));

                

        // Template was made with Dutch Excel:

        workbook.SetSeparators(',', ';');

        //Access first worksheet from the workbook instance.

        var worksheet = workbook.Worksheets[0];

        //Formula calculation is enabled for the sheet.

        worksheet.EnableSheetCalculations();

        // Fill header:

        worksheet.Range["I1"].Value = "Foo";

        // Set start values:

        var row = 7;

        foreach (var dto in data)

        {

            var column = 1;

            worksheet.Range[row, column].Text = dto.EmployeeNumber;

            worksheet.Range[row, ++column].Text = dto.Initials;

            worksheet.Range[row, ++column].Text = dto.Prefix;

            worksheet.Range[row, ++column].Text = dto.Surname;

            worksheet.Range[row, ++column].DateTime = dto.BirthDate;

            row++;

        }

        try

        {

            // Update formulas:

            row--;

            worksheet.Range["G7:G" + row].Formula = worksheet.Range["G7"].Formula; // Age

            // Recalculate formulas, doesn't seems to make any difference:

            string dummy = null;

            foreach (var range in worksheet.UsedCells)

            {

                if (range.HasFormula)

                    dummy = range.CalculatedValue;

            }

        }

        catch (Exception e)

        {

            Debug.WriteLine(e);

            throw;

        }

        //Formula calculation is disabled for the sheet.

        worksheet.DisableSheetCalculations();

        // Show borders:

        worksheet.UsedRange.BorderAround();

        worksheet.UsedRange.BorderInside();

        if (!string.IsNullOrEmpty(saveAsFilename))

        {

            // Debug mode:

            //Save the workbook to disk in xlsx format.

            workbook.SaveAs(saveAsFilename);

            Debug.WriteLine("File saved as " + saveAsFilename);

        }

        workbook.SaveAs(stream);

        //Resetting Memory stream position.

        stream.Position = 0;

        workbook.Close();

    }

    return stream;

}

My first question is how to recalculate the formulas? So when opening in LibreOffice the cells have the proper values.

I don't have a copy of Excel, so perhaps in Excel, this is also an issue.

My second question: Am I doing the filling of the sheet in the most efficient manner? I have up to 50,000 records to export and that is taking more than 60 seconds.

Thanks.

[Abirami Varadharajan]

Hi Paul, 

Thank you for contacting Syncfusion support. 

Please find the details for your queries below. 

Queries	Details
My first question is how to recalculate the formulas? So when opening in LibreOffice the cells have the proper values.  I don't have a copy of Excel, so perhaps in Excel, this is also an issue.	We have prepared a sample as per your requirement and the sample can be downloaded from the following link. Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/XlsIO_Sample1589179376
My second question: Am I doing the filling of the sheet in the most efficient manner? I have up to 50,000 records to export and that is taking more than 60 seconds	We request you to use IMigrantRange to improve performance and reduce the memory consumption. Kindly refer the below UG documentation link for the same.  UG Link: https://help.syncfusion.com/file-formats/xlsio/improving-performance#range-access

Please let us know if you have any concerns. 

Regards, 

Abirami. 

[Paul Meems]

Thanks for the sample. It really helped.

I've updated my code and am using IMigrantRange to improve performance and changed the formula to make the calculation of the age work.

[Paul Meems]

I've got one more related question.

I have several formulas:

• =INT(($D$1-F7)/365)
  
• =IF(H7>0, J7/H7, 0)
  
• =I7-H7
  
• =N7*H7
  
• =IF(OR(K7>=0, K7>13),"N",IF(AND(K7>$AP$2, K7>13), "?", "Y"))
  
• =IF(K7>$AP$2,$B$4,IF(K7>=$AP$3,$B$3,IF(K7>=$AP$4,$B$2,IF(K7>=0,$B$4))))
  

They all get populated well to the new rows and are recalculated, except the last two formulas.

They are not calculated until I do it manually in LibreOffice.

I've attached the template xlsx file I'm using.

Please advise again how to solve this last step.

Perhaps it is just a bug in LibreOffice, because I just opened the resulting xlsx file in Google Spreadsheets and then the cells are recalculated.

Attachment: Input_4ee6698b.7z

[Paul Meems]

Sorry for the confusion.

It seems it is a setting in LibreOffice which by default is set to NOT recalculate.

I've changed this setting (as mentioned here https://ask.libreoffice.org/en/question/12165/calc-auto-recalc-does-not-work/) and is fine now.

This issue can be closed now.

[Abirami Varadharajan]

Hi Paul, 

Thank you for updating us. 

We are glad that your requirement is fulfilled. Please let us know if you need any further assistance. 

Regards, 

Abirami.