**This method lets you export data from a range or a table into a `System.Data.DataTable`. It can be used to move data from a spreadsheet into a database, but also for export to other formats supported by the .NET `DataSet`/`DataTable` classes. This functionality is available from version 5.4.1** ## Default behaviour You can call this method without arguments, in this case the method will execute with its default behaviour. ```csharp var dataTable = worksheet.Cells["A1:F2"].ToDataTable(); ``` ```vbnet Dim dataTable = worksheet.Cells("A1:F2").ToDataTable() ``` In this case it is assumed that: - The first row of the range contains the column names, the remaining rows contains data. - Errors in the cells will be handled as blank values. - Blank values are not allowed for value types (`InvalidOperationException` will be thrown). - Rows where every cell is empty will be ignored. - The data type of each `DataColumn` in the `DataTable` will be determined by the first non blank cell in the corresponding column in the range. ## ToDataTableOptions You can control the behaviour of the `ToDataTable` method by setting values on a `ToDataTableOptions` instance. This can be done either by using the method signature with an `Action` argument: ```csharp // set the DataTable's Name property to "dt2" var dt2 = sheet.Cells["A1:F11"].ToDataTable(o => o.DataTableName = "dt2"); ``` ```vbnet ' set the DataTable's Name property to "dt2" Dim dt2 = sheet.Cells("A1:F11").ToDataTable(Sub(o) o.DataTableName = "dt2") ``` ...or by supplying an instance of the `ToDataTableOptions` class to the `ToDataTable` method: ```csharp var options = ToDataTableOptions.Create(); options.DataTableName = "dt2"; var dt2 = sheet.Cells["A1:F1"].ToDataTable(options); ``` ```vbnet Dim options = ToDataTableOptions.Create() options.DataTableName = "dt2" Dim dt2 = sheet.Cells("A1:F1").ToDataTable(options) ``` ### Properties Here are the properties you can set on the `ToDataTableOptions` class: | Name | Data type | Default value | Description | | --- | --- | --- | --- | | `FirstRowIsColumnNames` | `bool` | true | Indicates whether the first row of the range are the column names. | | `ColumnNameParsingStrategy` | `NameParsingStrategy` (enum) | Preserve | If the `FirstRowIsColumnNames` is true, this property controls how the column names are parsed. `Preserve` - Preserve the input string as it is, `SpaceToUnderscore` - Replaces any spaces with underscores, `RemoveSpace` - Removes all spaces. | | `SkipNumberOfRowsStart` | `int` | 0 | Number of rows that will be skipped from the start (top) of the range. If `FirstRowIsColumnNames` is true, this will be applied after the first row (column names) has been read. | | `SkipNumberOfRowsEnd` | `int` | 0 | Number of rows that will be skipped from the end (bottom) of the range. | | `ExcelErrorParsingStrategy` | `ExcelErrorParsingStrategy` (enum) | HandleExcelErrorsAsBlankCells | Sets how Excel error values are handled when detected. `HandleExcelErrorsAsBlankCells` - Excel Errors in cells will be handled as blank cells, `ThrowException` - An exception will be thrown when an error occurs in a cell, `IgnoreRowWithErrors` - If an error is detected, the entire row will be ignored | | `EmptyRowStrategy` | `EmptyRowStrategy` (enum) | Ignore | Controls how rows where all cells are blank should be handled. `Ignore`- Ignore the empty row and continue with next, `StopAtFirst` - Stop reading data into the DataTable when the first empty row occurs. | | `Mappings` | | | A collection of mappings that overrides the default behaviour for a specific column. | | `AlwaysAllowNull` | `bool` | `false` | If true null values will be allowed in all mappings per default. Added in v6.2.14 and v7.0.3 | | `PredefinedMappingsOnly` | `bool` | false | If true all columns that are not present in the `Mappings` collection will be ignored. | | `ColumnNamePrefix` | `string` | "column" | If column names are not read from the first row, column names will be generated using this prefix followed by a number (1..n) | | `DataTableName` | `string` | "dataTable1" | Name of the `DataTable` | | `DataTableNamespace` | `string` | | Namespace of the `DataTable` | | `Transpose` | `bool` | `false` | Transpose data on export | ### Methods `SetPrimaryKey(params string[] columnNames)` Sets the supplied column or columns to the primary key of the `DataTable` `SetPrimaryKey(params int[] zeroBasedRangeIndexes)` Sets the column or columns at the corresponding zero based index/indexes (in the range) to the primary key of the `DataTable`. ### Mappings You can configure how certain columns in the range are mapped to the columns in the `DataTable` by using the `Mappings` property of the `ToDataTableOptions` class. See this example: ```csharp var dataTable = sheet.Cells["A1:F11"].ToDataTable(c => { // set name and namespace c.DataTableName = "MyDataTable"; c.DataTableNamespace = "MyNamespace"; // Removes spaces in column names when read from the first row c.ColumnNameParsingStrategy = NameParsingStrategy.RemoveSpace; // Rename the third column from E-Mail to EmailAddress c.Mappings.Add(2, "EmailAddress"); // Ensure that the OrderDate column is casted to DateTime (in Excel it can sometimes be stored as a double/OADate) c.Mappings.Add(4, "OrderDate", typeof(DateTime)); // Change the OrderValue to a string c.Mappings.Add(5, "OrderValue", typeof(string), false, cellVal => "Val: " + cellVal.ToString()); // Skip the first 2 rows c.SkipNumberOfRowsStart = 2; // Skip the last 3 rows c.SkipNumberOfRowsEnd = 3; }); ``` ```vbnet Dim dataTable = sheet.Cells("A1:F11").ToDataTable(Sub(c) ' set name and namespace c.DataTableName = "MyDataTable" c.DataTableNamespace = "MyNamespace" ' Removes spaces in column names when read from the first row c.ColumnNameParsingStrategy = NameParsingStrategy.RemoveSpace ' Rename the third column from E-Mail to EmailAddress c.Mappings.Add(2, "EmailAddress") ' Ensure that the OrderDate column is casted to DateTime (in Excel it can sometimes be stored as a double/OADate) c.Mappings.Add(4, "OrderDate", GetType(DateTime)) ' Change the OrderValue to a string c.Mappings.Add(5, "OrderValue", GetType(String), False, Function(cellVal) "Val: " & cellVal.ToString()) ' Skip the first 2 rows c.SkipNumberOfRowsStart = 2 ' Skip the last 3 rows c.SkipNumberOfRowsEnd = 3 End Sub) ``` ### Add mappings You can add mappings by calling the `Add` method on the `Mappings` collection on the `ToDataTableOptions` instance. The first argument of these method signatures is always the zero based index of a column in the range on the spreadsheet. See examples above. - `Add(int zeroBasedIndexInRange, string columnName)` - `Add(int zeroBasedIndexInRange, string columnName, bool allowNull)` - `Add(int zeroBasedIndexInRange, string columnName, Func transformCellValueFunc)` - you can use the transformCellValueFunc to control how the values should be cast/transformed before they are written to the DataTable. - `Add(int zeroBasedIndexInRange, string columnName, Type columnDataType)` - if you specify a type, this type will be used for this column in the DataTable instead of using the first value in the range. - `Add(int zeroBasedIndexInRange, string columnName, Type columnDataType, bool allowNull, Func transformCellValueFunc)` - `Add(int zeroBasedIndexInRange, DataColumn dataColumn)` - `Add(int zeroBasedIndexInRange, DataColumn dataColumn, Func transformCellValueFunc)` ## Use an existing DataTable If you prefer you can setup the `DataTable` yourself instead of letting EPPlus create it as shown above. ```csharp var dataTable3 = new DataTable("myDataTableWithMappings", "myNamespace"); var col1 = dataTable3.Columns.Add("CompanyName"); var col2 = dataTable3.Columns.Add("Email"); sheet.Cells["A1:F11"].ToDataTable(o => { o.FirstRowIsColumnNames = true; o.Mappings.Add(0, col1); o.Mappings.Add(1, col2); } , dataTable3); ``` ```vbnet Dim dataTable3 = New DataTable("myDataTableWithMappings", "myNamespace") Dim col1 = dataTable3.Columns.Add("CompanyName") Dim col2 = dataTable3.Columns.Add("Email") sheet.Cells("A1:F11").ToDataTable(Sub(o) o.FirstRowIsColumnNames = True o.Mappings.Add(0, col1) o.Mappings.Add(1, col2) End Sub, dataTable3) ``` You can also use this method on an [Excel Table](). ## See also - [Sample 2.4-C\#]() and [Sample 2.4-VB]() - [Export data]()