Excel Table Formatting with SUBTOTAL Function using EPPlus .Net Library - Part-15(B)
EPPLUS Library - Beginners Guide Part-15(B)
How to Format Excel Table (Column Filter, Show Header, Show Total, Column Formula & Function, Table Style, Cell Formatting) using EPPlus .Net Library?
------------------------------------------------------------------------------------------------------
Suggested Video Link: • How to Create a Excel Table using EPP...
-------------------------------------------------------------------------------------------------------
Hindi Video Link: N/A
-------------------------------------------------------------------------
Blog: https://everyday-be-coding.blogspot.c...
-----------------------------------------------------------------------
Twitter : / everydaycoding
-----------------------------------------------------------------------
Facebook: / everydaybecoding
---------------------------------------------------------------------------------
Source code download link: https://goo.gl/LFVzZD
---------------------------------------------------------------------------------
We need to attach two more namespace
a) OfficeOpenXml.Table (for Excel Table)
b) OfficeOpenXml.Style (for Excel Table Style)
1) "Show Total" in an Excel Table?
ShowTotal: This property is responsible for showing table footer of excel table. By Default this property is false. It is a boolean property.
using (ExcelRange Rng = wsSheet1.Cells["B4:F12"])
{
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
table.ShowTotal = true;
}
4) "Totals Row Formula" & "Total Row Label" in an Excel Table?
These two properties are applied in excel table footer position & applicable for specific column index. Both properties are string type.
TotalRowLabel: Showing a label or text under the excel table footer position.
TotalRowFormula: Applying SUBTOTAL() function in excel table footer position.
Excel SUBTOTAL() function syntax has the following arguments:
Here first arguments function_num is defined a specific number & these numbers are pointing to a specific mathematical function. See this below table.
function_num function Name
101 AVERAGE
102 COUNT
103 COUNTA
104 MAX
105 MIN
106 PRODUCT
107 STDEV
108 STDEVP
109 SUM
110 VAR
111 VARP
For more information please go to this link:
https://support.office.com/en-us/arti...
using (ExcelRange Rng = wsSheet1.Cells["B4:F12"])
{
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
//Add TotalsRowLabel into Excel table Columns
table.Columns[0].TotalsRowLabel = "Total Rows";
//Add TotalsRowFormula into Excel table Columns
table.Columns[1].TotalsRowFormula = "SUBTOTAL(102,[Id])"; //102 = Count
table.Columns[2].TotalsRowFormula = "SUBTOTAL(109,[Sales Amount])"; //109 = Sum
table.Columns[3].TotalsRowFormula = "SUBTOTAL(101,[Profits])"; //101 = Average
}
5) "Totals Row Function" in an Excel Table:
TotalsRowFunction: Doing the same thing as SUBTOTAL() Excel function but in this case, we use RowFunctions enum for mathematics function.
RowFunctions enum
using (ExcelRange Rng = wsSheet1.Cells["B4:F12"])
{
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
//Add TotalsRowFunction into Excel table Columns
table.Columns[0].TotalsRowLabel = "Total Rows";
table.Columns[1].TotalsRowFunction = RowFunctions.Count;
table.Columns[2].TotalsRowFunction = RowFunctions.Sum;
table.Columns[3].TotalsRowFunction = RowFunctions.Average;
}
Now build & execute this code. The file is (ExcelTable.xlsx) store on D: drive of the computer.
Thank you for reading this article.