Skip to main content
All docs
V24.2

DevExpress v24.2 Update — Your Feedback Matters

Our What's New in v24.2 webpage includes product-specific surveys. Your response to our survey questions will help us measure product satisfaction for features released in this major update and help us refine our plans for our next major release.

Take the survey Not interested

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

  • 4 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

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;
    }
}

#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:

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

Workbook workbook = new Workbook();
workbook.LoadDocument("TopTradingPartners.xlsx");
Worksheet worksheet = workbook.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;
    }
}