How to: Export a Cell Range to a Data Table in the WPF Spreadsheet Control

• Apr 03, 2025
• 3 minutes to read

You can export data from a worksheet cell range to a DataTable. Spreadsheet API allows you to specify conversion methods and take control of the conversion process for every cell (analyze data and modify its value as required).

Follow the steps below to export cell values to a data table:

• Reference the DevExpress.Docs.v25.1.dll assembly or add the DevExpress.Document.Processor NuGet package. Note that the use of this library in production code requires a license to the DevExpress Office File API or the DevExpress Universal Subscription. Refer to the following page for pricing information: DevExpress Subscription.

• Call the WorksheetExtensions.CreateDataTable method to create an empty DataTable in which data from a specified worksheet range can fit. 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.

• Call the WorksheetExtensions.CreateDataTableExporter method to create a DataTableExporter instance.

• Call the DataTableExporterExtensions.Export method of the DataTableExporter.

The following example exports worksheet content to a DataTable:

XAML

<!-- The Book1.xlsx test file's build action is Resource -->
<dxsps:SpreadsheetControl x:Name="spreadsheet"
                          DocumentSource="pack://application:,,,/Data/Book1.xlsx"
                          DocumentLoaded="spreadsheet_DocumentLoaded">
</dxsps:SpreadsheetControl>

C#

using DevExpress.Spreadsheet;
using DevExpress.Spreadsheet.Export;
using DevExpress.Xpf.Core;
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.GetUsedRange();
            DataTable dataTable = worksheet.CreateDataTable(range, rangeHasHeaders: true);
            for (int col = 0; col < range.ColumnCount; col++) {
                CellValueType cellType = range[0, col].Value.Type;
                for (int r = 1; r < range.RowCount; r++) {
                    if (cellType != range[r, col].Value.Type) {
                        dataTable.Columns[col].DataType = typeof(string);
                        break;
                    }
                }
            }
            DataTableExporter exporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders: true);
            exporter.CellValueConversionError += exporter_CellValueConversionError;
            exporter.Export();
            // Do something with the resulting dataTable.
            //...
        }
        void exporter_CellValueConversionError(object sender, CellValueConversionErrorEventArgs e) {
            DXMessageBox.Show("Error in cell " + e.Cell.GetReferenceA1());
            e.DataTableValue = null;
            e.Action = DataTableExporterAction.Continue;
        }
    }
}

VB.NET

Imports DevExpress.Spreadsheet
Imports DevExpress.Spreadsheet.Export
Imports DevExpress.Xpf.Core
Imports System.Data
Imports System.Windows

Namespace WpfSpreadsheet
    Public Partial Class MainWindow
        Inherits Window

        Public Sub New()
            InitializeComponent()
        End Sub

        Private Sub spreadsheet_DocumentLoaded(ByVal sender As Object, ByVal e As EventArgs)
            Dim worksheet As Worksheet = spreadsheet.Document.Worksheets(0)
            Dim range As CellRange = worksheet.GetUsedRange()
            Dim dataTable As DataTable = worksheet.CreateDataTable(range, rangeHasHeaders:=True)

            For col As Integer = 0 To range.ColumnCount - 1
                Dim cellType As CellValueType = range(0, col).Value.Type

                For r As Integer = 1 To range.RowCount - 1

                    If cellType <> range(r, col).Value.Type Then
                        dataTable.Columns(col).DataType = GetType(String)
                        Exit For
                    End If
                Next
            Next

            Dim exporter As DataTableExporter = worksheet.CreateDataTableExporter(range, dataTable, rangeHasHeaders:=True)
            exporter.CellValueConversionError += AddressOf exporter_CellValueConversionError
            exporter.Export()
        End Sub

        Private Sub exporter_CellValueConversionError(ByVal sender As Object, ByVal e As CellValueConversionErrorEventArgs)
            DXMessageBox.Show("Error in cell " & e.Cell.GetReferenceA1())
            e.DataTableValue = Nothing
            e.Action = DataTableExporterAction.[Continue]
        End Sub
    End Class
End Namespace

Next Step

• How to: Use a Custom Converter for Export to a DataTable in the WPF Spreadsheet Control