Export data is equally important with import data for excel users. Export data from Exel worksheet directly to datatable escapes many troubles and saves much time. This post will introduce you a method to export data from excel worksheet to datatable for WPF with C#, VB.NET.

Spire.XLS for WPF enables you to quickly export your data from Excel worksheet to datatable by following the below three steps. Spire.XLS for WPF supports to operate Excel 97-2003, Excel 2007 and Excel 2010. However, .NET Framework and Visual Studio must be installed for using Spire.XLS for WPF.

Make sure Spire.XLS and Visual Studio are correctly installed. And then follow steps.

Step 1: Create a new project

Create a new project by choosing WPF Application in Visual Studio.

Add a button and dataGrid in MainWindow. The default button name is "Button1". You can set Button1 Content property to be "Run" in its properties by right clicking it.

Step 2: Add reference and project namespaces

Add Spire.XLS. Wpf.dll as reference in Project. The Default location of Spire.Doc for WPF is "C:\Program Files\e-iceblue\Spire.XLS for WPF".

Double click the "Run" button, you can see the following method has been added automatically:

namespace dataexport

{
    /// 
    /// Interaction logic for MainWindow.xaml
    /// 
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, RoutedEventArgs e)
        {
     }
    }
}

Namespace dataexport

	''' 
	''' Interaction logic for MainWindow.xaml
	''' 
	Public Partial Class MainWindow
		Inherits Window
		Public Sub New()
			InitializeComponent()
		End Sub

		Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
             End Sub
	 End Class
End Namespace

Add below namespaces at the top of the method

using Spire.Xls;
using System.Data;

Imports Spire.Xls
Imports System.Data

Step 3: Export data from excel worksheet to datatable

Create a new Excel workbook and load an excel file from system

Workbook workbook = new Workbook();            
workbook.LoadFromFile(@"D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003);
Worksheet sheet = workbook.Worksheets[0];

Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\e-iceblue\Spire.XLS\Demos\Data\dataexport.xls", ExcelVersion.Version97to2003)
Dim sheet As Worksheet = workbook.Worksheets(0)

Export data from excel worksheet to datatable:

DataTable dataTable = sheet.ExportDataTable();
DataView view = new DataView(dataTable);
this.dataGrid1.ItemsSource = view;
this.dataGrid1.AutoGenerateColumns = true;

Dim dataTable As DataTable = sheet.ExportDataTable()
Dim view As New DataView(dataTable)
Me.dataGrid1.ItemsSource = view
Me.dataGrid1.AutoGenerateColumns = True

Press F5 and click "Run" in MainWindow, you can see the datatable as below picture.

Effective Screeshot: