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

• Feb 03, 2025
• 5 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 should perform the required conversion. Subsequently, add an instance of a custom converter to the collection of custom converters available via the DataTableExporter.Options.CustomConverters notation using the Add method.

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

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);
// Change the data type of the "As Of" column to text.
dataTable.Columns["As Of"].DataType = System.Type.GetType("System.String");
// Create the exporter that obtains data from the specified range and populates the specified data table. 
DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, true);
// Handle value conversion errors.
exporter.CellValueConversionError += exporter_CellValueConversionError;

// Specify a custom converter for the "As Of" column.
DateTimeToStringConverter toDateStringConverter = new DateTimeToStringConverter();
exporter.Options.CustomConverters.Add("As Of", toDateStringConverter);
// Set the export value for empty cell.
toDateStringConverter.EmptyCellValue = "N/A";
// Specify that empty cells and cells with errors should be processed.
exporter.Options.ConvertEmptyCells = true;
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = false;

// Perform the export.
exporter.Export();

// A custom method that displays the resulting data table.
ShowResult(dataTable);


// A custom converter that converts DateTime values to "Month-Year" text strings.
class DateTimeToStringConverter : 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;
    }
}

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)
' Change the data type of the "As Of" column to text.
dataTable.Columns("As Of").DataType = System.Type.GetType("System.String")
' Create the exporter that obtains data from the specified range and populates the specified data table. 
Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, True)
' Handle value conversion errors.
AddHandler exporter.CellValueConversionError, AddressOf exporter_CellValueConversionError

' Specify a custom converter for the "As Of" column.
Dim toDateStringConverter As New DateTimeToStringConverter()
exporter.Options.CustomConverters.Add("As Of", toDateStringConverter)
' Set the export value for empty cell.
toDateStringConverter.EmptyCellValue = "N/A"
' Specify that empty cells and cells with errors should be processed.
exporter.Options.ConvertEmptyCells = True
exporter.Options.DefaultCellValueToColumnTypeConverter.SkipErrorValues = False

' Perform the export.
exporter.Export()

' A custom method that displays the resulting data table.
ShowResult(dataTable)


' A custom converter that converts DateTime values to "Month-Year" text strings.
Private Class DateTimeToStringConverter
    Implements ICellValueToColumnTypeConverter

    Public Property SkipErrorValues() As Boolean
    Public Property EmptyCellValue() As CellValue Implements ICellValueToColumnTypeConverter.EmptyCellValue

    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 Implements ICellValueToColumnTypeConverter.Convert
        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

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

Export In-Cell Images

You can also use ICellValueToColumnTypeConverter to export image values to a DataTable.

The following code snippet shows the ICellValueToColumnTypeConverter implementation that converts cell image data to images in PNG format:

MyConverter.cs

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;

Worksheet worksheet = spreadsheetControl1.Document.Worksheets[0];
DataTable dataTable = worksheet.CreateDataTable(worksheet["A1:C4"], true);
dataTable.Columns["Image"].DataType = typeof(byte[]);
var exporter = worksheet.CreateDataTableExporter(worksheet["A1:C4"], dataTable, true);

MyConverter converter = new MyConverter();
converter.SkipErrorValues = true;
exporter.Options.CustomConverters.Add("Image", converter);
exporter.Export();

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 = null;
        ConversionResult converted = ConversionResult.Success;
        if (readOnlyCell.Value.IsCellImage)
            result = readOnlyCell.Value.ImageValue.GetImageBytes(DevExpress.Office.Utils.OfficeImageFormat.Png);
        return converted;
    }
}

MyConverter.vb

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export

Dim worksheet As Worksheet = spreadsheetControl1.Document.Worksheets(0)
Dim dataTable As DataTable = worksheet.CreateDataTable(worksheet("A1:C4"), True)
dataTable.Columns("Image").DataType = GetType(Byte())
Dim exporter = worksheet.CreateDataTableExporter(worksheet("A1:C4"), dataTable, True)

Dim converter As New MyConverter()
converter.SkipErrorValues = True
exporter.Options.CustomConverters.Add("Image", converter)
exporter.Export()

Class MyConverter
    Inherits 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, <Out> ByRef result As Object) As ConversionResult
        result = Nothing
        Dim converted As ConversionResult = ConversionResult.Success
        If readOnlyCell.Value.IsCellImage Then result = readOnlyCell.Value.ImageValue.GetImageBytes(DevExpress.Office.Utils.OfficeImageFormat.Png)
        Return converted
    End Function
End Class