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