Tuesday, 26 July 2011 03:59

Import Data from Excel to Datatable

Excel import or export is always a hot issue for developers. When you go across technical forums, these forums are glutted with solutions, which make developers hard to find a really useful one. This section will introduce an effective way to easily and quickly import data from Excel to datatable via a .NET Excel component in C#, VB.NET.

By using Spire.XLS for .NET, you only need two lines of core code to finish the import or export data from excel to datatable. One is to import an Excel file by the Load method: Workbook.LoadFromFile(string fileName); the other is to get the datasource displayed in datagridview by calling the method: DataTable Xlsworksheet.ExportDataTable().

View the effective screenshot of Excel to datatable task as below firstly:

excel to datatable

Here comes to the steps:

  • Download Spire.XLS for .NET (or Spire.Office for .NET) and install it on your system.
  • Add Spire.XLS.dll as reference in the downloaded Bin folder thought the below path: "..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll".
  • Check the main codes as below:
[C#]
//Create a new workbook
Workbook workbook = new Workbook();       
//Load a file and imports its data
workbook.LoadFromFile(@”..\FandH.xlsx”);          
//Initialize worksheet
Worksheet sheet = workbook.Worksheets[0];
// get the data source that the grid is displaying data for
this.dataGridView1.DataSource = sheet.ExportDataTable();
[VB.NET]
Dim workbook As Workbook = New Workbook
workbook.LoadFromFile(..\ FandH.xlsx)
Dim sheet As Worksheet = workbook.Worksheets(0)
Me.dataGridView1.DataSource = sheet.ExportDataTable

Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. It supports C#, VB.NET, ASP.NET, ASP.NET MVC.

Published in Data Import/Export