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

In this tutorial, we will learn to create Excel Table using EPPlus and insert data into the table. This is an Epplus Beginners Guide Part-14.

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.

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;
}

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.

//[Id] Column
using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
    Rng.Value = Convert.ToInt32("1001");
}
//[Salesman Name] Columm
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] Columm
using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
    Rng.Value = "UK";
}
//[Date] Columm
using(ExcelRange Rng = wsSheet1.Cells["F5"]) {
    Rng.Style.Numberformat.Format = "mm/dd/yy";
    Rng.Value = Convert.ToDateTime("08/26/2017");
}

The output of the 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) 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