How to: Use a Custom Converter for Export to a DataTable in the WPF Spreadsheet Control
- 5 minutes to read
You can apply a custom conversion to worksheet cell data when you export it to a DataTable. For information on how to export cell data to a DataTable, refer to the following help topic: How to: Export a Cell Range to a Data Table in the WPF Spreadsheet Control.
Use the DataTableExportOptions.CustomConverters dictionary to store custom converters. Each converter is an ICellValueToColumnTypeConverter interface descendant that implements the ICellValueToColumnTypeConverter.Convert method. The Convert method is called for each cell that is exported to the specified column.
The following code example transforms DateTime values into strings of the MMMM-yyyy format, and displays “N/A” if a cell contains an error:
<!-- The TopTradingPartners.xlsx test file's build action is Resource -->
<dxsps:SpreadsheetControl x:Name="spreadsheet"
DocumentSource="pack://application:,,,/Data/TopTradingPartners.xlsx"
DocumentLoaded="spreadsheet_DocumentLoaded">
</dxsps:SpreadsheetControl>
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using System.Data;
using System.Windows;
namespace WpfSpreadsheet {
public partial class MainWindow : Window {
public MainWindow() {
InitializeComponent();
}
private void spreadsheet_DocumentLoaded(object sender, EventArgs e) {
Worksheet worksheet = spreadsheet.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, rangeHasHeaders: true);
// 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;
exporter.Export();
// Do something with the resulting dataTable. For example, you can show it nearby the original table in the same worksheet.
worksheet.Import(dataTable, true, 1, 11);
}
}
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;
}
}
}
Export In-Cell Images
The following example implements a custom ICellValueToColumnTypeConverter that converts cell image data from the Image worksheet column to images in the PNG format:
<!-- The Book.xlsx test file's build action is Resource -->
<dxsps:SpreadsheetControl x:Name="spreadsheet"
DocumentSource="pack://application:,,,/Data/Book.xlsx"
DocumentLoaded="spreadsheet_DocumentLoaded">
</dxsps:SpreadsheetControl>
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using System.Data;
using System.IO;
using System.Windows;
namespace WpfSpreadsheet {
public partial class MainWindow : Window {
public MainWindow() {
InitializeComponent();
}
private void spreadsheet_DocumentLoaded(object sender, EventArgs e) {
Worksheet worksheet = spreadsheet.Document.Worksheets[0];
DataTable dataTable = worksheet.CreateDataTable(worksheet["A1:D5"], true);
dataTable.Columns["Image"].DataType = typeof(byte[]);
var exporter = worksheet.CreateDataTableExporter(worksheet["A1:D5"], dataTable, true);
MyConverter converter = new MyConverter();
converter.SkipErrorValues = true;
exporter.Options.CustomConverters.Add("Image", converter);
exporter.Export();
// Do something with the resulting DataTable.
// ...
}
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;
}
}
}
}