It's your lucky day!
I was just looking at teh page in my code where I do just that...
public static System.Data.DataTable LoadExcel(string path, string columns = "*", string sheet = "Sheet1$", bool verify = true)
{
if (verify)
{
if (!File.Exists(path)) throw new IOException("The input file does not exist: " + path);
if (!GetExcelSheetNames(path).Contains(sheet)) throw new ArgumentException("The requested sheet does not exist: " + sheet);
}
if (string.IsNullOrWhiteSpace(columns)) columns = "*";
System.Data.DataTable dt = new System.Data.DataTable();
using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
{
con.Open();
string cmdStr = string.Format("SELECT {0} FROM [{1}]", columns, sheet);
using (OleDbCommand cmd = new OleDbCommand(cmdStr, con))
{
using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
{
da.Fill(dt);
}
}
}
return dt;
}
public static List<string> GetExcelSheetNames(string path)
{
List<string> sheetNames = new List<string>();
using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
{
con.Open();
using (DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
{
foreach (DataRow sheet in sheets.Rows)
{
if (sheet["TABLE_NAME"].ToString().Contains("$"))
{
sheetNames.Add(sheet["TABLE_NAME"].ToString());
}
}
}
}
return sheetNames;
}
private static string GetExcelConnectionString(string path)
{
return string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0", path);
}