CellValueToColumnTypeConverter.Convert(Cell, CellValue, Type, out Object) Method
Converts a worksheet cell value to a value that will be written in the DataTable.
Namespace: DevExpress.Spreadsheet.Export
Assembly: DevExpress.Spreadsheet.v24.1.Core.dll
NuGet Package: DevExpress.Spreadsheet.Core
Declaration
public virtual ConversionResult Convert(
Cell cell,
CellValue value,
Type destinationType,
out object result
)
Parameters
Name | Type | Description |
---|---|---|
cell | Cell | A Cell object that is the worksheet cell whose value is converted. |
value | CellValue | A CellValue object that is the value to be converted. |
destinationType | Type | A Type object that indicates the column data type to which a cell value is converted. |
result | Object | An object that is the result of conversion - a value that will be written in a DataTable. |
Returns
Type | Description |
---|---|
ConversionResult | A ConversionResult enumeration member that indicates whether the conversion is successful. |
Remarks
Supported destination data types (DataTable column data types) are listed below.
- Boolean
- Byte
- Char
- DateTime
- Decimal
- Double
- Guid
- Int16
- Int32
- Int64
- SByte
- Single
- String
- TimeSpan
- UInt16
- UInt32
- UInt64
- Byte[]
If the conversion is successful, the Convert method returns the ConversionResult.Success value.
Note
If an attempt to convert a value failed, the Convert method returns the ConversionResult.Error. Then, the DataTableExporter.CellValueConversionError event is raised. If the CellValueConversionError event is not handled, an exception is thrown.
using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
private void barButtonItemUseCustomConverter_ItemClick(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
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;
}