How to: Export a Worksheet Range to a DataTable

• Sep 19, 2023
• 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 DevExpress.Docs.v24.2.dll. Add this assembly to your project to use worksheet extensions. You need a license for the DevExpress Office File API Subscription or DevExpress Universal Subscription to use this assembly in production code.

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

3. Implement 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.

   View Example

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

   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
   

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.

   View Example

   Form1.cs

   using DevExpress.Spreadsheet;
   using DevExpress.Spreadsheet.Export;
   using System.Data;
   using System.Windows.Forms;
   using DevExpress.XtraBars;
   
   private void barBtnExport_ItemClick(object sender, ItemClickEventArgs e)
   {
       if (ds != null) return;
       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;
   }
   

   Form1.vb

   Imports DevExpress.Spreadsheet
   Imports DevExpress.Spreadsheet.Export
   Imports System.Data
   Imports System.Windows.Forms
   Imports DevExpress.XtraBars
   
   Private Sub barBtnExport_ItemClick(ByVal sender As Object, ByVal e As ItemClickEventArgs) Handles barBtnExport.ItemClick
       Dim wbook As New Workbook()
       wbook.LoadDocument("TopTradingPartners.xlsx")
       Dim worksheet As Worksheet = wbook.Worksheets(0)
       Dim range As CellRange = 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()
       '...
   End Sub
   
   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