**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` | 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` | 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(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(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") ```csharp 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"); ``` ```vbnet 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. ```csharp // 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); ``` ```vbnet ' 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. ```csharp tbl.DataRows[0].SetValues(1, 2) // or tbl.DataRows[0].SetValues(new int[]{ 1, 2 }); ``` ```vbnet 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` so it can easily be queried with Linq using the `GetValue` method. ```csharp var matchingRows = tbl.DataRows.Where(r => r.GetValue("Col1") == 1); // or use a 0-based column index matchingRows = tbl.DataRows.Where(r => r.GetValue(0) == 1); ``` ```vbnet 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. ```csharp // Add one new row at the bottom of the table ExcelTableRow newRow = tbl.DataRows.AddNewRow(); newRow.SetValue("Col1", 1); // or multiple rows IEnumerable newRows = tbl.DataRows.AddNewRows(2); newRows.First().SetValue("Col1", 1); ``` ```vbnet ' 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 ```csharp // 0-based position of the inserted row var position = 2; var numberOfRowsToInsert = 2; IEnumerable insertedRows = tbl.DataRows.InsertNewRows(position , numberOfRowsToInsert); ``` ```vbnet ' 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 ```csharp var position = 2; var numberOfRowsToDelete = 2; tbl.DataRows.DeleteRows(position, numberOfRowsToDelete ); ``` ```vbnet Dim position = 2 Dim numberOfRowsToDelete = 2 tbl.DataRows.DeleteRows(position, numberOfRowsToDelete) ``` ## See also - [Tables]()