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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Question on XlsIO data manipulation / calcualtion

Thread ID:





61789 Jun 1,2007 03:19 PM UTC Jun 4,2007 06:18 AM UTC WinForms 1
Tags: XlsIO
Asked On June 1, 2007 03:19 PM UTC

I do have a question on XlsIO to find out if the product may help with our problem.

We have an Excel template file which is opened by a VBA macro and populated with data. After an "Application.CalculateFull" (lots of calculations take place) the resulting data is copied to another file for later use. The original template file is saved under a new name.
This process is repeated numerous times. We think about moving this app to a server so using Excel is not an option unless essential.

Now, does XlsIO supports us in doing this? Can we open the template file, copy data to the worksheet and then collect the results that have been calculated? All in-memory, without the need to save the file in-between?

Thanks in advance and have a nice weekend!

Melba Winshia [Syncfusion]
Replied On June 4, 2007 06:18 AM UTC

Hi Gerald,

Thank you for your interest in Essential XlsIO.

Essential XlsIO does not have a calculation engine of its own, so it is not able to compute the values of the formulas. XlsIO parses the formula string and writes it native binary format understood by MS Excel. MS Excel computes the formula on opening the file. However, the computed value can be retrieved by using Essential Calculate along with Essential XlsIO. Please use the following code snippet to achieve this:


sheet.Range["B7"].Formula = "SUM(B2:B5)";
sheet.Range["C7"].Formula = "SUM(C2:C5)";
sheet.Range["D7"].Formula = "SUM(D2:D5)";
sheet.Range["E7"].Formula = "SUM(E2:E5)";

//Refresh the calculation engine. The second parameter is true since this is the
//first time we are calling this.RefreshCalcEngine

//Export Data from worksheet to data table
DataTable table = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ComputedFormulaValues);

//Import to new sheet
newworkbook.Worksheets[0].ImportDataTable(table, false, 1, 1);

MemoryStream stream = new MemoryStream();
stream.Seek(0, SeekOrigin.Begin);

//Saving the workbook to memory.

Here is a sample using Essential XlsIO and Essential Calculate to copy the resulting data to another file:


Kindly let me know if you have any other questions.



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.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon 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.Close Icon