How to: Export a Worksheet Range to a DataTable

• Apr 24, 2019
• 4 minutes to read

You can export data from a worksheet cell range to a DataTable. In this case, worksheet columns are transformed into DataTable columns. Cell values are used to populate the DataTable, and you can specify conversion methods and control the conversion process for every cell (analyze data and modify values as required).

To export cell values to a data table, perform the following steps.

1. Create an empty DataTable that will be able to hold data of the worksheet range.

   An empty DataTable, which will fit data contained in the specified worksheet range, can be created by using the CreateDataTable method of the Worksheet (WorksheetExtensions.CreateDataTable). The newly created DataTable contains the same number of columns as the worksheet range. Column data types are set automatically by analyzing the content of the first row in a range that contains data. Column names can be obtained from the first row of a range if the rangeHasHeaders method parameter is set to true.

   Important

   The WorksheetExtensions class is defined in the DevExpress.Docs.v19.1.dll assembly. Add this assembly to your project to use the worksheet extensions. You require a license to the DevExpress Office File API or DevExpress Universal Subscription to use this assembly in production code. Refer to the DevExpress Subscription page for pricing information.

2. Create a DataTableExporter instance using the WorksheetExtensions.CreateDataTableExporter method.

3. Impement a custom converter for a specific DataTable column if required.

   To accomplish this, create a class that implements the ICellValueToColumnTypeConverter interface. The ICellValueToColumnTypeConverter.Convert method should perform the required conversion. The Convert method is called for each cell that is exported to the specified column. The converter transforms DateTime values into strings in MMMM-yyyy format and displays the “N/A” text if a cell contains an error.

   Note

   A complete sample project is available at https://github.com/DevExpress-Examples/how-to-export-worksheet-range-to-a-datatable-without-spreadsheetcontrol-t482311

   MyConverter.vb

   Friend Class MyConverter
       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
   

   MyConverter.cs

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

4. Specify export options.

   Create a new instance of the DataTableExportOptions class and specify the required options. Add an instance of the previously implemented custom converter to the collection of custom converters available through the DataTableExportOptions.CustomConverters property.

5. Call the DataTableExporterExtensions.Export method.

Note

A complete sample project is available at https://github.com/DevExpress-Examples/how-to-export-worksheet-range-to-a-datatable-without-spreadsheetcontrol-t482311

Form1.vb

    Dim wbook As New Workbook()
    wbook.LoadDocument("TopTradingPartners.xlsx")
    Dim worksheet As Worksheet = wbook.Worksheets(0)
    Dim range As Range = 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

Form1.cs

    Workbook wbook = new Workbook();
    wbook.LoadDocument("TopTradingPartners.xlsx");
    Worksheet worksheet = wbook.Worksheets[0];
    Range 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;
}