Tables - DON-PHAM/EPPlus GitHub Wiki

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. From EPPlus 5.6 you can also create and modify custom table styles.

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

Images/Tables1.png

Change an existing table

using(var package = new ExcelPackage(file))
{
    var sheet = package.Workbook.Worksheets["Tables"];
    // get a table by its name and change properties
    var myTable = sheet.Tables["myTable"];
    myTable.ShowFirstColumn = false;
    myTable.ShowLastColumn = true;
    myTable.TableStyle = TableStyles.Medium8;

    package.Save();
}

This will result in the following table

Images/Tables2.png

Insert rows/cols

From EPPlus 5 you can add rows/cols to an existing table, references in affected cells will be updated/shifted. Here is an example where we add a row to an existing table.

// you can also supply number of new rows as an argument to the AddRow method
var rowRange = table.AddRow();
var newRowIx = rowRange.Start.Row;
sheet.Cells[newRowIx, 1].Value = new DateTimeFormatInfo().GetMonthName(newRowIx);
sheet.Cells[newRowIx, 2].Value = rnd.Next(10000, 100000);
sheet.Cells[newRowIx, 3].Formula = $"B{newRowIx} * 0.25";
sheet.Cells[newRowIx, 4].Formula = $"B{newRowIx} + C{newRowIx}";
rowRange.Style.Numberformat.Format = "€#,##0.00";

Export the table data 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.

This method was introduced in EPPlus 5.4.1.

var myTable = sheet.Tables["myTable"];
var dataTable = myTable.ToDataTable();

Delete a table

The delete method (see below) also has method signatures where you can choose to delete all data in the range.

using (var package = new ExcelPackage(file))
{
    var sheet = package.Workbook.Worksheets["Tables"];
    // get a table by its name and change properties
    sheet.Tables.Delete("myTable");

    package.Save();
}

See Table-filters-and-slicers for more advanced usage and Built in Table styles/custom table styles to explore the table styles.

See sample 4, 5 and 28 in the sample project Sample-.NET Framework or Sample-.NET Framework.