This example demonstrates how to assign formulas to cells.
[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