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"]) {
-
- ExcelTable table = wsSheet1.Tables.Add(Rng, "tblSalesman");
-
-
- ExcelTableCollection tblcollection = wsSheet1.Tables;
- ExcelTable table1 = tblcollection.Add(Rng, "tblSalesman");
-
- 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.ShowFilter = 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.
-
- using(ExcelRange Rng = wsSheet1.Cells["B5"]) {
- Rng.Value = Convert.ToInt32("1001");
- }
-
- using(ExcelRange Rng = wsSheet1.Cells["C5"]) {
- Rng.Value = "John";
- }
-
- using(ExcelRange Rng = wsSheet1.Cells["D5"]) {
- Rng.Value = Convert.ToDecimal("700.00");
- }
-
- using(ExcelRange Rng = wsSheet1.Cells["E5"]) {
- Rng.Value = "UK";
- }
-
- 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
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.