DataTableExporter Class

Performs export of the worksheet range to the data table.

Namespace: DevExpress.Spreadsheet.Export

Assembly: DevExpress.Spreadsheet.v21.1.Core.dll

NuGet Package: DevExpress.Spreadsheet.Core

Declaration

C#

public class DataTableExporter

VB.NET

Public Class DataTableExporter

Related API Members

The following members return DataTableExporter objects:

• WorksheetExtensions.CreateDataTableExporter(Worksheet, CellRange, DataTable, Boolean)

Remarks

To export a worksheet range to a DataTable, perform the following steps.

1. Create a new DataTable instance. You can also use the WorksheetExtensions.CreateDataTable method to create a DataTable based on the first row of the range containing data for export.

2. Instantiate a DataTableExporter object.

   Note

   When creating an exporter, make sure that the number of data columns in the data table is the same as the number of columns in the cell range from which data is obtained; otherwise, a System.ArgumentException will be thrown.

   Therefore, if you need to add an additional column to the data table (e.g., an auto-incremented or computed column), you should do it only after the DataTableExporter instance is created.

3. Specify general export options using the DataTableExporter.Options property.
4. Change default converter settings (if required) using the DataTableExportOptions.DefaultCellValueToColumnTypeConverter and the DataTableExportOptions.DefaultCellValueToStringConverter properties.
5. Add custom converters, if required, using the DataTableExportOptions.CustomConverters property.
6. Call the DataTableExporterExtensions.Export method.

Example

View Example

Form1.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
            Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
            CellRange range = worksheet.Tables[0].Range;

            // Create a data table with column names obtained from the first row in a range.
            // 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, true);

            // Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table. 
            DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
            // Handle value conversion errors.
            exporter.CellValueConversionError += exporter_CellValueConversionError;

            // Specify exporter options.
            exporter.Options.ConvertEmptyCells = true;
            exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0;
            exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = barCheckItemSkipErrors.Checked;

            // 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(0)
            Dim range As CellRange = worksheet.Tables(0).Range

            ' Create a data table with column names obtained from the first row in a range.
            ' 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, True)

            ' Create the exporter that obtains data from the specified range which has a header row and populates the previously created data table. 
            Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
            ' Handle value conversion errors.
            AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError

            ' Specify exporter options.
            exporter.Options.ConvertEmptyCells = True
            exporter.Options.DefaultCellValueToColumnTypeConverter.EmptyCellValue = 0
            exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = barCheckItemSkipErrors.Checked

            ' 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

Inheritance

Object

DataTableExporter

Extension Methods

Export()