DataTableExporterExtensions.Export(DataTableExporter) Method

Exports a cell range to a DataTable.

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

Assembly: DevExpress.Docs.v24.2.dll

NuGet Package: DevExpress.Document.Processor

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	An object that performs export to a data table.

Remarks

Use the WorksheetExtensions.CreateDataTableExporter method to create a new DataTableExporter instance. Call its Export method to export the specified cell range to the DataTable.

You can implement a ICellValueToColumnTypeConverter interface to control the conversion process (analyze cell values and modify them as required). See the How to: Use a Custom Converter for Export to a DataTable topic for details.

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