How to: Use a Custom Converter for Export to a DataTable

• Apr 17, 2023
• 3 minutes to read

The DataTableExportOptions.CustomConverters property enables you to specify a custom converter for a specific DataTable column. To do this, create a class that implements the ICellValueToColumnTypeConverter interface. The ICellValueToColumnTypeConverter.Convert method performs the required conversion. Call the Add method to add an instance of a custom converter to the DataTableExporter.Options.CustomConverters collection.

The Convert method is called for each cell that is exported to the specified column.

The following code snippet illustrates the technique. The converter transforms DateTime values into strings in MMMM-yyyy format, and displays the “N/A” text if a cell contains an error.

View Example: Spreadsheet Document API - Export a Worksheet Range to a DataTable

C#

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using System.Data;
using DevExpress.XtraBars.Ribbon;

public partial class Form1 : RibbonForm {
//...

    Workbook wbook = new Workbook();
    wbook.LoadDocument("TopTradingPartners.xlsx");
    Worksheet worksheet = wbook.Worksheets[0];
    CellRange range = worksheet.Tables[0].Range;

    DataTable dataTable = worksheet.CreateDataTable(range, true);

    // Change the data type of the "As Of" column to text.
    dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");

    DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
    exporter.CellValueConversionError += exporter_CellValueConversionError;
    MyConverter myconverter = new MyConverter();
    exporter.Options.CustomConverters.Add("As Of", myconverter);

    // Set the export value for empty cell.
    myconverter.EmptyCellValue = "N/A";
    exporter.Options.ConvertEmptyCells = true;

    exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;
    exporter.Export();

    void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e) {
        MessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
        e.DataTableValue = null;
        e.Action = DataTableExporterAction.Continue;
    }
}

class MyConverter : ICellValueToColumnTypeConverter {
    public bool SkipErrorValues { get; set; }
    public CellValue EmptyCellValue { get; set; }

    public ConversionResult Convert(Cell readOnlyCell, CellValue cellValue, Type dataColumnType, out object result) {
        result = DBNull.Value;
        ConversionResult converted = ConversionResult.Success;
        if (cellValue.IsEmpty) {
            result = EmptyCellValue;
            return converted;
        }
        if (cellValue.IsError) {
            // You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
            // return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
            result = "N/A";
            return ConversionResult.Success;
        }
        result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue);
        return converted;
    }
}

VB.NET

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export
Imports System.Data
Imports DevExpress.XtraBars.Ribbon

Partial Public Class Form1
    Inherits RibbonForm

'...

    Private wbook As New Workbook()
    wbook.LoadDocument("TopTradingPartners.xlsx")
    Dim worksheet As Worksheet = wbook.Worksheets(0)
    Dim range As CellRange = worksheet.Tables(0).Range

    Dim dataTable As DataTable = worksheet.CreateDataTable(range, True)

    ' Change the data type of the "As Of" column to text.
    dataTable.Columns("As Of").DataType = System.Type.GetType("System.String")

    Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
    AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError
    Dim myconverter As New MyConverter()
    exporter.Options.CustomConverters.Add("As Of", myconverter)

    ' Set the export value for empty cell.
    myconverter.EmptyCellValue = "N/A"
    exporter.Options.ConvertEmptyCells = True

    exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = False
    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
End Class

Friend Class MyConverter
    Implements ICellValueToColumnTypeConverter

    Public Property SkipErrorValues() As Boolean
    Public Property EmptyCellValue() As CellValue

    Public Function Convert(ByVal readOnlyCell As Cell, ByVal cellValue As CellValue, ByVal dataColumnType As Type, <System.Runtime.InteropServices.Out()> ByRef result As Object) As ConversionResult
        result = DBNull.Value
        Dim converted As ConversionResult = ConversionResult.Success
        If cellValue.IsEmpty Then
            result = EmptyCellValue
            Return converted
        End If
        If cellValue.IsError Then
            ' You can return an error, subsequently the exporter throws an exception if the CellValueConversionError event is unhandled.
            ' return SkipErrorValues ? ConversionResult.Success : ConversionResult.Error;
            result = "N/A"
            Return ConversionResult.Success
        End If
        result = String.Format("{0:MMMM-yyyy}", cellValue.DateTimeValue)
        Return converted
    End Function
End Class