DataTableExporterExtensions.Export(DataTableExporter) Method

Performs export of the cell range to the DataTable.

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

Namespace: DevExpress.Spreadsheet.Export

Assembly: DevExpress.Docs.v18.2.dll

Declaration

C#

public static void Export(
    this DataTableExporter exporter
)

VB.NET

<ExtensionAttribute>
Public Shared Sub Export(
    exporter As DataTableExporter
)

Parameters

Name	Type	Description
exporter	DataTableExporter	A DataTableExporter object created to export data.

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