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

You can call this method without arguments, in this case the method will execute with its default behaviour.

var 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.

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<ToDataTableOptions> argument:

// set the DataTable's Name property to "dt2"
var dt2 = sheet.Cells["A1:F11"].ToDataTable(o => o.DataTableName = "dt2");

...or by supplying an instance of the ToDataTableOptions class to the ToDataTable method:

var options = ToDataTableOptions.Create();
options.DataTableName = "dt2";
var dt2 = sheet.Cells["A1:F1"].ToDataTable(options);

Here are the properties you can set on the ToDataTableOptions class:

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.

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:

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;

});

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<object, object> 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<object, object> transformCellValueFunc)
• Add(int zeroBasedIndexInRange, DataColumn dataColumn)
• Add(int zeroBasedIndexInRange, DataColumn dataColumn, Func<object, object> transformCellValueFunc)

If you prefer you can setup the DataTable yourself instead of letting EPPlus create it as shown above.

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

You can also use this method on an Excel Table.

• Sample 2.4-C# and Sample 2.4-VB
• Export data