How to Use Formulas to Calculate Subtotal in Excel with C#

Formula plays a very important role in Excel, which is the reason why Excel is so powerful on calculating data. In this post, I will talk something about one frequently used formula: Subtotal.

Subtotal is widely used in reports. Besides calculating sum of numbers, subtotal also includes getting min or max number in one column or row and so on. In my example, I have an Excel file which displays product sales information. I will get how many products one table shows, the minimum sales quantity and sum of each product total sales.

Sales Report

The following shows details about how to meet my requirement.

Before starting with the following steps, I add two references: Spire.XLS, which I will use in my example and system.drawing, which is used to set format.

  1. Load file which I want to use formulas to calculate subtotal from my computer and initialize worksheet.

  2. Count product numbers in this sales report and get the result below the last row of Product No. column. Then, format result.

  3. Get minimum quantity number below the last row of Quantity column and format result.

  4. Get sum of each product total sales. Show result below the last row of Total Sales column and format it.

  5. Auto fit column and set alignment.

  6. Save and Launch file.

Coding:

using System;

using System.Drawing;

using Spire.Xls;

namespace subtotal

{

    class count

    {

        static void Main(string[] args)

        {

            //Load File and Initialize

            Workbook workbook = new Workbook();

            workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”);

            Worksheet sheet = workbook.Worksheets[0];

            //Product Count

            sheet.Range[“A10”].Text = “Product Count”;

            sheet.Range[“A11”].Formula = “SUBTOTAL(2,A3:A9)”;

            //Format Result

            sheet.Range[“A10:A11”].Style.Font.FontName=“Times New Roman”;

            sheet.Range[“A10”].Style.Font.Color=Color.Red;

            sheet.Range[“A11”].Style.Font.Color=Color.DarkCyan;

            sheet.Range[“A10:A11”].Style.Font.Size=12;

            sheet.Range[“A10”].Style.Font.IsItalic=true;

            sheet.Range[“A10:A11”].Style.Font.IsBold = true;

            //Quantity Minimum

            sheet.Range[“B10”].Text = “Minimum Quantity”;

            sheet.Range[“B11”].Formula = “SUBTOTAL(5,B3:B9)”;

            //Format Result

            sheet.Range[“B10:B11”].Style.Font.FontName = “Times New Roman”;

            sheet.Range[“B10”].Style.Font.Color = Color.Red;

            sheet.Range[“B11”].Style.Font.Color = Color.DarkCyan;

            sheet.Range[“B10:B11”].Style.Font.Size = 12;

            sheet.Range[“B10”].Style.Font.IsItalic = true;

            sheet.Range[“B10:B11”].Style.Font.IsBold = true;

            //Total Sales Sum

            sheet.Range[“D10”].Text = “Sales”;

            sheet.Range[“D11”].Formula = “SUBTOTAL(9,D3:D9)”;

            //Format Result

            sheet.Range[“D10:D11”].Style.Font.FontName = “Times New Roman”;

            sheet.Range[“D10”].Style.Font.Color = Color.Red;

            sheet.Range[“D11”].Style.Font.Color = Color.DarkCyan;

            sheet.Range[“D10:D11”].Style.Font.Size = 12;

            sheet.Range[“D10”].Style.Font.IsItalic = true;

            sheet.Range[“D10:D11”].Style.Font.IsBold = true;

            //Auto Fit Column and Set Alignment

            sheet.AllocatedRange.AutoFitColumns();

            sheet.AllocatedRange.HorizontalAlignment = HorizontalAlignType.Center;

            //Save and Launch File

            workbook.SaveToFile(“subtotal.xlsx”, ExcelVersion.Version2007);

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

        }

    }

}

Result:

————————————————————————————————————-

Click Here to learn more about Spire.XLS

Click Here to download Spire.XLS

Advertisements

2 thoughts on “How to Use Formulas to Calculate Subtotal in Excel with C#

  1. seo secrets says:

    Aw, this was a really nice post. In idea I would like to put in writing like this additionally – taking time and actual effort to make a very good article… but what can I say… I procrastinate alot and by no means seem to get something done.

  2. Lettie says:

    You share interesting things here. I think that your blog can go viral easily, but you must give
    it initial boost and i know how to do it, just search in google for – mundillo traffic
    increase go viral

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