Tables ‐ working with DataRows

The DataRows property of the ExcelTable class was introduced in EPPlus 8. This property makes it easier to modify the content of an ExcelTable.

The ExcelTableRow class

This class represents a table row and contains methods and properties to modify the row.

Method	Return type	Description
GetValue(string columnName)	System.object	Returns the cell value at the given column
GetValue<T>(string columnName)	An instance of T	Returns the cell value at the given column

Create table and set values

Create an empty table with two columns ("Col1" and "Col2")

using var p = new ExcelPackage();
var sheet = p.Workbook.Worksheets.Add("Sheet1");
sheet.Cells["A1"].Value = "Col1";
sheet.Cells["B1"].Value = "Col2";
var tbl = sheet.Tables.Add(sheet.Cells["A1:B2"], "Table1");

Excel tables are always created with column names and an empty row. Let's set values on the first row.

// use column names...
tbl.DataRows[0].SetValue("Col1", 1)
tbl.DataRows[0].SetValue("Col2", 2);
// or 0-based column index
tbl.DataRows[0].SetValue(0, 1)
tbl.DataRows[0].SetValue(1, 2);

You can also set multiple cell values via the SetValues function. The values will then be set left-to-right in the row's range.

tbl.DataRows[0].SetValues(1, 2)
// or
tbl.DataRows[0].SetValues(new int[]{ 1, 2 });

Read values via DataRows

The DataRows property is an IEnumerable<ExcelTableRow> so it can easily be queried with Linq using the GetValue method.

var matchingRows = tbl.DataRows.Where(r => r.GetValue<int>("Col1") == 1);
// or use a 0-based column index
matchingRows = tbl.DataRows.Where(r => r.GetValue<int>(1) == 1);

Add, Insert and Delete rows

Here is how to add new rows to a table.

// Add one new row at the bottom of the table
ExcelTableRow newRow = tbl.DataRows.AddNewRow();
newRow.SetValue("Col1", 1);
// or multiple rows
IEnumerable<ExcelTableRow> newRows = tbl.DataRows.AddNewRows(2);
newRows.First().SetValue("Col1", 1);

Insert new rows at a given position in the table

// 0-based position of the inserted row
var position = 2;
var numberOfRowsToInsert = 2;
IEnumerable<ExcelTableRow> insertedRows = tbl.DataRows.InsertNewRows(position , numberOfRowsToInsert);

Delete a number of rows at a given position

var position = 2;
var numberOfRowsToDelete = 2;
tbl.DataRows.DeleteRows(position, numberOfRowsToDelete );