ToDataTable

This method lets you export data from a range or a table into a System.Data.DataTable.

Default behaviour

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 called).
• 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<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);

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