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.

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. Lets 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);