Importing data from an Excel sheet

C# Example

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
  
namespace Excel2Table
{
    public class ExcelReader
    {
  
        /// <summary>
        /// Reads an excel file with OLEDB, and returns a datatable object from the content. The sheet to be imported
        /// needs to be named "Import" in the excel file.
        /// </summary>
        /// <param name="sFilehandle">Complete path to the excel file, including the filename and extension</param>
        /// <returns>A datatable containing the imported file</returns>
        public static DataTable Read(string sFilehandle)
        {
            string sConnection = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}{1}{0};Extended Properties=Excel 8.0", (char)34, sFilehandle);
            var oConnection = new OleDbConnection(sConnection);
            var oAdapter = new OleDbDataAdapter("SELECT * FROM [Import$]", oConnection);
            var oDataset = new DataSet();
            oAdapter.Fill(oDataset);
            return oDataset.Tables[0];
        }
  
    }
}

VB.NET Example

' --- Attach the excel sheet linked to in s_filename
'     the top row of the excel sheet contains the field names
  
Dim s_connectionstring As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & s_filename &  ";Extended Properties=Excel 8.0"
Dim myconnection As New OleDbConnection(s_connectionstring)
Dim myadapter As New OleDbDataAdapter("SELECT * FROM [Import$]", myconnection) ' Get all data from the sheet named import
Dim mydataset As DataSet = New DataSet()
myadapter.Fill(mydataset)
Dim myrow As DataRow
For Each myrow In mydataset.Tables(0).Rows 'Show results in output window
    ' --- Here you can reference the fields with myrow("fieldname")
    MsgBox(myrow("fieldname"))
    Application.DoEvents()
Next