WorksheetExtensions.CreateDataTable(Worksheet, CellRange, Boolean) Method

Creates an empty data table from the specified cell range.

You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this method in production code.

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Docs.v24.1.dll

NuGet Package: DevExpress.Document.Processor

Declaration

C#

public static DataTable CreateDataTable(
    this Worksheet sheet,
    CellRange range,
    bool rangeHasHeaders
)

VB.NET

<ExtensionAttribute>
Public Shared Function CreateDataTable(
    sheet As Worksheet,
    range As CellRange,
    rangeHasHeaders As Boolean
) As DataTable

Parameters

Name	Type	Description
sheet	Worksheet	A Worksheet object that is a worksheet containing the specified range.
range	CellRange	A CellRange of cells that will be transformed to a data table.
rangeHasHeaders	Boolean	true, to use the content of the first row in a range as column names in a data table; otherwise, false.

Returns

Type	Description
DataTable	A DataTable object that is the resulting data table.

Remarks

The CreateDataTable method creates an empty data table. The number of columns in a data table is the same as the number of columns in the specified range.

Pass true as the rangeHasHeaders parameter to use the first row to define column names. The first row determines the column data types if the rangeHasHeaders parameter is set to false.

Use the CellValue.Type of data contained in a cell to determine the column data type for the resulting data table.

Note

If the cell value type does not match the column type in the resulting data table, the exception Conversion error (Subscribe to the CellValueConversionError event) may occur. You can validate cell value types and handle the DataTableExporter.CellValueConversionError event to prevent this behavior.

You can also call the CreateDataTable(Worksheet, CellRange, Boolean, Boolean) method overload. In this case, all columns in the resulting table will have the string type.

Example

The example below shows how to export a cell range to a System.Data.DataTable object.

View Example

Form1.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;

Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
CellRange range = worksheet.Selection;
bool rangeHasHeaders = this.barCheckItemHasHeaders1.Checked;

// Create a data table with column names obtained from the first row in a range if it has headers.
// Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders);

//Validate cell value types. If cell value types in a column are different, the column values are exported as text.
int firstDataRowIndex = rangeHasHeaders ? 1 : 0;
int rowCount = range.RowCount;
if (firstDataRowIndex < rowCount) {
    for (int col = 0; col < range.ColumnCount; col++) {
        CellValueType cellType = range[firstDataRowIndex, col].Value.Type;
        for (int r = firstDataRowIndex + 1; r < rowCount; r++) {
            if (cellType != range[r, col].Value.Type) {
                dataTable.Columns[col].DataType = typeof(string);
                break;
            }
        }
    }
}

// Create the exporter that obtains data from the specified range, 
// skips the header row (if required) and populates the previously created data table. 
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;

// Perform the export.
exporter.Export();
void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e)
{
MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
e.DataTableValue = null;
e.Action = DataTableExporterAction.Continue;
}

Form1.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export

Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet
Dim range As CellRange = worksheet.Selection
Dim rangeHasHeaders As Boolean = Me.barCheckItemHasHeaders1.Checked

' Create a data table with column names obtained from the first row in a range if it has headers.
' Column data types are obtained from cell value types of cells in the first data row of the worksheet range.
Dim dataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders)

'Validate cell value types. If cell value types in a column are different, the column values are exported as text.
Dim firstDataRowIndex As Integer = If(rangeHasHeaders, 1, 0)
Dim rowCount As Integer = range.RowCount
If firstDataRowIndex < rowCount Then
    For col As Integer = 0 To range.ColumnCount - 1
        Dim cellType As CellValueType = range(firstDataRowIndex, col).Value.Type
        For r As Integer = firstDataRowIndex + 1 To rowCount - 1
            If cellType IsNot range(r, col).Value.Type Then
                dataTable.Columns(col).DataType = GetType(String)
                Exit For
            End If
        Next r
    Next col
End If

' Create the exporter that obtains data from the specified range, 
' skips the header row (if required) and populates the previously created data table. 
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError

' Perform the export.
exporter.Export()
Private Sub exporter_CellValueConversionError(ByVal sender As Object, ByVal e As CellValueConversionErrorEventArgs)
MessageBox.Show("Error in cell " & e.Cell.GetReferenceA1())
e.DataTableValue = Nothing
e.Action = DataTableExporterAction.Continue
End Sub