Dev Direct Solution Center

Calculation engine in your .NET application

Download the complete source code for this solution.

 Note: This solution requires the component to be installed first.
To download the installer, click here

Introduction

With the FarPoint spreadsheet products for .NET (Spread for Windows Forms and also Spread for Web Forms), there is a built in calculation engine that can be used integrated within the Spread control or as a standalone piece. The FarPoint Spread calculation engine has 320 of the Excel functions already built into the assembly and also the ability to add custom functions for calculations.

Detail

This C#/Vb.Net sample shows how we can open an Excel spreadsheet into the Farpoint Spread component, insert values from our running application, recalculate the sheet using the formulae it contains and then extract the results to display on the screen.

Set up

Install the Farpoint Spread for Windows Forms evaluation and then download and unzip the sample code using the links above.

There are two sample projects which are identical apart from the implementation language, one being C# the other VB.net

Load and run one of the projects, enter a value into the first text box and press the "Calculate" button. The application will run the calculation and display the results.

How It Works

The C# & .Net code references two assemblies FarPoint.Win and FarPoint.Win.Spread. The Vb.Net sample also references FarPoint.Excel.

The work is done by the FpSpread object. The sample contains an Excel spreadsheet called CalcEngineSample.xls. This is referenced by a filestream object which is then passed to the OpenExcel method of the FpSpread object.

VB
  'Create a new instance of the spreadsheet control 
  Dim fpSpread1 As New FpSpread()
  
  'File containing the formulae 
  Dim f As String = "..\..\CalcEngineSample.xls"
  'Stream object for the file 
  Dim s As New FileStream(f, FileMode.Open, FileAccess.ReadWrite)
  .
  .
  'Open the spreadsheet into the control 
  fpSpread1.OpenExcel(s)
 

C#
  //Create a new instance of the spreadsheet control
  FpSpread fpSpread1 = new FpSpread();
  //File containing the formulae
  string f = "..\\..\\CalcEngineSample.xls";
  //Stream object for the file
  FileStream s = new FileStream(f, FileMode.Open, FileAccess.ReadWrite);
  try
  {
    //Open the spreadsheet into the control
    fpSpread1.OpenExcel(s);

With the sheet loaded into fpSpread1 the parameter values can be passed into the sheet and the formulae that are build into the sheet can be recalculate to produce the results.

Farpoint Spread provides a rich object model for working with the contents of the spreadsheet.

VB
    'Insert the input value from the screen 
    fpSpread1.Sheets(0).Cells("B1").Value = Convert.ToInt32(txtNumFunctions.Text)

    'Get a calculation object 
    Dim cs As FarPoint.Win.Spread.Model.ICalculationSupport
    cs = DirectCast(fpSpread1.Sheets(0).Models.Data, FarPoint.Win.Spread.Model.ICalculationSupport)

    'Recalculate the spreadsheet control to get the new output values 
    cs.Recalculate()

    'Display the values from the sheet 
    txtCostToDevelop.Text = fpSpread1.Sheets(0).Cells("B6").Value.ToString()
    txtMaintenance.Text = fpSpread1.Sheets(0).Cells("B7").Value.ToString()

C#
    //Insert the input value from the screen
    fpSpread1.Sheets[0].Cells["B1"].Value = Convert.ToInt32(txtNumFunctions.Text);

    //Get a calculation object
    FarPoint.Win.Spread.Model.ICalculationSupport cs;
    cs = (FarPoint.Win.Spread.Model.ICalculationSupport)fpSpread1.Sheets[0].Models.Data;

    //Recalculate the spreadsheet control to get the new output values
    cs.Recalculate();

    //Display the values from the sheet
    txtCostToDevelop.Text = fpSpread1.Sheets[0].Cells["B6"].Value.ToString();
    txtMaintenance.Text = fpSpread1.Sheets[0].Cells["B7"].Value.ToString();

After the Recalculate method has been called, the resultant values are copied back into the calling application via the object model.

Conclusion

This simple example demonstrates how you can easily use Spread component to perform calculation from within an application.