DataTableExporter.CellValueConversionError Event

Fires if an error occurs during conversion of a specific cell value to the value which should be stored in a data table.

Namespace: DevExpress.Spreadsheet.Export

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

Declaration

C#

public event CellValueConversionErrorEventHandler CellValueConversionError

VB.NET

Public Event CellValueConversionError As CellValueConversionErrorEventHandler

Event Data

The CellValueConversionError event's data class is CellValueConversionErrorEventArgs. The following properties provide information specific to this event:

Property	Description
Action	Specifies the action performed after the DataTableExporter.CellValueConversionError event is handled.
Cell	Identifies the cell containing the value that cannot be converted.
CellValue	Gets the value that caused the error.
ConversionResult	Gets an enumeration member that identifies the cause of the conversion error.
DataColumn	Provides access to a column in a target DataTable that cannot be filled with data for the current row due to a conversion error.
DataTableValue	Gets or sets a value contained in the target DataTable.

The event data class exposes the following methods:

Method	Description
Initialize(Cell, CellValue, DataColumn, ConversionResult)	Initializes the object before passing it to a DataTableExporter.CellValueConversionError event handler.

Remarks

The following code illustrates how to handle the CellValueConversionError event.

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[0];
            Range 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 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.
            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

Related GitHub Examples

The following code snippets (auto-collected from DevExpress Examples) contain references to the CellValueConversionError event.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

C#

how-to-export-cell-range-to-a-datatable-e4997/CS/ExportToDataTableExample/Form1.cs#L104

// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;

gridview-how-to-upload-an-excel-file-via-uploadcontrol-and-show-its-data-in-a-grid-t576892/CS/UploadControlApplication/Models/HelperClass.cs#L21

DataTableExporter exporter = sheet.CreateDataTableExporter(range, table, false);
exporter.CellValueConversionError += exporter_CellValueConversionError;
exporter.Export();

VB.NET

how-to-export-cell-range-to-a-datatable-e4997/VB/ExportToDataTableExample/Form1.vb#L98

' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError

gridview-how-to-upload-an-excel-file-via-uploadcontrol-and-show-its-data-in-a-grid-t576892/VB/UploadControlApplication/Models/HelperClass.vb#L21

Dim exporter As DataTableExporter = sheet.CreateDataTableExporter(range, table, False)
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
exporter.Export()