Skip to content

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 ExcelTable.DataRows property

This property is an ExcelTableRowCollection and represents all rows with data in the table.

Method Return type Description
AddNewRow(bool copyStyles = true) ExcelTableRow Adds a new empty row at the bottom of the table
AddNewRows(int nRows, bool copyStyles = true) IEnumerable<ExcelTableRow> Add a number of new empty rows at the bottom the table.
InsertNewRow(int position, bool copyStyles = true) ExcelTableRow Inserts a new empty row at the specified position.
InsertNewRows(int position, int nRows = 1, bool copyStyles = true) IEnumerable<ExcelTableRow> Inserts one or more new empty rows at the specified position.
DeleteRows(int position, int numberOfRows = 1) void Deletes the specified number of rows at the given position in the table
Clear() void Clears/deletes all rows from the table, leaving only one empty row under the column headers.
this[int ix] ExcelTableRow indexer, 0-based index

The ExcelTableRow class

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

Properties

Property Property type Description
ColumnCount int Number of columns in the table
IsHidden bool Indicates if the table row is hidden.
IsEmpty bool Indicates if the table row is empty, i.e. has no values in its cells.
IsDeleted bool Indicates if the table row has been deleted
RowRange ExcelRangeBase The range of this table row, such as A8:F8

Methods

Method Return type Description
GetValue(string columnName) System.object Returns the cell value at the given column
GetValue<T>(string columnName) T Returns the cell value at the given column
GetValue(int offsetIndex) System.object Returns the cell value at the given column
GetValue<T>(int offsetIndex) T Returns the cell value at the given column
GetFormula(string columnName) string Returns formula by column name.
GetFormula(int offsetIndex) string Returns formula by 0-based column index
SetValue(string columnName, object value) ExcelTableRow Sets the cell value at the given column
SetValue(int offsetIndex, object value) ExcelTableRow Sets the cell value at the given column
SetValues(params object[] values) void Set all the cell values of the table row by providing an array of objects
Delete() void Deletes the row from the table
Clear() void Clear all cell values in the row's range.

Examples

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");

Using p As New ExcelPackage()
    Dim sheet = p.Workbook.Worksheets.Add("Sheet1")
    sheet.Cells("A1").Value = "Col1"
    sheet.Cells("B1").Value = "Col2"
    Dim tbl = sheet.Tables.Add(sheet.Cells("A1:B2"), "Table1")
End Using

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

' 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 });

tbl.DataRows(0).SetValues(1, 2)
' or
tbl.DataRows(0).SetValues(New Integer() {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>(0) == 1);

Dim matchingRows = tbl.DataRows.Where(Function(r) r.GetValue(Of Integer)("Col1") = 1)
' or use a 0-based column index
matchingRows = tbl.DataRows.Where(Function(r) r.GetValue(Of Integer)(0) = 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);

' Add one new row at the bottom of the table
Dim newRow As ExcelTableRow = tbl.DataRows.AddNewRow()
newRow.SetValue("Col1", 1)
' or multiple rows
Dim newRows As IEnumerable(Of ExcelTableRow) = 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);

' 0-based position of the inserted row
Dim position = 2
Dim numberOfRowsToInsert = 2
Dim insertedRows As IEnumerable(Of ExcelTableRow) = 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 );

Dim position = 2
Dim numberOfRowsToDelete = 2
tbl.DataRows.DeleteRows(position, numberOfRowsToDelete)

See also