Question on XlsIO data manipulation / calcualtion

[Gerald]

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]

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