[Expand]General Information
[Expand]WinForms Controls
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]ASP.NET Core Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Collapse]Office File API
  Demo Application
  Redistribution and Deployment
 [Collapse]Spreadsheet Document API
   Product Structure
   Getting Started
  [Expand]Spreadsheet Document
   Supported Formats
  [Expand]Cell Basics
  [Expand]Formulas
   Defined Names
   Data Binding
  [Expand]Pivot Tables
  [Expand]Charts and Graphics
  [Expand]Mail Merge
  [Collapse]Examples
   [Expand]Files
   [Expand]Workbooks
   [Expand]Worksheets
   [Expand]Rows and Columns
   [Expand]Cells
   [Expand]Formulas
   [Collapse]Import and Export Data
     How to: Import Data to a Worksheet
     How to: Export a Worksheet Range to a DataTable
   [Expand]Data Binding
    Sorting
   [Expand]Mail Merge
    Search
   [Expand]Shapes
   [Expand]Charts
   [Expand]Sparklines
   [Expand]Formatting Cells
   [Expand]Conditional Formatting
   [Expand]Group Data
   [Expand]Filter Data
   [Expand]Tables
   [Expand]Pivot Tables
   [Expand]Printing
   [Expand]Pictures
   [Expand]Protection
 [Expand]Word Processing Document API
 [Expand]PDF Document API
 [Expand]Excel Export Library
 [Expand]Snap Report API
 [Expand]Zip Compression and Archive API
 [Expand]Barcode Generation API
 [Expand]Unit Conversion API
 [Expand]API Reference
[Expand]Reporting
[Expand]Report and Dashboard Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]eXpress Persistent Objects
[Expand]CodeRush
[Expand]CodeRush Classic
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation
View this topic on docs.devexpress.com (Learn more)

How to: Export a Worksheet Range to a DataTable

You can export data from a worksheet cell range to a System.Data.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.

  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.

Is this topic helpful?​​​​​​​