C#-Write Formulas in Excel

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:

 

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s