MS Excel is always very popular among office workers because it is very powerful on managing data, especially calculating data.

Why is Excel good at calculating? The key is formulas. MS Excel provides users with various formulas which make all the calculation be easier. For example, we can use SUM to calculate total quantity of data in one row or column instead one plus another one. Actually, besides SUM, a kind of simple formula, Excel offers some complicated formulas, which may include functions, references and operator.

In this post, I will show you a method to write formulas with C# in Excel. In this example, I write a part of frequently used formulas we may need in the first column. Then, the result of each formula will be presented in the second column.

At first, because I used one component, Spire.XLS to meet my requirement, so I should add the DLL file as reference in my project.

After adding references, I need to write a column title to show what will show in each column and set its format. Then, start with calculating with formulas.

Use the code:

using System;

using Spire.Xls;

namespace WriteFormulas

{

class Formulas

{

static void Main(string[] args)

{

Workbook workbook = new Workbook();

workbook.CreateEmptySheets(1);

Worksheet sheet = workbook.Worksheets[0];

int currentRow = 1;

string currentFormula = string.Empty;

sheet.SetColumnWidth(1, 32);

sheet.SetColumnWidth(2, 16);

sheet.SetColumnWidth(3, 16);

sheet.Range[currentRow, 1].Value = "Formulas"; ;

sheet.Range[currentRow, 2].Value = "Results";

CellRange range = sheet.Range[currentRow, 1, currentRow, 2];

//Range.Value = "Formulas";

range.Style.Font.IsBold = true;

range.Style.KnownColor = ExcelColors.SkyBlue;

range.Style.FillPattern = ExcelPatternType.Solid;

range.Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

//str.

currentFormula = "=\"hello\"";

sheet.Range[++currentRow, 1].Text = "=\"hello\"";

sheet.Range[currentRow, 2].Formula = currentFormula;

//int.

currentFormula = "=300";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

// float

currentFormula = "=3389.639421";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

//bool.

currentFormula = "=false";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

currentFormula = "=1+2+3+4+5-6-7+8-9";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

currentFormula = "=33*3/4-2+10";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

// Sheet Reference

currentFormula = "=Sheet1!$B$3";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

// Sheet Area Reference

currentFormula = "=AVERAGE(Sheet1!$D$3:G$3)";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

// Functions

currentFormula = "=Count(3,5,8,10,2,34)";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

currentFormula = "=NOW()";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow, 2].Formula = currentFormula;

sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

currentFormula = "=SECOND(11)";

sheet.Range[++currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MINUTE(12)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MONTH(9)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=DAY(10)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=TIME(4,5,7)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=DATE(6,4,2)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=RAND()";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=HOUR(12)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MOD(5,3)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=WEEKDAY(3)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=YEAR(23)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=NOT(true)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=OR(true)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=AND(TRUE)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=VALUE(30)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=LEN(\"world\")";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MID(\"world\",4,2)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=ROUND(7,3)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=SIGN(4)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=INT(200)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=ABS(-1.21)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=LN(15)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=EXP(20)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=SQRT(40)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=PI()";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=COS(9)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=SIN(45)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MAX(10,30)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=MIN(5,7)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=AVERAGE(12,45)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=SUM(18,29)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=IF(4,2,2)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

currentFormula = "=SUBTOTAL(3,Sheet1!B2:E3)";

sheet.Range[currentRow, 1].Text = currentFormula;

sheet.Range[currentRow++, 2].Formula = currentFormula;

//Save File

workbook.SaveToFile("Formulas.xls");

//Lanuch File

System.Diagnostics.Process.Start(workbook.FileName);

}

}

}

Result: