NativeExcel2 for .NET Developer's Reference

Formula

Example

This example demonstrates how to assign formulas to cells.

Output file: console-formula.xls

[C#]
using System;
using NativeExcel;

namespace Console_Formula
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileName = "console-formula.xls";
            CreateWorkbook(FileName);
            OpenWorkbookWithExcel(FileName);
        }

        static void CreateWorkbook(string FileName) {
            //Create workbook
            IWorkbook book = NativeExcel.Factory.CreateWorkbook();
            //Add sheet
            IWorksheet sheet = book.Worksheets.Add();
            
            //Some data 
            int row = 2;
            int col = 2;
            sheet.Cells[row++, col].Value = 3.5;   //cell B2
            sheet.Cells[row++, col].Value = 2;     //cell B3
            sheet.Cells[row++, col].Value = 5.34;  //cell B4
            sheet.Cells[row++, col].Value = 25;    //cell B5
            sheet.Cells[row++, col].Value = 11;    //cell B6
            sheet.Cells[row++, col].Value = 2.44;  //cell B7

            row = 2;
            col = 4;
            sheet.Cells[row++, col].Value = 3.5; //cell D2
            sheet.Cells[row++, col].Value = 2;   //cell D3
            sheet.Cells[row++, col].Value = 100; //cell D4
            sheet.Cells[row++, col].Value = 255; //cell D5
            sheet.Cells[row++, col].Value = 20;  //cell D6
            sheet.Cells[row++, col].Value = 21;  //cell D7
            
            row = 10;
            col = 2;
            sheet.Cells[++row, col].Value = "Formula";
            sheet.Cells.Columns[col].ColumnWidth = 20;
            sheet.Cells[row, col + 1].Value = "Expression";
            sheet.Cells.Columns[col + 1].ColumnWidth = 40;
            sheet.Cells[row, col + 2].Value = "Calculated Value";
            sheet.Cells.Columns[col + 2].ColumnWidth = 20;
            
            string formula;
            IRange Cell;

            //Simple operators
            formula = "=(100-3)/(2+5/(2+1))";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula; 
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //Sum function
            formula = "=Sum(B2:B4;D2:D4)";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //IF,SUM,AVERAGE functions
            formula = "=IF(SUM(B2:B4)>SUM(D2:D4);AVERAGE(B2:B4);AVERAGE(D2:D4))";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //COUNT function
            formula = "=COUNT(B2:D4)";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //Power operation
            formula = "=8^2";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //Expression with float constants
            formula = "=2.33-.22";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //formula refers to cells with formula
            formula = "=SUM(B12:B15)";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            IWorksheet sheet2 = book.Worksheets.Add();
            sheet2.Cells[2,2].Value = 10;
            sheet2.Cells[3,2].Value = 20;
            
            //Formula with 3D reference  
            formula = "='Sheet2'!B2+'Sheet2'!B3";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;
            
            //Formula with 3D reference  
            formula = "=Sum('Sheet2'!B2:B3;D2:D3)";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;

            //Formula with 3D reference  
            formula = "=Sum('Sheet1':'Sheet2'!B2:B6)";
            row++;
            Cell = sheet.Cells[row, col];
            Cell.Formula = formula;
            sheet.Cells[row, col + 1].Value = "'" + formula;
            sheet.Cells[row, col + 2].Value = Cell.Value;


            book.SaveAs(FileName);
        }

        static void OpenWorkbookWithExcel(string FileName){
            try {
                System.Diagnostics.Process.Start(FileName);
            } catch {
                Console.WriteLine(FileName + " created in application folder");
            }
        }
    }
}
[Visual Basic]
imports System
imports NativeExcel

Module Console_Formula

    Sub Main()
        Dim FileName As String = "console-formula.xls"
        CreateWorkbook(FileName)
        OpenWorkbookWithExcel(FileName)
    End Sub

    Sub CreateWorkbook(FileName As String)

        'Create workbook
        Dim book As IWorkbook = NativeExcel.Factory.CreateWorkbook()
        'Add sheet
        Dim sheet As IWorksheet = book.Worksheets.Add()


        'Some data 
        Dim row As Integer = 2
        Dim col As Integer = 2
        sheet.Cells(row, col).Value = 3.5   'cell B2
        row = row + 1
        sheet.Cells(row, col).Value = 2     'cell B3
        row = row + 1
        sheet.Cells(row, col).Value = 5.34  'cell B4
        row = row + 1
        sheet.Cells(row, col).Value = 25    'cell B5
        row = row + 1
        sheet.Cells(row, col).Value = 11    'cell B6
        row = row + 1
        sheet.Cells(row, col).Value = 2.44  'cell B7

        row = 2
        col = 4
        sheet.Cells(row, col).Value = 3.5 'cell D2
        row = row + 1
        sheet.Cells(row, col).Value = 2   'cell D3
        row = row + 1
        sheet.Cells(row, col).Value = 100 'cell D4
        row = row + 1
        sheet.Cells(row, col).Value = 255 'cell D5
        row = row + 1
        sheet.Cells(row, col).Value = 20  'cell D6
        row = row + 1
        sheet.Cells(row, col).Value = 21  'cell D7
        
        row = 10
        col = 2
        sheet.Cells(row, col).Value = "Formula"
        sheet.Cells.Columns(col).ColumnWidth = 20
        sheet.Cells(row, col + 1).Value = "Expression"
        sheet.Cells.Columns(col + 1).ColumnWidth = 40
        sheet.Cells(row, col + 2).Value = "Calculated Value"
        sheet.Cells.Columns(col + 2).ColumnWidth = 20
        
        Dim formula As String
        Dim Cell As IRange

        'Simple operators
        formula = "=(100-3)/(2+5/(2+1))"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula 
        sheet.Cells(row, col + 2).Value = Cell.Value

        'Sum function
        formula = "=Sum(B2:B4;D2:D4)"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'IF,SUM,AVERAGE functions
        formula = "=IF(SUM(B2:B4)>SUM(D2:D4);AVERAGE(B2:B4);AVERAGE(D2:D4))"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'COUNT function
        formula = "=COUNT(B2:D4)"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'Power operation
        formula = "=8^2"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'Expression with float constants
        formula = "=2.33-.22"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'formula refers to cells with formula
        formula = "=SUM(B12:B15)"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        Dim sheet2 As IWorksheet = book.Worksheets.Add()
        sheet2.Cells(2,2).Value = 10
        sheet2.Cells(3,2).Value = 20
        
        'Formula with 3D reference  
        formula = "='Sheet2'!B2+'Sheet2'!B3"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value
        
        'Formula with 3D reference  
        formula = "=Sum('Sheet2'!B2:B3;D2:D3)"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value

        'Formula with 3D reference  
        formula = "=Sum('Sheet1':'Sheet2'!B2:B6)"
        row = row + 1
        Cell = sheet.Cells(row, col)
        Cell.Formula = formula
        sheet.Cells(row, col + 1).Value = "'" + formula
        sheet.Cells(row, col + 2).Value = Cell.Value


        'Save workbook
        book.SaveAs(FileName)

    End Sub

    Sub OpenWorkbookWithExcel(FileName As String)
        Try
           System.Diagnostics.Process.Start(FileName)
        Catch
           Console.WriteLine(FileName + " created in application folder")
        End Try  
    End Sub

End Module