WorksheetExtensions.CreateDataTable(Worksheet, Range, Boolean) Method

Creates an empty data table from the specified cell range.

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

Namespace: DevExpress.Spreadsheet

Assembly: DevExpress.Docs.v18.2.dll

Declaration

C#

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

VB.NET

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

Parameters

Name	Type	Description
sheet	Worksheet	A Worksheet object that is a worksheet containing the specified range.
range	Range	A Range 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. The first row of the specified cell range defines column names if the rangeHasHeaders parameter is set to true; otherwise the first row is used to determine the column data types.

If the first row in a specified range contains column names (the rangeHasHeaders parameter for the CreateDataTable method is set to true), the second row is used to determine the column data types.

To determine the column data type for the resulting data table, the CellValue.Type of data contained in a cell is used.

Note

During export, if the cell value type does not match the type of the column in the resulting data table, the exception Conversion error (Subscribe to the CellValueConversionError event) may occur. To prevent this, you can validate cell value types and handle the DataTableExporter.CellValueConversionError event, as illustrated in the following example. Another workaround is to use the WorksheetExtensions.CreateDataTable(Worksheet,Range,Boolean,Boolean) method, which treats all cell values as text. In this case, all columns in the resulting table will have the string type.

Example

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-export-cell-range-to-a-datatable-e4997

Form1.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
            Worksheet worksheet = spreadsheetControl1.Document.Worksheets.ActiveWorksheet;
            Range 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.
            for (int col = 0; col < range.ColumnCount; col++)
            {
                CellValueType cellType = range[0, col].Value.Type;
                for (int r = 1; r < range.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 Range = 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.
            For col As Integer = 0 To range.ColumnCount - 1
                Dim cellType As CellValueType = range(0, col).Value.Type
                For r As Integer = 1 To range.RowCount - 1
                    If cellType <> range(r, col).Value.Type Then
                        dataTable.Columns(col).DataType = GetType(String)
                        Exit For
                    End If
                Next r
            Next col

            ' 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