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.
Syncfusion Feedback

Question on XlsIO data manipulation / calcualtion

Thread ID:

Created:

Updated:

Platform:

Replies:

61789 Jun 1,2007 03:19 PM UTC Jun 4,2007 06:18 AM UTC Windows Forms 1
loading
Tags: XlsIO
Gerald
Asked On June 1, 2007 03:19 PM UTC

Hello,
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!
Gerald

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:

[C#]

//Formula
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
this.RefreshCalcEngine(workbook,true);

//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.
newworkbook.SaveAs(stream);

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

http://websamples.syncfusion.com/samples/XlsIO.Windows/F61789/main.htm

Kindly let me know if you have any other questions.

Thanks,
Melba

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.

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

;