Tables

Tables can be created via the Tables property in the ExcelWorksheet class. EPPlus has 60 built in table styles to choose from and many parameters to configure the appearence of the table

Add a table to a worksheet

Here is an example that will help you getting started with Excel Tables.

using(var package = new ExcelPackage(file))
{
    var sheet = package.Workbook.Worksheets.Add("Tables");

    // headers
    sheet.Cells["A1"].Value = "Month";
    sheet.Cells["B1"].Value = "Sales";
    sheet.Cells["C1"].Value = "VAT";
    sheet.Cells["D1"].Value = "Total";

    // Fill the table range with some data...
    var rnd = new Random();
    for (var row = 2; row < 12; row++)  
    {
        sheet.Cells[row, 1].Value = new DateTimeFormatInfo().GetMonthName(row);
        sheet.Cells[row, 2].Value = rnd.Next(10000, 100000);
        sheet.Cells[row, 3].Formula = $"B{row} * 0.25";
        sheet.Cells[row, 4].Formula = $"B{row} + C{row}";
    }
    sheet.Cells["B2:D13"].Style.Numberformat.Format = "€#,##0.00";

    // Table range including header row
    var range = sheet.Cells["A1:D11"];

    // create the table
    var table = sheet.Tables.Add(range, "myTable");
    // configure the table
    table.ShowHeader = true;
    table.ShowFirstColumn = true;
    table.TableStyle = TableStyles.Dark2;
    // add a totals row under the data
    table.ShowTotal = true;
    table.Columns[1].TotalsRowFunction = RowFunctions.Sum;
    table.Columns[2].TotalsRowFunction = RowFunctions.Sum;
    table.Columns[3].TotalsRowFunction = RowFunctions.Sum;

    // Calculate all the formulas including the totals row.
    // This will give input to the AutofitColumns call
    range.Calculate();
    range.AutoFitColumns();

    package.Save();
}

This will result in the following table

Export the table to a System.Data.DataTable

You can export the data in the table range to a DataTable. See the ToDataTable method to explore the possibilities.

var dataTable = table.ToDataTable();

See Table-filters-and-slicers for more advanced usage.

See sample 4 & 5 in the sample project Sample-.NET Framework or Sample-.NET Framework.