We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Added range with formulas doesn't get caclulated

Thread ID:

Created:

Updated:

Platform:

Replies:

132506 Sep 5,2017 03:19 PM Sep 8,2017 02:40 AM Windows Forms 5
loading
Tags: XlsIO
Paul Meems
Asked On September 5, 2017 03:19 PM

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 [Syncfusion]
Replied On September 6, 2017 02:10 PM

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. 



Please let us know if you have any concerns. 

Regards, 
Abirami. 


Paul Meems
Replied On September 7, 2017 04:47 AM

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
Replied On September 7, 2017 08:45 AM

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
Replied On September 7, 2017 09:14 AM

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 [Syncfusion]
Replied On September 8, 2017 02:40 AM

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. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;