Export Data from Excel to Datatable in WPF

In this section, I will introduce a solution to export Excel to datatable through a datagridview via this WPF Excel component Spire.XLS for WPF. Using it, we can quickly export any Excel sheet to datatable. First let us see below picture:

When we export Excel to datatable, first we need to initialize a new instance of the class Spire.Xls.Workbook, here it is named “workbook”, then, after loading our Excel file from system by this method: Spire.Xls.Workbook.LoadFromFile(string fileName, ExcelVersion version); we need to decide which sheet will be exported to datatable. When using Spire.XLS for WPF, we do not need to install MS Excel, so suppose we do not know how many sheets in our Excel file, we can use this class System.Random provided by Microsoft to get the page count and export any sheet to datatable by calling the method: Spire.Xls.Worksheet.ExportDataTable(). After export data to datatable, with the help of the two properties of the class System.Data.Dataview:System.Data.Dataview.AutoGenerateColumns and System.Data.Dataview.ItemsSource. The datatable columns will be automatically created and all the data in datatable will be shown in datagrid.

Here we can download Spire.XLS for WPF and install it on system. After adding Spire.Xls dll on system, we can start our task of excel to datatable by below code:

using System.Data;
using Spire.Xls;

namespace wpfexceltodatatable
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, RoutedEventArgs e)
        {
          //load an excel file 
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"..\excel.xls", ExcelVersion.Version97to2003);
            //get the count of excel worksheet
            int sheetCount = workbook.Worksheets.Count;
            Random r = new Random();
            int index = r.Next(0, sheetCount);
           //show datatable in datagrid
            Worksheet sheet = workbook.Worksheets[index];
            DataTable dataTable = sheet.ExportDataTable();
            DataView view = new DataView(dataTable);
            this.dataGrid1.ItemsSource = view;
            this.dataGrid1.AutoGenerateColumns = true;
        }
    }
}
          

Imports System.Data
Imports Spire.Xls

Namespace wpfexceltodatatable
	Public Partial Class MainWindow
		Inherits Window
		Public Sub New()
			InitializeComponent()
		End Sub
		Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
			'load an excel file 
			Dim workbook As New Workbook()
			workbook.LoadFromFile("..\excel.xls", ExcelVersion.Version97to2003)
			'get the count of excel worksheet
			Dim sheetCount As Integer = workbook.Worksheets.Count
			Dim r As New Random()
			Dim index As Integer = r.[Next](0, sheetCount)
			'show datatable in datagrid
			Dim sheet As Worksheet = workbook.Worksheets(index)
			Dim dataTable As DataTable = sheet.ExportDataTable()
			Dim view As New DataView(dataTable)
			Me.dataGrid1.ItemsSource = view
			Me.dataGrid1.AutoGenerateColumns = True
		End Sub
	End Class
End Namespace