Calculate With Formula

using System;
using System.IO;
using System.Windows.Forms;
using Spire.Xls;

namespace DataCalculateWithFormulas
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Stream buffer = WriteFormulas())
            {
                //load
                Workbook workbook = new Workbook();
                workbook.LoadFromStream(buffer);

                //calculate formula
                Object b3 = workbook.CaculateFormulaValue("=Sheet1!$B$3");
                Object c3 = workbook.CaculateFormulaValue("=Sheet1!$C$3");
                String formula = "=Sheet1!$B$3 + Sheet1!$C$3";
                Object value = workbook.CaculateFormulaValue(formula);
                String message
                    = String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value);
                MessageBox.Show(message, "Calculate Formulas");
            }
        }
        private static Stream WriteFormulas()
        {
            //Create Excel files

            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];//it's the first sheet of the worksheets

            int currentRow = 1;
            string currentFormula = string.Empty;
            sheet.SetColumnWidth(1, 32);//SetColumnWidth(columnIndex,width);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);
            sheet.Range[currentRow++, 1].Value = "Examples of formulas :";
            sheet.Range[++currentRow, 1].Value = "Test data:";

            CellRange range = sheet.Range["A1"];
            range.Style.Font.IsBold = true;
            range.Style.FillPattern = ExcelPatternType.Solid;
            range.Style.KnownColor = ExcelColors.LightGreen1;
            range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            //test data
            sheet.Range[currentRow, 2].NumberValue = 7.3;
            sheet.Range[currentRow, 3].NumberValue = 5; ;
            sheet.Range[currentRow, 4].NumberValue = 8.2;
            sheet.Range[currentRow, 5].NumberValue = 4;
            sheet.Range[currentRow, 6].NumberValue = 3;
            sheet.Range[currentRow, 7].NumberValue = 11.3;

            MemoryStream buffer = new MemoryStream();
            workbook.SaveToStream(buffer);
            buffer.Position = 0;
            return buffer;
        }
    }
}

Imports System.IO
Imports System.Windows.Forms
Imports Spire.Xls

Module Module1
    Private Function WriteFormulas() As Stream
        Dim workbook As Workbook = New Workbook()
        Dim sheet As Worksheet = workbook.Worksheets(0)

        Dim currentRow As Integer = 1
        Dim currentFormula As String = String.Empty

        sheet.SetColumnWidth(1, 32)
        sheet.SetColumnWidth(2, 16)
        sheet.SetColumnWidth(3, 16)

        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Examples of formulas :"
        currentRow = currentRow + 1
        sheet.Range(currentRow, 1).Value = "Test data:"

        Dim range As CellRange = sheet.Range("A1")
        range.Style.Font.IsBold = True
        range.Style.FillPattern = ExcelPatternType.Solid
        range.Style.KnownColor = ExcelColors.LightGreen1
        range.Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium

        'test data
        sheet.Range(currentRow, 2).NumberValue = 7.3
        sheet.Range(currentRow, 3).NumberValue = 5

        sheet.Range(currentRow, 4).NumberValue = 8.2
        sheet.Range(currentRow, 5).NumberValue = 4
        sheet.Range(currentRow, 6).NumberValue = 3
        sheet.Range(currentRow, 7).NumberValue = 11.3

        Dim buffer As New MemoryStream()
        workbook.SaveToStream(buffer)
        buffer.Position = 0

        Return buffer
    End Function
    Sub Main()
        Using buffer As Stream = WriteFormulas()
            'load
            Dim workbook As New Workbook()
            workbook.LoadFromStream(buffer)

            'calculate formula
            Dim b3 As Object = workbook.CaculateFormulaValue("=Sheet1!$B$3")
            Dim c3 As Object = workbook.CaculateFormulaValue("=Sheet1!$C$3")
            Dim formula As String = "=Sheet1!$B$3 + Sheet1!$C$3"
            Dim value As Object = workbook.CaculateFormulaValue(formula)
            Dim message As String = _
                String.Format("Sheet1!$B$3 = {0}, Sheet1!$C$3 = {1}, {2} = {3}", b3, c3, formula.Substring(1), value)
            MessageBox.Show(message, "Calculate Formulas")
        End Using
    End Sub
End Module