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");
  6. //Add TotalsRowLabel into Excel table Columns
  7. table.Columns[0].TotalsRowLabel = "Total Rows";
  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.