How To Format Excel Table Using EPPlus Library Part Fifteen

Video Tutorial - Click Here
Suggested Video - How to Create & Insert data into an Excel Tables Part-14(A)
Source Code - Click to download [463 KB]

We need to attach two more namespaces.

OfficeOpenXml.Table (for Excel Table)
OfficeOpenXml.Style (for Excel Table Style)

"Column Filter" in an Excel table

ShowFilter

This property is responsible for applying a filter to every column of Excel table. It is a boolean property. By default, this property is false.

using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
table.ShowFilter = true;
}

"Show Header" in an Excel table

This property is responsible for showing the table header in every column of the Excel table. By default, this property is true. It is a boolean property.

using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
table.ShowHeader = false;
}

"Show Total" in an Excel Table?

This property is responsible for showing the 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;
}

"Totals Row Formula" & "Total Row Label" in an Excel Table?

These two properties are applied in Excel table footer position & applicable to specific column index. Both properties are of 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, the first argument function_num is defined as specific numbers & 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 through this link.

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
}

"Totals Row Function" in an Excel Table

TotalsRowFunction: Does the same thing as SUBTOTAL() Excel function, but in this case, we use RowFunctions enum for mathematical function.

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;
}

"Table Style" in an Excel Table

Applying for predefined colorful themes in Excel table. Here, TableStyles.Dark9 is 59 number in an enumerator list.

using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
table.TableStyle = TableStyles.Dark9;
}

"Cell Formatting" in an Excel Table

This is a special type of format pattern for the specific data type in an Excel cell. The Numberformat property is the type of ExcelNumberFormat class. This ExcelNumberFormat class has Format property.

Column: "Id"

string IntCellFormat = "###0;";
using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
Rng.Style.Numberformat.Format = IntCellFormat; //for integer
Rng.Value = Convert.ToInt32("1001");
}

Column: "Salesman Name"

using (ExcelRange Rng = wsSheet1.Cells["C5"])
{
Rng.Value = "John";
}

Column: "Sales Amount"

string CurrencyCellFormat = "$###,###,##0.00";
using(ExcelRange Rng = wsSheet1.Cells["D5"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("700.00");
}

Column: "Country"

using (ExcelRange Rng = wsSheet1.Cells["E5"])
{
Rng.Value = "UK";
}

Column : "Profits"

string PersentageCellFormat = "#0\\.00%";
using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(39.9);
}

Column: "Date"

string DateCellFormat = "mm/dd/yyyy";
using(ExcelRange Rng = wsSheet1.Cells["F5"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("08/26/2017");
}

Full Source Code

using OfficeOpenXml;
using System.IO;
using OfficeOpenXml.Table;
using System;
using OfficeOpenXml.Style;
namespace EpplusDemo {
class Program {
static void Main(string[] args) {
ExcelPackage ExcelPkg = new ExcelPackage();
ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
using(ExcelRange Rng = wsSheet1.Cells["B2:I2"]) {
Rng.Value = "Everyday Be Coding - Format Table using EPPlus .Net Library - Part 15(B)";
Rng.Merge = true;
Rng.Style.Font.Size = 16;
Rng.Style.Font.Bold = true;
Rng.Style.Font.Italic = true;
}
using(ExcelRange Rng = wsSheet1.Cells["B4:G12"]) {
ExcelTableCollection tblcollection = wsSheet1.Tables;
ExcelTable table = tblcollection.Add(Rng, "tblSalesman");
//Set Columns position & name
table.Columns[0].Name = "Id";
table.Columns[1].Name = "Salesman Name";
table.Columns[2].Name = "Sales Amount";
table.Columns[3].Name = "Profits";
table.Columns[4].Name = "Country";
table.Columns[5].Name = "Date";
//table.ShowHeader = false;
table.ShowFilter = true;
table.ShowTotal = true;
//Add TotalsRowFormula into Excel table Columns
table.Columns[0].TotalsRowLabel = "Total Rows";
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
//Add TotalsRowFunction into Excel table Columns
//table.Columns[0].TotalsRowLabel = "Total Rows";
//able.Columns[1].TotalsRowFunction = RowFunctions.Count;
//table.Columns[2].TotalsRowFunction = RowFunctions.Sum;
//table.Columns[3].TotalsRowFunction = RowFunctions.Average;
table.TableStyle = TableStyles.Dark9;
}
//Insert data into the Excel Table Cells
//"ID" Column
string IntCellFormat = "###0;";
using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1001");
}
using(ExcelRange Rng = wsSheet1.Cells["B6"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1002");
}
using(ExcelRange Rng = wsSheet1.Cells["B7"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1003");
}
using(ExcelRange Rng = wsSheet1.Cells["B8"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1004");
}
using(ExcelRange Rng = wsSheet1.Cells["B9"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1005");
}
using(ExcelRange Rng = wsSheet1.Cells["B10"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1006");
}
using(ExcelRange Rng = wsSheet1.Cells["B11"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1007");
}
using(ExcelRange Rng = wsSheet1.Cells["B12"]) {
Rng.Style.Numberformat.Format = IntCellFormat;
Rng.Value = Convert.ToInt32("1008");
}
//"Salesman Name" Column
using(ExcelRange Rng = wsSheet1.Cells["C5"]) {
Rng.Value = "John";
}
using(ExcelRange Rng = wsSheet1.Cells["C6"]) {
Rng.Value = "Sunil";
}
using(ExcelRange Rng = wsSheet1.Cells["C7"]) {
Rng.Value = "Smith";
}
using(ExcelRange Rng = wsSheet1.Cells["C8"]) {
Rng.Value = "Rohit";
}
using(ExcelRange Rng = wsSheet1.Cells["C9"]) {
Rng.Value = "Matt";
}
using(ExcelRange Rng = wsSheet1.Cells["C10"]) {
Rng.Value = "Jack";
}
using(ExcelRange Rng = wsSheet1.Cells["C11"]) {
Rng.Value = "johnson";
}
using(ExcelRange Rng = wsSheet1.Cells["C12"]) {
Rng.Value = "Brown";
}
using(ExcelRange Rng = wsSheet1.Cells["C12"]) {
Rng.Value = "Brown";
}
using(ExcelRange Rng = wsSheet1.Cells["C13"]) {
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
}
//"Sales Amount" Column
string CurrencyCellFormat = "$###,###,##0.00";
using(ExcelRange Rng = wsSheet1.Cells["D5"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("700.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D6"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("800.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D7"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("1000.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D8"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("1100.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D9"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("5000.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D10"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("200.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D11"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("100.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D12"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Value = Convert.ToDecimal("200.00");
}
using(ExcelRange Rng = wsSheet1.Cells["D13"]) {
Rng.Style.Numberformat.Format = CurrencyCellFormat;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//"Profits" Column
string PersentageCellFormat = "#0\\.00%";
using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(50.01);
}
using(ExcelRange Rng = wsSheet1.Cells["E6"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(20.02);
}
using(ExcelRange Rng = wsSheet1.Cells["E7"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToInt32(56.30);
}
using(ExcelRange Rng = wsSheet1.Cells["E8"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(45.90);
}
using(ExcelRange Rng = wsSheet1.Cells["E9"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(90.92);
}
using(ExcelRange Rng = wsSheet1.Cells["E10"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(80.88);
}
using(ExcelRange Rng = wsSheet1.Cells["E11"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(76.90);
}
using(ExcelRange Rng = wsSheet1.Cells["E12"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Value = Convert.ToDecimal(39.9);
}
using(ExcelRange Rng = wsSheet1.Cells["E13"]) {
Rng.Style.Numberformat.Format = PersentageCellFormat;
Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
}
//"Country" Column
using(ExcelRange Rng = wsSheet1.Cells["F5"]) {
Rng.Value = "UK";
}
using(ExcelRange Rng = wsSheet1.Cells["F6"]) {
Rng.Value = "IND";
}
using(ExcelRange Rng = wsSheet1.Cells["F7"]) {
Rng.Value = "USA";
}
using(ExcelRange Rng = wsSheet1.Cells["F8"]) {
Rng.Value = "IND";
}
using(ExcelRange Rng = wsSheet1.Cells["F9"]) {
Rng.Value = "USA";
}
using(ExcelRange Rng = wsSheet1.Cells["F10"]) {
Rng.Value = "IND";
}
using(ExcelRange Rng = wsSheet1.Cells["F11"]) {
Rng.Value = "UK";
}
using(ExcelRange Rng = wsSheet1.Cells["F12"]) {
Rng.Value = "UK";
}
//"Date" Column
string DateCellFormat = "mm/dd/yyyy";
using(ExcelRange Rng = wsSheet1.Cells["G5"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("10/30/2016");
}
using(ExcelRange Rng = wsSheet1.Cells["G6"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("06/23/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G7"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("05/13/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G8"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("09/10/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G9"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("07/26/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G10"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("08/26/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G11"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("09/10/2017");
}
using(ExcelRange Rng = wsSheet1.Cells["G12"]) {
Rng.Style.Numberformat.Format = DateCellFormat;
Rng.Value = Convert.ToDateTime("09/10/2017");
}
wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatExcelTable.xlsx"));
}
}
}

Now, build and execute this code. The file is (ExcelTable.xlsx) stored on D: drive of the computer.

4 Comments

Join the conversation! Your thoughts help the community grow.

sharon joseph6y ago
Can i add a new row to an existing table in excel using EPPlus?
Naga Boosh7y ago
worksheet.Cells.Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells.Style.Fill.BackgroundColor.SetColor(Color.White);worksheet.Cells[1, 1].LoadFromDataTable(dtExportExcel, PrintHeaders: true, TableStyle: OfficeOpenXml.Table.TableStyles.Medium15); Table style background showed as white, i can't use both styles at time
Chris LaFave8y ago
I wanted to first thank you for these Epplus tutorials you've created, they've been tremendously helpful to me.
- Rajdip Sarkar8y ago
  Thank you.............