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.
- Load sales report and initialize worksheet.
- Add cue words under the last row of table. Then, get result below the cue words row by formulas.
- Set format for cue words and result row.
- Save and launch file.
static void Main(string args)
Workbook workbook = new Workbook();
workbook.LoadFromFile(@”D:\work\My Documents\Sales Report.xlsx”,ExcelVersion.Version2010);
Worksheet sheet = workbook.Worksheets;
//Sum of Sales
sheet.Range[“D10”].Text = “Sales”;
sheet.Range[“D11”].Formula = “SUM(D3:D9)”;
sheet.Range[“C10”].Text = “Average Price”;
sheet.Range[“C11”].Formula = “AVERAGE(C3:C9)”;
sheet.Range[“B10”].Text = “Max Quantity”;
sheet.Range[“B11”].Formula = “MAX(B3:B9)”;
sheet.Range[“A10”].Text = “Products”;
sheet.Range[“A11”].Formula = “COUNT(A3:A9)”;
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.Range[“A10:D11”].HorizontalAlignment = HorizontalAlignType.Center;
//Save and Launch File