I don't have a machine available to test this but it should work. First you will probably need to install the either the 2007 Office System Driver: Data Connectivity Components or the Microsoft Access Database Engine 2010 Redistributable. Then try the following code, note you will need to change the name of the Sheet in the Select statement below to match sheetname in your excel file:
using System.Data;
using System.Data.OleDb;
namespace Data_Migration_Process_Creator
{
class Class1
{
private DataTable GetDataTable(string sql, string connectionString)
{
DataTable dt = null;
using (OleDbConnection conn = new OleDbConnection(connectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand(sql, conn))
{
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
return dt;
}
}
}
}
private void GetExcel()
{
string fullPathToExcel = "<Path to Excel file>"; //ie C:\Temp\YourExcel.xls
string connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=yes'", fullPathToExcel);
DataTable dt = GetDataTable("SELECT * from [SheetName$]", connString);
foreach (DataRow dr in dt.Rows)
{
//Do what you need to do with your data here
}
}
}
}
Note: I don't have an environment to test this in (One with Office installed) so I can't say if it will work in your environment or not but I don't see why it shouldn't work.