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

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

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

See also

• Tables