How to Calculate with Formulas in Excel by Using C#

Several days ago, I show my method about how to use subtotal in Excel with C#. Subtotal includes many functions. Get function number and calculation range. And then the result will be shown. In this post, I pick up some frequently used functions and show how to set their formulas to get result with C#.

The functions I select are sum, average, max and count. The four functions are often used in transcripts, such as getting courses total marks, average store and so on.

In my example, I use sales report file which I have used in subtotal post. I will get product numbers, the maximum quantity, average price of these products and finally calculate sum of sales.

I still use the C# .NET Component Spire.XLS for .NET. So, add Spire.XLS for .NET dll file at the beginning. Then add system.drawing for formatting colors.

Step:

  1. Load sales report and initialize worksheet.
  2. Add cue words under the last row of table. Then, get result below the cue words row by formulas.
  3. Set format for cue words and result row.
  4. Save and launch file.

Coding:

using System;
using System.Drawing;
using Spire.Xls;

namespace Calculation
{
    class Calculate
    {
        static void Main(string[] args)
        {
            //Load File
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”,ExcelVersion.Version2010);
            Worksheet sheet = workbook.Worksheets[0];

            //Sum of Sales
            sheet.Range[“D10”].Text = “Sales”;
            sheet.Range[“D11”].Formula = “SUM(D3:D9)”;

            //Average Price
            sheet.Range[“C10”].Text = “Average Price”;
            sheet.Range[“C11”].Formula = “AVERAGE(C3:C9)”;

            //Max Quantity
            sheet.Range[“B10”].Text = “Max Quantity”;
            sheet.Range[“B11”].Formula = “MAX(B3:B9)”;

            //Product Count
            sheet.Range[“A10”].Text = “Products”;
            sheet.Range[“A11”].Formula = “COUNT(A3:A9)”;

            //Format Cells
            sheet.Range[“A10:D11”].Style.Font.FontName = “Times New Roman”;
            sheet.Range[“A10:D11”].Style.Font.Size = 13;
            sheet.Range[“A10:D10”].Style.Font.IsItalic = true;
            sheet.Range[“A10:D10”].Style.Font.Color = Color.Red;
            sheet.Range[“A10:D10”].Style.Font.IsBold = true;
            sheet.AllocatedRange.AutoFitColumns();
            sheet.Range[“A10:D11”].HorizontalAlignment = HorizontalAlignType.Center;

            //Save and Launch File
            workbook.SaveToFile(“calculation.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

Result:

Download Spire.XLS for .NET Here

Advertisements

One thought on “How to Calculate with Formulas in Excel by Using C#

  1. pozycjonowanie warszawa says:

    Thanx for the effort, keep up the good work Great work, I am going to start a small Blog Engine course work using your site I hope you enjoy blogging with the popular BlogEngine.net.Thethoughts you express are really awesome. Hope you will right some more posts.

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