How to: Export a Worksheet Range to a DataTable

• Dec 12, 2022
• 4 minutes to read

You can export data from a worksheet cell range to a DataTable. In this case, worksheet columns are transformed into DataTable columns. Cell values are used to populate the DataTable, and you can specify conversion methods and take control of the conversion process for every cell (analyze the data and modify the value as required ).

To export cell values to a data table, the following steps are required:

1. Add a reference to the DevExpress.Docs.v23.2.dll assembly to your project. Note that the Universal Subscription or an additional Office File API Subscription is required to distribute this assembly.
2. Create a DataTableExporter instance using the WorksheetExtensions.CreateDataTableExporter method.
3. Call the DataTableExporterExtensions.Export method of the DataTableExporter.

You can easily create an empty DataTable, which will fit the data contained in the specified worksheet range by using the CreateDataTable method of the Worksheet (WorksheetExtensions.CreateDataTable). The newly created DataTable contains the same number of columns as the worksheet range. Column data types are set automatically by analyzing the content of the first row in a range that contains data. Column names can be obtained from the first row of a range if the rangeHasHeaders method parameter is set to true.

Note

The CreateDataTable method is located in the WorksheetExtensions object. When you add a reference to the assembly containing this extension, all extension methods are added to the available Worksheet methods. You can call the method using the Worksheet.CreateDataTable notation.

The following code demonstrates how to export a selected range to a DataTable.

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