Post
Ask Question

How To Format Excel Table Using EPPlus Library Part Fifteen - C#

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.
  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {  
  2.     ExcelTableCollection tblcollection = wsSheet1.Tables;  
  3.     ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  4.     table.ShowFilter = true;  
  5. }  
"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.
  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {  
  2.     ExcelTableCollection tblcollection = wsSheet1.Tables;  
  3.     ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  4.     table.ShowHeader = false;  
  5. }  
"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.
  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {  
  2.     ExcelTableCollection tblcollection = wsSheet1.Tables;  
  3.     ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  4.     table.ShowTotal = true;  
  5. }  
"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.
  1. using (ExcelRange Rng = wsSheet1.Cells["B4:F12"])  
  2. {  
  3. ExcelTableCollection tblcollection = wsSheet1.Tables;   
  4. ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  5.   
  6. //Add TotalsRowLabel into Excel table Columns  
  7. table.Columns[0].TotalsRowLabel = "Total Rows";  
  8.   
  9. //Add TotalsRowFormula into Excel table Columns  
  10. table.Columns[1].TotalsRowFormula = "SUBTOTAL(102,[Id])"; //102 = Count  
  11. table.Columns[2].TotalsRowFormula = "SUBTOTAL(109,[Sales Amount])"; //109 = Sum  
  12. table.Columns[3].TotalsRowFormula = "SUBTOTAL(101,[Profits])"; //101 = Average  
  13. }  
"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.
  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {  
  2.     ExcelTableCollection tblcollection = wsSheet1.Tables;  
  3.     ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  4.     //Add TotalsRowFunction into Excel table Columns  
  5.     table.Columns[0].TotalsRowLabel = "Total Rows";  
  6.     table.Columns[1].TotalsRowFunction = RowFunctions.Count;  
  7.     table.Columns[2].TotalsRowFunction = RowFunctions.Sum;  
  8.     table.Columns[3].TotalsRowFunction = RowFunctions.Average;  
  9. }  
"Table Style" in an Excel Table

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

  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {  
  2.     ExcelTableCollection tblcollection = wsSheet1.Tables;  
  3.     ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  4.     table.TableStyle = TableStyles.Dark9;  
  5. }  
"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" 
  1. string IntCellFormat = "###0;";  
  2. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  3.     Rng.Style.Numberformat.Format = IntCellFormat; //for integer  
  4.     Rng.Value = Convert.ToInt32("1001");  
  5. }  
Column: "Salesman Name"
  1. using (ExcelRange Rng = wsSheet1.Cells["C5"])  
  2. {  
  3.    Rng.Value = "John";  
  4. }  
Column: "Sales Amount"
  1. string CurrencyCellFormat = "$###,###,##0.00";  
  2. using(ExcelRange Rng = wsSheet1.Cells["D5"]) {  
  3.     Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  4.     Rng.Value = Convert.ToDecimal("700.00");  
  5. }  
Column: "Country"
  1. using (ExcelRange Rng = wsSheet1.Cells["E5"])  
  2. {  
  3.    Rng.Value = "UK";  
  4. }  
Column : "Profits"
  1. string PersentageCellFormat = "#0\\.00%";  
  2. using(ExcelRange Rng = wsSheet1.Cells["E5"]) {  
  3.     Rng.Style.Numberformat.Format = PersentageCellFormat;  
  4.     Rng.Value = Convert.ToDecimal(39.9);  
  5. }  
Column: "Date"
  1. string DateCellFormat = "mm/dd/yyyy";  
  2. using(ExcelRange Rng = wsSheet1.Cells["F5"]) {  
  3.     Rng.Style.Numberformat.Format = DateCellFormat;  
  4.     Rng.Value = Convert.ToDateTime("08/26/2017");  
  5. }  
Full Source Code
  1. using OfficeOpenXml;  
  2. using System.IO;  
  3. using OfficeOpenXml.Table;  
  4. using System;  
  5. using OfficeOpenXml.Style;  
  6. namespace EpplusDemo {  
  7.     class Program {  
  8.         static void Main(string[] args) {  
  9.             ExcelPackage ExcelPkg = new ExcelPackage();  
  10.             ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");  
  11.             using(ExcelRange Rng = wsSheet1.Cells["B2:I2"]) {  
  12.                 Rng.Value = "Everyday Be Coding - Format Table using EPPlus .Net Library - Part 15(B)";  
  13.                 Rng.Merge = true;  
  14.                 Rng.Style.Font.Size = 16;  
  15.                 Rng.Style.Font.Bold = true;  
  16.                 Rng.Style.Font.Italic = true;  
  17.             }  
  18.             using(ExcelRange Rng = wsSheet1.Cells["B4:G12"]) {  
  19.                 ExcelTableCollection tblcollection = wsSheet1.Tables;  
  20.                 ExcelTable table = tblcollection.Add(Rng, "tblSalesman");  
  21.                 //Set Columns position & name  
  22.                 table.Columns[0].Name = "Id";  
  23.                 table.Columns[1].Name = "Salesman Name";  
  24.                 table.Columns[2].Name = "Sales Amount";  
  25.                 table.Columns[3].Name = "Profits";  
  26.                 table.Columns[4].Name = "Country";  
  27.                 table.Columns[5].Name = "Date";  
  28.                 //table.ShowHeader = false;  
  29.                 table.ShowFilter = true;  
  30.                 table.ShowTotal = true;  
  31.                 //Add TotalsRowFormula into Excel table Columns  
  32.                 table.Columns[0].TotalsRowLabel = "Total Rows";  
  33.                 table.Columns[1].TotalsRowFormula = "SUBTOTAL(102,[Id])"; //102 = Count  
  34.                 table.Columns[2].TotalsRowFormula = "SUBTOTAL(109,[Sales Amount])"; //109 = Sum  
  35.                 table.Columns[3].TotalsRowFormula = "SUBTOTAL(101,[Profits])"; //101 = Average  
  36.                 //Add TotalsRowFunction into Excel table Columns  
  37.                 //table.Columns[0].TotalsRowLabel = "Total Rows";  
  38.                 //able.Columns[1].TotalsRowFunction = RowFunctions.Count;  
  39.                 //table.Columns[2].TotalsRowFunction = RowFunctions.Sum;  
  40.                 //table.Columns[3].TotalsRowFunction = RowFunctions.Average;  
  41.                 table.TableStyle = TableStyles.Dark9;  
  42.             }  
  43.             //Insert data into the Excel Table Cells  
  44.             //"ID" Column  
  45.             string IntCellFormat = "###0;";  
  46.             using(ExcelRange Rng = wsSheet1.Cells["B5"]) {  
  47.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  48.                 Rng.Value = Convert.ToInt32("1001");  
  49.             }  
  50.             using(ExcelRange Rng = wsSheet1.Cells["B6"]) {  
  51.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  52.                 Rng.Value = Convert.ToInt32("1002");  
  53.             }  
  54.             using(ExcelRange Rng = wsSheet1.Cells["B7"]) {  
  55.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  56.                 Rng.Value = Convert.ToInt32("1003");  
  57.             }  
  58.             using(ExcelRange Rng = wsSheet1.Cells["B8"]) {  
  59.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  60.                 Rng.Value = Convert.ToInt32("1004");  
  61.             }  
  62.             using(ExcelRange Rng = wsSheet1.Cells["B9"]) {  
  63.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  64.                 Rng.Value = Convert.ToInt32("1005");  
  65.             }  
  66.             using(ExcelRange Rng = wsSheet1.Cells["B10"]) {  
  67.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  68.                 Rng.Value = Convert.ToInt32("1006");  
  69.             }  
  70.             using(ExcelRange Rng = wsSheet1.Cells["B11"]) {  
  71.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  72.                 Rng.Value = Convert.ToInt32("1007");  
  73.             }  
  74.             using(ExcelRange Rng = wsSheet1.Cells["B12"]) {  
  75.                 Rng.Style.Numberformat.Format = IntCellFormat;  
  76.                 Rng.Value = Convert.ToInt32("1008");  
  77.             }  
  78.             //"Salesman Name" Column  
  79.             using(ExcelRange Rng = wsSheet1.Cells["C5"]) {  
  80.                 Rng.Value = "John";  
  81.             }  
  82.             using(ExcelRange Rng = wsSheet1.Cells["C6"]) {  
  83.                 Rng.Value = "Sunil";  
  84.             }  
  85.             using(ExcelRange Rng = wsSheet1.Cells["C7"]) {  
  86.                 Rng.Value = "Smith";  
  87.             }  
  88.             using(ExcelRange Rng = wsSheet1.Cells["C8"]) {  
  89.                 Rng.Value = "Rohit";  
  90.             }  
  91.             using(ExcelRange Rng = wsSheet1.Cells["C9"]) {  
  92.                 Rng.Value = "Matt";  
  93.             }  
  94.             using(ExcelRange Rng = wsSheet1.Cells["C10"]) {  
  95.                 Rng.Value = "Jack";  
  96.             }  
  97.             using(ExcelRange Rng = wsSheet1.Cells["C11"]) {  
  98.                 Rng.Value = "johnson";  
  99.             }  
  100.             using(ExcelRange Rng = wsSheet1.Cells["C12"]) {  
  101.                 Rng.Value = "Brown";  
  102.             }  
  103.             using(ExcelRange Rng = wsSheet1.Cells["C12"]) {  
  104.                 Rng.Value = "Brown";  
  105.             }  
  106.             using(ExcelRange Rng = wsSheet1.Cells["C13"]) {  
  107.                 Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;  
  108.             }  
  109.             //"Sales Amount" Column   
  110.             string CurrencyCellFormat = "$###,###,##0.00";  
  111.             using(ExcelRange Rng = wsSheet1.Cells["D5"]) {  
  112.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  113.                 Rng.Value = Convert.ToDecimal("700.00");  
  114.             }  
  115.             using(ExcelRange Rng = wsSheet1.Cells["D6"]) {  
  116.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  117.                 Rng.Value = Convert.ToDecimal("800.00");  
  118.             }  
  119.             using(ExcelRange Rng = wsSheet1.Cells["D7"]) {  
  120.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  121.                 Rng.Value = Convert.ToDecimal("1000.00");  
  122.             }  
  123.             using(ExcelRange Rng = wsSheet1.Cells["D8"]) {  
  124.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  125.                 Rng.Value = Convert.ToDecimal("1100.00");  
  126.             }  
  127.             using(ExcelRange Rng = wsSheet1.Cells["D9"]) {  
  128.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  129.                 Rng.Value = Convert.ToDecimal("5000.00");  
  130.             }  
  131.             using(ExcelRange Rng = wsSheet1.Cells["D10"]) {  
  132.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  133.                 Rng.Value = Convert.ToDecimal("200.00");  
  134.             }  
  135.             using(ExcelRange Rng = wsSheet1.Cells["D11"]) {  
  136.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  137.                 Rng.Value = Convert.ToDecimal("100.00");  
  138.             }  
  139.             using(ExcelRange Rng = wsSheet1.Cells["D12"]) {  
  140.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  141.                 Rng.Value = Convert.ToDecimal("200.00");  
  142.             }  
  143.             using(ExcelRange Rng = wsSheet1.Cells["D13"]) {  
  144.                 Rng.Style.Numberformat.Format = CurrencyCellFormat;  
  145.                 Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;  
  146.             }  
  147.             //"Profits" Column  
  148.             string PersentageCellFormat = "#0\\.00%";  
  149.             using(ExcelRange Rng = wsSheet1.Cells["E5"]) {  
  150.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  151.                 Rng.Value = Convert.ToDecimal(50.01);  
  152.             }  
  153.             using(ExcelRange Rng = wsSheet1.Cells["E6"]) {  
  154.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  155.                 Rng.Value = Convert.ToDecimal(20.02);  
  156.             }  
  157.             using(ExcelRange Rng = wsSheet1.Cells["E7"]) {  
  158.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  159.                 Rng.Value = Convert.ToInt32(56.30);  
  160.             }  
  161.             using(ExcelRange Rng = wsSheet1.Cells["E8"]) {  
  162.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  163.                 Rng.Value = Convert.ToDecimal(45.90);  
  164.             }  
  165.             using(ExcelRange Rng = wsSheet1.Cells["E9"]) {  
  166.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  167.                 Rng.Value = Convert.ToDecimal(90.92);  
  168.             }  
  169.             using(ExcelRange Rng = wsSheet1.Cells["E10"]) {  
  170.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  171.                 Rng.Value = Convert.ToDecimal(80.88);  
  172.             }  
  173.             using(ExcelRange Rng = wsSheet1.Cells["E11"]) {  
  174.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  175.                 Rng.Value = Convert.ToDecimal(76.90);  
  176.             }  
  177.             using(ExcelRange Rng = wsSheet1.Cells["E12"]) {  
  178.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  179.                 Rng.Value = Convert.ToDecimal(39.9);  
  180.             }  
  181.             using(ExcelRange Rng = wsSheet1.Cells["E13"]) {  
  182.                 Rng.Style.Numberformat.Format = PersentageCellFormat;  
  183.                 Rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;  
  184.             }  
  185.             //"Country" Column  
  186.             using(ExcelRange Rng = wsSheet1.Cells["F5"]) {  
  187.                 Rng.Value = "UK";  
  188.             }  
  189.             using(ExcelRange Rng = wsSheet1.Cells["F6"]) {  
  190.                 Rng.Value = "IND";  
  191.             }  
  192.             using(ExcelRange Rng = wsSheet1.Cells["F7"]) {  
  193.                 Rng.Value = "USA";  
  194.             }  
  195.             using(ExcelRange Rng = wsSheet1.Cells["F8"]) {  
  196.                 Rng.Value = "IND";  
  197.             }  
  198.             using(ExcelRange Rng = wsSheet1.Cells["F9"]) {  
  199.                 Rng.Value = "USA";  
  200.             }  
  201.             using(ExcelRange Rng = wsSheet1.Cells["F10"]) {  
  202.                 Rng.Value = "IND";  
  203.             }  
  204.             using(ExcelRange Rng = wsSheet1.Cells["F11"]) {  
  205.                 Rng.Value = "UK";  
  206.             }  
  207.             using(ExcelRange Rng = wsSheet1.Cells["F12"]) {  
  208.                 Rng.Value = "UK";  
  209.             }  
  210.             //"Date" Column  
  211.             string DateCellFormat = "mm/dd/yyyy";  
  212.             using(ExcelRange Rng = wsSheet1.Cells["G5"]) {  
  213.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  214.                 Rng.Value = Convert.ToDateTime("10/30/2016");  
  215.             }  
  216.             using(ExcelRange Rng = wsSheet1.Cells["G6"]) {  
  217.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  218.                 Rng.Value = Convert.ToDateTime("06/23/2017");  
  219.             }  
  220.             using(ExcelRange Rng = wsSheet1.Cells["G7"]) {  
  221.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  222.                 Rng.Value = Convert.ToDateTime("05/13/2017");  
  223.             }  
  224.             using(ExcelRange Rng = wsSheet1.Cells["G8"]) {  
  225.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  226.                 Rng.Value = Convert.ToDateTime("09/10/2017");  
  227.             }  
  228.             using(ExcelRange Rng = wsSheet1.Cells["G9"]) {  
  229.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  230.                 Rng.Value = Convert.ToDateTime("07/26/2017");  
  231.             }  
  232.             using(ExcelRange Rng = wsSheet1.Cells["G10"]) {  
  233.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  234.                 Rng.Value = Convert.ToDateTime("08/26/2017");  
  235.             }  
  236.             using(ExcelRange Rng = wsSheet1.Cells["G11"]) {  
  237.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  238.                 Rng.Value = Convert.ToDateTime("09/10/2017");  
  239.             }  
  240.             using(ExcelRange Rng = wsSheet1.Cells["G12"]) {  
  241.                 Rng.Style.Numberformat.Format = DateCellFormat;  
  242.                 Rng.Value = Convert.ToDateTime("09/10/2017");  
  243.             }  
  244.             wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();  
  245.             ExcelPkg.SaveAs(new FileInfo(@ "D:\FormatExcelTable.xlsx"));  
  246.         }  
  247.     }  
  248. }  
Now, build and execute this code. The file is (ExcelTable.xlsx) stored on D: drive of the computer.
Next Recommended Reading How To Create An Excel File (Development) Using EPPlus .NET Library (C#) - Part One