.NET

How To Create A Table In An Excel Worksheet & Insert Data Into The Excel Table Cells Using Epplus .net Library (C#) - Part Fourteen

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
Ebook Download
View all
Learn
View all
Get Started

You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

Become a sharper developer and jumpstart your career.

Basic

$0

$

. 00

monthly

For Basic members:

  • Standard Profile
  • Access learning courses
  • Access free e-books
  • 1x point
Premium

$20

$

. 00

monthly

For Premium members:

  • Earn 2x reward points
  • Premium profile with Verified check
  • Premium Avatar with status update
  • Learning courses
  • Unlimited Certifications and badges on profile
  • Unlimited Book downloads
  • Access to Premium content
  • Online Training
  • Unlimited PDF conversions and downloads
  • Professional Resume Writing
  • Interview Preparations Guide
  • 25 messages per month
  • 1 "Kodzu" avatar, basic
Elite

$45

$

. 00

monthly

For Elite members:

  • Bundle E-books
  • Access to all Learn Series
  • Unlimited Apply Jobs