EPPlus Library Part-14a

EPPLUS Library - Beginners Guide Part-14

How to Create a Table in an Excel Worksheet & Insert Data into the Excel Table Cells Using EPPlus Library (.Net, C#)?                                      

Source Code:  Click to download [460 KB]
Video Tutorials:  Click here 

1) How to Create a table in an Excel Sheet?
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 of ExcelTableCollection class object by using Tables property of 
ExcelWorksheet class. 

Second, Directly access of 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 use ShowHeader, ShowFilter, ShowTotal this three boolean property 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 this below code.

      using (ExcelRange Rng = wsSheet1.Cells["B4:F12"])
            {
                //Indirectly access ExcelTableCollection class
                ExcelTable table = wsSheet1.Tables.Add(Rng, "tblSalesman");
                //table.Name = "tblSalesman";

                 //Directly access ExcelTableCollection class
                ExcelTableCollection tblcollection = wsSheet1.Tables;
                ExcelTable table1 = 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 = "Country";
                table.Columns[4].Name = "Date";

                //table.ShowHeader = false;
                table.ShowFilter = true;
                //table.ShowTotal = true;
            }

2) How to Insert data into the Excel Table Cells? 
Here I used the Value property of ExcelRange class object & assign a specific data type value as per column name. 

Please see this below code.

//[Id] Column
            using (ExcelRange Rng = wsSheet1.Cells["B5"])
            {
                Rng.Value = Convert.ToInt32("1001");
            }

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

//[Sales Amount] Column 
            using (ExcelRange Rng = wsSheet1.Cells["D5"])
            {
                Rng.Value = Convert.ToDecimal("700.00");
            }

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

//[Date] Column
            using (ExcelRange Rng = wsSheet1.Cells["F5"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";

                Rng.Value = Convert.ToDateTime("08/26/2017");
            }

The output of Code:

Full Source Code:
using OfficeOpenXml;
using System.IO;
using OfficeOpenXml.Table;
using System;

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["B4:F12"])
            {
                //Indirectly access ExcelTableCollection class
                ExcelTable table = wsSheet1.Tables.Add(Rng, "tblSalesman");
                //table.Name = "tblSalesman";

                //Directly access ExcelTableCollection class
                //ExcelTableCollection tblcollection = wsSheet1.Tables;
                //ExcelTable table1 = 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 = "Country";
                table.Columns[4].Name = "Date";

                //table.ShowHeader = false;
                table.ShowFilter = true;
                //table.ShowTotal = true;
            }

            //Insert data into the Excel Table Cells
            
            //[ID] Columm
            using (ExcelRange Rng = wsSheet1.Cells["B5"]) { Rng.Value = Convert.ToInt32("1001"); }
            using (ExcelRange Rng = wsSheet1.Cells["B6"]) { Rng.Value = Convert.ToInt32("1002"); }
            using (ExcelRange Rng = wsSheet1.Cells["B7"]) { Rng.Value = Convert.ToInt32("1003"); }
            using (ExcelRange Rng = wsSheet1.Cells["B8"]) { Rng.Value = Convert.ToInt32("1004"); }
            using (ExcelRange Rng = wsSheet1.Cells["B9"]) { Rng.Value = Convert.ToInt32("1005"); }
            using (ExcelRange Rng = wsSheet1.Cells["B10"]) { Rng.Value = Convert.ToInt32("1006"); }
            using (ExcelRange Rng = wsSheet1.Cells["B11"]) { Rng.Value = Convert.ToInt32("1007"); }
            using (ExcelRange Rng = wsSheet1.Cells["B12"])  { Rng.Value = Convert.ToInt32("1008"); }

            //[SALESMAN NAME] Columm
            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"; }

            //[Sales Amount] Column 
            using (ExcelRange Rng = wsSheet1.Cells["D5"]) { Rng.Value = Convert.ToDecimal("700.00"); }
            using (ExcelRange Rng = wsSheet1.Cells["D6"]) { Rng.Value = Convert.ToDecimal("800.00"); }
            using (ExcelRange Rng = wsSheet1.Cells["D7"]) { Rng.Value = Convert.ToDecimal("1000.00");}
            using (ExcelRange Rng = wsSheet1.Cells["D8"]) { Rng.Value = Convert.ToDecimal("1100.00");}
            using (ExcelRange Rng = wsSheet1.Cells["D9"]) { Rng.Value = Convert.ToDecimal("5000.00");}
            using (ExcelRange Rng = wsSheet1.Cells["D10"]) { Rng.Value = Convert.ToDecimal("200.00");}
            using (ExcelRange Rng = wsSheet1.Cells["D11"]) { Rng.Value = Convert.ToDecimal("100.00");}
            using (ExcelRange Rng = wsSheet1.Cells["D12"]) { Rng.Value = Convert.ToDecimal("200.00");}

            //[Country] Columm
            using (ExcelRange Rng = wsSheet1.Cells["E5"]) { Rng.Value = "UK"; }
            using (ExcelRange Rng = wsSheet1.Cells["E6"]) { Rng.Value = "IND"; }
            using (ExcelRange Rng = wsSheet1.Cells["E7"]) { Rng.Value = "USA"; }
            using (ExcelRange Rng = wsSheet1.Cells["E8"]) { Rng.Value = "IND"; }
            using (ExcelRange Rng = wsSheet1.Cells["E9"]) { Rng.Value = "USA"; }
            using (ExcelRange Rng = wsSheet1.Cells["E10"]) { Rng.Value = "IND"; }
            using (ExcelRange Rng = wsSheet1.Cells["E11"]) {  Rng.Value = "UK"; }
            using (ExcelRange Rng = wsSheet1.Cells["E12"]) { Rng.Value = "UK"; }

             //[Date] Columm
            using (ExcelRange Rng = wsSheet1.Cells["F5"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("10/30/2016");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F6"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("06/23/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F7"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("05/13/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F8"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("09/10/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F9"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("07/26/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F10"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("08/26/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F11"])
            { Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("09/10/2017");
            }
            using (ExcelRange Rng = wsSheet1.Cells["F12"])
            {
                Rng.Style.Numberformat.Format = "mm/dd/yy";
                Rng.Value = Convert.ToDateTime("09/10/2017");
            }

            wsSheet1.Cells[wsSheet1.Dimension.Address].AutoFitColumns();
            ExcelPkg.SaveAs(new FileInfo(@"D:\ExcelTable.xlsx"));
        }
    }
}

• Now build & execute this code. The file is (ExcelTable.xlsx) store on D: drive of the computer.

Thank you for reading this article. 
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