Video Tutorials - Click here

How to create a table in an Excel

We need to attach one more namespace OfficeOpenXml.Table, because ExcelTable class belongs to this namespace.

There are two ways to create a table in Excel worksheet.

First, Indirectly access ExcelTableCollection class object by using Tables property of ExcelWorksheet class.

Second, Directly access ExcelTableCollection class object.

The ExcelTable class is responsible for creating a table within the Excel worksheet. In this code, Tables is the property of ExcelWorksheet class. The type of this property is ExcelTableCollection class. This class has an Add() method & it indirectly involved by the property Tables of ExcelWorksheet class & this Add() method return a specific excel table.

By the direct process, we are creating an object of ExcelTableCollection class & the Add() the method of the ExcelTableCollection class returns an object of the ExcelTable class.

The ExcelTable class has Columns[index] property. By using this property, we can assign column with name & index position of excel table.

Here, I used ShowHeader, ShowFilter, ShowTotal, the three boolean properties of the ExcelTable class.

ShowHeader: Responsible for excel table header.
ShowFilter: Responsible for auto filter each & every column of excel table.
ShowTotal: Responsible for excel table footer operations.

Please see the below code.
  1. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {
  2. //Indirectly access ExcelTableCollection class
  3. ExcelTable table = wsSheet1.Tables.Add(Rng, "tblSalesman");
  4. //table.Name = "tblSalesman";
  5. //Directly access ExcelTableCollection class
  6. ExcelTableCollection tblcollection = wsSheet1.Tables;
  7. ExcelTable table1 = tblcollection.Add(Rng, "tblSalesman");
  8. //Set Columns position & name
  9. table.Columns[0].Name = "Id";
  10. table.Columns[1].Name = "Salesman Name";
  11. table.Columns[2].Name = "Sales Amount";
  12. table.Columns[3].Name = "Country";
  13. table.Columns[4].Name = "Date";
  14. //table.ShowHeader = false;
  15. table.ShowFilter = true;
  16. //table.ShowTotal = true;
  17. }
How to insert data into the Excel Table Cells?

Here, I used the Value property of ExcelRange class object & assigned a specific data type value as per column name.

Please see the below code.
  1. //[Id] Column
  2. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
  3. Rng.Value = Convert.ToInt32("1001");
  4. }
  5. //[Salesman Name] Columm
  6. using(ExcelRange Rng = wsSheet1.Cells["C5"]) {
  7. Rng.Value = "John";
  8. }
  9. //[Sales Amount] Column
  10. using(ExcelRange Rng = wsSheet1.Cells["D5"]) {
  11. Rng.Value = Convert.ToDecimal("700.00");
  12. }
  13. //[Country] Columm
  14. using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
  15. Rng.Value = "UK";
  16. }
  17. //[Date] Columm
  18. using(ExcelRange Rng = wsSheet1.Cells["F5"]) {
  19. Rng.Style.Numberformat.Format = "mm/dd/yy";
  20. Rng.Value = Convert.ToDateTime("08/26/2017");
  21. }
The output of the code.



Full Source Code
  1. using OfficeOpenXml;
  2. using System.IO;
  3. using OfficeOpenXml.Table;
  4. using System;
  5. namespace EpplusDemo {
  6. class Program {
  7. static void Main(string[] args) {
  8. ExcelPackage ExcelPkg = new ExcelPackage();
  9. ExcelWorksheet wsSheet1 = ExcelPkg.Workbook.Worksheets.Add("Sheet1");
  10. using(ExcelRange Rng = wsSheet1.Cells["B4:F12"]) {
  11. //Indirectly access ExcelTableCollection class
  12. ExcelTable table = wsSheet1.Tables.Add(Rng, "tblSalesman");
  13. //table.Name = "tblSalesman";
  14. //Directly access ExcelTableCollection class
  15. //ExcelTableCollection tblcollection = wsSheet1.Tables;
  16. //ExcelTable table1 = tblcollection.Add(Rng, "tblSalesman");
  17. //Set Columns position & name
  18. table.Columns[0].Name = "ID";
  19. table.Columns[1].Name = "Salesman Name";
  20. table.Columns[2].Name = "Sales Amount";
  21. table.Columns[3].Name = "Country";
  22. table.Columns[4].Name = "Date";
  23. //table.ShowHeader = false;
  24. table.ShowFilter = true;
  25. //table.ShowTotal = true;
  26. }
  27. //Insert data into the Excel Table Cells
  28. //[ID] Columm
  29. using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
  30. Rng.Value = Convert.ToInt32("1001");
  31. }
  32. using(ExcelRange Rng = wsSheet1.Cells["B6"]) {
  33. Rng.Value = Convert.ToInt32("1002");
  34. }
  35. using(ExcelRange Rng = wsSheet1.Cells["B7"]) {
  36. Rng.Value = Convert.ToInt32("1003");
  37. }
  38. using(ExcelRange Rng = wsSheet1.Cells["B8"]) {
  39. Rng.Value = Convert.ToInt32("1004");
  40. }
  41. using(ExcelRange Rng = wsSheet1.Cells["B9"]) {
  42. Rng.Value = Convert.ToInt32("1005");
  43. }
  44. using(ExcelRange Rng = wsSheet1.Cells["B10"]) {
  45. Rng.Value = Convert.ToInt32("1006");
  46. }
  47. using(ExcelRange Rng = wsSheet1.Cells["B11"]) {
  48. Rng.Value = Convert.ToInt32("1007");
  49. }
  50. using(ExcelRange Rng = wsSheet1.Cells["B12"]) {
  51. Rng.Value = Convert.ToInt32("1008");
  52. }
  53. //[SALESMAN NAME] Columm
  54. using(ExcelRange Rng = wsSheet1.Cells["C5"]) {
  55. Rng.Value = "John";
  56. }
  57. using(ExcelRange Rng = wsSheet1.Cells["C6"]) {
  58. Rng.Value = "Sunil";
  59. }
  60. using(ExcelRange Rng = wsSheet1.Cells["C7"]) {
  61. Rng.Value = "Smith";
  62. }
  63. using(ExcelRange Rng = wsSheet1.Cells["C8"]) {
  64. Rng.Value = "Rohit";
  65. }
  66. using(ExcelRange Rng = wsSheet1.Cells["C9"]) {
  67. Rng.Value = "Matt";
  68. }
  69. using(ExcelRange Rng = wsSheet1.Cells["C10"]) {
  70. Rng.Value = "Jack";
  71. }
  72. using(ExcelRange Rng = wsSheet1.Cells["C11"]) {
  73. Rng.Value = "johnson";
  74. }
  75. using(ExcelRange Rng = wsSheet1.Cells["C12"]) {
  76. Rng.Value = "Brown";
  77. }
  78. //[Sales Amount] Column
  79. using(ExcelRange Rng = wsSheet1.Cells["D5"]) {
  80. Rng.Value = Convert.ToDecimal("700.00");
  81. }
  82. using(ExcelRange Rng = wsSheet1.Cells["D6"]) {
  83. Rng.Value = Convert.ToDecimal("800.00");
  84. }
  85. using(ExcelRange Rng = wsSheet1.Cells["D7"]) {
  86. Rng.Value = Convert.ToDecimal("1000.00");
  87. }
  88. using(ExcelRange Rng = wsSheet1.Cells["D8"]) {
  89. Rng.Value = Convert.ToDecimal("1100.00");
  90. }
  91. using(ExcelRange Rng = wsSheet1.Cells["D9"]) {
  92. Rng.Value = Convert.ToDecimal("5000.00");
  93. }
  94. using(ExcelRange Rng = wsSheet1.Cells["D10"]) {
  95. Rng.Value = Convert.ToDecimal("200.00");
  96. }
  97. using(ExcelRange Rng = wsSheet1.Cells["D11"]) {
  98. Rng.Value = Convert.ToDecimal("100.00");
  99. }
  100. using(ExcelRange Rng = wsSheet1.Cells["D12"]) {
  101. Rng.Value = Convert.ToDecimal("200.00");
  102. }
  103. //[Country] Columm
  104. using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
  105. Rng.Value = "UK";
  106. }
  107. using(ExcelRange Rng = wsSheet1.Cells["E6"]) {
  108. Rng.Value = "IND";
  109. }
  110. using(ExcelRange Rng = wsSheet1.Cells["E7"]) {
  111. Rng.Value = "USA";
  112. }
  113. using(ExcelRange Rng = wsSheet1.Cells["E8"]) {
  114. Rng.Value = "IND";
  115. }
  116. using(ExcelRange Rng = wsSheet1.Cells["E9"]) {
  117. Rng.Value = "USA";
  118. }
  119. using(ExcelRange Rng = wsSheet1.Cells["E10"]) {
  120. Rng.Value = "IND";
  121. }
  122. using(ExcelRange Rng = wsSheet1.Cells["E11"]) {
  123. Rng.Value = "UK";
  124. }
  125. using(ExcelRange Rng = wsSheet1.Cells["E12"]) {
  126. Rng.Value = "UK";
  127. }
  128. //[Date] Columm
  129. using(ExcelRange Rng = wsSheet1.Cells["F5"]) {
  130. Rng.Style.Numberformat.Format = "mm/dd/yy";
  131. Rng.Value = Convert.ToDateTime("10/30/2016");
  132. }
  133. using(ExcelRange Rng = wsSheet1.Cells["F6"]) {
  134. Rng.Style.Numberformat.Format = "mm/dd/yy";
  135. Rng.Value = Convert.ToDateTime("06/23/2017");
  136. }
  137. using(ExcelRange Rng = wsSheet1.Cells["F7"]) {
  138. Rng.Style.Numberformat.Format = "mm/dd/yy";
  139. Rng.Value = Convert.ToDateTime("05/13/2017");
  140. }
  141. using(ExcelRange Rng = wsSheet1.Cells["F8"]) {
  142. Rng.Style.Numberformat.Format = "mm/dd/yy";
  143. Rng.Value = Convert.ToDateTime("09/10/2017");
  144. }
  145. using(ExcelRange Rng = wsSheet1.Cells["F9"]) {
  146. Rng.Style.Numberformat.Format = "mm/dd/yy";
  147. Rng.Value = Convert.ToDateTime("07/26/2017");
  148. }
  149. using(ExcelRange Rng = wsSheet1.Cells["F10"]) {
  150. Rng.Style.Numberformat.Format = "mm/dd/yy";
  151. Rng.Value = Convert.ToDateTime("08/26/2017");
  152. }
  153. using(ExcelRange Rng = wsSheet1.Cells["F11"]) {
  154. Rng.Style.Numberformat.Format = "mm/dd/yy";
  155. Rng.Value = Convert.ToDateTime("09/10/2017");
  156. }
  157. using(ExcelRange Rng = wsSheet1.Cells["F12"]) {
  158. Rng.Style.Numberformat.Format = "mm/dd/yy";
  159. Rng.Value = Convert.ToDateTime("09/10/2017");
  160. }
  161. wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
  162. ExcelPkg.SaveAs(new FileInfo(@ "D:\ExcelTable.xlsx"));
  163. }
  164. }
  165. }
Now, build & execute this code. The file is (ExcelTable.xlsx) stored on D: drive of the computer.
Thank you for reading this blog.

Please subscribe my YouTube Channel & don't forget to like and share.

YouTube :https://goo.gl/rt4tHH
Facebook :https://goo.gl/m2skDb
Twitter :https://goo.gl/nUwGnf